Subversion Repositories SmartDukaan

-- Circular -> web offer sync: provenance on dtr.web_offer, plus the shadow that lets a
-- human edit win over the sync.
--
-- See docs/superpowers/specs/2026-09-03-circular-to-web-offer-sync-design.md
--
-- Safe to run on a live table: every column is nullable or defaulted, and `source`
-- defaults to MANUAL so every existing row is correctly labelled as hand-authored and is
-- never touched by the sync.

ALTER TABLE dtr.web_offer
  ADD COLUMN source ENUM('MANUAL','CIRCULAR') NOT NULL DEFAULT 'MANUAL',
  -- The PDF's own coordinates, stored as VALUES rather than a foreign key.
  -- offers.offer.id is deliberately NOT used: ids are recreated on every re-ingest, so a
  -- stored id would dangle after the next parse. (document, page, row) comes from the PDF
  -- layout and is stable across re-parses.
  ADD COLUMN circular_document_id INT NULL,
  ADD COLUMN circular_page_no SMALLINT NULL,
  ADD COLUMN circular_row_no SMALLINT NULL,
  ADD COLUMN synced_at DATETIME NULL,
  ADD UNIQUE KEY uk_circular_row
      (circular_document_id, circular_page_no, circular_row_no);

-- What the sync last wrote. Per field, on each run: live value == shadow means the sync
-- still owns it and may overwrite; live value != shadow means a human edited it, so the
-- sync leaves that field alone from then on.
--
-- Chosen over a locked_fields flag on web_offer because flags would require every
-- existing edit endpoint (/web-offer/add, /web-offer-duration/update, and the V2 mirror)
-- to record each change - and one that forgot would silently lose a human's edit. The
-- shadow needs no change to any existing write path.
CREATE TABLE IF NOT EXISTS dtr.web_offer_sync_shadow (
  web_offer_id   INT NOT NULL PRIMARY KEY,
  title          VARCHAR(256)  NULL,
  small_text     VARCHAR(256)  NULL,
  detailed_text  VARCHAR(1024) NULL,
  start_date     DATETIME NULL,
  end_date       DATETIME NULL,
  synced_at      DATETIME NOT NULL,
  CONSTRAINT fk_wos_offer FOREIGN KEY (web_offer_id)
    REFERENCES dtr.web_offer(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;