-- Dining tables and permanent QR identity.
-- Apply once before deploying with TypeORM synchronize disabled.

CREATE TABLE `dining_tables` (
  `id` varchar(36) NOT NULL,
  `food_court_id` varchar(36) NOT NULL,
  `code` varchar(30) NOT NULL,
  `label` varchar(100) DEFAULT NULL,
  `zone` varchar(100) DEFAULT NULL,
  `capacity` smallint DEFAULT NULL,
  `public_token` varchar(64) NOT NULL,
  `is_active` tinyint NOT NULL DEFAULT 1,
  `sort_order` int NOT NULL DEFAULT 0,
  `scan_count` int unsigned NOT NULL DEFAULT 0,
  `last_scanned_at` datetime DEFAULT NULL,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQ_dining_tables_court_code` (`food_court_id`, `code`),
  UNIQUE KEY `UQ_dining_tables_public_token` (`public_token`),
  CONSTRAINT `FK_dining_tables_food_court`
    FOREIGN KEY (`food_court_id`) REFERENCES `food_courts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `orders`
  ADD COLUMN `table_id` varchar(36) DEFAULT NULL AFTER `table_number`,
  ADD KEY `IDX_orders_table_id` (`table_id`),
  ADD CONSTRAINT `FK_orders_dining_table`
    FOREIGN KEY (`table_id`) REFERENCES `dining_tables` (`id`) ON DELETE SET NULL;