-- ============================================================================
-- PMPos Cloud Print System — Database Schema
-- MySQL 8.0+ required (uses SELECT ... FOR UPDATE SKIP LOCKED for safe
-- concurrent job dispatch — this is what prevents duplicate printing when
-- an agent polls, and lets you safely run more than one agent later).
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- print_agents: one row per Windows print agent (e.g. "Front Desk Rongta").
-- Tokens are never stored in plaintext — only a SHA-256 hash. The plaintext
-- token is shown exactly once, at creation time, by admin/generate_token.php.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS print_agents (
    id              INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    agent_name      VARCHAR(100)    NOT NULL,
    token_hash      CHAR(64)        NOT NULL COMMENT 'sha256(token), hex',
    is_active       TINYINT(1)      NOT NULL DEFAULT 1,
    printer_target  VARCHAR(64)     NOT NULL DEFAULT 'rongta_80mm',
    last_seen_at    DATETIME        NULL,
    last_seen_ip    VARCHAR(45)     NULL,
    agent_version   VARCHAR(32)     NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at      DATETIME        NULL,
    UNIQUE KEY uq_print_agents_token_hash (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- print_jobs: the queue itself.
--
-- Duplicate-printing prevention has two independent layers:
--   1. `idempotency_key` is UNIQUE. The caller (PMPos checkout code) must
--      pass a stable key derived from the sale (e.g. "sale-482991"). If the
--      same sale is submitted twice (double-click, retry, webhook replay),
--      the INSERT fails on the unique key and PrintQueue::createJob()
--      simply returns the existing job instead of creating a second one.
--   2. `status` + `locked_by` acts as a claim/lease. An agent can only claim
--      a job with SKIP LOCKED, and can only ack a job it currently holds the
--      lock on (locked_by = its own agent id). Two agents polling at the
--      same moment can never both receive the same job.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS print_jobs (
    id                  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    job_uuid            CHAR(36)        NOT NULL COMMENT 'public opaque id, safe to expose to frontend',
    idempotency_key     VARCHAR(191)    NOT NULL COMMENT 'stable per-sale key, e.g. sale id',
    invoice_no          VARCHAR(64)     NOT NULL,
    printer_target      VARCHAR(64)     NOT NULL DEFAULT 'rongta_80mm',

    payload_json        MEDIUMTEXT      NOT NULL COMMENT 'structured receipt data, for audit / reprint / re-render',
    escpos_base64        MEDIUMTEXT      NOT NULL COMMENT 'rendered ESC/POS byte stream, base64-encoded',

    status              ENUM('queued','sent','printed','failed','retry')
                                         NOT NULL DEFAULT 'queued',
    attempts            INT UNSIGNED    NOT NULL DEFAULT 0,
    max_attempts        INT UNSIGNED    NOT NULL DEFAULT 3,
    next_attempt_at     DATETIME        NULL,

    locked_by           INT UNSIGNED    NULL COMMENT 'print_agents.id currently holding this job',
    locked_at           DATETIME        NULL,

    error_message       VARCHAR(500)    NULL,

    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    printed_at          DATETIME        NULL,

    UNIQUE KEY uq_print_jobs_idempotency (idempotency_key),
    UNIQUE KEY uq_print_jobs_uuid (job_uuid),
    KEY idx_print_jobs_status_created (status, created_at),
    KEY idx_print_jobs_locked (locked_by, status),
    CONSTRAINT fk_print_jobs_agent FOREIGN KEY (locked_by)
        REFERENCES print_agents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- print_job_logs: append-only audit trail per job. This is what backs the
-- "clear success/failure messages and logs" requirement server-side —
-- every state transition is recorded here, independent of the Windows
-- agent's own local log file.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS print_job_logs (
    id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    job_id      BIGINT UNSIGNED NOT NULL,
    event       VARCHAR(50)     NOT NULL COMMENT 'created|duplicate_suppressed|sent_to_agent|printed|failed|retry_scheduled|reaped_stale',
    message     VARCHAR(1000)   NULL,
    agent_id    INT UNSIGNED    NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_print_job_logs_job (job_id),
    CONSTRAINT fk_print_job_logs_job FOREIGN KEY (job_id)
        REFERENCES print_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;