-- ================================================================================================
-- DATABASE ALTERATIONS FOR STATUS ALIGNMENT
-- Run these commands manually in your MySQL/MariaDB database.
-- ================================================================================================

-- 1. Update 'orders' table status column
-- We use VARCHAR if you prefer, but if it's an ENUM, we need to expand it.
-- This command expands the ENUM to include all requested statuses.
ALTER TABLE `orders` 
MODIFY COLUMN `status` ENUM(
    'Running', 
    'Pending', 
    'RunningKOT', 
    'Preparing', 
    'Printed', 
    'Ready', 
    'Paid', 
    'Served', 
    'completed', 
    'cancelled'
) NOT NULL DEFAULT 'Running';

-- 2. Update 'kot_master' table status column
ALTER TABLE `kot_master` 
MODIFY COLUMN `status` ENUM(
    'Running', 
    'Pending', 
    'RunningKOT', 
    'Preparing', 
    'Printed', 
    'Ready', 
    'Paid', 
    'Served'
) NOT NULL DEFAULT 'Pending';

-- 3. Update 'restaurant_tables' table status column (to ensure consistency)
ALTER TABLE `restaurant_tables` 
MODIFY COLUMN `status` ENUM(
    'Blank',
    'Running', 
    'Pending', 
    'RunningKOT', 
    'Preparing', 
    'Printed', 
    'Ready', 
    'Paid', 
    'Served'
) NOT NULL DEFAULT 'Blank';

-- 4. (Optional) Migrate existing data if needed
-- UPDATE `orders` SET `status` = 'Running' WHERE `status` = 'pending';
-- UPDATE `orders` SET `status` = 'Preparing' WHERE `status` = 'preparing';
-- UPDATE `orders` SET `status` = 'Ready' WHERE `status` = 'ready';
-- UPDATE `orders` SET `status` = 'Served' WHERE `status` = 'served';

-- UPDATE `kot_master` SET `status` = 'Pending' WHERE `status` = 'pending';
-- UPDATE `kot_master` SET `status` = 'Preparing' WHERE `status` = 'preparing';
-- UPDATE `kot_master` SET `status` = 'Ready' WHERE `status` = 'ready';
-- UPDATE `kot_master` SET `status` = 'Served' WHERE `status` = 'served';

-- 5. Add parcel_charges to menu_items table
ALTER TABLE `menu_items` 
ADD COLUMN `parcel_charges` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `sgst_rate`;