-- Step 33: Add serving time analytics tables
-- These tables collect serving time data for the intelligent queue management system.
-- itemServingMetrics: rolling avg/min/max per vendor+menuItem
-- itemServingTimeSamples: raw samples with queue depth and signal weight

CREATE TABLE `itemServingMetrics` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) NOT NULL,
  `menuItemId` int(11) UNSIGNED NOT NULL,
  `sampleCount` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `avgServingTime` decimal(8,1) NOT NULL DEFAULT 0.0,
  `minServingTime` decimal(8,1) DEFAULT NULL,
  `maxServingTime` decimal(8,1) DEFAULT NULL,
  `lastUpdated` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vendor_item` (`vendorId`,`menuItemId`),
  KEY `fk_itemServingMetrics_menuItemId` (`menuItemId`),
  CONSTRAINT `fk_itemServingMetrics_vendorId` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_itemServingMetrics_menuItemId` FOREIGN KEY (`menuItemId`) REFERENCES `menuItems` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `itemServingTimeSamples` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `vendorId` varchar(50) NOT NULL,
  `menuItemId` int(11) UNSIGNED NOT NULL,
  `servingTime` decimal(8,1) NOT NULL,
  `queueDepth` tinyint(3) UNSIGNED NOT NULL,
  `weight` decimal(3,2) NOT NULL DEFAULT 1.00,
  `recordedAt` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_vendor_item` (`vendorId`,`menuItemId`),
  CONSTRAINT `fk_itemServingTimeSamples_vendorId` FOREIGN KEY (`vendorId`) REFERENCES `vendors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;