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;