-- ============================================================
-- HOTEL + RESTAURANT POS / BILLING / MANAGEMENT SYSTEM
-- Database: petzy_pos (MySQL 8+, phpMyAdmin compatible)
-- Money: DECIMAL(12,2) always. No floating point for financial values.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS petzy_pos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE petzy_pos;

-- ============================================================
-- 1. MULTI-TENANT CORE: COMPANY -> OUTLET -> USERS
-- ============================================================

CREATE TABLE companies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    legal_name VARCHAR(150) NULL,
    logo VARCHAR(255) NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(20) NULL,
    address VARCHAR(255) NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    country VARCHAR(100) NULL DEFAULT 'India',
    pincode VARCHAR(20) NULL,
    gstin VARCHAR(20) NULL,
    pan VARCHAR(20) NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'INR',
    timezone VARCHAR(60) NOT NULL DEFAULT 'Asia/Kolkata',
    invoice_footer VARCHAR(255) NULL,
    terms TEXT NULL,
    status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE outlets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(20) NOT NULL,
    type ENUM('restaurant','hotel','resort','cafe','qsr','other') NOT NULL DEFAULT 'restaurant',
    address VARCHAR(255) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    gstin VARCHAR(20) NULL,
    invoice_prefix VARCHAR(20) NOT NULL DEFAULT 'INV',
    kot_prefix VARCHAR(20) NOT NULL DEFAULT 'KOT',
    reservation_prefix VARCHAR(20) NOT NULL DEFAULT 'RES',
    room_prefix VARCHAR(20) NOT NULL DEFAULT 'ROOM',
    timezone VARCHAR(60) NOT NULL DEFAULT 'Asia/Kolkata',
    opening_time TIME NULL,
    closing_time TIME NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_outlet_company_code (company_id, code),
    CONSTRAINT fk_outlet_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NULL, -- NULL = system role template
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    is_system BOOLEAN NOT NULL DEFAULT 0,
    max_discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_company_slug (company_id, slug),
    CONSTRAINT fk_role_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module VARCHAR(60) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_permission (role_id, permission_id),
    CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(20) NULL,
    password VARCHAR(255) NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    avatar VARCHAR(255) NULL,
    is_super_admin BOOLEAN NOT NULL DEFAULT 0,
    status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    refresh_token_hash VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_company_email (company_id, email),
    CONSTRAINT fk_user_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

CREATE TABLE user_outlets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NOT NULL,
    is_default BOOLEAN NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_outlet (user_id, outlet_id),
    CONSTRAINT fk_uo_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_uo_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 2. CUSTOMERS / SUPPLIERS
-- ============================================================

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    mobile VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    birthday DATE NULL,
    anniversary DATE NULL,
    gstin VARCHAR(20) NULL,
    notes TEXT NULL,
    address VARCHAR(255) NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    total_visits INT UNSIGNED NOT NULL DEFAULT 0,
    total_orders INT UNSIGNED NOT NULL DEFAULT 0,
    total_spending DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    loyalty_points INT NOT NULL DEFAULT 0,
    last_visit_at DATETIME NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_customer_company_mobile (company_id, mobile),
    CONSTRAINT fk_customer_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE customer_addresses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    label VARCHAR(50) NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    pincode VARCHAR(20) NULL,
    is_default BOOLEAN NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_caddr_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE customer_loyalty (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    tier VARCHAR(50) NOT NULL DEFAULT 'Standard',
    points_balance INT NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_cl_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE suppliers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    company_name VARCHAR(150) NULL,
    gstin VARCHAR(20) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    address VARCHAR(255) NULL,
    payment_terms VARCHAR(100) NULL,
    bank_details VARCHAR(255) NULL,
    notes TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_supplier_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE supplier_contacts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supplier_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    designation VARCHAR(100) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    CONSTRAINT fk_sc_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 3. MENU MANAGEMENT
-- ============================================================

CREATE TABLE menu_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    parent_id INT UNSIGNED NULL,
    name VARCHAR(100) NOT NULL,
    image VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_mc_parent (parent_id),
    CONSTRAINT fk_mc_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_mc_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE,
    CONSTRAINT fk_mc_parent FOREIGN KEY (parent_id) REFERENCES menu_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE kitchen_stations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    printer_id INT UNSIGNED NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ks_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE menu_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    category_id INT UNSIGNED NOT NULL,
    station_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    sku VARCHAR(50) NULL,
    barcode VARCHAR(50) NULL,
    description VARCHAR(500) NULL,
    image VARCHAR(255) NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_id INT UNSIGNED NULL, -- legacy: superseded by menu_item_taxes (an item can carry more than one GST component)
    food_type ENUM('veg','non_veg','vegan','egg','other') NOT NULL DEFAULT 'veg',
    is_available BOOLEAN NOT NULL DEFAULT 1,
    online_available BOOLEAN NOT NULL DEFAULT 0,
    room_service_available BOOLEAN NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_item_company (company_id),
    KEY idx_item_category (category_id),
    CONSTRAINT fk_mi_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_mi_category FOREIGN KEY (category_id) REFERENCES menu_categories(id),
    CONSTRAINT fk_mi_station FOREIGN KEY (station_id) REFERENCES kitchen_stations(id)
) ENGINE=InnoDB;

