-- ================================================================================================
-- KDS (Kitchen Display System) — DATABASE SCHEMA (FIXED VERSION)
-- Sweet Heart Hotel POS
-- ================================================================================================

-- ------------------------------------------------------------------------------------------------
-- 1. orders table update
-- ------------------------------------------------------------------------------------------------

-- MariaDB supports IF NOT EXISTS for ADD COLUMN
ALTER TABLE `orders` 
ADD COLUMN IF NOT EXISTS `arrival_time` DATETIME NULL DEFAULT NULL;

-- ------------------------------------------------------------------------------------------------
-- 2. kot_master — one row per "Send to Kitchen" action
-- ------------------------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `kot_master` (
    `id`            INT NOT NULL AUTO_INCREMENT,
    `order_id`      VARCHAR(60) NOT NULL,
    `status`        ENUM('pending', 'preparing', 'ready', 'served') NOT NULL DEFAULT 'pending',
    `is_printed`    TINYINT(1) NOT NULL DEFAULT 0,
    `request_id`    VARCHAR(100) NULL DEFAULT NULL,
    `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `started_at`    DATETIME NULL DEFAULT NULL,
    `completed_at`  DATETIME NULL DEFAULT NULL,
    `served_at`     DATETIME NULL DEFAULT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_kot_request_id` (`request_id`),
    INDEX `idx_kot_status` (`status`),
    INDEX `idx_kot_order_id` (`order_id`),
    INDEX `idx_kot_created` (`created_at`),

    CONSTRAINT `fk_kot_master_order` 
        FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------------------------------------------
-- 3. kot_items — individual line items inside a KOT
-- ------------------------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `kot_items` (
    `id`                INT NOT NULL AUTO_INCREMENT,
    `kot_id`            INT NOT NULL,
    `menu_item_id`      INT NULL DEFAULT NULL,
    `name`              VARCHAR(255) NOT NULL,
    `quantity`          INT NOT NULL DEFAULT 1,
    `portion`           VARCHAR(50) NULL DEFAULT NULL,
    `modifiers`         TEXT NULL DEFAULT NULL,
    `is_done`           TINYINT(1) NOT NULL DEFAULT 0,
    `is_complimentary`  TINYINT(1) NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    INDEX `idx_ki_kot_id` (`kot_id`),
    INDEX `idx_ki_menu_item_id` (`menu_item_id`),
    INDEX `idx_ki_is_done` (`is_done`),

    CONSTRAINT `fk_kot_items_kot` 
        FOREIGN KEY (`kot_id`) REFERENCES `kot_master` (`id`) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
) ENGINE=InnoDB;