-- Alembic Revision: None

-- In order to rollback, we need to delete any rows with `key`s greater than 2^31, so that we can reset
-- the `key` column to a 32 bit integer.

-- no fks

lock tables `daily_cache_logs` write;

delete from `daily_cache_logs` where `id` > 2147483647;
alter table `daily_cache_logs` modify `id` int(11) not null;
alter table `daily_cache_logs` modify `id` int(11) not null auto_increment;

unlock tables;


lock tables `scheduled_tasks` write;

delete from `scheduled_tasks` where `id` > 2147483647;
alter table `scheduled_tasks` modify `id` int(11) not null;
alter table `scheduled_tasks` modify `id` int(11) not null auto_increment;

unlock tables;

lock tables `ticket_close_candidates` write;

delete from `ticket_close_candidates` where `id` > 2147483647;
alter table `ticket_close_candidates` modify `id` int(11) not null;
alter table `ticket_close_candidates` modify `id` int(11) not null auto_increment;

unlock tables;

lock tables `price_levels` write;

delete from `price_levels` where `key` > 2147483647;
alter table `price_levels` modify `key` int(11) not null;
alter table `price_levels` modify `key` int(11) not null auto_increment;

unlock tables;

lock tables `option_sets` write;

delete from `option_sets` where `key` > 2147483647;
alter table `option_sets` modify `key` int(11) not null;
alter table `option_sets` modify `key` int(11) not null auto_increment;

unlock tables;

-- pay_rates

lock tables
    `pay_rate_levels` write,
    `pay_rates` write;

delete from `pay_rate_levels` where `key` > 2147483647;
delete from `pay_rates` where `key` > 2147483647;

     alter table `pay_rate_levels`
drop foreign key `fk_pay_rate_levels_pay_rates`,
          modify `key` int(11) not null,
          modify `pay_rate_key` int(11) not null;
alter table `pay_rate_levels` modify `key` int(11) not null auto_increment;

alter table `pay_rates` modify `key` int(11) not null;
alter table `pay_rates` modify `key` int(11) not null auto_increment;

   alter table `pay_rate_levels`
add constraint `fk_pay_rate_levels_pay_rates` foreign key (`pay_rate_key`, `_cache_id`)
    references `pay_rates` (`key`, `_cache_id`) on delete cascade on update cascade;

unlock tables;


-- ticket_items

lock tables
    `ticket_item_discounts` write,
    `ticket_item_modifier_closures` write,
    `ticket_items` write,
    `ticket_item_modifiers` write;

delete from `ticket_items` where `key` > 2147483647;
delete from `ticket_item_modifiers` where `key` > 2147483647;

     alter table `ticket_item_discounts`
drop foreign key `fk_ticket_item_discounts_ticket_items`,
          modify `ticket_item_key` int(11) not null;

     alter table `ticket_item_modifier_closures`
drop foreign key `fk_ticket_item_modifier_closures_item_modifiers`,
drop foreign key `fk_ticket_item_modifier_closures_item_modifier_targets`,
drop foreign key `fk_ticket_item_modifier_closures_item_targets`,
          modify `modifier_key` int(11) not null,
          modify `target_modifier_key` int(11) default null,
          modify `target_item_key` int(11) default null;

alter table `ticket_items` modify `key` int(11) not null;
alter table `ticket_items` modify `key` int(11) not null auto_increment;

alter table `ticket_item_modifiers` modify `key` int(11) not null;
alter table `ticket_item_modifiers` modify `key` int(11) not null auto_increment;

   alter table `ticket_item_discounts`
add constraint `fk_ticket_item_discounts_ticket_items` foreign key (`ticket_item_key`, `_cache_id`) references `ticket_items` (`key`, `_cache_id`) on delete cascade on update cascade;

   alter table `ticket_item_modifier_closures`
add constraint `fk_ticket_item_modifier_closures_item_modifier_targets` foreign key (`target_modifier_key`, `_cache_id`) references `ticket_item_modifiers` (`key`, `_cache_id`) on delete cascade on update cascade,
add constraint `fk_ticket_item_modifier_closures_item_modifiers` foreign key (`modifier_key`, `_cache_id`) references `ticket_item_modifiers` (`key`, `_cache_id`) on delete cascade on update cascade,
add constraint `fk_ticket_item_modifier_closures_item_targets` foreign key (`target_item_key`, `_cache_id`) references `ticket_items` (`key`, `_cache_id`) on delete cascade on update cascade;

unlock tables;


-- held_ticket_items

lock tables
    `held_ticket_item_discounts` write,
    `held_ticket_item_modifier_closures` write,
    `held_ticket_items` write,
    `held_ticket_item_modifiers` write;

delete from `held_ticket_items` where `key` > 2147483647;
delete from `held_ticket_item_modifiers` where `key` > 2147483647;

     alter table `held_ticket_item_discounts`
drop foreign key `fk_held_ticket_item_discounts_ticket_items`,
          modify `ticket_item_key` int(11) not null;

     alter table `held_ticket_item_modifier_closures`
drop foreign key `fk_held_ticket_item_modifier_closures_item_modifiers`,
drop foreign key `fk_held_ticket_item_modifier_closures_item_modifier_targets`,
drop foreign key `fk_held_ticket_item_modifier_closures_item_targets`,
          modify `modifier_key` int(11) not null,
          modify `target_modifier_key` int(11) default null,
          modify `target_item_key` int(11) default null;

alter table `held_ticket_items` modify `key` int(11) not null;
alter table `held_ticket_items` modify `key` int(11) not null auto_increment;

alter table `held_ticket_item_modifiers` modify `key` int(11) not null;
alter table `held_ticket_item_modifiers` modify `key` int(11) not null auto_increment;

   alter table `held_ticket_item_discounts`
add constraint `fk_held_ticket_item_discounts_ticket_items` foreign key (`ticket_item_key`, `_cache_id`) references `held_ticket_items` (`key`, `_cache_id`) on delete cascade on update cascade;

   alter table `held_ticket_item_modifier_closures`
add constraint `fk_held_ticket_item_modifier_closures_item_modifiers` foreign key (`target_modifier_key`, `_cache_id`) references `held_ticket_item_modifiers` (`key`, `_cache_id`) on delete cascade on update cascade,
add constraint `fk_held_ticket_item_modifier_closures_item_modifier_targets` foreign key (`modifier_key`, `_cache_id`) references `held_ticket_item_modifiers` (`key`, `_cache_id`) on delete cascade on update cascade,
add constraint `fk_held_ticket_item_modifier_closures_item_targets` foreign key (`target_item_key`, `_cache_id`) references `held_ticket_items` (`key`, `_cache_id`) on delete cascade on update cascade;

unlock tables;