CREATE TABLE menu_item_variations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    is_default BOOLEAN NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    CONSTRAINT fk_miv_item FOREIGN KEY (item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE menu_item_addons (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    min_select INT UNSIGNED NOT NULL DEFAULT 0,
    max_select INT UNSIGNED NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    CONSTRAINT fk_mia_item FOREIGN KEY (item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE menu_item_modifiers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    CONSTRAINT fk_mim_item FOREIGN KEY (item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE menu_combos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    image VARCHAR(255) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_combo_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE menu_combo_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    combo_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    CONSTRAINT fk_ci_combo FOREIGN KEY (combo_id) REFERENCES menu_combos(id) ON DELETE CASCADE,
    CONSTRAINT fk_ci_item FOREIGN KEY (item_id) REFERENCES menu_items(id)
) ENGINE=InnoDB;

-- ============================================================
-- 4. TAX / DISCOUNT / COUPON
-- ============================================================

CREATE TABLE taxes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    type ENUM('CGST','SGST','IGST','CESS','OTHER') NOT NULL DEFAULT 'OTHER',
    is_inclusive BOOLEAN NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tax_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Links a menu item to one or more tax components at once — e.g. CGST 6% + SGST 6%
-- together for one intra-state GST slab, or a single IGST row for inter-state.
-- Replaces the old single menu_items.tax_id, which could only ever point at one
-- component, so a GST item would only ever charge CGST (or SGST) alone.
CREATE TABLE menu_item_taxes (
    item_id INT UNSIGNED NOT NULL,
    tax_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (item_id, tax_id),
    CONSTRAINT fk_mit_item FOREIGN KEY (item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_mit_tax FOREIGN KEY (tax_id) REFERENCES taxes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE discounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage',
    value DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    max_discount_amount DECIMAL(12,2) NULL,
    applies_to ENUM('order','item') NOT NULL DEFAULT 'order',
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_discount_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE coupons (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    code VARCHAR(50) NOT NULL,
    type ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage',
    value DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    min_order_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    max_discount_amount DECIMAL(12,2) NULL,
    valid_from DATE NULL,
    valid_to DATE NULL,
    usage_limit INT UNSIGNED NULL,
    used_count INT UNSIGNED NOT NULL DEFAULT 0,
    customer_id INT UNSIGNED NULL,
    outlet_id INT UNSIGNED NULL,
    order_type VARCHAR(30) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_coupon_company_code (company_id, code),
    CONSTRAINT fk_coupon_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 5. FLOOR / TABLE MANAGEMENT
-- ============================================================

CREATE TABLE floors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    CONSTRAINT fk_floor_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE tables (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    floor_id INT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    capacity INT UNSIGNED NOT NULL DEFAULT 2,
    shape ENUM('square','round','rectangle') NOT NULL DEFAULT 'square',
    pos_x INT NOT NULL DEFAULT 0,
    pos_y INT NOT NULL DEFAULT 0,
    status ENUM('available','occupied','reserved','cleaning','blocked') NOT NULL DEFAULT 'available',
    current_order_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_table_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE,
    CONSTRAINT fk_table_floor FOREIGN KEY (floor_id) REFERENCES floors(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE table_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    table_id INT UNSIGNED NOT NULL,
    from_status VARCHAR(20) NULL,
    to_status VARCHAR(20) NOT NULL,
    changed_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tsh_table FOREIGN KEY (table_id) REFERENCES tables(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE restaurant_reservations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NULL,
    table_id INT UNSIGNED NULL,
    reservation_date DATE NOT NULL,
    reservation_time TIME NOT NULL,
    guests INT UNSIGNED NOT NULL DEFAULT 1,
    special_request VARCHAR(255) NULL,
    source VARCHAR(50) NOT NULL DEFAULT 'walk_in',
    status ENUM('pending','confirmed','arrived','seated','completed','cancelled','no_show') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_rr_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE,
    CONSTRAINT fk_rr_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_rr_table FOREIGN KEY (table_id) REFERENCES tables(id)
) ENGINE=InnoDB;

-- ============================================================
-- 6. ORDERS / PAYMENTS
-- ============================================================

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NOT NULL,
    order_number VARCHAR(50) NOT NULL,
    order_type ENUM('dine_in','takeaway','delivery','pickup','room_service','counter_sale','online') NOT NULL DEFAULT 'dine_in',
    table_id INT UNSIGNED NULL,
    customer_id INT UNSIGNED NULL,
    hotel_room_id INT UNSIGNED NULL,
    waiter_id INT UNSIGNED NULL,
    cashier_id INT UNSIGNED NULL,
    guests INT UNSIGNED NOT NULL DEFAULT 1,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    extra_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'flat non-taxable addition, e.g. packing/delivery charge',
    extra_charge_label VARCHAR(100) NULL,
    round_off DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    grand_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    balance_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    notes VARCHAR(255) NULL,
    status ENUM('draft','held','placed','preparing','ready','served','billed','paid','cancelled','closed') NOT NULL DEFAULT 'draft',
    cancel_reason VARCHAR(255) NULL,
    charged_to_room BOOLEAN NOT NULL DEFAULT 0,
    placed_at DATETIME NULL,
    closed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_order_outlet_number (outlet_id, order_number),
    KEY idx_order_company_date (company_id, created_at),
    CONSTRAINT fk_order_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_table FOREIGN KEY (table_id) REFERENCES tables(id),
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    variation_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    special_instruction VARCHAR(255) NULL,
    status ENUM('active','cancelled') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_oi_item FOREIGN KEY (item_id) REFERENCES menu_items(id)
) ENGINE=InnoDB;

CREATE TABLE order_item_addons (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_item_id BIGINT UNSIGNED NOT NULL,
    addon_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_oia_orderitem FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_item_modifiers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_item_id BIGINT UNSIGNED NOT NULL,
    modifier_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_oim_orderitem FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_taxes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    tax_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    taxable_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_ot_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_discounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    discount_id INT UNSIGNED NULL,
    coupon_id INT UNSIGNED NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage',
    value DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    approved_by INT UNSIGNED NULL,
    CONSTRAINT fk_od_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    folio_id INT UNSIGNED NULL,
    payment_number VARCHAR(50) NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    method ENUM('cash','card','upi','bank_transfer','wallet','online','cheque','cod','room_charge','credit','other') NOT NULL DEFAULT 'cash',
    reference_number VARCHAR(100) NULL,
    received_by INT UNSIGNED NULL,
    status ENUM('success','pending','failed','refunded') NOT NULL DEFAULT 'success',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_payment_outlet_number (outlet_id, payment_number),
    CONSTRAINT fk_payment_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_payment_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

CREATE TABLE payment_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_id BIGINT UNSIGNED NOT NULL,
    gateway VARCHAR(50) NULL,
    gateway_transaction_id VARCHAR(150) NULL,
    raw_response TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pt_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE refunds (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    payment_id BIGINT UNSIGNED NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    reason VARCHAR(255) NOT NULL,
    reference_number VARCHAR(100) NULL,
    approved_by INT UNSIGNED NULL,
    refunded_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_refund_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

-- ============================================================
-- 7. KOT / KITCHEN
-- ============================================================

CREATE TABLE kot (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    kot_number VARCHAR(50) NOT NULL,
    table_id INT UNSIGNED NULL,
    order_type VARCHAR(30) NOT NULL,
    waiter_id INT UNSIGNED NULL,
    priority ENUM('normal','high') NOT NULL DEFAULT 'normal',
    status ENUM('new','accepted','preparing','ready','served','cancelled') NOT NULL DEFAULT 'new',
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_kot_outlet_number (outlet_id, kot_number),
    CONSTRAINT fk_kot_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE kot_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kot_id BIGINT UNSIGNED NOT NULL,
    order_item_id BIGINT UNSIGNED NOT NULL,
    station_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    modifiers VARCHAR(255) NULL,
    notes VARCHAR(255) NULL,
    status ENUM('new','accepted','preparing','ready','served','cancelled') NOT NULL DEFAULT 'new',
    CONSTRAINT fk_ki_kot FOREIGN KEY (kot_id) REFERENCES kot(id) ON DELETE CASCADE,
    CONSTRAINT fk_ki_orderitem FOREIGN KEY (order_item_id) REFERENCES order_items(id)
) ENGINE=InnoDB;

CREATE TABLE kitchen_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kot_id BIGINT UNSIGNED NOT NULL,
    station_id INT UNSIGNED NOT NULL,
    accepted_at DATETIME NULL,
    started_at DATETIME NULL,
    ready_at DATETIME NULL,
    completed_at DATETIME NULL,
    status ENUM('new','accepted','preparing','ready','completed','cancelled') NOT NULL DEFAULT 'new',
    CONSTRAINT fk_ko_kot FOREIGN KEY (kot_id) REFERENCES kot(id) ON DELETE CASCADE,
    CONSTRAINT fk_ko_station FOREIGN KEY (station_id) REFERENCES kitchen_stations(id)
) ENGINE=InnoDB;

-- ============================================================
-- 8. RECIPES / INVENTORY
-- ============================================================

CREATE TABLE inventory_units (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    short_code VARCHAR(10) NOT NULL,
    CONSTRAINT fk_iu_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE inventory_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    sku VARCHAR(50) NULL,
    unit_id INT UNSIGNED NOT NULL,
    purchase_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    selling_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    reorder_level DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    minimum_stock DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    maximum_stock DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    supplier_id INT UNSIGNED NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_ii_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_ii_unit FOREIGN KEY (unit_id) REFERENCES inventory_units(id),
    CONSTRAINT fk_ii_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
) ENGINE=InnoDB;

CREATE TABLE inventory_stocks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inventory_item_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NOT NULL,
    current_stock DECIMAL(14,3) NOT NULL DEFAULT 0.000,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_stock_item_outlet (inventory_item_id, outlet_id),
    CONSTRAINT fk_is_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_is_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE inventory_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inventory_item_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NOT NULL,
    type ENUM('purchase','sale','consumption','wastage','adjustment','transfer_out','transfer_in','return','opening_stock','closing_adjustment') NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    balance_after DECIMAL(14,3) NOT NULL DEFAULT 0.000,
    unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    reference_type VARCHAR(50) NULL,
    reference_id BIGINT UNSIGNED NULL,
    notes VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_it_item_outlet (inventory_item_id, outlet_id),
    CONSTRAINT fk_it_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id),
    CONSTRAINT fk_it_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id)
) ENGINE=InnoDB;

CREATE TABLE stock_adjustments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    inventory_item_id INT UNSIGNED NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    reason VARCHAR(255) NOT NULL,
    adjusted_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sa_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id),
    CONSTRAINT fk_sa_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id)
) ENGINE=InnoDB;

CREATE TABLE stock_transfers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    transfer_number VARCHAR(50) NOT NULL,
    from_outlet_id INT UNSIGNED NOT NULL,
    to_outlet_id INT UNSIGNED NOT NULL,
    status ENUM('draft','requested','approved','dispatched','received','cancelled') NOT NULL DEFAULT 'draft',
    requested_by INT UNSIGNED NULL,
    approved_by INT UNSIGNED NULL,
    dispatched_at DATETIME NULL,
    received_at DATETIME NULL,
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_transfer_company_number (company_id, transfer_number),
    CONSTRAINT fk_st_from FOREIGN KEY (from_outlet_id) REFERENCES outlets(id),
    CONSTRAINT fk_st_to FOREIGN KEY (to_outlet_id) REFERENCES outlets(id)
) ENGINE=InnoDB;

CREATE TABLE stock_transfer_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stock_transfer_id INT UNSIGNED NOT NULL,
    inventory_item_id INT UNSIGNED NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    CONSTRAINT fk_sti_transfer FOREIGN KEY (stock_transfer_id) REFERENCES stock_transfers(id) ON DELETE CASCADE,
    CONSTRAINT fk_sti_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id)
) ENGINE=InnoDB;

