-- ============================================================
-- FoodRush - Food Delivery Platform
-- Database Schema v1.0
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE `users` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`            VARCHAR(100) NOT NULL,
  `email`           VARCHAR(150) NOT NULL UNIQUE,
  `phone`           VARCHAR(20) DEFAULT NULL,
  `password`        VARCHAR(255) NOT NULL,
  `role`            ENUM('customer','restaurant_owner','rider','admin') NOT NULL DEFAULT 'customer',
  `status`          ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
  `avatar`          VARCHAR(255) DEFAULT NULL,
  `email_verified`  TINYINT(1) DEFAULT 0,
  `phone_verified`  TINYINT(1) DEFAULT 0,
  `wallet_balance`  DECIMAL(10,2) DEFAULT 0.00,
  `fcm_token`       TEXT DEFAULT NULL,
  `push_subscribed` TINYINT(1) DEFAULT 0,
  `last_login`      TIMESTAMP NULL DEFAULT NULL,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`),
  INDEX `idx_role` (`role`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SESSIONS / AUTH TOKENS
-- ============================================================
CREATE TABLE `auth_tokens` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED NOT NULL,
  `token`      VARCHAR(512) NOT NULL,
  `type`       ENUM('jwt','remember','reset_password','email_verify') NOT NULL DEFAULT 'jwt',
  `expires_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_token` (`token`(100)),
  INDEX `idx_user_type` (`user_id`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ADDRESSES
-- ============================================================
CREATE TABLE `addresses` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`      INT UNSIGNED NOT NULL,
  `label`        VARCHAR(50) NOT NULL DEFAULT 'Home',
  `name`         VARCHAR(100) NOT NULL,
  `phone`        VARCHAR(20) NOT NULL,
  `address_line` TEXT NOT NULL,
  `city`         VARCHAR(100) NOT NULL,
  `area`         VARCHAR(100) DEFAULT NULL,
  `zip`          VARCHAR(20) DEFAULT NULL,
  `lat`          DECIMAL(10,8) DEFAULT NULL,
  `lng`          DECIMAL(11,8) DEFAULT NULL,
  `is_default`   TINYINT(1) DEFAULT 0,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- RESTAURANT CATEGORIES (Admin-managed)
-- ============================================================
CREATE TABLE `restaurant_categories` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(100) NOT NULL,
  `slug`        VARCHAR(120) NOT NULL UNIQUE,
  `icon`        VARCHAR(255) DEFAULT NULL,
  `image`       VARCHAR(255) DEFAULT NULL,
  `sort_order`  INT DEFAULT 0,
  `is_active`   TINYINT(1) DEFAULT 1,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- RESTAURANTS
-- ============================================================
CREATE TABLE `restaurants` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `owner_id`            INT UNSIGNED NOT NULL,
  `category_id`         INT UNSIGNED DEFAULT NULL,
  `name`                VARCHAR(150) NOT NULL,
  `slug`                VARCHAR(180) NOT NULL UNIQUE,
  `description`         TEXT DEFAULT NULL,
  `logo`                VARCHAR(255) DEFAULT NULL,
  `banner`              VARCHAR(255) DEFAULT NULL,
  `phone`               VARCHAR(20) DEFAULT NULL,
  `email`               VARCHAR(150) DEFAULT NULL,
  `address`             TEXT NOT NULL,
  `city`                VARCHAR(100) NOT NULL,
  `lat`                 DECIMAL(10,8) DEFAULT NULL,
  `lng`                 DECIMAL(11,8) DEFAULT NULL,
  `delivery_radius`     DECIMAL(5,2) DEFAULT 10.00 COMMENT 'KM',
  `min_order`           DECIMAL(10,2) DEFAULT 0.00,
  `delivery_fee`        DECIMAL(10,2) DEFAULT 0.00,
  `free_delivery_above` DECIMAL(10,2) DEFAULT NULL,
  `avg_delivery_time`   INT DEFAULT 30 COMMENT 'Minutes',
  `commission_rate`     DECIMAL(5,2) DEFAULT 10.00 COMMENT 'Platform commission %',
  `rating`              DECIMAL(3,2) DEFAULT 0.00,
  `total_reviews`       INT DEFAULT 0,
  `total_orders`        INT DEFAULT 0,
  `is_featured`         TINYINT(1) DEFAULT 0,
  `is_verified`         TINYINT(1) DEFAULT 0,
  `status`              ENUM('pending','active','inactive','rejected','suspended') DEFAULT 'pending',
  `rejection_reason`    TEXT DEFAULT NULL,
  `opened_at`           TIME DEFAULT '08:00:00',
  `closed_at`           TIME DEFAULT '22:00:00',
  `is_open_now`         TINYINT(1) DEFAULT 1,
  `accept_cash`         TINYINT(1) DEFAULT 1,
  `accept_online`       TINYINT(1) DEFAULT 0,
  `created_at`          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `restaurant_categories`(`id`) ON DELETE SET NULL,
  INDEX `idx_city` (`city`),
  INDEX `idx_status` (`status`),
  INDEX `idx_slug` (`slug`),
  INDEX `idx_featured` (`is_featured`),
  INDEX `idx_lat_lng` (`lat`, `lng`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- RESTAURANT BUSINESS HOURS
-- ============================================================
CREATE TABLE `restaurant_hours` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `day`           TINYINT NOT NULL COMMENT '0=Sun,1=Mon,...6=Sat',
  `opens_at`      TIME NOT NULL DEFAULT '09:00:00',
  `closes_at`     TIME NOT NULL DEFAULT '22:00:00',
  `is_closed`     TINYINT(1) DEFAULT 0,
  FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uq_restaurant_day` (`restaurant_id`, `day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- MENU CATEGORIES (Per Restaurant)
-- ============================================================
CREATE TABLE `menu_categories` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `name`          VARCHAR(100) NOT NULL,
  `description`   TEXT DEFAULT NULL,
  `image`         VARCHAR(255) DEFAULT NULL,
  `sort_order`    INT DEFAULT 0,
  `is_active`     TINYINT(1) DEFAULT 1,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON DELETE CASCADE,
  INDEX `idx_restaurant_id` (`restaurant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- MENU ITEMS
-- ============================================================
CREATE TABLE `menu_items` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `restaurant_id`  INT UNSIGNED NOT NULL,
  `category_id`    INT UNSIGNED DEFAULT NULL,
  `name`           VARCHAR(150) NOT NULL,
  `slug`           VARCHAR(180) NOT NULL,
  `description`    TEXT DEFAULT NULL,
  `image`          VARCHAR(255) DEFAULT NULL,
  `base_price`     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `discount_price` DECIMAL(10,2) DEFAULT NULL,
  `is_available`   TINYINT(1) DEFAULT 1,
  `is_veg`         TINYINT(1) DEFAULT 0,
  `is_featured`    TINYINT(1) DEFAULT 0,
  `calories`       INT DEFAULT NULL,
  `prep_time`      INT DEFAULT NULL COMMENT 'Minutes',
  `sort_order`     INT DEFAULT 0,
  `total_orders`   INT DEFAULT 0,
  `rating`         DECIMAL(3,2) DEFAULT 0.00,
  `created_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `menu_categories`(`id`) ON DELETE SET NULL,
  INDEX `idx_restaurant_id` (`restaurant_id`),
  INDEX `idx_category_id` (`category_id`),
  UNIQUE KEY `uq_restaurant_slug` (`restaurant_id`, `slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ITEM VARIANTS (sizes, options)
-- ============================================================
CREATE TABLE `item_variants` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `item_id`     INT UNSIGNED NOT NULL,
  `name`        VARCHAR(100) NOT NULL COMMENT 'e.g. Small, Medium, Large',
  `price`       DECIMAL(10,2) NOT NULL,
  `is_default`  TINYINT(1) DEFAULT 0,
  `is_active`   TINYINT(1) DEFAULT 1,
  FOREIGN KEY (`item_id`) REFERENCES `menu_items`(`id`) ON DELETE CASCADE,
  INDEX `idx_item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ITEM ADD-ONS / EXTRAS
-- ============================================================
CREATE TABLE `item_addons` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `item_id`    INT UNSIGNED NOT NULL,
  `name`       VARCHAR(100) NOT NULL,
  `price`      DECIMAL(10,2) DEFAULT 0.00,
  `is_active`  TINYINT(1) DEFAULT 1,
  FOREIGN KEY (`item_id`) REFERENCES `menu_items`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- COUPONS
-- ============================================================
CREATE TABLE `coupons` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `restaurant_id`   INT UNSIGNED DEFAULT NULL COMMENT 'NULL = platform-wide',
  `code`            VARCHAR(50) NOT NULL UNIQUE,
  `type`            ENUM('percentage','fixed','free_delivery') NOT NULL DEFAULT 'percentage',
  `value`           DECIMAL(10,2) NOT NULL,
  `min_order`       DECIMAL(10,2) DEFAULT 0.00,
  `max_discount`    DECIMAL(10,2) DEFAULT NULL,
  `usage_limit`     INT DEFAULT NULL COMMENT 'NULL = unlimited',
  `usage_per_user`  INT DEFAULT 1,
  `used_count`      INT DEFAULT 0,
  `valid_from`      TIMESTAMP NULL DEFAULT NULL,
  `valid_until`     TIMESTAMP NULL DEFAULT NULL,
  `is_active`       TINYINT(1) DEFAULT 1,
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON DELETE CASCADE,
  INDEX `idx_code` (`code`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CARTS
-- ============================================================
CREATE TABLE `carts` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`       INT UNSIGNED NOT NULL,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `item_id`       INT UNSIGNED NOT NULL,
  `variant_id`    INT UNSIGNED DEFAULT NULL,
  `addons`        JSON DEFAULT NULL,
  `quantity`      INT NOT NULL DEFAULT 1,
  `unit_price`    DECIMAL(10,2) NOT NULL,
  `notes`         VARCHAR(255) DEFAULT NULL,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`item_id`) REFERENCES `menu_items`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ORDERS
-- ============================================================
CREATE TABLE `orders` (
  `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_number`     VARCHAR(30) NOT NULL UNIQUE,
  `user_id`          INT UNSIGNED NOT NULL,
  `restaurant_id`    INT UNSIGNED NOT NULL,
  `rider_id`         INT UNSIGNED DEFAULT NULL,
  `address_id`       INT UNSIGNED DEFAULT NULL,
  `address_snapshot` JSON DEFAULT NULL COMMENT 'Copy of address at order time',
  `coupon_id`        INT UNSIGNED DEFAULT NULL,
  `status`           ENUM('pending','accepted','preparing','ready','picked_up','delivered','cancelled','refunded') NOT NULL DEFAULT 'pending',
  `payment_method`   ENUM('cash','online','wallet') DEFAULT 'cash',
  `payment_status`   ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
  `subtotal`         DECIMAL(10,2) NOT NULL,
  `delivery_fee`     DECIMAL(10,2) DEFAULT 0.00,
  `discount`         DECIMAL(10,2) DEFAULT 0.00,
  `tax`              DECIMAL(10,2) DEFAULT 0.00,
  `total`            DECIMAL(10,2) NOT NULL,
  `platform_fee`     DECIMAL(10,2) DEFAULT 0.00,
  `restaurant_payout`DECIMAL(10,2) DEFAULT 0.00,
  `prep_time`        INT DEFAULT NULL COMMENT 'Minutes - set by restaurant',
  `est_delivery_time`INT DEFAULT NULL COMMENT 'Minutes',
  `special_notes`    TEXT DEFAULT NULL,
  `cancellation_reason` TEXT DEFAULT NULL,
  `accepted_at`      TIMESTAMP NULL DEFAULT NULL,
  `prepared_at`      TIMESTAMP NULL DEFAULT NULL,
  `picked_at`        TIMESTAMP NULL DEFAULT NULL,
  `delivered_at`     TIMESTAMP NULL DEFAULT NULL,
  `alert_count`      INT DEFAULT 0 COMMENT 'Number of alerts sent to restaurant',
  `last_alert_at`    TIMESTAMP NULL DEFAULT NULL,
  `created_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`),
  FOREIGN KEY (`rider_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`address_id`) REFERENCES `addresses`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON DELETE SET NULL,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_restaurant_id` (`restaurant_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_order_number` (`order_number`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ORDER ITEMS
-- ============================================================
CREATE TABLE `order_items` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id`    INT UNSIGNED NOT NULL,
  `item_id`     INT UNSIGNED NOT NULL,
  `variant_id`  INT UNSIGNED DEFAULT NULL,
  `name`        VARCHAR(150) NOT NULL COMMENT 'Snapshot at order time',
  `variant_name`VARCHAR(100) DEFAULT NULL,
  `addons`      JSON DEFAULT NULL,
  `quantity`    INT NOT NULL DEFAULT 1,
  `unit_price`  DECIMAL(10,2) NOT NULL,
  `total_price` DECIMAL(10,2) NOT NULL,
  `notes`       VARCHAR(255) DEFAULT NULL,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`item_id`) REFERENCES `menu_items`(`id`),
  INDEX `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TRANSACTIONS
-- ============================================================
CREATE TABLE `transactions` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     INT UNSIGNED NOT NULL,
  `order_id`    INT UNSIGNED DEFAULT NULL,
  `type`        ENUM('credit','debit','refund','commission','payout') NOT NULL,
  `amount`      DECIMAL(10,2) NOT NULL,
  `balance_before` DECIMAL(10,2) NOT NULL,
  `balance_after`  DECIMAL(10,2) NOT NULL,
  `reference`   VARCHAR(100) DEFAULT NULL,
  `notes`       TEXT DEFAULT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE SET NULL,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- REVIEWS
-- ============================================================
CREATE TABLE `reviews` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id`      INT UNSIGNED NOT NULL UNIQUE,
  `user_id`       INT UNSIGNED NOT NULL,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `item_id`       INT UNSIGNED DEFAULT NULL,
  `food_rating`   TINYINT NOT NULL DEFAULT 5,
  `delivery_rating` TINYINT DEFAULT NULL,
  `comment`       TEXT DEFAULT NULL,
  `images`        JSON DEFAULT NULL,
  `is_approved`   TINYINT(1) DEFAULT 1,
  `reply`         TEXT DEFAULT NULL,
  `replied_at`    TIMESTAMP NULL DEFAULT NULL,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON DELETE CASCADE,
  INDEX `idx_restaurant_id` (`restaurant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- NOTIFICATIONS
-- ============================================================
CREATE TABLE `notifications` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     INT UNSIGNED DEFAULT NULL COMMENT 'NULL = broadcast',
  `type`        VARCHAR(60) NOT NULL COMMENT 'order_placed, order_accepted, etc.',
  `title`       VARCHAR(200) NOT NULL,
  `body`        TEXT NOT NULL,
  `data`        JSON DEFAULT NULL,
  `image`       VARCHAR(255) DEFAULT NULL,
  `is_read`     TINYINT(1) DEFAULT 0,
  `sent_push`   TINYINT(1) DEFAULT 0,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_is_read` (`is_read`),
  INDEX `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PUSH SUBSCRIPTIONS (Web Push API)
-- ============================================================
CREATE TABLE `push_subscriptions` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     INT UNSIGNED NOT NULL,
  `endpoint`    TEXT NOT NULL,
  `p256dh`      TEXT NOT NULL,
  `auth`        TEXT NOT NULL,
  `user_agent`  VARCHAR(255) DEFAULT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- DELIVERY RIDERS
-- ============================================================
CREATE TABLE `delivery_riders` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`       INT UNSIGNED NOT NULL UNIQUE,
  `vehicle_type`  ENUM('bicycle','motorcycle','car') DEFAULT 'motorcycle',
  `vehicle_number` VARCHAR(30) DEFAULT NULL,
  `id_proof`      VARCHAR(255) DEFAULT NULL,
  `is_online`     TINYINT(1) DEFAULT 0,
  `is_verified`   TINYINT(1) DEFAULT 0,
  `current_lat`   DECIMAL(10,8) DEFAULT NULL,
  `current_lng`   DECIMAL(11,8) DEFAULT NULL,
  `last_location_at` TIMESTAMP NULL DEFAULT NULL,
  `total_deliveries` INT DEFAULT 0,
  `total_earnings`   DECIMAL(10,2) DEFAULT 0.00,
  `rating`           DECIMAL(3,2) DEFAULT 0.00,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FAVORITES
-- ============================================================
CREATE TABLE `favorites` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`       INT UNSIGNED NOT NULL,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uq_user_restaurant` (`user_id`, `restaurant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BANNERS (Admin managed)
-- ============================================================
CREATE TABLE `banners` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title`       VARCHAR(150) DEFAULT NULL,
  `image`       VARCHAR(255) NOT NULL,
  `link`        VARCHAR(255) DEFAULT NULL,
  `type`        ENUM('home_slider','promo','restaurant') DEFAULT 'home_slider',
  `sort_order`  INT DEFAULT 0,
  `is_active`   TINYINT(1) DEFAULT 1,
  `starts_at`   TIMESTAMP NULL DEFAULT NULL,
  `ends_at`     TIMESTAMP NULL DEFAULT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SETTINGS
-- ============================================================
CREATE TABLE `settings` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key`        VARCHAR(100) NOT NULL UNIQUE,
  `value`      TEXT DEFAULT NULL,
  `group`      VARCHAR(60) DEFAULT 'general',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ACTIVITY LOGS
-- ============================================================
CREATE TABLE `activity_logs` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     INT UNSIGNED DEFAULT NULL,
  `action`      VARCHAR(100) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `ip`          VARCHAR(45) DEFAULT NULL,
  `user_agent`  VARCHAR(255) DEFAULT NULL,
  `data`        JSON DEFAULT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_action` (`action`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- COUPON USAGE TRACKING
-- ============================================================
CREATE TABLE `coupon_usage` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `coupon_id`  INT UNSIGNED NOT NULL,
  `user_id`    INT UNSIGNED NOT NULL,
  `order_id`   INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ORDER ALERTS QUEUE (For retry notification system)
-- ============================================================
CREATE TABLE `order_alerts` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id`      INT UNSIGNED NOT NULL,
  `restaurant_id` INT UNSIGNED NOT NULL,
  `alert_number`  INT DEFAULT 1,
  `status`        ENUM('pending','sent','acknowledged') DEFAULT 'pending',
  `next_alert_at` TIMESTAMP NOT NULL,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  INDEX `idx_next_alert` (`status`, `next_alert_at`),
  INDEX `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- SEED DEFAULT SETTINGS
-- ============================================================
INSERT INTO `settings` (`key`, `value`, `group`) VALUES
('site_name', 'FoodRush', 'general'),
('site_tagline', 'Hunger? Solved.', 'general'),
('site_email', 'hello@foodrush.com', 'general'),
('site_phone', '+92 300 0000000', 'general'),
('currency', 'PKR', 'general'),
('currency_symbol', '₨', 'general'),
('tax_rate', '5', 'billing'),
('platform_commission', '10', 'billing'),
('default_delivery_fee', '50', 'billing'),
('free_delivery_above', '500', 'billing'),
('max_delivery_radius', '20', 'general'),
('order_auto_cancel_minutes', '30', 'orders'),
('alert_repeat_interval', '30', 'notifications'),
('max_alert_retries', '10', 'notifications'),
('vapid_public_key', '', 'push'),
('vapid_private_key', '', 'push'),
('smtp_host', '', 'email'),
('smtp_port', '587', 'email'),
('smtp_user', '', 'email'),
('smtp_pass', '', 'email'),
('sms_provider', 'twilio', 'sms'),
('maintenance_mode', '0', 'general');

-- ============================================================
-- SEED RESTAURANT CATEGORIES
-- ============================================================
INSERT INTO `restaurant_categories` (`name`, `slug`, `icon`, `sort_order`) VALUES
('Burgers', 'burgers', '🍔', 1),
('Pizza', 'pizza', '🍕', 2),
('Biryani', 'biryani', '🍛', 3),
('Karahi', 'karahi', '🫕', 4),
('Sushi', 'sushi', '🍣', 5),
('Chinese', 'chinese', '🥡', 6),
('Desserts', 'desserts', '🍰', 7),
('Drinks', 'drinks', '🥤', 8),
('Shawarma', 'shawarma', '🌯', 9),
('Breakfast', 'breakfast', '🍳', 10);

-- ============================================================
-- SEED ADMIN USER (password: Admin@123)
-- ============================================================
INSERT INTO `users` (`name`, `email`, `phone`, `password`, `role`, `status`, `email_verified`) VALUES
('Super Admin', 'admin@foodrush.com', '+920000000000',
 '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active', 1);
-- Note: Default password hash above is "Admin@123" - CHANGE IN PRODUCTION