-- Full Food Court schema baseline for production installs.
-- Safe to run on a fresh database. For existing DBs prefer incremental migrations.

CREATE TABLE IF NOT EXISTS `food_courts` (
  `id` varchar(36) NOT NULL,
  `name` varchar(150) NOT NULL,
  `name_ar` varchar(150) DEFAULT NULL,
  `slug` varchar(150) NOT NULL,
  `description` text,
  `description_ar` text,
  `logo_url` varchar(500) DEFAULT NULL,
  `is_active` tinyint NOT NULL DEFAULT 1,
  `settings` json 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_food_courts_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurants` (
  `id` varchar(36) NOT NULL,
  `food_court_id` varchar(36) NOT NULL,
  `name` varchar(150) NOT NULL,
  `name_ar` varchar(150) DEFAULT NULL,
  `slug` varchar(150) NOT NULL,
  `description` text,
  `description_ar` text,
  `logo_url` varchar(500) DEFAULT NULL,
  `cover_url` varchar(500) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `estimated_prep_minutes` int NOT NULL DEFAULT 20,
  `opening_time` varchar(5) NOT NULL DEFAULT '10:00',
  `closing_time` varchar(5) NOT NULL DEFAULT '23:00',
  `is_open` tinyint NOT NULL DEFAULT 1,
  `sort_order` int NOT NULL DEFAULT 0,
  `is_active` tinyint NOT NULL DEFAULT 1,
  `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`),
  KEY `IDX_restaurants_food_court` (`food_court_id`),
  CONSTRAINT `FK_restaurants_food_court`
    FOREIGN KEY (`food_court_id`) REFERENCES `food_courts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` varchar(36) NOT NULL,
  `food_court_id` varchar(36) DEFAULT NULL,
  `restaurant_id` varchar(36) DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `email` varchar(180) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('court_admin','restaurant_admin') NOT NULL,
  `is_active` tinyint NOT NULL DEFAULT 1,
  `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_admin_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `banners` (
  `id` varchar(36) NOT NULL,
  `food_court_id` varchar(36) NOT NULL,
  `restaurant_id` varchar(36) NOT NULL,
  `placement` enum('court','restaurant') NOT NULL DEFAULT 'court',
  `title` varchar(160) NOT NULL,
  `title_ar` varchar(160) DEFAULT NULL,
  `subtitle` varchar(240) DEFAULT NULL,
  `subtitle_ar` varchar(240) DEFAULT NULL,
  `image_url` varchar(500) NOT NULL,
  `target_type` enum('restaurant','category','item') NOT NULL,
  `target_id` varchar(36) NOT NULL,
  `is_active` tinyint NOT NULL DEFAULT 1,
  `sort_order` int NOT NULL DEFAULT 0,
  `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`),
  KEY `IDX_banners_public_order` (`food_court_id`, `placement`, `is_active`, `sort_order`),
  KEY `IDX_banners_restaurant` (`restaurant_id`, `placement`, `is_active`, `sort_order`),
  CONSTRAINT `FK_banners_food_court`
    FOREIGN KEY (`food_court_id`) REFERENCES `food_courts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_banners_restaurant`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `menu_categories` (
  `id` varchar(36) NOT NULL,
  `restaurant_id` varchar(36) NOT NULL,
  `name` varchar(120) NOT NULL,
  `name_ar` varchar(120) DEFAULT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `is_active` tinyint NOT NULL DEFAULT 1,
  `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`),
  KEY `IDX_menu_categories_restaurant` (`restaurant_id`),
  CONSTRAINT `FK_menu_categories_restaurant`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `menu_items` (
  `id` varchar(36) NOT NULL,
  `restaurant_id` varchar(36) NOT NULL,
  `category_id` varchar(36) DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `name_ar` varchar(150) DEFAULT NULL,
  `description` text,
  `description_ar` text,
  `item_type` varchar(30) NOT NULL DEFAULT 'item',
  `customizations` json DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `is_available` tinyint NOT NULL DEFAULT 1,
  `sort_order` int NOT NULL DEFAULT 0,
  `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`),
  KEY `IDX_menu_items_restaurant` (`restaurant_id`),
  KEY `IDX_menu_items_category` (`category_id`),
  CONSTRAINT `FK_menu_items_restaurant`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_menu_items_category`
    FOREIGN KEY (`category_id`) REFERENCES `menu_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `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;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` varchar(36) NOT NULL,
  `food_court_id` varchar(36) NOT NULL,
  `restaurant_id` varchar(36) NOT NULL,
  `order_number` varchar(30) NOT NULL,
  `tracking_token` varchar(64) NOT NULL,
  `table_number` varchar(20) DEFAULT NULL,
  `table_id` varchar(36) DEFAULT NULL,
  `customer_name` varchar(120) DEFAULT NULL,
  `customer_phone` varchar(30) DEFAULT NULL,
  `status` enum('pending','confirmed','preparing','ready','delivered','cancelled') NOT NULL DEFAULT 'pending',
  `payment_method` enum('pay_at_counter') NOT NULL DEFAULT 'pay_at_counter',
  `payment_status` enum('unpaid','paid','refunded') NOT NULL DEFAULT 'unpaid',
  `total_amount` decimal(10,2) NOT NULL,
  `notes` text,
  `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_orders_tracking_token` (`tracking_token`),
  KEY `IDX_orders_food_court` (`food_court_id`),
  KEY `IDX_orders_restaurant` (`restaurant_id`),
  KEY `IDX_orders_table_id` (`table_id`),
  CONSTRAINT `FK_orders_food_court`
    FOREIGN KEY (`food_court_id`) REFERENCES `food_courts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_orders_restaurant`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_orders_dining_table`
    FOREIGN KEY (`table_id`) REFERENCES `dining_tables` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_items` (
  `id` varchar(36) NOT NULL,
  `order_id` varchar(36) NOT NULL,
  `menu_item_id` varchar(36) DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `quantity` int NOT NULL DEFAULT 1,
  `notes` text,
  `selected_options` json DEFAULT NULL,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `IDX_order_items_order` (`order_id`),
  CONSTRAINT `FK_order_items_order`
    FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;