CREATE TABLE wastage (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    inventory_item_id INT UNSIGNED NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    reason ENUM('expired','damaged','overproduction','spillage','wrong_preparation','other') NOT NULL DEFAULT 'other',
    cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    employee_id INT UNSIGNED NULL,
    wastage_date DATE NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_w_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id),
    CONSTRAINT fk_w_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id)
) ENGINE=InnoDB;

CREATE TABLE recipes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED NOT NULL,
    yield_quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    wastage_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    food_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_recipe_item FOREIGN KEY (item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE recipe_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    recipe_id INT UNSIGNED NOT NULL,
    inventory_item_id INT UNSIGNED NOT NULL,
    quantity DECIMAL(12,3) NOT NULL,
    unit_id INT UNSIGNED NOT NULL,
    cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_ri_recipe FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE,
    CONSTRAINT fk_ri_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id)
) ENGINE=InnoDB;

-- ============================================================
-- 9. PURCHASE
-- ============================================================

CREATE TABLE purchase_orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NOT NULL,
    supplier_id INT UNSIGNED NOT NULL,
    po_number VARCHAR(50) NOT NULL,
    status ENUM('draft','sent','partially_received','received','cancelled') NOT NULL DEFAULT 'draft',
    expected_date DATE NULL,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_po_company_number (company_id, po_number),
    CONSTRAINT fk_po_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
) ENGINE=InnoDB;

