CREATE TABLE IF NOT EXISTS kitchens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hotel_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(80) NOT NULL,
    name VARCHAR(160) NOT NULL,
    type ENUM('RESTAURANT','ROOM_SERVICE','CENTRAL') NOT NULL DEFAULT 'RESTAURANT',
    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,
    UNIQUE KEY uq_kitchen_code (hotel_id, code),
    CONSTRAINT fk_kitchens_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kitchen_stations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kitchen_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(80) NOT NULL,
    name VARCHAR(160) NOT NULL,
    display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    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,
    UNIQUE KEY uq_station_code (kitchen_id, code),
    CONSTRAINT fk_stations_kitchen FOREIGN KEY (kitchen_id) REFERENCES kitchens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hotel_id BIGINT UNSIGNED NOT NULL,
    public_id CHAR(26) NOT NULL,
    kitchen_id BIGINT UNSIGNED NULL,
    code VARCHAR(80) NOT NULL,
    name VARCHAR(160) NOT NULL,
    type ENUM('RESTAURANT','CAFE','ROOM_SERVICE') NOT NULL,
    description TEXT NULL,
    logo_media_id BIGINT UNSIGNED NULL,
    display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    discoverable TINYINT(1) NOT NULL DEFAULT 1,
    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,
    UNIQUE KEY uq_restaurant_public_id (public_id),
    UNIQUE KEY uq_restaurant_code (hotel_id, code),
    INDEX idx_restaurant_hotel_status (hotel_id, status, display_order),
    CONSTRAINT fk_restaurant_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_restaurant_kitchen FOREIGN KEY (kitchen_id) REFERENCES kitchens(id) ON DELETE SET NULL,
    CONSTRAINT fk_restaurant_logo FOREIGN KEY (logo_media_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_hours (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    weekday TINYINT UNSIGNED NOT NULL COMMENT '1=Monday ... 7=Sunday',
    opens_at TIME NULL,
    closes_at TIME NULL,
    is_closed TINYINT(1) NOT NULL DEFAULT 0,
    valid_from DATE NULL,
    valid_to DATE NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_restaurant_hours_lookup (restaurant_id, weekday, valid_from, valid_to),
    CONSTRAINT fk_restaurant_hours_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    CONSTRAINT chk_restaurant_hours_weekday CHECK (weekday BETWEEN 1 AND 7)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_service_channels (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    service_type ENUM('DINE_IN','ROOM_SERVICE','TAKEAWAY','PRE_ORDER') NOT NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_restaurant_service_channel (restaurant_id, service_type),
    CONSTRAINT fk_restaurant_channels_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_tables (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    public_id CHAR(26) NOT NULL,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(100) NOT NULL,
    section VARCHAR(100) NULL,
    capacity SMALLINT UNSIGNED NOT NULL DEFAULT 2,
    minimum_capacity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    maximum_capacity SMALLINT UNSIGNED NOT NULL DEFAULT 2,
    status ENUM('AVAILABLE','RESERVED','OCCUPIED','CLEANING','OUT_OF_SERVICE') NOT NULL DEFAULT 'AVAILABLE',
    map_x DECIMAL(10,2) NULL,
    map_y DECIMAL(10,2) NULL,
    map_width DECIMAL(10,2) NULL,
    map_height DECIMAL(10,2) NULL,
    map_rotation DECIMAL(6,2) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_table_public_id (public_id),
    UNIQUE KEY uq_table_code (restaurant_id, code),
    INDEX idx_tables_restaurant_status (restaurant_id, status),
    CONSTRAINT fk_tables_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    CONSTRAINT chk_table_capacity CHECK (minimum_capacity <= capacity AND capacity <= maximum_capacity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hotel_rooms (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hotel_id BIGINT UNSIGNED NOT NULL,
    public_id CHAR(26) NOT NULL,
    room_number VARCHAR(50) NOT NULL,
    floor VARCHAR(50) NULL,
    section VARCHAR(100) NULL,
    status ENUM('ACTIVE','INACTIVE','OUT_OF_SERVICE') NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_room_public_id (public_id),
    UNIQUE KEY uq_room_number (hotel_id, room_number),
    INDEX idx_rooms_hotel_status (hotel_id, status),
    CONSTRAINT fk_rooms_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS table_qr_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    table_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    token_ciphertext TEXT NOT NULL,
    status ENUM('ACTIVE','REVOKED') NOT NULL DEFAULT 'ACTIVE',
    issued_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_table_qr_hash (token_hash),
    INDEX idx_table_qr_active (table_id, status),
    CONSTRAINT fk_table_qr_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS room_qr_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    token_ciphertext TEXT NOT NULL,
    status ENUM('ACTIVE','REVOKED') NOT NULL DEFAULT 'ACTIVE',
    issued_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_room_qr_hash (token_hash),
    INDEX idx_room_qr_active (room_id, status),
    CONSTRAINT fk_room_qr_room FOREIGN KEY (room_id) REFERENCES hotel_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS table_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    table_id BIGINT UNSIGNED NOT NULL,
    status ENUM('ACTIVE','CLOSED','EXPIRED') NOT NULL DEFAULT 'ACTIVE',
    started_at DATETIME NOT NULL,
    closed_at DATETIME NULL,
    expires_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_table_session_public (public_id),
    INDEX idx_table_session_active (table_id, status),
    CONSTRAINT fk_table_sessions_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS room_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    room_id BIGINT UNSIGNED NOT NULL,
    source ENUM('QR_DEMO','PMS','STAFF') NOT NULL DEFAULT 'QR_DEMO',
    status ENUM('ACTIVE','CHECKED_OUT','EXPIRED') NOT NULL DEFAULT 'ACTIVE',
    checkin_at DATETIME NOT NULL,
    checkout_at DATETIME NULL,
    expires_at DATETIME NULL,
    room_charge_authorized TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_room_session_public (public_id),
    INDEX idx_room_session_active (room_id, status),
    CONSTRAINT fk_room_sessions_room FOREIGN KEY (room_id) REFERENCES hotel_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    table_session_id BIGINT UNSIGNED NULL,
    room_session_id BIGINT UNSIGNED NULL,
    token_hash CHAR(64) NOT NULL,
    device_hash CHAR(64) NULL,
    last_seen_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_customer_session_public (public_id),
    UNIQUE KEY uq_customer_token_hash (token_hash),
    INDEX idx_customer_table_session (table_session_id),
    INDEX idx_customer_room_session (room_session_id),
    CONSTRAINT fk_customer_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_customer_table_session FOREIGN KEY (table_session_id) REFERENCES table_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_customer_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE CASCADE,
    CONSTRAINT chk_customer_context CHECK ((table_session_id IS NOT NULL) OR (room_session_id IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;