-- 024_order_verification.sql
-- StarPost public scan reference for customer QR scans and internal staff scanners.
-- The QR image contains only cfg.appUrl + /s/:scanRef; all order data remains server-side.

CREATE TABLE IF NOT EXISTS order_verification (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  payment_id      UUID NOT NULL REFERENCES payments(payment_id) ON DELETE RESTRICT,
  customer_id     UUID NOT NULL REFERENCES customers(customer_id) ON DELETE RESTRICT,
  scan_ref        TEXT NOT NULL UNIQUE,
  status          TEXT NOT NULL DEFAULT 'verified'
                  CHECK (status IN ('verified', 'revoked', 'void')),
  first_scan_at   TIMESTAMPTZ,
  last_scan_at    TIMESTAMPTZ,
  scan_count      INTEGER NOT NULL DEFAULT 0 CHECK (scan_count >= 0),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT order_verification_one_per_customer UNIQUE (customer_id),
  CONSTRAINT order_verification_one_per_payment UNIQUE (payment_id),
  CONSTRAINT order_verification_scan_ref_format
    CHECK (scan_ref ~ '^SP1-[A-Z0-9]{4}-[A-Z0-9]{4}-[A-Z0-9]{4}$')
);

CREATE INDEX IF NOT EXISTS idx_order_verification_scan_ref
  ON order_verification(scan_ref);

CREATE INDEX IF NOT EXISTS idx_order_verification_customer_id
  ON order_verification(customer_id);

CREATE INDEX IF NOT EXISTS idx_order_verification_payment_id
  ON order_verification(payment_id);

CREATE INDEX IF NOT EXISTS idx_order_verification_status
  ON order_verification(status);

COMMENT ON TABLE order_verification IS 'Public scan reference for StarPost QR/product verification. Contains no PII.';
COMMENT ON COLUMN order_verification.scan_ref IS 'Public ref encoded into QR URLs, e.g. SP1-8M2K-4QJD-H7XW.';
COMMENT ON COLUMN order_verification.status IS 'verified = public page shows authentic; revoked/void = not valid.';