CREATE TABLE purchase_order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_order_id INT UNSIGNED NOT NULL,
    inventory_item_id INT UNSIGNED NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_poi_po FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_poi_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id)
) ENGINE=InnoDB;

CREATE TABLE purchase_invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NOT NULL,
    purchase_order_id INT UNSIGNED NULL,
    supplier_id INT UNSIGNED NOT NULL,
    invoice_number VARCHAR(50) NOT NULL,
    supplier_invoice_number VARCHAR(100) NULL,
    invoice_date DATE NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    balance_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('unpaid','partially_paid','paid') NOT NULL DEFAULT 'unpaid',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_pi_company_number (company_id, invoice_number),
    CONSTRAINT fk_pi_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
) ENGINE=InnoDB;

-- tax_id/tax_rate/tax_amount capture the GST paid to the supplier on this line —
-- this is the Input Tax Credit (ITC) side of GST filing (see docs/API.md GST Filing).
CREATE TABLE purchase_invoice_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_invoice_id INT UNSIGNED NOT NULL,
    inventory_item_id INT UNSIGNED NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_id INT UNSIGNED NULL,
    tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_pii_invoice FOREIGN KEY (purchase_invoice_id) REFERENCES purchase_invoices(id) ON DELETE CASCADE,
    CONSTRAINT fk_pii_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id),
    CONSTRAINT fk_pii_tax FOREIGN KEY (tax_id) REFERENCES taxes(id)
) ENGINE=InnoDB;

