-- ============================================================
--  FOOD DELIVERY SYSTEM — Database Schema
--  Engine: MySQL 8+   Charset: utf8mb4
-- ============================================================

CREATE DATABASE IF NOT EXISTS food_delivery
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE food_delivery;

-- ============================================================
-- 1. ROLES & PERMISSIONS (RBAC)
-- ============================================================

CREATE TABLE roles (
    id         TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(50)  NOT NULL UNIQUE,          -- super_admin, manager, kitchen, rider, customer
    label      VARCHAR(100) NOT NULL,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id         SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL UNIQUE,          -- e.g. menu.create, order.view
    label      VARCHAR(150) NOT NULL,
    module     VARCHAR(50)  NOT NULL,                 -- menu, order, report, user, voucher
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    role_id       TINYINT  UNSIGNED NOT NULL,
    permission_id SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id)       REFERENCES roles(id)       ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 2. USERS
-- ============================================================

CREATE TABLE users (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id          TINYINT UNSIGNED NOT NULL,
    name             VARCHAR(100) NOT NULL,
    email            VARCHAR(150) NOT NULL UNIQUE,
    phone            VARCHAR(20)  NOT NULL,
    password_hash    VARCHAR(255) NOT NULL,
    profile_image    VARCHAR(255) DEFAULT NULL,
    is_active        TINYINT(1)   NOT NULL DEFAULT 1,
    email_verified   TINYINT(1)   NOT NULL DEFAULT 0,
    login_attempts   TINYINT      NOT NULL DEFAULT 0,
    locked_until     DATETIME     DEFAULT NULL,
    last_login       DATETIME     DEFAULT NULL,
    created_at       TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

-- Password reset tokens
CREATE TABLE password_resets (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    token      VARCHAR(100) NOT NULL UNIQUE,
    expires_at DATETIME     NOT NULL,
    used       TINYINT(1)   NOT NULL DEFAULT 0,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- API / Flutter JWT tokens
CREATE TABLE api_tokens (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    token      VARCHAR(512) NOT NULL UNIQUE,
    device     VARCHAR(100) DEFAULT NULL,
    expires_at DATETIME     NOT NULL,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 3. CUSTOMERS  (extended profile for customer role)
-- ============================================================

CREATE TABLE customers (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL UNIQUE,
    fcm_token  VARCHAR(255) DEFAULT NULL,             -- Firebase push token
    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
) ENGINE=InnoDB;

CREATE TABLE customer_addresses (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT UNSIGNED NOT NULL,
    label        VARCHAR(50)  NOT NULL DEFAULT 'Home', -- Home, Office, Other
    address_line VARCHAR(255) NOT NULL,
    city         VARCHAR(100) NOT NULL,
    latitude     DECIMAL(10,8) DEFAULT NULL,
    longitude    DECIMAL(11,8) DEFAULT NULL,
    is_default   TINYINT(1)   NOT NULL DEFAULT 0,
    created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 4. MENU — CATEGORIES
-- ============================================================

CREATE TABLE categories (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    slug        VARCHAR(120) NOT NULL UNIQUE,
    icon_image  VARCHAR(255) DEFAULT NULL,            -- path to uploaded icon
    sort_order  SMALLINT     NOT NULL DEFAULT 0,
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    created_by  INT UNSIGNED DEFAULT NULL,
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 5. MENU — ITEMS
-- ============================================================

CREATE TABLE menu_items (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id   INT UNSIGNED NOT NULL,
    name          VARCHAR(150) NOT NULL,
    slug          VARCHAR(170) NOT NULL UNIQUE,
    description   TEXT         DEFAULT NULL,
    base_price    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    item_image    VARCHAR(255)  DEFAULT NULL,
    is_available  TINYINT(1)   NOT NULL DEFAULT 1,
    sort_order    SMALLINT     NOT NULL DEFAULT 0,
    created_by    INT UNSIGNED DEFAULT NULL,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by)  REFERENCES users(id)      ON DELETE SET NULL
) ENGINE=InnoDB;

-- Choices / variants per item  (e.g. Size: Small/Medium/Large)
CREATE TABLE item_choice_groups (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_item_id INT UNSIGNED NOT NULL,
    name         VARCHAR(100) NOT NULL,               -- "Size", "Crust", "Extras"
    is_required  TINYINT(1)   NOT NULL DEFAULT 1,     -- must pick one?
    min_select   TINYINT      NOT NULL DEFAULT 1,
    max_select   TINYINT      NOT NULL DEFAULT 1,
    sort_order   SMALLINT     NOT NULL DEFAULT 0,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE item_choices (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    choice_group_id INT UNSIGNED   NOT NULL,
    label           VARCHAR(100)   NOT NULL,           -- "Small", "Medium", "Large"
    price_modifier  DECIMAL(10,2)  NOT NULL DEFAULT 0.00, -- added to base_price
    is_default      TINYINT(1)     NOT NULL DEFAULT 0,
    sort_order      SMALLINT       NOT NULL DEFAULT 0,
    FOREIGN KEY (choice_group_id) REFERENCES item_choice_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 6. PROMOTIONS — DISCOUNTS, DEALS, VOUCHERS
-- ============================================================

-- Item/category level discount offers
CREATE TABLE discount_offers (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title           VARCHAR(150)  NOT NULL,
    discount_type   ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage',
    discount_value  DECIMAL(10,2) NOT NULL,
    applies_to      ENUM('item','category','order') NOT NULL DEFAULT 'item',
    target_id       INT UNSIGNED  DEFAULT NULL,        -- item_id or category_id (NULL = whole order)
    min_order_amt   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    max_discount    DECIMAL(10,2) DEFAULT NULL,        -- cap for percentage type (NULL = no cap)
    starts_at       DATETIME      NOT NULL,
    expires_at      DATETIME      NOT NULL,
    is_active       TINYINT(1)    NOT NULL DEFAULT 1,
    created_by      INT UNSIGNED  DEFAULT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Combo deals
CREATE TABLE deals (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title         VARCHAR(150)  NOT NULL,
    description   TEXT          DEFAULT NULL,
    deal_price    DECIMAL(10,2) NOT NULL,
    deal_image    VARCHAR(255)  DEFAULT NULL,
    starts_at     DATETIME      NOT NULL,
    expires_at    DATETIME      NOT NULL,
    is_active     TINYINT(1)    NOT NULL DEFAULT 1,
    created_by    INT UNSIGNED  DEFAULT NULL,
    created_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE deal_items (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    deal_id      INT UNSIGNED NOT NULL,
    menu_item_id INT UNSIGNED NOT NULL,
    quantity     TINYINT      NOT NULL DEFAULT 1,
    FOREIGN KEY (deal_id)      REFERENCES deals(id)      ON DELETE CASCADE,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Voucher / coupon codes
CREATE TABLE vouchers (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code            VARCHAR(30)   NOT NULL UNIQUE,
    description     VARCHAR(255)  DEFAULT NULL,
    discount_type   ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage',
    discount_value  DECIMAL(10,2) NOT NULL,
    min_order_amt   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    max_discount    DECIMAL(10,2) DEFAULT NULL,        -- cap for percentage type
    usage_limit     INT UNSIGNED  DEFAULT NULL,        -- NULL = unlimited
    used_count      INT UNSIGNED  NOT NULL DEFAULT 0,
    per_user_limit  TINYINT       NOT NULL DEFAULT 1,
    starts_at       DATETIME      NOT NULL,
    expires_at      DATETIME      NOT NULL,
    is_active       TINYINT(1)    NOT NULL DEFAULT 1,
    created_by      INT UNSIGNED  DEFAULT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE voucher_usages (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    voucher_id  INT UNSIGNED NOT NULL,
    user_id     INT UNSIGNED NOT NULL,
    order_id    INT UNSIGNED DEFAULT NULL,             -- linked after order placed
    used_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (voucher_id) REFERENCES vouchers(id)  ON DELETE CASCADE,
    FOREIGN KEY (user_id)    REFERENCES users(id)     ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 7. ORDERS
-- ============================================================

CREATE TABLE orders (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_number     VARCHAR(20)   NOT NULL UNIQUE,   -- e.g. ORD-20240619-0001
    customer_id      INT UNSIGNED  NOT NULL,
    address_id       INT UNSIGNED  DEFAULT NULL,
    delivery_address VARCHAR(300)  DEFAULT NULL,       -- snapshot at order time
    rider_id         INT UNSIGNED  DEFAULT NULL,
    status           ENUM(
                         'pending',
                         'confirmed',
                         'cooking',
                         'ready',
                         'picked_up',
                         'on_the_way',
                         'delivered',
                         'cancelled'
                     ) NOT NULL DEFAULT 'pending',
    payment_method   ENUM('cash','online','card') NOT NULL DEFAULT 'cash',
    payment_status   ENUM('unpaid','paid','refunded') NOT NULL DEFAULT 'unpaid',
    subtotal         DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    voucher_id       INT UNSIGNED  DEFAULT NULL,
    voucher_discount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    delivery_fee     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_amount     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    special_notes    TEXT          DEFAULT NULL,
    cancelled_reason VARCHAR(255)  DEFAULT NULL,
    placed_at        TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
    FOREIGN KEY (address_id)  REFERENCES customer_addresses(id) ON DELETE SET NULL,
    FOREIGN KEY (rider_id)    REFERENCES users(id)     ON DELETE SET NULL,
    FOREIGN KEY (voucher_id)  REFERENCES vouchers(id)  ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id              INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
    order_id        INT UNSIGNED  NOT NULL,
    menu_item_id    INT UNSIGNED  DEFAULT NULL,        -- NULL if item deleted later
    deal_id         INT UNSIGNED  DEFAULT NULL,        -- set if item came from a deal
    item_name       VARCHAR(150)  NOT NULL,            -- snapshot
    item_image      VARCHAR(255)  DEFAULT NULL,        -- snapshot
    choices_summary VARCHAR(255)  DEFAULT NULL,        -- e.g. "Size: Large, Crust: Thin"
    unit_price      DECIMAL(10,2) NOT NULL,
    quantity        SMALLINT      NOT NULL DEFAULT 1,
    item_discount   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    line_total      DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)     REFERENCES orders(id)     ON DELETE CASCADE,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE SET NULL,
    FOREIGN KEY (deal_id)      REFERENCES deals(id)      ON DELETE SET NULL
) ENGINE=InnoDB;

-- Choices selected per order item (detailed)
CREATE TABLE order_item_choices (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_item_id INT UNSIGNED  NOT NULL,
    group_name    VARCHAR(100)  NOT NULL,              -- "Size"
    choice_label  VARCHAR(100)  NOT NULL,              -- "Large"
    price_modifier DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Full status history log
CREATE TABLE order_status_log (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id   INT UNSIGNED NOT NULL,
    status     VARCHAR(30)  NOT NULL,
    note       VARCHAR(255) DEFAULT NULL,
    changed_by INT UNSIGNED DEFAULT NULL,
    changed_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id)   REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id)  ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 8. INVOICES
-- ============================================================

CREATE TABLE invoices (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id       INT UNSIGNED  NOT NULL UNIQUE,
    invoice_number VARCHAR(20)   NOT NULL UNIQUE,      -- INV-20240619-0001
    issued_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    pdf_path       VARCHAR(255)  DEFAULT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 9. NOTIFICATIONS
-- ============================================================

CREATE TABLE notifications (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED  NOT NULL,
    type        VARCHAR(50)   NOT NULL,                -- new_order, status_update, promo
    title       VARCHAR(150)  NOT NULL,
    body        TEXT          DEFAULT NULL,
    data        JSON          DEFAULT NULL,            -- extra payload
    is_read     TINYINT(1)    NOT NULL DEFAULT 0,
    sent_at     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 10. SETTINGS
-- ============================================================

CREATE TABLE settings (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    key_name    VARCHAR(100) NOT NULL UNIQUE,
    value       TEXT         DEFAULT NULL,
    updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Roles
INSERT INTO roles (name, label) VALUES
('super_admin', 'Super Admin'),
('manager',     'Manager'),
('kitchen',     'Kitchen Staff'),
('rider',       'Rider'),
('customer',    'Customer');

-- Core permissions
INSERT INTO permissions (name, label, module) VALUES
-- User management
('user.view',        'View Users',           'user'),
('user.create',      'Create User',          'user'),
('user.edit',        'Edit User',            'user'),
('user.delete',      'Delete User',          'user'),
-- Menu
('menu.view',        'View Menu',            'menu'),
('menu.create',      'Create Menu Item',     'menu'),
('menu.edit',        'Edit Menu Item',       'menu'),
('menu.delete',      'Delete Menu Item',     'menu'),
-- Category
('category.view',    'View Categories',      'menu'),
('category.create',  'Create Category',      'menu'),
('category.edit',    'Edit Category',        'menu'),
('category.delete',  'Delete Category',      'menu'),
-- Orders
('order.view',       'View Orders',          'order'),
('order.manage',     'Manage Order Status',  'order'),
('order.cancel',     'Cancel Order',         'order'),
-- Vouchers / Deals
('voucher.view',     'View Vouchers',        'voucher'),
('voucher.create',   'Create Voucher',       'voucher'),
('voucher.edit',     'Edit Voucher',         'voucher'),
('voucher.delete',   'Delete Voucher',       'voucher'),
('deal.view',        'View Deals',           'deal'),
('deal.create',      'Create Deal',          'deal'),
('deal.edit',        'Edit Deal',            'deal'),
('deal.delete',      'Delete Deal',          'deal'),
-- Reports
('report.view',      'View Reports',         'report'),
('report.export',    'Export Reports',       'report'),
-- Settings
('settings.manage',  'Manage Settings',      'settings');

-- Assign ALL permissions to super_admin (role id=1)
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

-- Manager: everything except user.delete and settings.manage
INSERT INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions
WHERE name NOT IN ('user.delete','settings.manage');

-- Kitchen: view orders + manage status only
INSERT INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions
WHERE name IN ('order.view','order.manage','menu.view','category.view');

-- Rider: view and update own orders
INSERT INTO role_permissions (role_id, permission_id)
SELECT 4, id FROM permissions
WHERE name IN ('order.view','order.manage');

-- Default settings
INSERT INTO settings (key_name, value) VALUES
('restaurant_name',      'My Restaurant'),
('restaurant_phone',     ''),
('restaurant_address',   ''),
('restaurant_logo',      ''),
('currency_symbol',      'Rs.'),
('tax_rate',             '0'),
('delivery_fee',         '0'),
('order_alert_sound',    '1'),
('min_order_amount',     '0'),
('max_image_size_kb',    '2048'),
('app_maintenance_mode', '0');

-- Default super admin  (password: Admin@1234)
INSERT INTO users (role_id, name, email, phone, password_hash, is_active, email_verified)
VALUES (1, 'Super Admin', 'admin@restaurant.com', '0300-0000000',
        '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 1);
