-- ============================================================
-- Tap2Order - Multi-Restaurant QR Menu & Ordering Platform
-- Complete Database Schema
-- MySQL 5.7+ / MariaDB 10.3+ Compatible
-- PHP 7.4 Compatible
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- ============================================================
-- DROP & CREATE DATABASE
-- ============================================================
DROP DATABASE IF EXISTS `tap2order`;
CREATE DATABASE `tap2order`
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;
USE `tap2order`;

-- ============================================================
-- 1. roles
-- ============================================================
DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `permissions` LONGTEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_roles_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. permissions
-- ============================================================
DROP TABLE IF EXISTS `permissions`;
CREATE TABLE `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `module` VARCHAR(50) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_permissions_name` (`name`),
  KEY `idx_permissions_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. role_permissions
-- ============================================================
DROP TABLE IF EXISTS `role_permissions`;
CREATE TABLE `role_permissions` (
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  KEY `idx_rp_permission` (`permission_id`),
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_rp_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. subscription_plans (platform-level plans)
-- ============================================================
DROP TABLE IF EXISTS `subscription_plans`;
CREATE TABLE `subscription_plans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `monthly_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `annual_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `max_menu_items` INT NOT NULL DEFAULT 50,
  `max_staff` INT NOT NULL DEFAULT 3,
  `max_tables` INT NOT NULL DEFAULT 10,
  `max_branches` INT NOT NULL DEFAULT 1,
  `features` JSON DEFAULT NULL,
  `is_recommended` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sp_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. restaurants
-- ============================================================
DROP TABLE IF EXISTS `restaurants`;
CREATE TABLE `restaurants` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `code` VARCHAR(20) NOT NULL,
  `owner_name` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `whatsapp` VARCHAR(30) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `currency` VARCHAR(10) DEFAULT 'USD',
  `timezone` VARCHAR(50) DEFAULT 'UTC',
  `logo` VARCHAR(500) DEFAULT NULL,
  `cover_image` VARCHAR(500) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `plan_id` INT UNSIGNED DEFAULT NULL,
  `trial_start` DATE DEFAULT NULL,
  `trial_end` DATE DEFAULT NULL,
  `subscription_start` DATE DEFAULT NULL,
  `subscription_end` DATE DEFAULT NULL,
  `status` ENUM('trial','active','suspended','expired','cancelled') NOT NULL DEFAULT 'trial',
  `operating_mode` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=view_only, 2=ordering',
  `max_menu_items` INT NOT NULL DEFAULT 50,
  `max_staff` INT NOT NULL DEFAULT 3,
  `max_tables` INT NOT NULL DEFAULT 10,
  `max_branches` INT NOT NULL DEFAULT 1,
  `domain` VARCHAR(255) DEFAULT NULL,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_restaurants_slug` (`slug`),
  UNIQUE KEY `uk_restaurants_code` (`code`),
  KEY `idx_restaurants_plan` (`plan_id`),
  KEY `idx_restaurants_status` (`status`),
  KEY `idx_restaurants_is_demo` (`is_demo`),
  KEY `idx_restaurants_is_active` (`is_active`),
  CONSTRAINT `fk_restaurants_plan` FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. users
-- ============================================================
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` INT UNSIGNED NOT NULL,
  `restaurant_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `avatar` VARCHAR(500) DEFAULT NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `last_login` TIMESTAMP NULL DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `last_login_ua` VARCHAR(500) DEFAULT NULL,
  `login_attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_users_email` (`email`),
  KEY `idx_users_role` (`role_id`),
  KEY `idx_users_restaurant` (`restaurant_id`),
  KEY `idx_users_status` (`status`),
  KEY `idx_users_is_demo` (`is_demo`),
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_users_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. restaurant_settings
-- ============================================================
DROP TABLE IF EXISTS `restaurant_settings`;
CREATE TABLE `restaurant_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `google_maps_link` VARCHAR(500) DEFAULT NULL,
  `facebook_url` VARCHAR(500) DEFAULT NULL,
  `instagram_url` VARCHAR(500) DEFAULT NULL,
  `twitter_url` VARCHAR(500) DEFAULT NULL,
  `min_order` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `tax_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `service_charge_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `delivery_charge` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `prep_time_minutes` INT NOT NULL DEFAULT 15,
  `dine_in_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `takeaway_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `delivery_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `menu_mode` VARCHAR(30) DEFAULT 'standard',
  `order_acceptance` TINYINT(1) NOT NULL DEFAULT 1,
  `online_offline` TINYINT(1) NOT NULL DEFAULT 1,
  `primary_color` VARCHAR(20) DEFAULT '#E63946',
  `secondary_color` VARCHAR(20) DEFAULT '#1D3557',
  `font_preference` VARCHAR(50) DEFAULT 'Poppins',
  `menu_layout` VARCHAR(30) DEFAULT 'grid',
  `item_card_style` VARCHAR(30) DEFAULT 'classic',
  `footer_message` VARCHAR(500) DEFAULT NULL,
  `customer_notice` TEXT DEFAULT NULL,
  `wifi_name` VARCHAR(100) DEFAULT NULL,
  `wifi_password` VARCHAR(100) DEFAULT NULL,
  `terms` TEXT DEFAULT NULL,
  `allergy_notice` TEXT DEFAULT NULL,
  `order_prefix` VARCHAR(10) DEFAULT 'ORD',
  `order_start_number` INT NOT NULL DEFAULT 1000,
  `refresh_interval` INT NOT NULL DEFAULT 30,
  `sound_notification` TINYINT(1) NOT NULL DEFAULT 1,
  `opening_hours` JSON DEFAULT NULL,
  `closed_days` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rs_restaurant` (`restaurant_id`),
  CONSTRAINT `fk_rs_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. categories
-- ============================================================
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `available_days` VARCHAR(100) DEFAULT NULL COMMENT 'Comma-separated: mon,tue,wed,...',
  `start_time` TIME DEFAULT NULL,
  `end_time` TIME DEFAULT NULL,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_categories_restaurant` (`restaurant_id`),
  KEY `idx_categories_status` (`status`),
  KEY `idx_categories_sort` (`sort_order`),
  KEY `idx_categories_is_demo` (`is_demo`),
  CONSTRAINT `fk_categories_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. menu_items
-- ============================================================
DROP TABLE IF EXISTS `menu_items`;
CREATE TABLE `menu_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `category_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `short_description` VARCHAR(500) DEFAULT NULL,
  `full_description` TEXT DEFAULT NULL,
  `main_image` VARCHAR(500) DEFAULT NULL,
  `gallery_images` JSON DEFAULT NULL,
  `regular_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `offer_price` DECIMAL(10,2) DEFAULT NULL,
  `cost_price` DECIMAL(10,2) DEFAULT NULL,
  `tax_setting` VARCHAR(30) DEFAULT 'default',
  `prep_time` INT DEFAULT NULL COMMENT 'minutes',
  `calories` INT DEFAULT NULL,
  `ingredients` TEXT DEFAULT NULL,
  `allergens` VARCHAR(500) DEFAULT NULL,
  `is_available` TINYINT(1) NOT NULL DEFAULT 1,
  `stock_status` VARCHAR(20) DEFAULT 'in_stock',
  `stock_quantity` INT DEFAULT NULL,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `is_new` TINYINT(1) NOT NULL DEFAULT 0,
  `is_bestseller` TINYINT(1) NOT NULL DEFAULT 0,
  `is_chefs_choice` TINYINT(1) NOT NULL DEFAULT 0,
  `is_spicy` TINYINT(1) NOT NULL DEFAULT 0,
  `is_vegetarian` TINYINT(1) NOT NULL DEFAULT 0,
  `is_vegan` TINYINT(1) NOT NULL DEFAULT 0,
  `is_halal` TINYINT(1) NOT NULL DEFAULT 0,
  `is_gluten_free` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `view_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `order_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mi_restaurant` (`restaurant_id`),
  KEY `idx_mi_category` (`category_id`),
  KEY `idx_mi_status` (`is_available`),
  KEY `idx_mi_featured` (`is_featured`),
  KEY `idx_mi_sort` (`sort_order`),
  KEY `idx_mi_is_demo` (`is_demo`),
  CONSTRAINT `fk_mi_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_mi_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. item_variations