CREATE TABLE purchase_returns (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_invoice_id INT UNSIGNED NOT NULL,
    return_number VARCHAR(50) NOT NULL,
    reason VARCHAR(255) NULL,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pr_invoice FOREIGN KEY (purchase_invoice_id) REFERENCES purchase_invoices(id)
) ENGINE=InnoDB;

CREATE TABLE purchase_return_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_return_id INT UNSIGNED NOT NULL,
    inventory_item_id INT UNSIGNED NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_pri_return FOREIGN KEY (purchase_return_id) REFERENCES purchase_returns(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 10. HOTEL MODULE
-- ============================================================

CREATE TABLE hotel_room_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500) NULL,
    capacity INT UNSIGNED NOT NULL DEFAULT 2,
    adult_capacity INT UNSIGNED NOT NULL DEFAULT 2,
    child_capacity INT UNSIGNED NOT NULL DEFAULT 0,
    base_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_id INT UNSIGNED NULL,
    image VARCHAR(255) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_hrt_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE hotel_room_amenities (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_type_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    CONSTRAINT fk_hra_roomtype FOREIGN KEY (room_type_id) REFERENCES hotel_room_types(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE hotel_rooms (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    room_type_id INT UNSIGNED NOT NULL,
    room_number VARCHAR(20) NOT NULL,
    floor VARCHAR(20) NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    capacity INT UNSIGNED NOT NULL DEFAULT 2,
    notes VARCHAR(255) NULL,
    status ENUM('available','occupied','reserved','cleaning','maintenance','blocked') NOT NULL DEFAULT 'available',
    housekeeping_status ENUM('clean','dirty','cleaning','inspected','maintenance','blocked') NOT NULL DEFAULT 'clean',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_room_outlet_number (outlet_id, room_number),
    CONSTRAINT fk_hr_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE,
    CONSTRAINT fk_hr_roomtype FOREIGN KEY (room_type_id) REFERENCES hotel_room_types(id)
) ENGINE=InnoDB;

CREATE TABLE hotel_rate_plans (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_type_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    valid_from DATE NULL,
    valid_to DATE NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    CONSTRAINT fk_hrp_roomtype FOREIGN KEY (room_type_id) REFERENCES hotel_room_types(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE hotel_room_blocks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_id INT UNSIGNED NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    reason VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    CONSTRAINT fk_hrb_room FOREIGN KEY (room_id) REFERENCES hotel_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE hotel_guests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    mobile VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    dob DATE NULL,
    address VARCHAR(255) NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    country VARCHAR(100) NULL,
    id_type ENUM('passport','aadhaar','driving_license','voter_id','other') NULL,
    id_number VARCHAR(100) NULL,
    id_document VARCHAR(255) NULL,
    nationality VARCHAR(100) NULL,
    company_name VARCHAR(150) NULL,
    gstin VARCHAR(20) NULL,
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_hg_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE hotel_room_reservations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    reservation_number VARCHAR(50) NOT NULL,
    guest_id INT UNSIGNED NOT NULL,
    room_id INT UNSIGNED NULL,
    room_type_id INT UNSIGNED NOT NULL,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    adults INT UNSIGNED NOT NULL DEFAULT 1,
    children INT UNSIGNED NOT NULL DEFAULT 0,
    rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    advance_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    source VARCHAR(50) NOT NULL DEFAULT 'walk_in',
    special_requests VARCHAR(255) NULL,
    status ENUM('pending','confirmed','checked_in','checked_out','cancelled','no_show') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_hres_outlet_number (outlet_id, reservation_number),
    CONSTRAINT fk_hres_guest FOREIGN KEY (guest_id) REFERENCES hotel_guests(id),
    CONSTRAINT fk_hres_room FOREIGN KEY (room_id) REFERENCES hotel_rooms(id),
    CONSTRAINT fk_hres_roomtype FOREIGN KEY (room_type_id) REFERENCES hotel_room_types(id)
) ENGINE=InnoDB;

CREATE TABLE hotel_checkins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT UNSIGNED NOT NULL,
    room_id INT UNSIGNED NOT NULL,
    guest_id INT UNSIGNED NOT NULL,
    checked_in_by INT UNSIGNED NULL,
    checked_in_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    id_verified BOOLEAN NOT NULL DEFAULT 0,
    CONSTRAINT fk_hci_reservation FOREIGN KEY (reservation_id) REFERENCES hotel_room_reservations(id),
    CONSTRAINT fk_hci_room FOREIGN KEY (room_id) REFERENCES hotel_rooms(id)
) ENGINE=InnoDB;

CREATE TABLE hotel_checkouts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT UNSIGNED NOT NULL,
    checkin_id INT UNSIGNED NOT NULL,
    checked_out_by INT UNSIGNED NULL,
    checked_out_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    final_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_hco_reservation FOREIGN KEY (reservation_id) REFERENCES hotel_room_reservations(id),
    CONSTRAINT fk_hco_checkin FOREIGN KEY (checkin_id) REFERENCES hotel_checkins(id)
) ENGINE=InnoDB;

CREATE TABLE hotel_folios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT UNSIGNED NOT NULL,
    guest_id INT UNSIGNED NOT NULL,
    room_id INT UNSIGNED NOT NULL,
    folio_number VARCHAR(50) NOT NULL,
    total_charges DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_discount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_paid DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('open','closed') NOT NULL DEFAULT 'open',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_folio_number (folio_number),
    CONSTRAINT fk_folio_reservation FOREIGN KEY (reservation_id) REFERENCES hotel_room_reservations(id),
    CONSTRAINT fk_folio_guest FOREIGN KEY (guest_id) REFERENCES hotel_guests(id)
) ENGINE=InnoDB;

