CREATE TABLE IF NOT EXISTS carts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    customer_session_id BIGINT UNSIGNED NOT NULL,
    table_session_id BIGINT UNSIGNED NULL,
    room_session_id BIGINT UNSIGNED NULL,
    service_type ENUM('DINE_IN','ROOM_SERVICE','TAKEAWAY','PRE_ORDER') NOT NULL,
    status ENUM('ACTIVE','ORDERED','ABANDONED') 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_cart_public_id (public_id),
    INDEX idx_cart_customer_status (customer_session_id,status,updated_at),
    INDEX idx_cart_context (table_session_id,room_session_id,status),
    CONSTRAINT fk_carts_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_carts_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE RESTRICT,
    CONSTRAINT fk_carts_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_carts_table_session FOREIGN KEY (table_session_id) REFERENCES table_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_carts_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE CASCADE,
    CONSTRAINT chk_cart_context CHECK ((table_session_id IS NOT NULL) OR (room_session_id IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cart_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    cart_id BIGINT UNSIGNED NOT NULL,
    menu_item_id BIGINT UNSIGNED NOT NULL,
    variant_id BIGINT UNSIGNED NULL,
    variant_code_snapshot VARCHAR(80) NULL,
    flavour_id BIGINT UNSIGNED NULL,
    flavour_code_snapshot VARCHAR(80) NULL,
    quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    special_instruction VARCHAR(500) NULL,
    requested_course VARCHAR(40) NULL,
    requested_serving_time 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_cart_item_public_id (public_id),
    INDEX idx_cart_items_cart (cart_id,created_at),
    CONSTRAINT fk_cart_items_cart FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    CONSTRAINT fk_cart_items_menu FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_cart_items_variant FOREIGN KEY (variant_id) REFERENCES menu_item_variants(id) ON DELETE SET NULL,
    CONSTRAINT fk_cart_items_flavour FOREIGN KEY (flavour_id) REFERENCES menu_item_flavours(id) ON DELETE SET NULL,
    CONSTRAINT chk_cart_item_quantity CHECK (quantity BETWEEN 1 AND 50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cart_item_addons (
    cart_item_id BIGINT UNSIGNED NOT NULL,
    addon_id BIGINT UNSIGNED NOT NULL,
    quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (cart_item_id,addon_id),
    CONSTRAINT fk_cart_item_addons_item FOREIGN KEY (cart_item_id) REFERENCES cart_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_cart_item_addons_addon FOREIGN KEY (addon_id) REFERENCES addons(id) ON DELETE RESTRICT,
    CONSTRAINT chk_cart_addon_quantity CHECK (quantity BETWEEN 1 AND 20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    order_number VARCHAR(40) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    table_session_id BIGINT UNSIGNED NULL,
    room_session_id BIGINT UNSIGNED NULL,
    customer_session_id BIGINT UNSIGNED NOT NULL,
    reservation_id BIGINT UNSIGNED NULL COMMENT 'Foreign key added when reservation module is installed',
    service_type ENUM('DINE_IN','ROOM_SERVICE','TAKEAWAY','PRE_ORDER') NOT NULL,
    status ENUM('RECEIVED','ACCEPTED','PREPARING','READY','SERVED','CANCELLED') NOT NULL DEFAULT 'RECEIVED',
    currency CHAR(3) NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    requested_time DATETIME NULL,
    estimated_ready_time DATETIME NULL,
    actual_ready_time DATETIME NULL,
    notes VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_order_public_id (public_id),
    UNIQUE KEY uq_order_number (hotel_id,order_number),
    INDEX idx_orders_hotel_status (hotel_id,status,created_at),
    INDEX idx_orders_restaurant_status (restaurant_id,status,created_at),
    INDEX idx_orders_table_session (table_session_id,created_at),
    INDEX idx_orders_room_session (room_session_id,created_at),
    INDEX idx_orders_customer (customer_session_id,created_at),
    CONSTRAINT fk_orders_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE RESTRICT,
    CONSTRAINT fk_orders_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE RESTRICT,
    CONSTRAINT fk_orders_table_session FOREIGN KEY (table_session_id) REFERENCES table_sessions(id) ON DELETE SET NULL,
    CONSTRAINT fk_orders_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE SET NULL,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    menu_item_id BIGINT UNSIGNED NULL,
    item_name_snapshot VARCHAR(180) NOT NULL,
    item_description_snapshot TEXT NULL,
    price_snapshot DECIMAL(12,2) NOT NULL,
    tax_snapshot DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
    quantity SMALLINT UNSIGNED NOT NULL,
    variant VARCHAR(120) NULL,
    variant_price_snapshot DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    flavour VARCHAR(120) NULL,
    flavour_price_snapshot DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    unit_subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    unit_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    unit_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    line_subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    line_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    special_instruction VARCHAR(500) NULL,
    preparation_time SMALLINT UNSIGNED NULL,
    requested_course VARCHAR(40) NULL,
    requested_serving_time DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_items_order (order_id,id),
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_menu FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_item_addons (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_item_id BIGINT UNSIGNED NOT NULL,
    addon_id BIGINT UNSIGNED NULL,
    addon_name_snapshot VARCHAR(160) NOT NULL,
    price_snapshot DECIMAL(12,2) NOT NULL,
    tax_snapshot DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
    quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_item_addons_item (order_item_id),
    CONSTRAINT fk_order_item_addons_order_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_item_addons_addon FOREIGN KEY (addon_id) REFERENCES addons(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_courses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    course_code VARCHAR(40) NOT NULL,
    sequence SMALLINT UNSIGNED NOT NULL DEFAULT 10,
    requested_serving_time DATETIME NULL,
    status ENUM('PENDING','RELEASED','SERVED','CANCELLED') NOT NULL DEFAULT 'PENDING',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_order_course (order_id,course_code),
    CONSTRAINT fk_order_courses_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    old_status VARCHAR(40) NULL,
    new_status VARCHAR(40) NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    customer_session_id BIGINT UNSIGNED NULL,
    notes VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_status_history (order_id,created_at),
    CONSTRAINT fk_order_history_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_order_history_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS idempotency_keys (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hotel_id BIGINT UNSIGNED NOT NULL,
    scope VARCHAR(80) NOT NULL,
    idempotency_key_hash CHAR(64) NOT NULL,
    request_hash CHAR(64) NOT NULL,
    resource_type VARCHAR(80) NULL,
    resource_id BIGINT UNSIGNED NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_idempotency_scope (hotel_id,scope,idempotency_key_hash),
    INDEX idx_idempotency_expiry (expires_at),
    CONSTRAINT fk_idempotency_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions (code,name) VALUES
('orders.view','View orders'),
('orders.manage','Manage orders')
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','RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER')
  AND p.code IN ('orders.view','orders.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 IN ('WAITER','ROOM_SERVICE_STAFF','KITCHEN_MANAGER','READ_ONLY_MANAGER')
  AND p.code='orders.view';

INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type)
SELECT id,'menu_prices_include_tax','1','BOOLEAN' FROM hotels
ON DUPLICATE KEY UPDATE setting_value=setting_value;