-- ═══════════════════════════════════════════════════════════════════════════
-- KITCHEN & ORDERS ADVANCED MODULE — Database Migration
-- ═══════════════════════════════════════════════════════════════════════════
-- Run this ONCE on your database.
-- Builds on existing: restaurant_tables, table_cart_items, billing_records
-- ═══════════════════════════════════════════════════════════════════════════

-- ──────────────────────────────────────────────────────────────────────────────
-- 1. orders — Live KOT tickets (maps to active restaurant_tables sessions)
-- ──────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orders (
    order_id     VARCHAR(60)  PRIMARY KEY,          -- e.g. TBL7-20260418-143022
    table_id     INT          NOT NULL,             -- references restaurant_tables.id
    table_number VARCHAR(50)  NOT NULL,             -- snapshot of table number
    device_id    VARCHAR(100) DEFAULT NULL,         -- source device (TAB-02, POS-01)
    waiter_id    VARCHAR(50)  DEFAULT NULL,         -- e.g. WID-104
    customer_name VARCHAR(255) DEFAULT NULL,
    pax          INT          DEFAULT NULL,
    comments     TEXT         DEFAULT NULL,
    status       ENUM(
                   'pending',
                   'preparing',
                   'ready',
                   'served',
                   'completed',
                   'cancelled'
                 ) DEFAULT 'pending',
    subtotal     DECIMAL(10, 2) DEFAULT 0.00,
    tax          DECIMAL(10, 2) DEFAULT 0.00,
    discount     DECIMAL(10, 2) DEFAULT 0.00,
    total        DECIMAL(10, 2) DEFAULT 0.00,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_orders_status (status),
    INDEX idx_orders_table_id (table_id),
    INDEX idx_orders_waiter_id (waiter_id)
);

-- ──────────────────────────────────────────────────────────────────────────────
-- 2. order_items — Snapshot of menu items for each order ticket
-- ──────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS order_items (
    id             INT          AUTO_INCREMENT PRIMARY KEY,
    order_id       VARCHAR(60)  NOT NULL,
    menu_item_id   INT          DEFAULT NULL,       -- references menu/items table
    name           VARCHAR(255) NOT NULL,           -- snapshot at time of order
    quantity       INT          NOT NULL DEFAULT 1,
    unit_price     DECIMAL(10, 2) NOT NULL,         -- snapshot at time of order
    item_discount  DECIMAL(10, 2) DEFAULT 0.00,
    is_complimentary BOOLEAN   DEFAULT FALSE,
    selected_modifiers JSON    DEFAULT NULL,        -- snapshot of modifiers
    line_total     DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    created_at     TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    INDEX idx_order_items_order_id (order_id)
);

-- ──────────────────────────────────────────────────────────────────────────────
-- 3. invoices — Settled billing records linked to orders
-- ──────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS invoices (
    invoice_id      VARCHAR(60)  PRIMARY KEY,       -- e.g. INV-20260418-143022
    order_id        VARCHAR(60)  NOT NULL,
    billing_record_id INT        DEFAULT NULL,      -- link to existing billing_records.id
    payment_method  VARCHAR(100) DEFAULT 'Cash',
    amount_paid     DECIMAL(10, 2) NOT NULL,
    settled_by      VARCHAR(100) DEFAULT NULL,      -- admin/cashier identifier
    settled_at      TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    is_split_bill   BOOLEAN     DEFAULT FALSE,
    notes           TEXT        DEFAULT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    INDEX idx_invoices_order_id (order_id),
    INDEX idx_invoices_settled_at (settled_at)
);

-- ──────────────────────────────────────────────────────────────────────────────
-- 4. order_status_log — Audit trail of every status change per order
-- ──────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS order_status_log (
    id         INT         AUTO_INCREMENT PRIMARY KEY,
    order_id   VARCHAR(60) NOT NULL,
    old_status VARCHAR(50) DEFAULT NULL,
    new_status VARCHAR(50) NOT NULL,
    changed_by VARCHAR(100) DEFAULT 'system',
    changed_at TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    INDEX idx_status_log_order_id (order_id)
);