CREATE TABLE hotel_folio_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    folio_id INT UNSIGNED NOT NULL,
    charge_type ENUM('room','restaurant','room_service','laundry','minibar','extra_bed','parking','other') NOT NULL,
    reference_type VARCHAR(50) NULL,
    reference_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    posted_by INT UNSIGNED NULL,
    posted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_hfi_folio FOREIGN KEY (folio_id) REFERENCES hotel_folios(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE hotel_room_charges (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    folio_id INT UNSIGNED NOT NULL,
    charge_type ENUM('room_rent','extra_bed','early_checkin','late_checkout','other') NOT NULL DEFAULT 'room_rent',
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    charge_date DATE NOT NULL,
    CONSTRAINT fk_hrc_folio FOREIGN KEY (folio_id) REFERENCES hotel_folios(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE restaurant_room_charges (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    folio_id INT UNSIGNED NOT NULL,
    folio_item_id INT UNSIGNED NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    charged_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_rrc_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_rrc_folio FOREIGN KEY (folio_id) REFERENCES hotel_folios(id)
) ENGINE=InnoDB;

-- ============================================================
-- 11. CASH / EXPENSE / DAY END
-- ============================================================

CREATE TABLE cash_registers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    opened_by INT UNSIGNED NOT NULL,
    closed_by INT UNSIGNED NULL,
    opening_cash DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    expected_cash DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    actual_cash DECIMAL(12,2) NULL,
    difference DECIMAL(12,2) NULL,
    status ENUM('open','closed') NOT NULL DEFAULT 'open',
    opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    CONSTRAINT fk_cr_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id)
) ENGINE=InnoDB;

CREATE TABLE cash_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cash_register_id INT UNSIGNED NOT NULL,
    type ENUM('sale','expense','refund','deposit','withdrawal') NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    reference_type VARCHAR(50) NULL,
    reference_id BIGINT UNSIGNED NULL,
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ct_register FOREIGN KEY (cash_register_id) REFERENCES cash_registers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE day_end_closings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    business_date DATE NOT NULL,
    gross_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discounts DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    net_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    cash_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    card_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    upi_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    online_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    room_charge_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    refunds DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    expenses DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    expected_cash DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    actual_cash DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    difference DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    kot_count INT UNSIGNED NOT NULL DEFAULT 0,
    orders_count INT UNSIGNED NOT NULL DEFAULT 0,
    cancelled_orders INT UNSIGNED NOT NULL DEFAULT 0,
    closed_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_dayend_outlet_date (outlet_id, business_date),
    CONSTRAINT fk_de_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id)
) ENGINE=InnoDB;

