-- ═══════════════════════════════════════════════════════════════════════════
-- Migration: v2_menu_hierarchy.sql
-- Description: Upgrade from flat Category→Item to Category→Subcategory→Item
-- Run each STEP in order. Safe to run on a fresh DB or an existing one.
-- ═══════════════════════════════════════════════════════════════════════════


-- ───────────────────────────────────────────────────────────────────────────
-- STEP 1: Create menu_subcategories table (middle level of hierarchy)
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS menu_subcategories (
    id                INT          AUTO_INCREMENT PRIMARY KEY,
    category_id       INT          NOT NULL,
    sub_category_name VARCHAR(100) NOT NULL,
    status            BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at        TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_subcat_category
        FOREIGN KEY (category_id)
        REFERENCES menu_categories(id)
        ON DELETE CASCADE
);


-- ───────────────────────────────────────────────────────────────────────────
-- STEP 2: Add new columns to menu_items
--         sub_category_id  → FK link to subcategory
--         short_code       → Short identifier (e.g. "vcb")
--         base_price       → Temporary price column (will be dropped in Step 7)
--         diet_type        → Replaces boolean `is_veg` with veg/non-veg/egg
--         modifiers        → JSON array of modifier groups
-- ───────────────────────────────────────────────────────────────────────────
ALTER TABLE menu_items
    ADD COLUMN sub_category_id INT          DEFAULT NULL     AFTER category_id,
    ADD COLUMN short_code      VARCHAR(50)  DEFAULT NULL     AFTER item_code,
    ADD COLUMN base_price      DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER price,
    ADD COLUMN diet_type       ENUM('veg','non-veg','egg') NOT NULL DEFAULT 'veg' AFTER is_veg,
    ADD COLUMN modifiers       JSON         DEFAULT NULL     AFTER rating;


-- ───────────────────────────────────────────────────────────────────────────
-- STEP 3: Add FK constraint for sub_category_id
--         ON DELETE SET NULL → deleting a subcategory unlinks items (safe)
-- ───────────────────────────────────────────────────────────────────────────
ALTER TABLE menu_items
    ADD CONSTRAINT fk_item_subcategory
        FOREIGN KEY (sub_category_id)
        REFERENCES menu_subcategories(id)
        ON DELETE SET NULL;


-- ───────────────────────────────────────────────────────────────────────────
-- STEP 4: Migrate data — sync old columns to new columns
--         4a. Copy `price` → `base_price` for all existing rows
--         4b. Derive `diet_type` from boolean `is_veg`
--             is_veg = true  → 'veg'
--             is_veg = false → 'non-veg'
-- ───────────────────────────────────────────────────────────────────────────
UPDATE menu_items
    SET base_price = price
    WHERE base_price = 0.00 OR base_price IS NULL;

UPDATE menu_items
    SET diet_type = CASE
        WHEN is_veg = 1 THEN 'veg'
        ELSE 'non-veg'
    END;


-- ───────────────────────────────────────────────────────────────────────────
-- STEP 5: Drop old/unused columns from menu_items
--         `price`  → replaced by `base_price`
--         `is_veg` → replaced by `diet_type`
-- ───────────────────────────────────────────────────────────────────────────
ALTER TABLE menu_items
    DROP COLUMN price,
    DROP COLUMN is_veg;


-- ───────────────────────────────────────────────────────────────────────────
-- STEP 6: Drop unused columns from menu_categories
--         `description` → removed from API and business logic, no longer needed
-- ───────────────────────────────────────────────────────────────────────────
ALTER TABLE menu_categories
    DROP COLUMN description;


-- ───────────────────────────────────────────────────────────────────────────
-- STEP 7: Clean up old price columns from menu_items
--         Rename `base_price` → `price` (single clean selling price column)
--         Drop `discount_price` → removed (no longer used)
--         Drop `stock`          → removed (no longer tracked)
-- ───────────────────────────────────────────────────────────────────────────
ALTER TABLE menu_items
    CHANGE COLUMN base_price price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    DROP COLUMN discount_price,
    DROP COLUMN stock;


-- ───────────────────────────────────────────────────────────────────────────
-- STEP 8: Verification queries (run to confirm the migration is correct)
-- ───────────────────────────────────────────────────────────────────────────
-- DESCRIBE menu_subcategories;
-- DESCRIBE menu_items;
-- SELECT id, item_name, price, diet_type, sub_category_id, modifiers FROM menu_items LIMIT 10;
