-- Alembic Revision: 2505d6f40bee

CREATE TABLE `menu_combos` (
  `_cache_id` varchar(128) COLLATE utf8_bin NOT NULL,
  `id` varchar(64) COLLATE utf8_bin NOT NULL,
  `pos_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `price_per_unit` int(11) DEFAULT NULL,
  `_stub` tinyint(1) DEFAULT NULL,
  `_registered_property_values` blob,
  PRIMARY KEY (`_cache_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `menu_combo_item_groups` (
  `_cache_id` varchar(128) COLLATE utf8_bin NOT NULL,
  `id` varchar(64) COLLATE utf8_bin NOT NULL,
  `menu_combo_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `pos_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `_stub` tinyint(1) DEFAULT NULL,
  `_registered_property_values` blob,
  PRIMARY KEY (`_cache_id`,`id`),
  KEY `idx_menu_combo_item_group_menu_combo_id` (`_cache_id`,`menu_combo_id`),
  CONSTRAINT `fk_menu_combo_item_groups_menu_combos` FOREIGN KEY (`_cache_id`, `menu_combo_id`) REFERENCES `menu_combos` (`_cache_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `menu_combo_items` (
  `_cache_id` varchar(128) COLLATE utf8_bin NOT NULL,
  `id` varchar(64) COLLATE utf8_bin NOT NULL,
  `menu_combo_item_group_id` varchar(64) COLLATE utf8_bin NOT NULL,
  `menu_item_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `pos_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `default` tinyint(1) DEFAULT NULL,
  `price_per_unit` int(11) DEFAULT NULL,
  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `_stub` tinyint(1) DEFAULT NULL,
  `_registered_property_values` blob,
  PRIMARY KEY (`_cache_id`,`id`),
  KEY `idx_menu_combo_item_menu_item_id` (`_cache_id`,`menu_item_id`),
  KEY `idx_menu_combo_item_menu_combo_item_group_id` (`_cache_id`,`menu_combo_item_group_id`),
  CONSTRAINT `fk_menu_combo_items_menu_combo_item_groups` FOREIGN KEY (`_cache_id`, `menu_combo_item_group_id`) REFERENCES `menu_combo_item_groups` (`_cache_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_menu_combo_items_menu_items` FOREIGN KEY (`_cache_id`, `menu_item_id`) REFERENCES `menu_items` (`_cache_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `held_ticket_combos` (
  `_cache_id` varchar(128) COLLATE utf8_bin NOT NULL,
  `key` bigint(20) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `split` int(11) DEFAULT NULL,
  `price` int(11) NOT NULL,
  `comment` text COLLATE utf8_bin,
  `sent` tinyint(1) DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `void` tinyint(1) NOT NULL,
  `void_type_id` int(11) DEFAULT NULL,
  `approving_employee_id` int(11) DEFAULT NULL,
  `menu_combo_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `price_per_unit` int(11) DEFAULT NULL,
  `price_level_id` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`key`,`_cache_id`),
  UNIQUE KEY `idx_held_ticket_combo_id_ticket_id` (`_cache_id`,`ticket_id`,`id`),
  CONSTRAINT `fk_held_ticket_combos_held_tickets` FOREIGN KEY (`_cache_id`, `ticket_id`) REFERENCES `held_tickets` (`_cache_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `held_ticket_combo_items` (
  `_cache_id` varchar(128) COLLATE utf8_bin NOT NULL,
  `key` bigint(20) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ticket_combo_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `split` int(11) DEFAULT NULL,
  `price` int(11) NOT NULL,
  `comment` text COLLATE utf8_bin,
  `sent` tinyint(1) DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `menu_combo_item_group_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `menu_combo_item_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `menu_item_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`key`,`_cache_id`),
  UNIQUE KEY `idx_held_ticket_combo_items_ticket_id_id` (`_cache_id`,`ticket_id`,`id`),
  KEY `fk_held_ticket_combo_items_held_ticket_combos` (`_cache_id`,`ticket_id`,`ticket_combo_id`),
  CONSTRAINT `fk_held_ticket_combo_items_held_ticket_combos` FOREIGN KEY (`_cache_id`, `ticket_id`, `ticket_combo_id`) REFERENCES `held_ticket_combos` (`_cache_id`, `ticket_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_held_ticket_combo_items_held_tickets` FOREIGN KEY (`_cache_id`, `ticket_id`) REFERENCES `held_tickets` (`_cache_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `ticket_combos` (
  `_cache_id` varchar(128) COLLATE utf8_bin NOT NULL,
  `key` bigint(20) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `split` int(11) DEFAULT NULL,
  `price` int(11) NOT NULL,
  `comment` text COLLATE utf8_bin,
  `sent` tinyint(1) DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `void` tinyint(1) NOT NULL,
  `void_type_id` int(11) DEFAULT NULL,
  `approving_employee_id` int(11) DEFAULT NULL,
  `menu_combo_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`key`,`_cache_id`),
  UNIQUE KEY `idx_ticket_combos_ticket_id_id` (`_cache_id`,`ticket_id`,`id`),
  CONSTRAINT `fk_ticket_combos_tickets` FOREIGN KEY (`_cache_id`, `ticket_id`) REFERENCES `tickets` (`_cache_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `ticket_combo_items` (
  `_cache_id` varchar(128) COLLATE utf8_bin NOT NULL,
  `key` bigint(20) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ticket_combo_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `split` int(11) DEFAULT NULL,
  `price` int(11) NOT NULL,
  `comment` text COLLATE utf8_bin,
  `sent` tinyint(1) DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `menu_combo_item_group_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `menu_combo_item_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `menu_item_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`key`,`_cache_id`),
  UNIQUE KEY `idx_ticket_combo_items_ticket_id_id` (`_cache_id`,`ticket_id`,`id`),
  KEY `fk_ticket_combo_items_ticket_combos` (`_cache_id`,`ticket_id`,`ticket_combo_id`),
  CONSTRAINT `fk_ticket_combo_items_ticket_combos` FOREIGN KEY (`_cache_id`, `ticket_id`, `ticket_combo_id`) REFERENCES `ticket_combos` (`_cache_id`, `ticket_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ticket_combo_items_tickets` FOREIGN KEY (`_cache_id`, `ticket_id`) REFERENCES `tickets` (`_cache_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE held_ticket_item_modifier_closures
  ADD COLUMN target_combo_item_key BIGINT(20) DEFAULT NULL,
  ADD KEY `fk_held_ticket_item_modifier_closures_combo_item_targets` (`target_combo_item_key`,`_cache_id`),
  ADD KEY `idx_held_target_combo_item_key` (`_cache_id`,`target_combo_item_key`),
  ADD CONSTRAINT `fk_held_ticket_item_modifier_closures_combo_item_targets` FOREIGN KEY (`target_combo_item_key`, `_cache_id`)
    REFERENCES `held_ticket_combo_items` (`key`, `_cache_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE held_ticket_totals
  ADD COLUMN combos INT(11) NOT NULL AFTER due;

ALTER TABLE ticket_item_modifier_closures
  ADD COLUMN target_combo_item_key BIGINT(20) DEFAULT NULL,
  ADD KEY `fk_ticket_item_modifier_closures_combo_item_targets` (`target_combo_item_key`,`_cache_id`),
  ADD KEY `idx_target_combo_item_key` (`_cache_id`,`target_combo_item_key`),
  ADD CONSTRAINT `fk_ticket_item_modifier_closures_combo_item_targets` FOREIGN KEY (`target_combo_item_key`, `_cache_id`)
    REFERENCES `ticket_combo_items` (`key`, `_cache_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE ticket_totals
  ADD COLUMN combos INT(11) NOT NULL AFTER due;