CREATE TABLE IF NOT EXISTS public_work_events (
    public_work_event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_uuid CHAR(36) NOT NULL,
    event_type VARCHAR(80) NOT NULL,
    action_code VARCHAR(80) NOT NULL,
    badge_token VARCHAR(160) NULL,
    work_token VARCHAR(160) NULL,
    component_scan_key VARCHAR(160) NULL,
    transport_manifest_token VARCHAR(160) NULL,
    job_no VARCHAR(160) NULL,
    station_code VARCHAR(80) NULL,
    result_code VARCHAR(80) NULL,
    quantity_good DECIMAL(12,4) NOT NULL DEFAULT 0,
    quantity_rework DECIMAL(12,4) NOT NULL DEFAULT 0,
    quantity_scrap DECIMAL(12,4) NOT NULL DEFAULT 0,
    worker_name VARCHAR(160) NULL,
    worker_group VARCHAR(80) NULL,
    event_note TEXT NULL,
    source_payload_hash CHAR(64) NOT NULL,
    raw_payload_json LONGTEXT NOT NULL,
    remote_ip VARCHAR(80) NULL,
    user_agent VARCHAR(500) NULL,
    import_status VARCHAR(40) NOT NULL DEFAULT 'pending',
    imported_at DATETIME NULL,
    last_error TEXT NULL,
    submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (public_work_event_id),
    UNIQUE KEY uq_public_work_events_uuid (event_uuid),
    UNIQUE KEY uq_public_work_events_hash (source_payload_hash),
    KEY idx_public_work_events_status (import_status, submitted_at),
    KEY idx_public_work_events_tokens (badge_token, work_token, component_scan_key),
    KEY idx_public_work_events_type (event_type, action_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_event_email_queue (
    public_event_email_queue_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_uuid CHAR(36) NOT NULL,
    event_type VARCHAR(80) NOT NULL,
    action_code VARCHAR(80) NOT NULL,
    source_public_id BIGINT UNSIGNED NULL,
    payload_hash CHAR(64) NOT NULL,
    recipient_email VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    text_body MEDIUMTEXT NOT NULL,
    html_body MEDIUMTEXT NULL,
    payload_json LONGTEXT NOT NULL,
    attachment_manifest_json LONGTEXT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'queued',
    attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_error TEXT NULL,
    queued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (public_event_email_queue_id),
    UNIQUE KEY uq_public_event_email_queue_event_recipient (event_uuid, recipient_email),
    KEY idx_public_event_email_queue_status (status, queued_at),
    KEY idx_public_event_email_queue_source (source_public_id),
    KEY idx_public_event_email_queue_type (event_type, action_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_work_event_files (
    public_work_event_file_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    public_work_event_id BIGINT UNSIGNED NOT NULL,
    event_uuid CHAR(36) NOT NULL,
    file_role VARCHAR(80) NOT NULL DEFAULT 'attachment',
    original_file_name VARCHAR(255) NOT NULL,
    stored_file_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    file_size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    sha256_hash CHAR(64) NOT NULL,
    storage_status VARCHAR(40) NOT NULL DEFAULT 'stored',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (public_work_event_file_id),
    UNIQUE KEY uq_public_work_event_files_event_hash (event_uuid, sha256_hash),
    KEY idx_public_work_event_files_event (public_work_event_id),
    KEY idx_public_work_event_files_uuid (event_uuid),
    CONSTRAINT fk_public_work_event_files_event
        FOREIGN KEY (public_work_event_id)
        REFERENCES public_work_events (public_work_event_id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_phone_registration_requests (
    public_phone_registration_request_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    registration_uuid CHAR(36) NOT NULL,
    session_uuid CHAR(36) NOT NULL,
    challenge_token VARCHAR(160) NOT NULL,
    challenge_token_hash CHAR(64) NOT NULL,
    badge_token VARCHAR(160) NOT NULL,
    session_token_hash CHAR(64) NOT NULL,
    device_id_hash CHAR(64) NULL,
    device_label VARCHAR(160) NULL,
    remote_ip VARCHAR(80) NULL,
    user_agent VARCHAR(500) NULL,
    request_status VARCHAR(40) NOT NULL DEFAULT 'pending',
    erp_result_json LONGTEXT NULL,
    last_error TEXT NULL,
    submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (public_phone_registration_request_id),
    UNIQUE KEY uq_public_phone_registration_uuid (registration_uuid),
    UNIQUE KEY uq_public_phone_registration_session (session_uuid),
    KEY idx_public_phone_registration_status (request_status, submitted_at),
    KEY idx_public_phone_registration_badge (badge_token),
    KEY idx_public_phone_registration_challenge (challenge_token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_phone_sessions (
    public_phone_session_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    session_uuid CHAR(36) NOT NULL,
    registration_uuid CHAR(36) NOT NULL,
    session_token_hash CHAR(64) NOT NULL,
    badge_token VARCHAR(160) NOT NULL,
    worker_name VARCHAR(160) NULL,
    worker_group VARCHAR(80) NULL,
    badge_mode_code VARCHAR(80) NULL,
    badge_mode_label VARCHAR(160) NULL,
    allowed_station_codes TEXT NULL,
    site_code VARCHAR(80) NULL,
    station_scope VARCHAR(80) NULL,
    device_id_hash CHAR(64) NULL,
    user_agent_hash CHAR(64) NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'pending',
    valid_from DATETIME NULL,
    expires_at DATETIME NULL,
    revoked_at DATETIME NULL,
    last_seen_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (public_phone_session_id),
    UNIQUE KEY uq_public_phone_session_uuid (session_uuid),
    UNIQUE KEY uq_public_phone_session_token (session_token_hash),
    UNIQUE KEY uq_public_phone_session_registration (registration_uuid),
    KEY idx_public_phone_session_status (status, expires_at),
    KEY idx_public_phone_session_badge (badge_token),
    KEY idx_public_phone_session_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_phone_order_snapshot_requests (
    public_phone_order_snapshot_request_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    request_uuid CHAR(36) NOT NULL,
    session_uuid CHAR(36) NOT NULL,
    session_token_hash CHAR(64) NOT NULL,
    order_scan_token VARCHAR(180) NOT NULL,
    request_status VARCHAR(40) NOT NULL DEFAULT 'pending',
    last_error TEXT NULL,
    remote_ip VARCHAR(80) NULL,
    user_agent VARCHAR(500) NULL,
    submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (public_phone_order_snapshot_request_id),
    UNIQUE KEY uq_public_phone_order_snapshot_request_uuid (request_uuid),
    KEY idx_public_phone_order_snapshot_request_status (request_status, submitted_at),
    KEY idx_public_phone_order_snapshot_request_session (session_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_phone_order_snapshots (
    public_phone_order_snapshot_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    request_uuid CHAR(36) NOT NULL,
    session_uuid CHAR(36) NOT NULL,
    snapshot_status VARCHAR(40) NOT NULL DEFAULT 'ready',
    snapshot_json LONGTEXT NOT NULL,
    generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (public_phone_order_snapshot_id),
    UNIQUE KEY uq_public_phone_order_snapshot_request_uuid (request_uuid),
    KEY idx_public_phone_order_snapshot_session (session_uuid),
    KEY idx_public_phone_order_snapshot_generated (generated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS public_phone_scan_commands (
    public_phone_scan_command_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    command_uuid CHAR(36) NOT NULL,
    session_uuid CHAR(36) NOT NULL,
    session_token_hash CHAR(64) NOT NULL,
    command_type VARCHAR(80) NOT NULL DEFAULT 'component_scan',
    order_scan_token VARCHAR(180) NULL,
    component_scan_key VARCHAR(180) NULL,
    action_code VARCHAR(80) NOT NULL DEFAULT 'done',
    command_status VARCHAR(40) NOT NULL DEFAULT 'pending',
    last_error TEXT NULL,
    result_json LONGTEXT NULL,
    remote_ip VARCHAR(80) NULL,
    user_agent VARCHAR(500) NULL,
    submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (public_phone_scan_command_id),
    UNIQUE KEY uq_public_phone_scan_command_uuid (command_uuid),
    KEY idx_public_phone_scan_command_status (command_status, submitted_at),
    KEY idx_public_phone_scan_command_session (session_uuid),
    KEY idx_public_phone_scan_command_tokens (order_scan_token, component_scan_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;