-- ============================================================
DROP TABLE IF EXISTS `item_variations`;
CREATE TABLE `item_variations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `menu_item_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_iv_restaurant` (`restaurant_id`),
  KEY `idx_iv_menu_item` (`menu_item_id`),
  KEY `idx_iv_is_demo` (`is_demo`),
  CONSTRAINT `fk_iv_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_iv_menu_item` FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11. addon_groups
-- ============================================================
DROP TABLE IF EXISTS `addon_groups`;
CREATE TABLE `addon_groups` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `is_required` TINYINT(1) NOT NULL DEFAULT 0,
  `min_select` INT NOT NULL DEFAULT 0,
  `max_select` INT NOT NULL DEFAULT 10,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ag_restaurant` (`restaurant_id`),
  KEY `idx_ag_is_demo` (`is_demo`),
  CONSTRAINT `fk_ag_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. addons
-- ============================================================
DROP TABLE IF EXISTS `addons`;
CREATE TABLE `addons` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `addon_group_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_addons_restaurant` (`restaurant_id`),
  KEY `idx_addons_group` (`addon_group_id`),
  KEY `idx_addons_is_demo` (`is_demo`),
  CONSTRAINT `fk_addons_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_addons_group` FOREIGN KEY (`addon_group_id`) REFERENCES `addon_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 13. item_addon_groups (pivot)
-- ============================================================
DROP TABLE IF EXISTS `item_addon_groups`;
CREATE TABLE `item_addon_groups` (
  `menu_item_id` INT UNSIGNED NOT NULL,
  `addon_group_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`menu_item_id`, `addon_group_id`),
  KEY `idx_iag_addon_group` (`addon_group_id`),
  CONSTRAINT `fk_iag_menu_item` FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_iag_addon_group` FOREIGN KEY (`addon_group_id`) REFERENCES `addon_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14. offers
-- ============================================================
DROP TABLE IF EXISTS `offers`;
CREATE TABLE `offers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `offer_type` ENUM('percentage','fixed','bogo','combo','coupon') NOT NULL DEFAULT 'percentage',
  `discount_value` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `coupon_code` VARCHAR(50) DEFAULT NULL,
  `banner_image` VARCHAR(500) DEFAULT NULL,
  `start_date` DATE DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `start_time` TIME DEFAULT NULL,
  `end_time` TIME DEFAULT NULL,
  `min_order` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `max_uses` INT DEFAULT NULL,
  `used_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_offers_restaurant` (`restaurant_id`),
  KEY `idx_offers_active` (`is_active`),
  KEY `idx_offers_dates` (`start_date`, `end_date`),
  KEY `idx_offers_coupon` (`coupon_code`),
  KEY `idx_offers_is_demo` (`is_demo`),
  CONSTRAINT `fk_offers_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 15. offer_items (pivot)
-- ============================================================
DROP TABLE IF EXISTS `offer_items`;
CREATE TABLE `offer_items` (
  `offer_id` INT UNSIGNED NOT NULL,
  `menu_item_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`offer_id`, `menu_item_id`),
  KEY `idx_oi_menu_item` (`menu_item_id`),
  CONSTRAINT `fk_oi_offer` FOREIGN KEY (`offer_id`) REFERENCES `offers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_oi_menu_item` FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 16. offer_categories (pivot)
