-- KYN POS Phase 1 Foundation Schema
-- Purpose: Minimal foundation tables for app settings, location links, devices, sessions, and audit logs.
-- Notes: This is not the full POS schema. Core checks/items/payments/resupply tables come in later phases.

CREATE TABLE IF NOT EXISTS pos_location_links (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_uuid CHAR(36) NOT NULL,
    kyn_location_id BIGINT UNSIGNED NULL,
    location_code VARCHAR(50) NOT NULL,
    display_name VARCHAR(150) 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_location_links_location_uuid (location_uuid),
    UNIQUE KEY uq_pos_location_links_location_code (location_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_app_settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NULL,
    value_type ENUM('string','number','boolean','json') NOT NULL DEFAULT 'string',
    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_app_settings_location_key (location_id, setting_key),
    CONSTRAINT fk_pos_app_settings_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_devices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    device_uuid CHAR(36) NOT NULL,
    device_name VARCHAR(120) NOT NULL,
    device_type ENUM('pos_terminal','kitchen_display','manager_station','tablet','unknown') NOT NULL DEFAULT 'unknown',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_seen_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_devices_device_uuid (device_uuid),
    KEY idx_pos_devices_location_id (location_id),
    CONSTRAINT fk_pos_devices_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_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NOT NULL,
    staff_id BIGINT UNSIGNED NOT NULL,
    device_id BIGINT UNSIGNED NULL,
    session_token_hash CHAR(64) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pos_staff_sessions_token_hash (session_token_hash),
    KEY idx_pos_staff_sessions_location_staff (location_id, staff_id),
    KEY idx_pos_staff_sessions_device_id (device_id),
    CONSTRAINT fk_pos_staff_sessions_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_pos_staff_sessions_device_id FOREIGN KEY (device_id) REFERENCES pos_devices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_audit_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id BIGINT UNSIGNED NULL,
    staff_id BIGINT UNSIGNED NULL,
    device_id BIGINT UNSIGNED NULL,
    event_type VARCHAR(100) NOT NULL,
    event_summary VARCHAR(255) NOT NULL,
    event_payload JSON NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pos_audit_logs_location_created (location_id, created_at),
    KEY idx_pos_audit_logs_staff_created (staff_id, created_at),
    KEY idx_pos_audit_logs_event_type (event_type),
    CONSTRAINT fk_pos_audit_logs_location_id FOREIGN KEY (location_id) REFERENCES pos_location_links(id) ON DELETE SET NULL,
    CONSTRAINT fk_pos_audit_logs_device_id FOREIGN KEY (device_id) REFERENCES pos_devices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;