-- KYN POS Phase 2 Core POS Schema
-- Purpose: Core operational tables for staff, tables, menu, modifiers, checks, payments, voids, discounts, and daily close.
-- Design rule: POS records clean operational events. KYN/Food Cost/TFF intelligence is fed from finalized POS facts, not from patched legacy K41 code.
-- Apply after: 001_foundation_schema.sql

-- =========================================================
-- Staff, roles, PINs, and shifts
-- =========================================================

CREATE TABLE IF NOT EXISTS pos_staff_roles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    role_key VARCHAR(50) NOT NULL,
    role_name VARCHAR(100) NOT NULL,
    role_description VARCHAR(255) NULL,
    is_manager_role TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_staff_roles_role_key (role_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_staff (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    staff_uuid CHAR(36) NOT NULL,
    first_name VARCHAR(80) NOT NULL,
    last_name VARCHAR(80) NOT NULL,
    display_name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(30) NULL,
    password_hash VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_staff_staff_uuid (staff_uuid),
    UNIQUE KEY uq_pos_staff_location_email (location_id, email),
    KEY idx_pos_staff_location_active (location_id, is_active),
    CONSTRAINT fk_pos_staff_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_staff_role_assignments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    staff_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_staff_role_assignments_staff_role (staff_id, role_id),
    KEY idx_pos_staff_role_assignments_role_id (role_id),
    CONSTRAINT fk_pos_staff_role_assignments_staff_id FOREIGN KEY (staff_id) REFERENCES pos_staff(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_staff_role_assignments_role_id FOREIGN KEY (role_id) REFERENCES pos_staff_roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_staff_pins (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    staff_id BIGINT UNSIGNED NOT NULL,
    pin_hash VARCHAR(255) NOT NULL,
    failed_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    last_used_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_staff_pins_staff_active (staff_id, is_active),
    CONSTRAINT fk_pos_staff_pins_staff_id FOREIGN KEY (staff_id) REFERENCES pos_staff(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_shifts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    staff_id BIGINT UNSIGNED NOT NULL,
    device_id BIGINT UNSIGNED NULL,
    business_date DATE NOT NULL,
    clock_in_at DATETIME NOT NULL,
    clock_out_at DATETIME NULL,
    shift_status ENUM('open','closed','voided') NOT NULL DEFAULT 'open',
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_shifts_location_date (location_id, business_date),
    KEY idx_pos_shifts_staff_status (staff_id, shift_status),
    KEY idx_pos_shifts_device_id (device_id),
    CONSTRAINT fk_pos_shifts_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_shifts_staff_id FOREIGN KEY (staff_id) REFERENCES pos_staff(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_shifts_device_id FOREIGN KEY (device_id) REFERENCES pos_devices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- Dining room and table map
-- =========================================================

CREATE TABLE IF NOT EXISTS pos_dining_zones (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    zone_name VARCHAR(100) NOT NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_dining_zones_location_name (location_id, zone_name),
    KEY idx_pos_dining_zones_location_order (location_id, display_order),
    CONSTRAINT fk_pos_dining_zones_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_dining_tables (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    zone_id BIGINT UNSIGNED NULL,
    table_code VARCHAR(30) NOT NULL,
    table_name VARCHAR(80) NOT NULL,
    seat_count SMALLINT UNSIGNED NOT NULL DEFAULT 2,
    table_type ENUM('standard','bar','patio','takeout','delivery','virtual') NOT NULL DEFAULT 'standard',
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    can_be_merged TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_dining_tables_location_code (location_id, table_code),
    KEY idx_pos_dining_tables_location_zone (location_id, zone_id),
    KEY idx_pos_dining_tables_location_active (location_id, is_active),
    CONSTRAINT fk_pos_dining_tables_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_dining_tables_zone_id FOREIGN KEY (zone_id) REFERENCES pos_dining_zones(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_table_merge_rules (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    primary_table_id BIGINT UNSIGNED NOT NULL,
    secondary_table_id BIGINT UNSIGNED NOT NULL,
    combined_seat_count SMALLINT UNSIGNED NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_table_merge_rules_pair (primary_table_id, secondary_table_id),
    KEY idx_pos_table_merge_rules_location (location_id),
    CONSTRAINT fk_pos_table_merge_rules_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_table_merge_rules_primary_id FOREIGN KEY (primary_table_id) REFERENCES pos_dining_tables(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_table_merge_rules_secondary_id FOREIGN KEY (secondary_table_id) REFERENCES pos_dining_tables(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- Menu, modifiers, tax, service type
-- =========================================================

CREATE TABLE IF NOT EXISTS pos_service_types (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    service_type_key VARCHAR(50) NOT NULL,
    service_type_name VARCHAR(100) NOT NULL,
    affects_packaging TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_service_types_key (service_type_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_tax_rates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    tax_rate_name VARCHAR(100) NOT NULL,
    rate_percent DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    effective_start DATE NOT NULL,
    effective_end DATE NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_tax_rates_location_active (location_id, is_active),
    CONSTRAINT fk_pos_tax_rates_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_sales_departments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    department_key VARCHAR(60) NOT NULL,
    department_name VARCHAR(120) NOT NULL,
    kyn_department_uuid CHAR(36) NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_sales_departments_location_key (location_id, department_key),
    KEY idx_pos_sales_departments_kyn_uuid (kyn_department_uuid),
    CONSTRAINT fk_pos_sales_departments_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_menu_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    department_id BIGINT UNSIGNED NOT NULL,
    category_key VARCHAR(60) NOT NULL,
    category_name VARCHAR(120) NOT NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_menu_categories_location_key (location_id, category_key),
    KEY idx_pos_menu_categories_department (department_id, display_order),
    CONSTRAINT fk_pos_menu_categories_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_menu_categories_department_id FOREIGN KEY (department_id) REFERENCES pos_sales_departments(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_menu_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    tax_rate_id BIGINT UNSIGNED NULL,
    menu_item_uuid CHAR(36) NOT NULL,
    sku VARCHAR(80) NULL,
    item_name VARCHAR(160) NOT NULL,
    kitchen_name VARCHAR(120) NULL,
    description TEXT NULL,
    base_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    course_default ENUM('none','appetizer','second','main','dessert','beverage') NOT NULL DEFAULT 'none',
    is_available TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_menu_items_uuid (menu_item_uuid),
    UNIQUE KEY uq_pos_menu_items_location_sku (location_id, sku),
    KEY idx_pos_menu_items_category_active (category_id, is_active, display_order),
    KEY idx_pos_menu_items_location_active (location_id, is_active),
    CONSTRAINT fk_pos_menu_items_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_menu_items_category_id FOREIGN KEY (category_id) REFERENCES pos_menu_categories(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_menu_items_tax_rate_id FOREIGN KEY (tax_rate_id) REFERENCES pos_tax_rates(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_menu_item_prices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    menu_item_id BIGINT UNSIGNED NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    effective_start DATETIME NOT NULL,
    effective_end DATETIME NULL,
    created_by_staff_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_menu_item_prices_item_dates (menu_item_id, effective_start, effective_end),
    CONSTRAINT fk_pos_menu_item_prices_item_id FOREIGN KEY (menu_item_id) REFERENCES pos_menu_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_menu_item_prices_staff_id FOREIGN KEY (created_by_staff_id) REFERENCES pos_staff(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_modifier_groups (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    group_key VARCHAR(80) NOT NULL,
    group_name VARCHAR(120) NOT NULL,
    min_select SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    max_select SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    is_required TINYINT(1) NOT NULL DEFAULT 0,
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_modifier_groups_location_key (location_id, group_key),
    CONSTRAINT fk_pos_modifier_groups_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_modifiers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    modifier_uuid CHAR(36) NOT NULL,
    modifier_name VARCHAR(140) NOT NULL,
    kitchen_name VARCHAR(100) NULL,
    price_delta DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    food_cost_behavior ENUM('none','add','remove','replace','portion_factor','packaging') NOT NULL DEFAULT 'none',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_modifiers_uuid (modifier_uuid),
    KEY idx_pos_modifiers_location_active (location_id, is_active),
    CONSTRAINT fk_pos_modifiers_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_modifier_group_options (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    modifier_group_id BIGINT UNSIGNED NOT NULL,
    modifier_id BIGINT UNSIGNED NOT NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_modifier_group_options_group_modifier (modifier_group_id, modifier_id),
    KEY idx_pos_modifier_group_options_modifier_id (modifier_id),
    CONSTRAINT fk_pos_modifier_group_options_group_id FOREIGN KEY (modifier_group_id) REFERENCES pos_modifier_groups(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_modifier_group_options_modifier_id FOREIGN KEY (modifier_id) REFERENCES pos_modifiers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_menu_item_modifier_groups (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    menu_item_id BIGINT UNSIGNED NOT NULL,
    modifier_group_id BIGINT UNSIGNED NOT NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_menu_item_modifier_groups_item_group (menu_item_id, modifier_group_id),
    KEY idx_pos_menu_item_modifier_groups_group_id (modifier_group_id),
    CONSTRAINT fk_pos_menu_item_modifier_groups_item_id FOREIGN KEY (menu_item_id) REFERENCES pos_menu_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_menu_item_modifier_groups_group_id FOREIGN KEY (modifier_group_id) REFERENCES pos_modifier_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- Checks, lines, modifiers, courses, events
-- =========================================================

CREATE TABLE IF NOT EXISTS pos_checks (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    check_uuid CHAR(36) NOT NULL,
    business_date DATE NOT NULL,
    table_id BIGINT UNSIGNED NULL,
    service_type_id BIGINT UNSIGNED NOT NULL,
    opened_by_staff_id BIGINT UNSIGNED NOT NULL,
    closed_by_staff_id BIGINT UNSIGNED NULL,
    shift_id BIGINT UNSIGNED NULL,
    guest_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    check_status ENUM('open','sent','partially_sent','ready','paid','voided','cancelled','refunded') NOT NULL DEFAULT 'open',
    subtotal_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    comp_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tip_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    balance_due_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_checks_uuid (check_uuid),
    KEY idx_pos_checks_location_business_date (location_id, business_date),
    KEY idx_pos_checks_table_status (table_id, check_status),
    KEY idx_pos_checks_status (check_status),
    KEY idx_pos_checks_staff_date (opened_by_staff_id, business_date),
    CONSTRAINT fk_pos_checks_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_checks_table_id FOREIGN KEY (table_id) REFERENCES pos_dining_tables(id) ON DELETE SET NULL,
    CONSTRAINT fk_pos_checks_service_type_id FOREIGN KEY (service_type_id) REFERENCES pos_service_types(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_checks_opened_by_staff_id FOREIGN KEY (opened_by_staff_id) REFERENCES pos_staff(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_checks_closed_by_staff_id FOREIGN KEY (closed_by_staff_id) REFERENCES pos_staff(id) ON DELETE SET NULL,
    CONSTRAINT fk_pos_checks_shift_id FOREIGN KEY (shift_id) REFERENCES pos_shifts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_check_lines (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    check_id BIGINT UNSIGNED NOT NULL,
    menu_item_id BIGINT UNSIGNED NOT NULL,
    parent_line_id BIGINT UNSIGNED NULL,
    line_uuid CHAR(36) NOT NULL,
    line_number INT UNSIGNED NOT NULL,
    quantity DECIMAL(10,3) NOT NULL DEFAULT 1.000,
    item_name_snapshot VARCHAR(160) NOT NULL,
    kitchen_name_snapshot VARCHAR(120) NULL,
    unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    modifier_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    line_subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    course_key ENUM('none','appetizer','second','main','dessert','beverage') NOT NULL DEFAULT 'none',
    line_status ENUM('draft','sent','fired','in_progress','ready','served','voided','comped') NOT NULL DEFAULT 'draft',
    seat_number SMALLINT UNSIGNED NULL,
    special_instructions VARCHAR(255) NULL,
    sent_at DATETIME NULL,
    fired_at DATETIME NULL,
    ready_at DATETIME NULL,
    served_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_check_lines_uuid (line_uuid),
    KEY idx_pos_check_lines_check_status (check_id, line_status),
    KEY idx_pos_check_lines_menu_item_id (menu_item_id),
    KEY idx_pos_check_lines_parent_line_id (parent_line_id),
    CONSTRAINT fk_pos_check_lines_check_id FOREIGN KEY (check_id) REFERENCES pos_checks(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_check_lines_menu_item_id FOREIGN KEY (menu_item_id) REFERENCES pos_menu_items(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_check_lines_parent_line_id FOREIGN KEY (parent_line_id) REFERENCES pos_check_lines(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_check_line_modifiers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    check_line_id BIGINT UNSIGNED NOT NULL,
    modifier_group_id BIGINT UNSIGNED NULL,
    modifier_id BIGINT UNSIGNED NOT NULL,
    modifier_name_snapshot VARCHAR(140) NOT NULL,
    quantity DECIMAL(10,3) NOT NULL DEFAULT 1.000,
    price_delta DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_delta DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    food_cost_behavior_snapshot ENUM('none','add','remove','replace','portion_factor','packaging') NOT NULL DEFAULT 'none',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_check_line_modifiers_line_id (check_line_id),
    KEY idx_pos_check_line_modifiers_modifier_id (modifier_id),
    CONSTRAINT fk_pos_check_line_modifiers_line_id FOREIGN KEY (check_line_id) REFERENCES pos_check_lines(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_check_line_modifiers_group_id FOREIGN KEY (modifier_group_id) REFERENCES pos_modifier_groups(id) ON DELETE SET NULL,
    CONSTRAINT fk_pos_check_line_modifiers_modifier_id FOREIGN KEY (modifier_id) REFERENCES pos_modifiers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_check_line_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    check_line_id BIGINT UNSIGNED NOT NULL,
    staff_id BIGINT UNSIGNED NULL,
    event_type ENUM('created','sent','fired','in_progress','ready','served','voided','comped','note_added','price_changed') NOT NULL,
    from_status VARCHAR(40) NULL,
    to_status VARCHAR(40) NULL,
    event_note VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_check_line_events_line_created (check_line_id, created_at),
    KEY idx_pos_check_line_events_staff_created (staff_id, created_at),
    CONSTRAINT fk_pos_check_line_events_line_id FOREIGN KEY (check_line_id) REFERENCES pos_check_lines(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_check_line_events_staff_id FOREIGN KEY (staff_id) REFERENCES pos_staff(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_course_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    check_id BIGINT UNSIGNED NOT NULL,
    staff_id BIGINT UNSIGNED NULL,
    course_key ENUM('appetizer','second','main','dessert','beverage') NOT NULL,
    event_type ENUM('sent','fired','ready','served') NOT NULL,
    event_note VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_course_events_check_course (check_id, course_key, created_at),
    KEY idx_pos_course_events_staff_created (staff_id, created_at),
    CONSTRAINT fk_pos_course_events_check_id FOREIGN KEY (check_id) REFERENCES pos_checks(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_course_events_staff_id FOREIGN KEY (staff_id) REFERENCES pos_staff(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- Discounts, comps, voids, taxes, tips, payments
-- =========================================================

CREATE TABLE IF NOT EXISTS pos_discount_reason_codes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    reason_key VARCHAR(60) NOT NULL,
    reason_name VARCHAR(120) NOT NULL,
    requires_manager_approval TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_discount_reason_codes_key (reason_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_void_reason_codes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    reason_key VARCHAR(60) NOT NULL,
    reason_name VARCHAR(120) NOT NULL,
    food_cost_impact ENUM('none','waste','remake','theft_review','training','unknown') NOT NULL DEFAULT 'unknown',
    requires_manager_approval TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_void_reason_codes_key (reason_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_check_discounts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    check_id BIGINT UNSIGNED NOT NULL,
    check_line_id BIGINT UNSIGNED NULL,
    reason_code_id BIGINT UNSIGNED NOT NULL,
    created_by_staff_id BIGINT UNSIGNED NOT NULL,
    approved_by_staff_id BIGINT UNSIGNED NULL,
    discount_type ENUM('amount','percent') NOT NULL DEFAULT 'amount',
    discount_value DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    note VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_check_discounts_check_id (check_id),
    KEY idx_pos_check_discounts_line_id (check_line_id),
    CONSTRAINT fk_pos_check_discounts_check_id FOREIGN KEY (check_id) REFERENCES pos_checks(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_check_discounts_line_id FOREIGN KEY (check_line_id) REFERENCES pos_check_lines(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_check_discounts_reason_id FOREIGN KEY (reason_code_id) REFERENCES pos_discount_reason_codes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_check_discounts_created_staff_id FOREIGN KEY (created_by_staff_id) REFERENCES pos_staff(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_check_discounts_approved_staff_id FOREIGN KEY (approved_by_staff_id) REFERENCES pos_staff(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_check_line_voids (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    check_id BIGINT UNSIGNED NOT NULL,
    check_line_id BIGINT UNSIGNED NOT NULL,
    reason_code_id BIGINT UNSIGNED NOT NULL,
    created_by_staff_id BIGINT UNSIGNED NOT NULL,
    approved_by_staff_id BIGINT UNSIGNED NULL,
    void_stage ENUM('before_send','after_send','after_fire','after_ready','after_payment') NOT NULL DEFAULT 'before_send',
    void_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    food_cost_should_count TINYINT(1) NOT NULL DEFAULT 0,
    note VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_check_line_voids_line_id (check_line_id),
    KEY idx_pos_check_line_voids_check_id (check_id),
    CONSTRAINT fk_pos_check_line_voids_check_id FOREIGN KEY (check_id) REFERENCES pos_checks(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_check_line_voids_line_id FOREIGN KEY (check_line_id) REFERENCES pos_check_lines(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_check_line_voids_reason_id FOREIGN KEY (reason_code_id) REFERENCES pos_void_reason_codes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_check_line_voids_created_staff_id FOREIGN KEY (created_by_staff_id) REFERENCES pos_staff(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_check_line_voids_approved_staff_id FOREIGN KEY (approved_by_staff_id) REFERENCES pos_staff(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_check_tax_lines (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    check_id BIGINT UNSIGNED NOT NULL,
    tax_rate_id BIGINT UNSIGNED NULL,
    taxable_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_rate_percent DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_check_tax_lines_check_id (check_id),
    CONSTRAINT fk_pos_check_tax_lines_check_id FOREIGN KEY (check_id) REFERENCES pos_checks(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_check_tax_lines_tax_rate_id FOREIGN KEY (tax_rate_id) REFERENCES pos_tax_rates(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_payment_methods (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    method_key VARCHAR(60) NOT NULL,
    method_name VARCHAR(120) NOT NULL,
    method_type ENUM('cash','credit_card','debit_card','gift_card','check','house_account','other') NOT NULL DEFAULT 'other',
    requires_processor_reference TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    display_order INT UNSIGNED NOT NULL DEFAULT 100,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_payment_methods_key (method_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_payments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    check_id BIGINT UNSIGNED NOT NULL,
    payment_method_id BIGINT UNSIGNED NOT NULL,
    created_by_staff_id BIGINT UNSIGNED NOT NULL,
    payment_status ENUM('pending','approved','declined','voided','refunded') NOT NULL DEFAULT 'approved',
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tip_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tendered_amount DECIMAL(10,2) NULL,
    change_due_amount DECIMAL(10,2) NULL,
    processor_reference VARCHAR(190) NULL,
    last_four VARCHAR(4) NULL,
    paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_payments_check_id (check_id),
    KEY idx_pos_payments_method_status (payment_method_id, payment_status),
    KEY idx_pos_payments_paid_at (paid_at),
    CONSTRAINT fk_pos_payments_check_id FOREIGN KEY (check_id) REFERENCES pos_checks(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_payments_payment_method_id FOREIGN KEY (payment_method_id) REFERENCES pos_payment_methods(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_payments_created_staff_id FOREIGN KEY (created_by_staff_id) REFERENCES pos_staff(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_payment_allocations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    payment_id BIGINT UNSIGNED NOT NULL,
    check_id BIGINT UNSIGNED NOT NULL,
    allocation_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_payment_allocations_payment_id (payment_id),
    KEY idx_pos_payment_allocations_check_id (check_id),
    CONSTRAINT fk_pos_payment_allocations_payment_id FOREIGN KEY (payment_id) REFERENCES pos_payments(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_payment_allocations_check_id FOREIGN KEY (check_id) REFERENCES pos_checks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_tip_lines (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    check_id BIGINT UNSIGNED NOT NULL,
    payment_id BIGINT UNSIGNED NULL,
    staff_id BIGINT UNSIGNED NULL,
    tip_type ENUM('cash','credit_card','other') NOT NULL DEFAULT 'other',
    tip_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_tip_lines_check_id (check_id),
    KEY idx_pos_tip_lines_payment_id (payment_id),
    CONSTRAINT fk_pos_tip_lines_check_id FOREIGN KEY (check_id) REFERENCES pos_checks(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_tip_lines_payment_id FOREIGN KEY (payment_id) REFERENCES pos_payments(id) ON DELETE SET NULL,
    CONSTRAINT fk_pos_tip_lines_staff_id FOREIGN KEY (staff_id) REFERENCES pos_staff(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- Cash drawer and daily close
-- =========================================================

CREATE TABLE IF NOT EXISTS pos_cash_drawer_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    device_id BIGINT UNSIGNED NULL,
    opened_by_staff_id BIGINT UNSIGNED NOT NULL,
    closed_by_staff_id BIGINT UNSIGNED NULL,
    business_date DATE NOT NULL,
    opening_cash_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    expected_cash_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    counted_cash_amount DECIMAL(10,2) NULL,
    over_short_amount DECIMAL(10,2) NULL,
    drawer_status ENUM('open','closed','voided') NOT NULL DEFAULT 'open',
    opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_cash_drawer_sessions_location_date (location_id, business_date),
    KEY idx_pos_cash_drawer_sessions_status (drawer_status),
    CONSTRAINT fk_pos_cash_drawer_sessions_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_cash_drawer_sessions_device_id FOREIGN KEY (device_id) REFERENCES pos_devices(id) ON DELETE SET NULL,
    CONSTRAINT fk_pos_cash_drawer_sessions_opened_staff_id FOREIGN KEY (opened_by_staff_id) REFERENCES pos_staff(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pos_cash_drawer_sessions_closed_staff_id FOREIGN KEY (closed_by_staff_id) REFERENCES pos_staff(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_cash_drawer_entries (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    drawer_session_id BIGINT UNSIGNED NOT NULL,
    staff_id BIGINT UNSIGNED NOT NULL,
    entry_type ENUM('cash_in','cash_out','paid_in','paid_out','correction') NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    note VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_cash_drawer_entries_drawer_id (drawer_session_id),
    CONSTRAINT fk_pos_cash_drawer_entries_drawer_id FOREIGN KEY (drawer_session_id) REFERENCES pos_cash_drawer_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_cash_drawer_entries_staff_id FOREIGN KEY (staff_id) REFERENCES pos_staff(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_daily_close_batches (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    close_uuid CHAR(36) NOT NULL,
    business_date DATE NOT NULL,
    closed_by_staff_id BIGINT UNSIGNED NOT NULL,
    close_status ENUM('draft','balanced','approved','posted_to_kyn','reopened') NOT NULL DEFAULT 'draft',
    gross_sales_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    net_sales_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    comp_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    void_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tip_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    payment_total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    guest_count INT UNSIGNED NOT NULL DEFAULT 0,
    check_count INT UNSIGNED NOT NULL DEFAULT 0,
    opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_at DATETIME NULL,
    posted_to_kyn_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_daily_close_batches_uuid (close_uuid),
    UNIQUE KEY uq_pos_daily_close_batches_location_date (location_id, business_date),
    CONSTRAINT fk_pos_daily_close_batches_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_daily_close_batches_staff_id FOREIGN KEY (closed_by_staff_id) REFERENCES pos_staff(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_daily_close_tenders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    close_batch_id BIGINT UNSIGNED NOT NULL,
    payment_method_id BIGINT UNSIGNED NOT NULL,
    payment_count INT UNSIGNED NOT NULL DEFAULT 0,
    payment_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tip_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_daily_close_tenders_batch_method (close_batch_id, payment_method_id),
    CONSTRAINT fk_pos_daily_close_tenders_batch_id FOREIGN KEY (close_batch_id) REFERENCES pos_daily_close_batches(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_daily_close_tenders_method_id FOREIGN KEY (payment_method_id) REFERENCES pos_payment_methods(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_daily_close_departments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    close_batch_id BIGINT UNSIGNED NOT NULL,
    department_id BIGINT UNSIGNED NOT NULL,
    item_quantity DECIMAL(12,3) NOT NULL DEFAULT 0.000,
    gross_sales_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    net_sales_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_daily_close_departments_batch_dept (close_batch_id, department_id),
    CONSTRAINT fk_pos_daily_close_departments_batch_id FOREIGN KEY (close_batch_id) REFERENCES pos_daily_close_batches(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_daily_close_departments_department_id FOREIGN KEY (department_id) REFERENCES pos_sales_departments(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_daily_close_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    close_batch_id BIGINT UNSIGNED NOT NULL,
    menu_item_id BIGINT UNSIGNED NOT NULL,
    item_quantity DECIMAL(12,3) NOT NULL DEFAULT 0.000,
    gross_sales_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    net_sales_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    void_quantity DECIMAL(12,3) NOT NULL DEFAULT 0.000,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_daily_close_items_batch_item (close_batch_id, menu_item_id),
    CONSTRAINT fk_pos_daily_close_items_batch_id FOREIGN KEY (close_batch_id) REFERENCES pos_daily_close_batches(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_daily_close_items_menu_item_id FOREIGN KEY (menu_item_id) REFERENCES pos_menu_items(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- KYN / Food Cost bridge placeholders
-- External UUIDs intentionally have no foreign keys because KYN ERP and Food Cost may live in separate databases.
-- =========================================================

CREATE TABLE IF NOT EXISTS pos_menu_item_kyn_links (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    menu_item_id BIGINT UNSIGNED NOT NULL,
    kyn_sales_department_uuid CHAR(36) NULL,
    kyn_sales_category_uuid CHAR(36) NULL,
    kyn_sales_item_uuid CHAR(36) NULL,
    food_cost_recipe_uuid CHAR(36) NULL,
    tff_resupply_sku_uuid CHAR(36) NULL,
    packaging_profile_uuid CHAR(36) NULL,
    effective_start DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    effective_end DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_menu_item_kyn_links_item_active (menu_item_id, is_active),
    KEY idx_pos_menu_item_kyn_links_recipe_uuid (food_cost_recipe_uuid),
    KEY idx_pos_menu_item_kyn_links_tff_sku_uuid (tff_resupply_sku_uuid),
    CONSTRAINT fk_pos_menu_item_kyn_links_item_id FOREIGN KEY (menu_item_id) REFERENCES pos_menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_modifier_kyn_links (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    modifier_id BIGINT UNSIGNED NOT NULL,
    food_cost_delta_uuid CHAR(36) NULL,
    packaging_delta_uuid CHAR(36) NULL,
    tff_resupply_sku_uuid CHAR(36) NULL,
    effective_start DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    effective_end DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_modifier_kyn_links_modifier_active (modifier_id, is_active),
    KEY idx_pos_modifier_kyn_links_delta_uuid (food_cost_delta_uuid),
    CONSTRAINT fk_pos_modifier_kyn_links_modifier_id FOREIGN KEY (modifier_id) REFERENCES pos_modifiers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_kyn_export_batches (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    close_batch_id BIGINT UNSIGNED NULL,
    export_uuid CHAR(36) NOT NULL,
    export_type ENUM('daily_close','check_detail','item_sales','theoretical_usage_seed') NOT NULL DEFAULT 'daily_close',
    export_status ENUM('pending','posted','failed','cancelled') NOT NULL DEFAULT 'pending',
    idempotency_key CHAR(64) NOT NULL,
    payload_json JSON NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    posted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_kyn_export_batches_uuid (export_uuid),
    UNIQUE KEY uq_pos_kyn_export_batches_idempotency (idempotency_key),
    KEY idx_pos_kyn_export_batches_location_status (location_id, export_status),
    CONSTRAINT fk_pos_kyn_export_batches_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_kyn_export_batches_close_id FOREIGN KEY (close_batch_id) REFERENCES pos_daily_close_batches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;