-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Schema Pack Q — Per-counter event sequence v1.0
--
--  Apply AFTER schema-phase0-core-v1.sql.
--
--  =================================================================
--  WHY A COLUMN INSTEAD OF MAX(local_seq)
--  =================================================================
--
--  Event sequences were allocated with
--
--      SELECT COALESCE(MAX(local_seq),0)+1 FROM event_log
--       WHERE counter_id = ?
--
--  which has two faults, both found by running three processes against
--  one shop rather than by reading the code.
--
--  1. THE SNAPSHOT. MySQL fixes a transaction's read view at its first
--     consistent read, not at BEGIN. Billing reads the item, batch and
--     stock before writing its event, so this SELECT returned a value
--     from before the other session committed. Two writers picked the
--     same number and the unique key refused the loser — two thirds of
--     bills, when three sessions shared a counter.
--
--  2. THE GAP LOCK. Making it a locking read fixed the snapshot and
--     introduced deadlocks: the aggregate scans uk_event_seq and locks
--     the supremum gap, which is SHARED ACROSS ALL COUNTERS. Counter 1
--     allocating a number blocked counter 3 allocating a different one.
--     InnoDB reported exactly that.
--
--  A column on the counter row has neither problem. UPDATE always reads
--  current data, so there is no snapshot; and it locks one row, so
--  counters do not interfere with each other at all.
--
--  local_seq stays on event_log — it is the replication ordering and
--  the unique key still guards it. This column is only the allocator.
-- =====================================================================

SET NAMES utf8mb4;

ALTER TABLE counter
  ADD COLUMN last_event_seq BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER last_seen_at;

-- Existing installations: carry over whatever the log already reached,
-- or the next allocation would collide with history.
UPDATE counter c
   SET c.last_event_seq = COALESCE(
       (SELECT MAX(e.local_seq) FROM event_log e WHERE e.counter_id = c.id), 0);

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. Three sessions on one counter all bill: 120 of 120.
--   2. Counters do not block each other — no cross-counter deadlock.
--   3. The column is never behind the log: an allocation that returned
--      a number already used would fail on uk_event_seq, which is the
--      backstop, not the mechanism.
-- =====================================================================