-- ============================================================
DROP TABLE IF EXISTS `offer_categories`;
CREATE TABLE `offer_categories` (
  `offer_id` INT UNSIGNED NOT NULL,
  `category_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`offer_id`, `category_id`),
  KEY `idx_oc_category` (`category_id`),
  CONSTRAINT `fk_oc_offer` FOREIGN KEY (`offer_id`) REFERENCES `offers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_oc_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 17. advertisements
-- ============================================================
DROP TABLE IF EXISTS `advertisements`;
CREATE TABLE `advertisements` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `mobile_image` VARCHAR(500) DEFAULT NULL,
  `button_text` VARCHAR(100) DEFAULT NULL,
  `button_link` VARCHAR(500) DEFAULT NULL,
  `position` ENUM('top_banner','hero_slider','section_banner','popup','bottom_sticky','category_banner','checkout_banner') NOT NULL DEFAULT 'top_banner',
  `start_date` DATE DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `display_frequency` VARCHAR(30) DEFAULT 'always',
  `popup_settings` JSON DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ads_restaurant` (`restaurant_id`),
  KEY `idx_ads_position` (`position`),
  KEY `idx_ads_active` (`is_active`),
  KEY `idx_ads_dates` (`start_date`, `end_date`),
  KEY `idx_ads_is_demo` (`is_demo`),
  CONSTRAINT `fk_ads_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 18. tables
-- ============================================================
DROP TABLE IF EXISTS `tables`;
CREATE TABLE `tables` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `table_number` VARCHAR(20) NOT NULL,
  `table_name` VARCHAR(100) DEFAULT NULL,
  `capacity` INT NOT NULL DEFAULT 4,
  `area` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('available','occupied','ordered','preparing','ready','bill_requested','reserved','cleaning') NOT NULL DEFAULT 'available',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tables_restaurant` (`restaurant_id`),
  KEY `idx_tables_status` (`status`),
  KEY `idx_tables_is_demo` (`is_demo`),
  CONSTRAINT `fk_tables_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 19. qr_codes
-- ============================================================
DROP TABLE IF EXISTS `qr_codes`;
CREATE TABLE `qr_codes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `table_id` INT UNSIGNED DEFAULT NULL,
  `code` VARCHAR(100) NOT NULL,
  `url` VARCHAR(500) NOT NULL,
  `type` ENUM('restaurant','table') NOT NULL DEFAULT 'restaurant',
  `logo_watermark` TINYINT(1) NOT NULL DEFAULT 1,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_qr_code` (`code`),
  KEY `idx_qr_restaurant` (`restaurant_id`),
  KEY `idx_qr_table` (`table_id`),
  CONSTRAINT `fk_qr_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_qr_table` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 20. customers
-- ============================================================
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `total_orders` INT UNSIGNED NOT NULL DEFAULT 0,
  `total_spent` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `last_order_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customers_restaurant` (`restaurant_id`),
  KEY `idx_customers_phone` (`phone`),
  KEY `idx_customers_email` (`email`),
  CONSTRAINT `fk_customers_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 21. orders
-- ============================================================
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `customer_id` INT UNSIGNED DEFAULT NULL,
  `table_id` INT UNSIGNED DEFAULT NULL,
  `order_number` VARCHAR(30) NOT NULL,
  `order_type` ENUM('dine_in','takeaway','delivery') NOT NULL DEFAULT 'dine_in',
  `customer_name` VARCHAR(255) DEFAULT NULL,
  `customer_phone` VARCHAR(30) DEFAULT NULL,
  `customer_address` TEXT DEFAULT NULL,
  `customer_city` VARCHAR(100) DEFAULT NULL,
  `order_notes` TEXT DEFAULT NULL,
  `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `tax` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `service_charge` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `delivery_charge` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `grand_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `payment_method` VARCHAR(30) DEFAULT NULL,
  `payment_status` ENUM('pending','paid','partial','failed','refunded') NOT NULL DEFAULT 'pending',
  `order_status` ENUM('new','confirmed','preparing','ready','served','completed','cancelled','rejected') NOT NULL DEFAULT 'new',
  `cancellation_reason` TEXT DEFAULT NULL,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_orders_number` (`restaurant_id`, `order_number`),
  KEY `idx_orders_customer` (`customer_id`),
  KEY `idx_orders_table` (`table_id`),
  KEY `idx_orders_order_status` (`order_status`),
  KEY `idx_orders_payment_status` (`payment_status`),
  KEY `idx_orders_order_type` (`order_type`),
  KEY `idx_orders_created` (`created_at`),
  KEY `idx_orders_is_demo` (`is_demo`),
  CONSTRAINT `fk_orders_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_table` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 22. order_items
-- ============================================================
DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `menu_item_id` INT UNSIGNED DEFAULT NULL,
  `item_name` VARCHAR(255) NOT NULL,
  `variation_name` VARCHAR(255) DEFAULT NULL,
  `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
  `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `variation_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `addons_total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `item_total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `item_notes` TEXT DEFAULT NULL,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_oi_order` (`order_id`),
  KEY `idx_oi_menu_item` (`menu_item_id`),
  KEY `idx_oi_is_demo` (`is_demo`),
  CONSTRAINT `fk_oi_order_item` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_oi_order_menu_item` FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 23. order_item_addons
-- ============================================================
DROP TABLE IF EXISTS `order_item_addons`;
CREATE TABLE `order_item_addons` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_item_id` INT UNSIGNED NOT NULL,
  `addon_name` VARCHAR(255) NOT NULL,
  `addon_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_oia_order_item` (`order_item_id`),
  CONSTRAINT `fk_oia_order_item` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 24. order_status_history
-- ============================================================
DROP TABLE IF EXISTS `order_status_history`;
CREATE TABLE `order_status_history` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `from_status` VARCHAR(30) DEFAULT NULL,
  `to_status` VARCHAR(30) NOT NULL,
  `notes` TEXT DEFAULT NULL,
  `changed_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_osh_order` (`order_id`),
  KEY `idx_osh_changed_by` (`changed_by`),
  CONSTRAINT `fk_osh_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_osh_changed_by` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 25. payments
-- ============================================================
DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `payment_method` VARCHAR(30) DEFAULT NULL,
  `transaction_ref` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_payments_order` (`order_id`),
  KEY `idx_payments_restaurant` (`restaurant_id`),
  KEY `idx_payments_status` (`status`),
  CONSTRAINT `fk_payments_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_payments_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 26. notifications
-- ============================================================
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `restaurant_id` INT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT DEFAULT NULL,
  `type` VARCHAR(50) DEFAULT 'info',
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `url` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notif_user` (`user_id`),
  KEY `idx_notif_restaurant` (`restaurant_id`),
  KEY `idx_notif_read` (`is_read`),
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_notif_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 27. enquiries
-- ============================================================
DROP TABLE IF EXISTS `enquiries`;
CREATE TABLE `enquiries` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `restaurant_name` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `message` TEXT DEFAULT NULL,
  `preferred_plan` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('new','read','replied') NOT NULL DEFAULT 'new',
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_enquiries_status` (`status`),
  KEY `idx_enquiries_is_demo` (`is_demo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 28. pricing_plans (website display)
-- ============================================================
DROP TABLE IF EXISTS `pricing_plans`;
CREATE TABLE `pricing_plans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `plan_name` VARCHAR(100) NOT NULL,
  `monthly_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `annual_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `description` TEXT DEFAULT NULL,
  `features` JSON DEFAULT NULL,
  `is_recommended` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 29. testimonials
-- ============================================================
DROP TABLE IF EXISTS `testimonials`;
CREATE TABLE `testimonials` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `restaurant_name` VARCHAR(255) DEFAULT NULL,
  `role` VARCHAR(100) DEFAULT NULL,
  `content` TEXT NOT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `rating` TINYINT UNSIGNED NOT NULL DEFAULT 5,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_testimonials_active` (`is_active`),
  KEY `idx_testimonials_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 30. faqs
-- ============================================================
DROP TABLE IF EXISTS `faqs`;
CREATE TABLE `faqs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `question` VARCHAR(500) NOT NULL,
  `answer` TEXT NOT NULL,
  `category` VARCHAR(100) DEFAULT 'general',
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_faqs_category` (`category`),
  KEY `idx_faqs_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 31. website_settings
-- ============================================================
DROP TABLE IF EXISTS `website_settings`;
CREATE TABLE `website_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  `setting_type` ENUM('text','image','color','json') NOT NULL DEFAULT 'text',
  `group_name` VARCHAR(50) DEFAULT 'general',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ws_key` (`setting_key`),
  KEY `idx_ws_group` (`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 32. website_sections
-- ============================================================
DROP TABLE IF EXISTS `website_sections`;
CREATE TABLE `website_sections` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `section_key` VARCHAR(100) NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `subtitle` VARCHAR(500) DEFAULT NULL,
  `content` TEXT DEFAULT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ws_section_key` (`section_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 33. activity_logs
-- ============================================================
DROP TABLE IF EXISTS `activity_logs`;
CREATE TABLE `activity_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `restaurant_id` INT UNSIGNED DEFAULT NULL,
  `module` VARCHAR(50) NOT NULL,
  `action` VARCHAR(50) NOT NULL,
  `details` TEXT DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_al_user` (`user_id`),
  KEY `idx_al_restaurant` (`restaurant_id`),
  KEY `idx_al_module` (`module`),
  KEY `idx_al_action` (`action`),
  KEY `idx_al_created` (`created_at`),
  CONSTRAINT `fk_al_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_al_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 34. qr_scan_logs
-- ============================================================
DROP TABLE IF EXISTS `qr_scan_logs`;
CREATE TABLE `qr_scan_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `table_id` INT UNSIGNED DEFAULT NULL,
  `qr_code_id` INT UNSIGNED DEFAULT NULL,
  `session_id` VARCHAR(100) DEFAULT NULL,
  `device_type` VARCHAR(30) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `scanned_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_qsl_restaurant` (`restaurant_id`),
  KEY `idx_qsl_table` (`table_id`),
  KEY `idx_qsl_qr_code` (`qr_code_id`),
  KEY `idx_qsl_scanned` (`scanned_at`),
  CONSTRAINT `fk_qsl_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_qsl_table` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_qsl_qr_code` FOREIGN KEY (`qr_code_id`) REFERENCES `qr_codes` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 35. menu_view_logs
-- ============================================================
DROP TABLE IF EXISTS `menu_view_logs`;
CREATE TABLE `menu_view_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `category_id` INT UNSIGNED DEFAULT NULL,
  `menu_item_id` INT UNSIGNED DEFAULT NULL,
  `session_id` VARCHAR(100) DEFAULT NULL,
  `event_type` ENUM('view','add_to_cart','checkout_start','order_placed') NOT NULL DEFAULT 'view',
  `device_type` VARCHAR(30) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mvl_restaurant` (`restaurant_id`),
  KEY `idx_mvl_category` (`category_id`),
  KEY `idx_mvl_menu_item` (`menu_item_id`),
  KEY `idx_mvl_event` (`event_type`),
  KEY `idx_mvl_created` (`created_at`),
  CONSTRAINT `fk_mvl_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_mvl_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_mvl_menu_item` FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 36. system_settings
-- ============================================================
DROP TABLE IF EXISTS `system_settings`;
CREATE TABLE `system_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  `setting_type` ENUM('text','image','color','json','boolean','integer') NOT NULL DEFAULT 'text',
  `group_name` VARCHAR(50) DEFAULT 'general',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ss_key` (`setting_key`),
  KEY `idx_ss_group` (`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 37. password_resets
-- ============================================================
DROP TABLE IF EXISTS `password_resets`;
CREATE TABLE `password_resets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pr_email` (`email`),
  KEY `idx_pr_token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- RE-ENABLE FOREIGN KEY CHECKS
-- ============================================================
SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- SEED DATA: Default Roles
-- ============================================================
INSERT INTO `roles` (`id`, `name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'super_admin', 'Full system access with all permissions', NOW(), NOW()),
(2, 'restaurant_admin', 'Restaurant owner with full restaurant access', NOW(), NOW()),
(3, 'restaurant_manager', 'Restaurant manager with operational access', NOW(), NOW()),
(4, 'cashier', 'Cashier with order and payment access', NOW(), NOW()),
(5, 'kitchen_staff', 'Kitchen staff with order viewing access', NOW(), NOW()),
(6, 'waiter', 'Waiter with table and order management access', NOW(), NOW());

-- ============================================================
-- SEED DATA: Default Permissions
-- ============================================================
INSERT INTO `permissions` (`name`, `module`, `description`, `created_at`) VALUES
-- Dashboard
('dashboard.view', 'dashboard', 'View dashboard', NOW()),
('dashboard.analytics', 'dashboard', 'View analytics', NOW()),
-- Restaurant
('restaurant.view', 'restaurant', 'View restaurant details', NOW()),
('restaurant.edit', 'restaurant', 'Edit restaurant settings', NOW()),
('restaurant.manage', 'restaurant', 'Manage restaurant (full)', NOW()),
-- Menu
('menu.view', 'menu', 'View menu items', NOW()),
('menu.create', 'menu', 'Create menu items', NOW()),
('menu.edit', 'menu', 'Edit menu items', NOW()),
('menu.delete', 'menu', 'Delete menu items', NOW()),
('menu.manage_categories', 'menu', 'Manage categories', NOW()),
('menu.manage_addons', 'menu', 'Manage addons', NOW()),
('menu.manage_variations', 'menu', 'Manage variations', NOW()),
-- Orders
('orders.view', 'orders', 'View orders', NOW()),
('orders.create', 'orders', 'Create orders', NOW()),
('orders.edit_status', 'orders', 'Edit order status', NOW()),
('orders.cancel', 'orders', 'Cancel orders', NOW()),
('orders.delete', 'orders', 'Delete orders', NOW()),
-- Tables
('tables.view', 'tables', 'View tables', NOW()),
('tables.manage', 'tables', 'Manage tables', NOW()),
-- Payments
('payments.view', 'payments', 'View payments', NOW()),
('payments.process', 'payments', 'Process payments', NOW()),
('payments.refund', 'payments', 'Refund payments', NOW()),
-- Customers
('customers.view', 'customers', 'View customers', NOW()),
('customers.manage', 'customers', 'Manage customers', NOW()),
-- Offers
('offers.view', 'offers', 'View offers', NOW()),
('offers.manage', 'offers', 'Manage offers', NOW()),
-- Advertisements
('advertisements.view', 'advertisements', 'View advertisements', NOW()),
('advertisements.manage', 'advertisements', 'Manage advertisements', NOW()),
-- Reports
('reports.view', 'reports', 'View reports', NOW()),
('reports.export', 'reports', 'Export reports', NOW()),
-- Users
('users.view', 'users', 'View users', NOW()),
('users.manage', 'users', 'Manage staff users', NOW()),
-- Settings
('settings.view', 'settings', 'View settings', NOW()),
('settings.edit', 'settings', 'Edit settings', NOW()),
-- Notifications
('notifications.view', 'notifications', 'View notifications', NOW()),
('notifications.manage', 'notifications', 'Manage notifications', NOW()),
-- Super Admin only
('system.settings', 'system', 'Manage system settings', NOW()),
('system.plans', 'system', 'Manage subscription plans', NOW()),
('system.restaurants', 'system', 'Manage all restaurants', NOW()),
('system.users', 'system', 'Manage all users', NOW()),
('system.logs', 'system', 'View system logs', NOW()),
('system.website', 'system', 'Manage website settings', NOW());

-- ============================================================
-- SEED DATA: Role-Permission Assignments (Super Admin gets all)
-- ============================================================
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, `id` FROM `permissions`;

-- Restaurant Admin gets most permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 2, `id` FROM `permissions` WHERE `module` IN ('dashboard','restaurant','menu','orders','tables','payments','customers','offers','advertisements','reports','notifications');

-- Restaurant Manager
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 3, `id` FROM `permissions` WHERE `name` IN (
  'dashboard.view','dashboard.analytics',
  'restaurant.view',
  'menu.view','menu.create','menu.edit',
  'orders.view','orders.create','orders.edit_status','orders.cancel',
  'tables.view','tables.manage',
  'payments.view','payments.process',
  'customers.view','customers.manage',
  'offers.view',
  'reports.view',
  'notifications.view'
);

-- Cashier
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 4, `id` FROM `permissions` WHERE `name` IN (
  'dashboard.view',
  'menu.view',
  'orders.view','orders.create','orders.edit_status',
  'tables.view',
  'payments.view','payments.process',
  'customers.view',
  'notifications.view'
);

-- Kitchen Staff
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 5, `id` FROM `permissions` WHERE `name` IN (
  'dashboard.view',
  'menu.view',
  'orders.view','orders.edit_status',
  'notifications.view'
);

-- Waiter
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 6, `id` FROM `permissions` WHERE `name` IN (
  'dashboard.view',
  'menu.view',
  'orders.view','orders.create','orders.edit_status',
  'tables.view','tables.manage',
  'customers.view',
  'notifications.view'
);

-- ============================================================
-- SEED DATA: Super Admin User
-- Password: Admin@123
-- Generated via PHP password_hash('Admin@123', PASSWORD_DEFAULT)
-- ============================================================
INSERT INTO `users` (`id`, `role_id`, `restaurant_id`, `name`, `email`, `password`, `phone`, `status`, `is_demo`, `created_at`, `updated_at`) VALUES
(1, 1, NULL, 'Super Admin', 'superadmin@tap2order.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', NULL, 'active', 0, NOW(), NOW());

-- ============================================================
-- SEED DATA: Subscription Plans
-- ============================================================
INSERT INTO `subscription_plans` (`id`, `name`, `slug`, `description`, `monthly_price`, `annual_price`, `max_menu_items`, `max_staff`, `max_tables`, `max_branches`, `features`, `is_recommended`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES
(1, 'Basic', 'basic', 'Perfect for small restaurants just getting started with digital menus.', 29.00, 290.00, 50, 3, 10, 1, '["Digital Menu","QR Codes","Basic Analytics","Email Support","1 Branch"]', 0, 1, 1, NOW(), NOW()),
(2, 'Professional', 'professional', 'Ideal for growing restaurants that need advanced ordering features.', 79.00, 790.00, 200, 10, 50, 3, '["Digital Menu","QR Codes","Online Ordering","Advanced Analytics","Table Management","Payment Tracking","Priority Support","3 Branches","Offers & Coupons","Advertisements"]', 1, 1, 2, NOW(), NOW()),
(3, 'Enterprise', 'enterprise', 'Complete solution for restaurant chains and large operations.', 199.00, 1990.00, -1, -1, -1, -1, '["Unlimited Menu Items","Unlimited Staff","Unlimited Tables","Unlimited Branches","White Label Domain","Custom Branding","API Access","Dedicated Support","Multi-Branch Management","Advanced Reports","All Features Included"]', 0, 1, 3, NOW(), NOW());

-- ============================================================
-- SEED DATA: Pricing Plans (Website Display)
-- ============================================================
INSERT INTO `pricing_plans` (`plan_name`, `monthly_price`, `annual_price`, `description`, `features`, `is_recommended`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES
('Basic', 29.00, 290.00, 'Perfect for small restaurants just getting started.', '["Digital Menu","QR Codes","Up to 50 Items","3 Staff Members","10 Tables","1 Branch","Basic Analytics","Email Support"]', 0, 1, 1, NOW(), NOW()),
('Professional', 79.00, 790.00, 'Ideal for growing restaurants needing more features.', '["Digital Menu","QR Codes","Online Ordering","Up to 200 Items","10 Staff Members","50 Tables","3 Branches","Advanced Analytics","Table Management","Payment Tracking","Offers & Coupons","Priority Support"]', 1, 1, 2, NOW(), NOW()),
('Enterprise', 199.00, 1990.00, 'Complete solution for restaurant chains.', '["Unlimited Everything","White Label","Custom Branding","API Access","Dedicated Support","Multi-Branch Management","Advanced Reports","All Features Included"]', 0, 1, 3, NOW(), NOW());

-- ============================================================
-- SEED DATA: Website Settings
-- ============================================================
INSERT INTO `website_settings` (`setting_key`, `setting_value`, `setting_type`, `group_name`, `created_at`, `updated_at`) VALUES
('site_name', 'Tap2Order', 'text', 'general', NOW(), NOW()),
('site_tagline', 'Smart QR Menu & Ordering Platform for Restaurants', 'text', 'general', NOW(), NOW()),
('site_description', 'Transform your restaurant with Tap2Order - the all-in-one QR menu and ordering platform. Go digital in minutes.', 'text', 'general', NOW(), NOW()),
('site_logo', 'assets/images/logo.png', 'image', 'general', NOW(), NOW()),
('site_favicon', 'assets/images/favicon.ico', 'image', 'general', NOW(), NOW()),
('contact_email', 'support@tap2order.com', 'text', 'contact', NOW(), NOW()),
('contact_phone', '+1 (555) 123-4567', 'text', 'contact', NOW(), NOW()),
('contact_whatsapp', '+15551234567', 'text', 'contact', NOW(), NOW()),
('contact_address', '123 Business Ave, Tech City, TC 10001', 'text', 'contact', NOW(), NOW()),
('social_facebook', 'https://facebook.com/tap2order', 'text', 'social', NOW(), NOW()),
('social_instagram', 'https://instagram.com/tap2order', 'text', 'social', NOW(), NOW()),
('social_twitter', 'https://twitter.com/tap2order', 'text', 'social', NOW(), NOW()),
('social_linkedin', 'https://linkedin.com/company/tap2order', 'text', 'social', NOW(), NOW()),
('social_youtube', 'https://youtube.com/@tap2order', 'text', 'social', NOW(), NOW()),
('primary_color', '#E63946', 'color', 'appearance', NOW(), NOW()),
('secondary_color', '#1D3557', 'color', 'appearance', NOW(), NOW()),
('accent_color', '#F1FAEE', 'color', 'appearance', NOW(), NOW()),
('meta_title', 'Tap2Order - QR Menu & Ordering Platform for Restaurants', 'text', 'seo', NOW(), NOW()),
('meta_description', 'Transform your restaurant with smart QR menus and online ordering. Tap2Order makes it easy to go digital.', 'text', 'seo', NOW(), NOW()),
('meta_keywords', 'QR menu, restaurant ordering, digital menu, online ordering, restaurant platform', 'text', 'seo', NOW(), NOW()),
('google_analytics_id', '', 'text', 'analytics', NOW(), NOW()),
('facebook_pixel_id', '', 'text', 'analytics', NOW(), NOW()),
('terms_page_url', '/pages/terms', 'text', 'legal', NOW(), NOW()),
('privacy_page_url', '/pages/privacy', 'text', 'legal', NOW(), NOW()),
('copyright_text', '© 2026 Tap2Order. All rights reserved.', 'text', 'general', NOW(), NOW());

-- ============================================================
-- SEED DATA: Website Sections
-- ============================================================
INSERT INTO `website_sections` (`section_key`, `title`, `subtitle`, `content`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES
('hero', 'Transform Your Restaurant with Smart QR Menus', 'Go digital in minutes. Let customers scan, browse & order from their phones.', NULL, 1, 1, NOW(), NOW()),
('features', 'Everything You Need to Go Digital', 'Powerful features designed specifically for modern restaurants.', NULL, 1, 2, NOW(), NOW()),
('how_it_works', 'How It Works', 'Get started in 3 simple steps', NULL, 1, 3, NOW(), NOW()),
('pricing', 'Simple, Transparent Pricing', 'Choose the plan that fits your restaurant.', NULL, 1, 4, NOW(), NOW()),
('testimonials', 'Loved by Restaurant Owners', 'See what our customers have to say', NULL, 1, 5, NOW(), NOW()),
('cta', 'Ready to Go Digital?', 'Join thousands of restaurants already using Tap2Order.', NULL, 1, 6, NOW(), NOW()),
('faq', 'Frequently Asked Questions', 'Find answers to common questions', NULL, 1, 7, NOW(), NOW()),
('footer', 'Tap2Order', 'The smart QR menu and ordering platform for modern restaurants.', NULL, 1, 8, NOW(), NOW());

-- ============================================================
-- SEED DATA: System Settings
-- ============================================================
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `group_name`, `created_at`, `updated_at`) VALUES
('platform_name', 'Tap2Order', 'text', 'general', NOW(), NOW()),
('platform_version', '1.0.0', 'text', 'general', NOW(), NOW()),
('platform_url', 'https://tap2order.com', 'text', 'general', NOW(), NOW()),
('admin_email', 'admin@tap2order.com', 'text', 'general', NOW(), NOW()),
('support_email', 'support@tap2order.com', 'text', 'general', NOW(), NOW()),
('default_currency', 'USD', 'text', 'general', NOW(), NOW()),
('default_timezone', 'UTC', 'text', 'general', NOW(), NOW()),
('trial_days', '14', 'integer', 'subscription', NOW(), NOW()),
('max_trial_extensions', '2', 'integer', 'subscription', NOW(), NOW()),
('demo_mode', '0', 'boolean', 'general', NOW(), NOW()),
('maintenance_mode', '0', 'boolean', 'general', NOW(), NOW()),
('registration_enabled', '1', 'boolean', 'general', NOW(), NOW()),
('email_verification', '0', 'boolean', 'auth', NOW(), NOW()),
('sms_verification', '0', 'boolean', 'auth', NOW(), NOW()),
('smtp_host', '', 'text', 'mail', NOW(), NOW()),
('smtp_port', '587', 'integer', 'mail', NOW(), NOW()),
('smtp_username', '', 'text', 'mail', NOW(), NOW()),
('smtp_password', '', 'text', 'mail', NOW(), NOW()),
('smtp_encryption', 'tls', 'text', 'mail', NOW(), NOW()),
('smtp_from_email', 'noreply@tap2order.com', 'text', 'mail', NOW(), NOW()),
('smtp_from_name', 'Tap2Order', 'text', 'mail', NOW(), NOW()),
('order_notification_email', '1', 'boolean', 'notifications', NOW(), NOW()),
('order_notification_sms', '0', 'boolean', 'notifications', NOW(), NOW()),
('order_notification_sound', '1', 'boolean', 'notifications', NOW(), NOW()),
('max_upload_size', '5', 'integer', 'uploads', NOW(), NOW()),
('allowed_file_types', 'jpg,jpeg,png,gif,webp,svg', 'text', 'uploads', NOW(), NOW()),
('auto_backup_enabled', '1', 'boolean', 'system', NOW(), NOW()),
('backup_frequency', 'daily', 'text', 'system', NOW(), NOW()),
('log_retention_days', '90', 'integer', 'system', NOW(), NOW()),
('tax_name', 'Tax', 'text', 'orders', NOW(), NOW()),
('currency_symbol', '$', 'text', 'general', NOW(), NOW()),
('currency_position', 'before', 'text', 'general', NOW(), NOW()),
('date_format', 'Y-m-d', 'text', 'general', NOW(), NOW()),
('time_format', 'H:i', 'text', 'general', NOW(), NOW());

-- ============================================================
-- SEED DATA: FAQs
-- ============================================================
INSERT INTO `faqs` (`question`, `answer`, `category`, `sort_order`, `is_active`, `created_at`) VALUES
('What is Tap2Order?', 'Tap2Order is a smart QR menu and ordering platform that lets restaurants go digital. Customers can scan a QR code, browse your menu, and place orders directly from their phones.', 'general', 1, 1, NOW()),
('How do QR codes work?', 'Each table gets a unique QR code. When customers scan it with their phone camera, they are taken directly to your restaurant''s digital menu. They can browse items, customize orders, and pay — all from their device.', 'general', 2, 1, NOW()),
('Is there a free trial?', 'Yes! We offer a 14-day free trial on all plans. No credit card required. You can explore all features before committing to a subscription.', 'billing', 3, 1, NOW()),
('Can I manage multiple branches?', 'Yes, depending on your plan. The Professional plan supports up to 3 branches, and the Enterprise plan offers unlimited branches with centralized management.', 'features', 4, 1, NOW()),
('Do customers need to install an app?', 'No! Tap2Order is completely web-based. Customers simply scan the QR code with their phone''s camera and the menu opens in their browser. No app download required.', 'general', 5, 1, NOW()),
('What payment methods are supported?', 'We support cash on delivery, credit/debit cards, and various digital payment methods. The available options can be configured in your restaurant settings.', 'billing', 6, 1, NOW()),
('Can I customize the look and feel?', 'Absolutely! You can customize colors, fonts, layout, and branding to match your restaurant''s identity. Enterprise customers also get white-label options.', 'features', 7, 1, NOW()),
('How do I get started?', 'Simply sign up for a free trial, add your menu items, generate QR codes for your tables, and you''re ready to go! Our onboarding process takes just a few minutes.', 'general', 8, 1, NOW());

-- ============================================================
-- SEED DATA: Testimonials
-- ============================================================
INSERT INTO `testimonials` (`name`, `restaurant_name`, `role`, `content`, `image`, `rating`, `is_active`, `sort_order`, `created_at`) VALUES
('Marco Rossi', 'Bella Italia', 'Owner', 'Tap2Order transformed our restaurant. We went from paper menus to a sleek digital experience in just one day. Our customers love it and orders have increased by 35%!', NULL, 5, 1, 1, NOW()),
('Sarah Chen', 'Dragon Palace', 'Manager', 'The best investment we made this year. Table management and online ordering have streamlined our operations completely. Support team is fantastic too.', NULL, 5, 1, 2, NOW()),
('Ahmed Hassan', 'Spice Garden', 'Owner', 'Very easy to set up and use. Our customers appreciate the modern touch, and we save so much on printing costs. The analytics are incredibly helpful.', NULL, 5, 1, 3, NOW()),
('Emily Johnson', 'The Green Fork', 'Chef & Owner', 'As a small restaurant owner, I was worried about going digital. Tap2Order made it so simple and affordable. Now I can focus on what I love - cooking great food!', NULL, 4, 1, 4, NOW()),
('Carlos Mendez', 'Casa Mexicana', 'Manager', 'The QR code ordering system is a game changer. Customers scan, order, and pay without waiting. Our turnover rate has improved significantly.', NULL, 5, 1, 5, NOW());

-- ============================================================
-- SEED DATA: Sample Testimonials Data
-- ============================================================
INSERT INTO `testimonials` (`name`, `restaurant_name`, `role`, `content`, `image`, `rating`, `is_active`, `sort_order`, `created_at`) VALUES
('Priya Sharma', 'Tandoori Nights', 'Owner', 'Switched from our old POS system to Tap2Order and never looked back. The digital menu is beautiful and our international customers find it so much easier to order now.', NULL, 5, 1, 6, NOW()),
('David Kim', 'Seoul Kitchen', 'Operations Director', 'Multi-branch management is seamless. We run 5 locations and Tap2Order gives us a unified view of everything. Highly recommended for restaurant chains.', NULL, 5, 1, 7, NOW());

-- ============================================================
-- login_attempts
-- ============================================================
DROP TABLE IF EXISTS `login_attempts`;
CREATE TABLE `login_attempts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `attempted_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_login_attempts_email` (`email`),
  KEY `idx_login_attempts_attempted_at` (`attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
