-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Schema Pack P — Print jobs v1.0
--
--  Apply AFTER schema-phase0-core-v1.sql.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  A BILL WAS SAVED AND NEVER PRINTED
--  =================================================================
--
--  print_profile existed — layouts, column counts, command sets, all of
--  it. There was nowhere to put a rendered slip. /print/job was declared
--  in the route table with no handler, so it answered 501, and
--  PrintEngine::render() had no caller anywhere.
--
--  Meanwhile the counter screen told the biller "Saved as
--  C1/2026-27/000001 and sent to the printer". The one sentence on that
--  screen that was simply false.
--
--  =================================================================
--  WHY A QUEUE AND NOT A DIRECT WRITE
--  =================================================================
--
--  The printer is on the counter PC and the software may be on the
--  cloud. More importantly a printer is off, jammed or out of paper
--  several times a week, and none of that may block a sale. So the slip
--  is rendered and queued; the Counter Helper collects it and reports
--  back what actually came out.
--
--  SENT and PRINTED are different states on purpose. Until the Helper
--  acknowledges, nobody may claim the customer has their bill.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE print_job (
  id            CHAR(26)      NOT NULL,
  tenant_id     CHAR(26)          NULL,
  store_id      CHAR(26)      NOT NULL,
  -- Which till. A helper on counter 2 must not collect counter 1's
  -- slips and print them there — the customer is standing at the other
  -- till.
  counter_id    CHAR(26)          NULL,
  doc_type      ENUM('SALE','CREDIT_NOTE','RECEIPT','PO','LABEL','TEST') NOT NULL DEFAULT 'SALE',
  doc_id        CHAR(26)          NULL,
  profile_id    CHAR(26)          NULL,
  -- Rendered bytes, base64 so an ESC/POS control sequence survives JSON
  -- and any encoding conversion between here and the printer port.
  payload       MEDIUMTEXT    NOT NULL,
  copies        TINYINT       NOT NULL DEFAULT 1,
  status        ENUM('QUEUED','SENT','PRINTED','FAILED','CANCELLED') NOT NULL DEFAULT 'QUEUED',
  attempts      TINYINT       NOT NULL DEFAULT 0,
  last_error    VARCHAR(300)      NULL,
  queued_at     DATETIME(3)   NOT NULL,
  sent_at       DATETIME(3)       NULL,
  printed_at    DATETIME(3)       NULL,
  created_at    DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_pj_pickup (store_id, status, queued_at),
  KEY ix_pj_doc (doc_type, doc_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- A default layout, so a fresh store can print. Without one the counter
-- refuses with "no print layout is set up", which is honest but useless
-- on day one.
INSERT INTO print_profile
  (id, code, name, device_type, columns, paper_width_mm, command_set,
   template_body, is_active, row_ver, created_at, updated_at)
VALUES
  ('01JSEEDPRF000000000000001','TH80_STD','80mm thermal, standard','THERMAL',48,80,'ESCPOS',
   'ALIGN CENTER\nBOLD ON\nTEXT {store.name}\nBOLD OFF\nTEXT {store.address_line1}\nTEXT DL {const.licence}\nALIGN LEFT\nRULE\nTEXT Bill {bill.bill_no}   {bill.bill_date}\nRULE\nCOLS 22,6,6,10\nHEAD Item,Batch,Qty,Amount\nLINES {lines}\nRULE\nTOTAL Net payable,{bill.net_amount}\nTEXT Pharmacist: {const.pharmacist}\nFEED 3\nCUT',
   1, 0, NOW(3), NOW(3))
ON DUPLICATE KEY UPDATE updated_at = print_profile.updated_at;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. Saving a bill produces a queued slip.
--   2. A helper collects only its own counter's jobs.
--   3. SENT is not PRINTED: a job is only printed once acknowledged.
--   4. A failed print keeps the job and its reason, and does not lose
--      the bill.
--   5. A store with no profile is told so, rather than failing silently.
-- =====================================================================