-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Schema Pack M — Self-update v1.0
--
--  Apply AFTER packs K (auth) and L (backup).
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  THE OPERATING RULE THIS ENCODES
--  =================================================================
--
--  Updates install during WORKING HOURS, lunch slot preferred, and
--  NEVER overnight.
--
--  That is the opposite of what most software does, and it is
--  deliberate. An overnight update that goes wrong is discovered by a
--  chemist at 9am with customers queuing and nobody at Caresoft awake.
--  The same update applied at 3pm is discovered in ninety seconds by
--  someone who can phone a partner who can phone us. Choosing the
--  inconvenient window is choosing to be present when it fails.
--
--  =================================================================
--  AND THE ORDER THAT MATTERS
--  =================================================================
--
--   1. verify the package signature   — before it is unpacked
--   2. take a PRE_UPGRADE backup      — and verify THAT by restoring it
--   3. apply schema packs             — in the declared order
--   4. swap the files
--   5. run the invariant suite        — the same one that runs nightly
--   6. on any failure, roll back      — files AND database
--
--  Step 5 is the one usually skipped. A store that starts an upgrade
--  and finishes it with a broken ledger has not been upgraded, it has
--  been broken, and nobody will know until the GST return.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE update_run (
  id                CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  from_version      VARCHAR(20)   NOT NULL,
  to_version        VARCHAR(20)   NOT NULL,
  channel           ENUM('CANARY','EARLY','GENERAL') NOT NULL DEFAULT 'GENERAL',
  trigger_kind      ENUM('SCHEDULED','MANUAL','FORCED') NOT NULL DEFAULT 'SCHEDULED',
  package_sha256    CHAR(64)      NOT NULL,
  signature_ok      TINYINT(1)    NOT NULL DEFAULT 0,
  pre_backup_id     CHAR(26)          NULL COMMENT 'Refuses to proceed without one',
  -- Progress, so a failure says WHERE rather than "update failed"
  stage             ENUM('QUEUED','VERIFYING','BACKING_UP','MIGRATING','SWAPPING',
                         'CHECKING','DONE','ROLLED_BACK','FAILED') NOT NULL DEFAULT 'QUEUED',
  packs_applied     SMALLINT      NOT NULL DEFAULT 0,
  invariants_run    SMALLINT      NOT NULL DEFAULT 0,
  invariants_failed SMALLINT      NOT NULL DEFAULT 0,
  failure           VARCHAR(500)      NULL,
  rolled_back       TINYINT(1)    NOT NULL DEFAULT 0,
  started_at        DATETIME(3)   NOT NULL,
  finished_at       DATETIME(3)       NULL,
  duration_ms       INT UNSIGNED  NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY ix_ur_store (store_id, started_at),
  KEY ix_ur_stage (stage, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
--  Which ring a store is in, and when it may be interrupted.
--
--  Rings exist so a bad release reaches five shops rather than two
--  thousand. The canary ring should be stores whose owners know they
--  are in it and have a direct line to the partner.
-- ---------------------------------------------------------------------
CREATE TABLE update_policy (
  id                CHAR(26)      NOT NULL,
  store_id          CHAR(26)      NOT NULL,
  channel           ENUM('CANARY','EARLY','GENERAL') NOT NULL DEFAULT 'GENERAL',
  window_from_hour  TINYINT       NOT NULL DEFAULT 13 COMMENT 'Local hour, inclusive',
  window_to_hour    TINYINT       NOT NULL DEFAULT 16 COMMENT 'Local hour, exclusive',
  -- A shop that is genuinely 24x7 still has a quiet hour; it does not
  -- have a closed one. The window is per store for that reason.
  paused            TINYINT(1)    NOT NULL DEFAULT 0,
  paused_reason     VARCHAR(200)      NULL,
  min_idle_seconds  SMALLINT      NOT NULL DEFAULT 120
                    COMMENT 'No billing activity for this long before starting',
  current_version   VARCHAR(20)   NOT NULL DEFAULT '0.0.0',
  row_ver           BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at        DATETIME(3)   NOT NULL,
  updated_at        DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_up (store_id),
  KEY ix_up_channel (channel, paused)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. An unsigned or tampered package is never unpacked.
--   2. No upgrade starts without a VERIFIED pre-upgrade backup.
--   3. No upgrade starts outside the store's window, or while the
--      counter is busy.
--   4. A failed invariant check rolls the store back, database included.
--   5. A rollback leaves the store on its previous version, working.
--   6. Every stage is recorded, so a failure says where it stopped.
-- =====================================================================