CREATE TABLE expenses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NOT NULL,
    category ENUM('rent','electricity','salary','transport','maintenance','marketing','supplies','miscellaneous') NOT NULL DEFAULT 'miscellaneous',
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    expense_date DATE NOT NULL,
    description VARCHAR(255) NULL,
    payment_mode ENUM('cash','card','upi','bank_transfer','other') NOT NULL DEFAULT 'cash',
    employee_id INT UNSIGNED NULL,
    attachment VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_exp_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id)
) ENGINE=InnoDB;

-- ============================================================
-- 12. EMPLOYEES
-- ============================================================

CREATE TABLE employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    user_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    employee_code VARCHAR(30) NOT NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    department VARCHAR(100) NULL,
    designation VARCHAR(100) NULL,
    joining_date DATE NULL,
    salary DECIMAL(12,2) NULL,
    salary_type ENUM('monthly','daily','hourly') NOT NULL DEFAULT 'monthly',
    hra DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_allowances DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    week_off_day TINYINT UNSIGNED NULL COMMENT '0=Sunday .. 6=Saturday, this employee''s fixed weekly off',
    bank_account_number VARCHAR(30) NULL,
    bank_ifsc VARCHAR(15) NULL,
    bank_name VARCHAR(100) NULL,
    pan_number VARCHAR(20) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_emp_company_code (company_id, employee_code),
    CONSTRAINT fk_emp_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 'half_day' pays 0.5 of a day; 'week_off'/'holiday' are auto-paid non-working
-- days; 'comp_worked' is a working day worked on what would otherwise be a
-- week_off/holiday, and earns a Comp Off credit the employee can use as a leave
-- later (see leave_types/leave_requests below).
CREATE TABLE attendance (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NOT NULL,
    attendance_date DATE NOT NULL,
    check_in TIME NULL,
    check_out TIME NULL,
    status ENUM('present','late','half_day','absent','on_leave','week_off','holiday','comp_worked','overtime') NOT NULL DEFAULT 'present',
    notes VARCHAR(255) NULL,
    UNIQUE KEY uq_attendance_emp_date (employee_id, attendance_date),
    CONSTRAINT fk_att_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Company-wide holiday calendar (spec-adjacent: used to auto-mark attendance
-- as 'holiday' and to price payroll days correctly). outlet_id NULL = applies
-- to every outlet in the company.
CREATE TABLE holidays (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    holiday_date DATE NOT NULL,
    name VARCHAR(150) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_hol_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Leave types: quota-based (Casual/Sick/Earned, is_paid) or earned-only
-- (Comp Off — annual_quota 0, balance instead comes from comp_worked
-- attendance days, see payrollService.leaveBalance).
CREATE TABLE leave_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    is_paid BOOLEAN NOT NULL DEFAULT 1,
    annual_quota DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_leavetype_company_code (company_id, code),
    CONSTRAINT fk_lt_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE leave_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    employee_id INT UNSIGNED NOT NULL,
    leave_type_id INT UNSIGNED NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    is_half_day BOOLEAN NOT NULL DEFAULT 0,
    day_count DECIMAL(5,1) NOT NULL DEFAULT 1.0,
    reason VARCHAR(255) NULL,
    status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
    approved_by INT UNSIGNED NULL,
    approved_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lr_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_lr_leavetype FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
) ENGINE=InnoDB;

-- One run per outlet (or company-wide, outlet_id NULL) per calendar month —
-- computes every active employee's payable days from attendance + approved
-- leave, and their gross/net salary, as a reviewable draft before finalizing.
CREATE TABLE payroll_runs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    month TINYINT UNSIGNED NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    status ENUM('draft','finalized') NOT NULL DEFAULT 'draft',
    generated_by INT UNSIGNED NULL,
    generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finalized_by INT UNSIGNED NULL,
    finalized_at DATETIME NULL,
    CONSTRAINT fk_pr_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- One row per employee per payroll run — the actual payslip: attendance
-- breakdown, gross pay, Loss-of-Pay deduction for unpaid absence/leave, net
-- pay, and payment status once disbursed (see payrollController.markItemPaid,
-- which also posts a matching `expenses` row category='salary').
CREATE TABLE payroll_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payroll_run_id INT UNSIGNED NOT NULL,
    employee_id INT UNSIGNED NOT NULL,
    present_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    half_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    absent_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    paid_leave_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    unpaid_leave_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    week_off_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    holiday_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    comp_worked_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    payable_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    total_days_in_period SMALLINT UNSIGNED NOT NULL,
    basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    hra DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_allowances DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    gross_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    lop_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    net_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('pending','paid') NOT NULL DEFAULT 'pending',
    paid_at DATETIME NULL,
    payment_method VARCHAR(30) NULL,
    payment_reference VARCHAR(100) NULL,
    expense_id INT UNSIGNED NULL,
    UNIQUE KEY uq_payitem_run_employee (payroll_run_id, employee_id),
    CONSTRAINT fk_pi_run FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
    CONSTRAINT fk_pi_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

-- ============================================================
-- 13. LOYALTY
-- ============================================================

CREATE TABLE loyalty_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    type ENUM('earn','redeem','expire','adjust') NOT NULL,
    points INT NOT NULL,
    balance_after INT NOT NULL DEFAULT 0,
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lt_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 14. ONLINE ORDERS / QR
-- ============================================================

CREATE TABLE online_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    source ENUM('website','qr','mobile_app','aggregator','other') NOT NULL DEFAULT 'website',
    external_reference VARCHAR(100) NULL,
    customer_name VARCHAR(150) NULL,
    customer_phone VARCHAR(20) NULL,
    status ENUM('received','accepted','preparing','ready','out_for_delivery','completed','cancelled') NOT NULL DEFAULT 'received',
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_oo_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id)
) ENGINE=InnoDB;

