CREATE TABLE IF NOT EXISTS kitchen_devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kitchen_id BIGINT UNSIGNED NOT NULL,
    station_id BIGINT UNSIGNED NULL,
    name VARCHAR(160) NOT NULL,
    device_token_hash CHAR(64) NULL,
    last_seen_at DATETIME NULL,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_kitchen_devices (kitchen_id,station_id,status),
    CONSTRAINT fk_kitchen_devices_kitchen FOREIGN KEY (kitchen_id) REFERENCES kitchens(id) ON DELETE CASCADE,
    CONSTRAINT fk_kitchen_devices_station FOREIGN KEY (station_id) REFERENCES kitchen_stations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kitchen_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    kitchen_id BIGINT UNSIGNED NOT NULL,
    station_id BIGINT UNSIGNED NULL,
    ticket_number VARCHAR(60) NOT NULL,
    status ENUM('NEW','ACCEPTED','PREPARING','DELAYED','READY','SERVED','CANCELLED') NOT NULL DEFAULT 'NEW',
    delayed_from_status ENUM('NEW','ACCEPTED','PREPARING') NULL,
    delay_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    delay_reason VARCHAR(255) NULL,
    accepted_at DATETIME NULL,
    preparing_at DATETIME NULL,
    ready_at DATETIME NULL,
    served_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    delayed_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_kitchen_order_public (public_id),
    UNIQUE KEY uq_kitchen_ticket_number (ticket_number),
    UNIQUE KEY uq_kitchen_order_route (order_id,kitchen_id,station_id),
    INDEX idx_kds_queue (kitchen_id,station_id,status,created_at),
    INDEX idx_kitchen_order_order (order_id,status),
    CONSTRAINT fk_kitchen_orders_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_kitchen_orders_kitchen FOREIGN KEY (kitchen_id) REFERENCES kitchens(id) ON DELETE RESTRICT,
    CONSTRAINT fk_kitchen_orders_station FOREIGN KEY (station_id) REFERENCES kitchen_stations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kitchen_order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kitchen_order_id BIGINT UNSIGNED NOT NULL,
    order_item_id BIGINT UNSIGNED NOT NULL,
    quantity SMALLINT UNSIGNED NOT NULL,
    status ENUM('NEW','ACCEPTED','PREPARING','READY','SERVED','CANCELLED') NOT NULL DEFAULT 'NEW',
    requested_time DATETIME NULL,
    started_at DATETIME NULL,
    ready_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_kitchen_order_item (order_item_id),
    INDEX idx_kitchen_items_ticket (kitchen_order_id,status),
    CONSTRAINT fk_kitchen_order_items_ticket FOREIGN KEY (kitchen_order_id) REFERENCES kitchen_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_kitchen_order_items_order_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kitchen_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kitchen_order_id BIGINT UNSIGNED NOT NULL,
    old_status VARCHAR(40) NULL,
    new_status VARCHAR(40) NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    notes VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_kitchen_history (kitchen_order_id,created_at),
    CONSTRAINT fk_kitchen_history_ticket FOREIGN KEY (kitchen_order_id) REFERENCES kitchen_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_kitchen_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    customer_session_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    type VARCHAR(60) NOT NULL,
    channel ENUM('IN_APP','KDS','EMAIL','SMS','WHATSAPP','PUSH') NOT NULL DEFAULT 'IN_APP',
    title VARCHAR(180) NOT NULL,
    message VARCHAR(500) NOT NULL,
    data_json JSON NULL,
    status ENUM('PENDING','DELIVERED','READ','FAILED') NOT NULL DEFAULT 'DELIVERED',
    read_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_notification_public (public_id),
    INDEX idx_notification_customer (customer_session_id,status,created_at),
    INDEX idx_notification_user (user_id,status,created_at),
    INDEX idx_notification_hotel_type (hotel_id,type,created_at),
    CONSTRAINT fk_notifications_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_notifications_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions (code,name) VALUES
('kds.view','View kitchen display system'),
('kds.manage','Manage kitchen ticket status'),
('kitchen.manage','Manage kitchen configuration')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN') AND p.code IN ('kds.view','kds.manage','kitchen.manage');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code='KITCHEN_MANAGER' AND p.code IN ('kds.view','kds.manage','kitchen.manage','orders.view');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code='KITCHEN_STAFF' AND p.code IN ('kds.view','kds.manage','orders.view');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER','READ_ONLY_MANAGER') AND p.code='kds.view';