-- Step 28: Add requestable items feature
-- Architecture:
--   requestableItems         — global master list of all possible items/services
--   vendorRequestableItems   — per-vendor selection (which items a vendor offers)
--   sessionCustomerRequestItems — which items were selected by a customer in a request

-- 1. Global master list (no vendor FK — shared across all vendors)
CREATE TABLE `requestableItems` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` enum('binary','quantity') NOT NULL DEFAULT 'quantity',
  `icon` varchar(50) DEFAULT NULL COMMENT 'Font Awesome icon name, e.g. fork (renders as fal fa-fork)',
  `sortOrder` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 2. Per-vendor selection (junction: which items the vendor has chosen to offer)
CREATE TABLE `vendorRequestableItems` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) NOT NULL,
  `requestableItemId` int(10) UNSIGNED NOT NULL,
  `sortOrder` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vendor_item` (`vendorId`, `requestableItemId`),
  KEY `idx_vendorrequestableitems_vendor` (`vendorId`),
  CONSTRAINT `fk_vendorRequestableItems_vendorId` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vendorRequestableItems_itemId` FOREIGN KEY (`requestableItemId`) REFERENCES `requestableItems` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 4. Seed global master list (sorted alphabetically — sortOrder reflects alphabetical position)
--    binary  = customer requests it once (yes/no)
--    quantity = customer can request more than one
--    icon    = Font Awesome icon name (without fal/far prefix), null if no good match
INSERT INTO `requestableItems` (`name`, `type`, `icon`, `sortOrder`) VALUES
  ('Beer refill',           'binary',   'beer-mug',        1),
  ('Chili sauce',           'binary',   'pepper-hot',      2),
  ('Chopsticks',            'quantity', 'chopsticks',      3),
  ('Clean table',           'binary',   'broom',           4),
  ('Clean under the table', 'binary',   'broom',           5),
  ('Extra bowl',            'quantity', 'bowl-food',       6),
  ('Extra cup',             'quantity', 'mug',             7),
  ('Extra glass',           'quantity', 'whiskey-glass',   8),
  ('Extra plate',           'quantity', 'plate-utensils',  9),
  ('Extra small bowl',      'quantity', 'bowl-food',      10),
  ('Extra small plate',     'quantity', 'plate-utensils', 11),
  ('Fork',                  'quantity', 'fork',           12),
  ('High chair',            'binary',   'baby',           13),
  ('Ice',                   'binary',   'snowflake',      14),
  ('Ketchup',               'binary',   'bottle-droplet', 15),
  ('Knife',                 'quantity', 'knife',          16),
  ('Napkin',                'quantity', NULL,             17),
  ('Pepper',                'binary',   'pepper-hot',     18),
  ('Salt',                  'binary',   'shaker',         19),
  ('Soy sauce',             'binary',   NULL,             20),
  ('Spoon',                 'quantity', 'spoon',          21),
  ('Straw',                 'quantity', 'straw',          22),
  ('Sugar',                 'binary',   'cube',           23),
  ('Sugar syrup',           'binary',   NULL,             24),
  ('Takeaway box',          'quantity', 'bag-shopping',   25),
  ('Teaspoon',              'quantity', 'spoon',          26),
  ('Toothpicks',            'binary',   NULL,             27),
  ('Water for dog',         'binary',   'dog',            28),
  ('Wet wipes',             'binary',   'hand-sparkles',  29);

-- 5. Seed a default selection for all existing vendors:
--    Enable the most universally relevant items (exclude niche ones: ashtray, high chair, water for dog, beer refill)
INSERT INTO `vendorRequestableItems` (`vendorId`, `requestableItemId`, `sortOrder`)
SELECT v.`id`, ri.`id`, ri.`sortOrder`
FROM `vendors` v
CROSS JOIN `requestableItems` ri
WHERE ri.`name` NOT IN ('High chair', 'Water for dog', 'Beer refill');
