-- Restaurant / Table Planner module for PMS
-- Run this once in your rooms database before uploading the PHP files.

CREATE TABLE IF NOT EXISTS restaurant_areas (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    property_id INT UNSIGNED NULL,
    area_name VARCHAR(120) NOT NULL,
    sort_order INT NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rest_area_property (property_id),
    INDEX idx_rest_area_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_tables (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    property_id INT UNSIGNED NULL,
    area_id INT UNSIGNED NULL,
    table_no VARCHAR(40) NOT NULL,
    table_name VARCHAR(120) NULL,
    seats INT UNSIGNED NOT NULL DEFAULT 2,
    pos_x INT NOT NULL DEFAULT 0,
    pos_y INT NOT NULL DEFAULT 0,
    shape ENUM('round','square','rect') NOT NULL DEFAULT 'round',
    status ENUM('available','occupied','reserved','dirty','blocked') NOT NULL DEFAULT 'available',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_rest_table_property_no (property_id, table_no),
    INDEX idx_rest_table_area (area_id),
    INDEX idx_rest_table_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_menu_categories (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    property_id INT UNSIGNED NULL,
    category_name VARCHAR(120) NOT NULL,
    printer_role VARCHAR(60) NULL,
    sort_order INT NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rest_menu_cat_property (property_id),
    INDEX idx_rest_menu_cat_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_menu_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    property_id INT UNSIGNED NULL,
    category_id INT UNSIGNED NULL,
    inventory_item_id INT UNSIGNED NULL,
    item_name VARCHAR(180) NOT NULL,
    item_code VARCHAR(60) NULL,
    description TEXT NULL,
    price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    tax_rate DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
    printer_role VARCHAR(60) NULL,
    default_printer_id INT UNSIGNED NULL,
    is_food TINYINT(1) NOT NULL DEFAULT 0,
    is_bar TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rest_menu_item_category (category_id),
    INDEX idx_rest_menu_item_inventory (inventory_item_id),
    INDEX idx_rest_menu_item_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_menu_modifiers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    property_id INT UNSIGNED NULL,
    modifier_name VARCHAR(120) NOT NULL,
    price_delta DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_rest_modifier_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_item_modifiers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    menu_item_id INT UNSIGNED NOT NULL,
    modifier_id INT UNSIGNED NOT NULL,
    UNIQUE KEY uq_rest_item_modifier (menu_item_id, modifier_id),
    INDEX idx_rest_item_modifier_item (menu_item_id),
    INDEX idx_rest_item_modifier_modifier (modifier_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    property_id INT UNSIGNED NULL,
    area_id INT UNSIGNED NULL,
    table_id INT UNSIGNED NULL,
    order_no VARCHAR(60) NOT NULL,
    order_type ENUM('table','room','walkin','takeaway') NOT NULL DEFAULT 'table',
    linked_order_id INT UNSIGNED NULL COMMENT 'PMS orders.id when posted/linked to lodge guest',
    linked_folio_id INT UNSIGNED NULL COMMENT 'folios.id when charged to room/master/guest folio',
    guest_name VARCHAR(180) NULL,
    covers INT UNSIGNED NOT NULL DEFAULT 1,
    status ENUM('open','sent','closed','void') NOT NULL DEFAULT 'open',
    subtotal DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    tax_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    payment_status ENUM('unpaid','part_paid','paid','room_charged','void') NOT NULL DEFAULT 'unpaid',
    opened_by INT UNSIGNED NULL,
    closed_by INT UNSIGNED NULL,
    opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_rest_order_no (order_no),
    INDEX idx_rest_order_table_status (table_id, status),
    INDEX idx_rest_order_linked_order (linked_order_id),
    INDEX idx_rest_order_folio (linked_folio_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_order_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    restaurant_order_id BIGINT UNSIGNED NOT NULL,
    menu_item_id INT UNSIGNED NULL,
    inventory_item_id INT UNSIGNED NULL,
    item_name VARCHAR(180) NOT NULL,
    qty DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    tax_rate DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
    tax_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    printer_role VARCHAR(60) NULL,
    print_status ENUM('pending','sent','printed','void') NOT NULL DEFAULT 'pending',
    notes VARCHAR(255) NULL,
    status ENUM('active','void') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_rest_order_item_order (restaurant_order_id),
    INDEX idx_rest_order_item_menu (menu_item_id),
    INDEX idx_rest_order_item_inventory (inventory_item_id),
    INDEX idx_rest_order_item_print (print_status, printer_role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_order_item_modifiers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_item_id BIGINT UNSIGNED NOT NULL,
    modifier_id INT UNSIGNED NULL,
    modifier_name VARCHAR(120) NOT NULL,
    price_delta DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_rest_order_item_modifier_item (order_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_payments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    restaurant_order_id BIGINT UNSIGNED NOT NULL,
    payment_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    payment_method VARCHAR(60) NOT NULL,
    amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    bank_account_id INT UNSIGNED NULL,
    pos_machine_id INT UNSIGNED NULL,
    reference_no VARCHAR(120) NULL,
    created_by INT UNSIGNED NULL,
    status ENUM('posted','void') NOT NULL DEFAULT 'posted',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_rest_payment_order (restaurant_order_id),
    INDEX idx_rest_payment_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restaurant_print_jobs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    restaurant_order_id BIGINT UNSIGNED NOT NULL,
    printer_role VARCHAR(60) NOT NULL,
    job_type ENUM('station','master','receipt','void') NOT NULL DEFAULT 'station',
    payload LONGTEXT NOT NULL,
    status ENUM('pending','printing','printed','failed') NOT NULL DEFAULT 'pending',
    error_message TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    printed_at DATETIME NULL,
    INDEX idx_rest_print_status (status),
    INDEX idx_rest_print_order (restaurant_order_id),
    INDEX idx_rest_print_role (printer_role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Useful starter data
INSERT INTO restaurant_areas (area_name, sort_order)
SELECT 'Restaurant', 1 WHERE NOT EXISTS (SELECT 1 FROM restaurant_areas WHERE area_name='Restaurant');
INSERT INTO restaurant_areas (area_name, sort_order)
SELECT 'Bar', 2 WHERE NOT EXISTS (SELECT 1 FROM restaurant_areas WHERE area_name='Bar');
INSERT INTO restaurant_areas (area_name, sort_order)
SELECT 'Terrace', 3 WHERE NOT EXISTS (SELECT 1 FROM restaurant_areas WHERE area_name='Terrace');

INSERT INTO restaurant_menu_categories (category_name, printer_role, sort_order)
SELECT 'Kitchen', 'kitchen', 1 WHERE NOT EXISTS (SELECT 1 FROM restaurant_menu_categories WHERE category_name='Kitchen');
INSERT INTO restaurant_menu_categories (category_name, printer_role, sort_order)
SELECT 'Bar', 'bar', 2 WHERE NOT EXISTS (SELECT 1 FROM restaurant_menu_categories WHERE category_name='Bar');
INSERT INTO restaurant_menu_categories (category_name, printer_role, sort_order)
SELECT 'Dessert / Ice Cream', 'dessert', 3 WHERE NOT EXISTS (SELECT 1 FROM restaurant_menu_categories WHERE category_name='Dessert / Ice Cream');
