-- ============================================================
--  ORDER TABLES OPTIMISATION
--  Safe to run on a live database — all changes are additive
--  (new indexes, column type shrinks, ROW_FORMAT).
--  No columns are dropped or renamed.
--  Run once in phpMyAdmin on database: food_delivery
-- ============================================================

-- ── 1. orders ─────────────────────────────────────────────────────────────────

-- customer_id is NOT NULL in schema but was set to allow NULL in practice
-- (guest orders). Allow NULL safely.
ALTER TABLE orders
    MODIFY COLUMN customer_id INT UNSIGNED DEFAULT NULL;

-- Shrink money columns: DECIMAL(10,2) → DECIMAL(8,2)
-- Max value = 999,999.99 — more than enough for any order total.
ALTER TABLE orders
    MODIFY COLUMN subtotal         DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    MODIFY COLUMN discount_amount  DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    MODIFY COLUMN voucher_discount DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    MODIFY COLUMN delivery_fee     DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    MODIFY COLUMN tax_amount       DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    MODIFY COLUMN total_amount     DECIMAL(8,2) NOT NULL DEFAULT 0.00;

-- Shrink GPS columns: DECIMAL(10,7) is 8 bytes; FLOAT is 4 bytes and
-- accurate to ~1 m — good enough for delivery routing.
ALTER TABLE orders
    MODIFY COLUMN delivery_lat FLOAT DEFAULT NULL,
    MODIFY COLUMN delivery_lng FLOAT DEFAULT NULL;

-- Compress row storage (saves ~20-30 % on text-heavy rows)
ALTER TABLE orders ROW_FORMAT=COMPRESSED;

-- ── Indexes on orders ─────────────────────────────────────────────────────────
-- Admin order list: filter by status, sort by placed_at
ALTER TABLE orders
    ADD INDEX IF NOT EXISTS idx_orders_status_placed  (status, placed_at DESC),
    ADD INDEX IF NOT EXISTS idx_orders_customer       (customer_id, placed_at DESC),
    ADD INDEX IF NOT EXISTS idx_orders_rider          (rider_id, status),
    ADD INDEX IF NOT EXISTS idx_orders_placed_at      (placed_at DESC);


-- ── 2. order_items ────────────────────────────────────────────────────────────

-- Shrink money columns
ALTER TABLE order_items
    MODIFY COLUMN unit_price     DECIMAL(8,2) NOT NULL,
    MODIFY COLUMN item_discount  DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    MODIFY COLUMN line_total     DECIMAL(8,2) NOT NULL;

-- quantity is max 99 in practice — TINYINT UNSIGNED (0-255) saves 1 byte/row
ALTER TABLE order_items
    MODIFY COLUMN quantity TINYINT UNSIGNED NOT NULL DEFAULT 1;

ALTER TABLE order_items ROW_FORMAT=COMPRESSED;

-- Covering index: fetch all items for an order without hitting the main row
ALTER TABLE order_items
    ADD INDEX IF NOT EXISTS idx_oi_order (order_id, id);


-- ── 3. order_item_choices ─────────────────────────────────────────────────────

ALTER TABLE order_item_choices
    MODIFY COLUMN price_modifier DECIMAL(8,2) NOT NULL DEFAULT 0.00;

ALTER TABLE order_item_choices
    ADD INDEX IF NOT EXISTS idx_oic_item (order_item_id);


-- ── 4. order_status_log ───────────────────────────────────────────────────────

-- status column: VARCHAR(30) → ENUM saves ~10 bytes/row and enforces values
ALTER TABLE order_status_log
    MODIFY COLUMN status ENUM(
        'pending','confirmed','cooking','ready',
        'picked_up','on_the_way','delivered','cancelled'
    ) NOT NULL;

-- Most common query: fetch log for one order sorted by time
ALTER TABLE order_status_log
    ADD INDEX IF NOT EXISTS idx_osl_order_time (order_id, changed_at DESC);


-- ── 5. users (rider GPS columns) ─────────────────────────────────────────────

-- GPS accuracy to 1 m is enough — FLOAT (4 bytes) vs DECIMAL(10,7) (5 bytes)
ALTER TABLE users
    MODIFY COLUMN current_lat FLOAT DEFAULT NULL,
    MODIFY COLUMN current_lng FLOAT DEFAULT NULL;


-- ── 6. Analyse tables so the query optimiser uses the new indexes ─────────────
ANALYZE TABLE orders;
ANALYZE TABLE order_items;
ANALYZE TABLE order_item_choices;
ANALYZE TABLE order_status_log;