CREATE TABLE online_order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    online_order_id BIGINT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_ooi_order FOREIGN KEY (online_order_id) REFERENCES online_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE qr_menus (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    qr_code VARCHAR(255) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_qm_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id)
) ENGINE=InnoDB;

CREATE TABLE qr_menu_tables (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    qr_menu_id INT UNSIGNED NOT NULL,
    table_id INT UNSIGNED NOT NULL,
    CONSTRAINT fk_qmt_menu FOREIGN KEY (qr_menu_id) REFERENCES qr_menus(id) ON DELETE CASCADE,
    CONSTRAINT fk_qmt_table FOREIGN KEY (table_id) REFERENCES tables(id)
) ENGINE=InnoDB;

-- Optional hand-picked product list for a QR menu. With rows here the public
-- QR page shows only these items; with none it shows the outlet's whole
-- online-available menu (see routes/qrPublicRoutes.js).
CREATE TABLE qr_menu_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    qr_menu_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_qmi_menu_item (qr_menu_id, item_id),
    CONSTRAINT fk_qmi_menu FOREIGN KEY (qr_menu_id) REFERENCES qr_menus(id) ON DELETE CASCADE,
    CONSTRAINT fk_qmi_item FOREIGN KEY (item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 15. SYSTEM: NOTIFICATIONS / AUDIT / SETTINGS / MEDIA / PRINTERS
-- ============================================================

CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    user_id INT UNSIGNED NULL,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(150) NOT NULL,
    message VARCHAR(500) NULL,
    reference_type VARCHAR(50) NULL,
    reference_id BIGINT UNSIGNED NULL,
    is_read BOOLEAN NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_notif_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NULL,
    user_id INT UNSIGNED NULL,
    action VARCHAR(50) NOT NULL,
    module VARCHAR(50) NOT NULL,
    record_id BIGINT UNSIGNED NULL,
    old_data JSON NULL,
    new_data JSON NULL,
    ip_address VARCHAR(60) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_company_module (company_id, module)
) ENGINE=InnoDB;

CREATE TABLE settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    `key` VARCHAR(100) NOT NULL,
    `value` TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_setting_scope_key (company_id, outlet_id, `key`),
    CONSTRAINT fk_setting_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE social_integrations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    provider VARCHAR(50) NOT NULL,
    config JSON NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'inactive',
    CONSTRAINT fk_si_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE printers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    outlet_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('bill','kot','kitchen','receipt','label') NOT NULL DEFAULT 'kot',
    paper_size ENUM('58mm','80mm','A4') NOT NULL DEFAULT '80mm',
    ip_address VARCHAR(60) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    CONSTRAINT fk_printer_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE printer_routes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    printer_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NULL,
    station_id INT UNSIGNED NULL,
    order_type VARCHAR(30) NULL,
    CONSTRAINT fk_proute_printer FOREIGN KEY (printer_id) REFERENCES printers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    folder VARCHAR(50) NOT NULL DEFAULT 'media',
    file_name VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size INT UNSIGNED NOT NULL DEFAULT 0,
    uploaded_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_media_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE backup_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NULL,
    type ENUM('database','media') NOT NULL DEFAULT 'database',
    file_name VARCHAR(255) NULL,
    status ENUM('success','failed') NOT NULL DEFAULT 'success',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- FOREIGN KEY: tables.current_order_id -> orders.id (added after orders exists)
-- ============================================================
ALTER TABLE tables ADD CONSTRAINT fk_table_current_order FOREIGN KEY (current_order_id) REFERENCES orders(id);

SET FOREIGN_KEY_CHECKS = 1;