-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Schema Pack L — Backup runs v1.0
--
--  Apply AFTER schema-phase0-core-v1.sql.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  Support-cost rule S6: silent continuous backup, one-click restore.
--  Chemists lose databases to disk failure with depressing regularity,
--  and the incumbent's local-backup story is thin enough that "you will
--  not lose your data" is a sales line on its own.
--
--  NOTE THE STATUS VALUES. There is no "SUCCESS". A backup that has
--  been written but never restored is UNVERIFIED, because that is what
--  it is. Calling it a success is how everyone finds out on the worst
--  possible day that it never worked.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE backup_run (
  id            CHAR(26)      NOT NULL,
  store_id      CHAR(26)      NOT NULL,
  kind          ENUM('SCHEDULED','MANUAL','PRE_UPGRADE','PRE_RESTORE') NOT NULL DEFAULT 'SCHEDULED',
  destination   VARCHAR(30)   NOT NULL COMMENT 'LOCAL | S3 | ...',
  locator       VARCHAR(400)      NULL COMMENT 'NULL once pruned',
  bytes         BIGINT UNSIGNED NOT NULL DEFAULT 0,
  sha256        CHAR(64)      NOT NULL COMMENT 'Of the compressed plaintext, before encryption',
  table_count   INT UNSIGNED  NOT NULL DEFAULT 0,
  row_count     BIGINT UNSIGNED NOT NULL DEFAULT 0,
  duration_ms   INT UNSIGNED  NOT NULL DEFAULT 0,
  status        ENUM('UNVERIFIED','VERIFIED','FAILED_VERIFY','CORRUPT','DELETED')
                NOT NULL DEFAULT 'UNVERIFIED',
  note          VARCHAR(300)      NULL,
  taken_at      DATETIME(3)   NOT NULL,
  verified_at   DATETIME(3)       NULL,
  created_at    DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  KEY ix_br_store (store_id, taken_at),
  KEY ix_br_status (store_id, status, taken_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. An archive is encrypted at rest and unreadable without the key.
--   2. A tampered archive fails to decrypt rather than restoring garbage.
--   3. Verification actually RESTORES and compares row counts.
--   4. A restore refuses an unverified archive.
--   5. A restore requires the store code typed back.
--   6. Retention never leaves a store with no verified archive.
-- =====================================================================