-- ─────────────────────────────────────────────────────────────────────────────
-- 063 — Separate WAL retention from backup retention
--
-- Until now there was one `retentionDays`, and — worth saying plainly — NOTHING
-- enforced it. Only `retentionCount` pruned anything, by count. The days value
-- was displayed, configurable, and decorative. This migration makes retention
-- real and splits it, because the two halves buy genuinely different things.
--
-- ── THE TWO-TIER MODEL, AND THE TRAP IN IT ───────────────────────────────────
--
-- Keeping WAL 5 days and backups 15 days does NOT give 15 days of point-in-time
-- recovery. It gives two tiers:
--
--   0–5 days ago    ANY SECOND.        WAL exists, so it can be replayed.
--   5–15 days ago   BACKUP INSTANTS.   No journal survives; a backup restores
--                                      to the moment it was taken, nothing
--                                      between.
--   > 15 days       Nothing.
--
-- That is a sensible, cheap policy — recent mistakes are the ones you recover
-- from precisely, and older ones you recover from coarsely. It is only
-- dangerous if the screen implies 15 days of PITR, so the admin UI states both
-- tiers explicitly and computes them from these values rather than reciting a
-- number somebody typed.
--
-- ── WHY WAL IS THE SHORTER ONE ───────────────────────────────────────────────
--
-- WAL is charged per 16 MB segment regardless of how full it is, so its cost
-- scales with archive_timeout and time, not with how much you actually changed.
-- Backups compress to a fraction of the cluster (measured here: 435 MB -> 45 MB)
-- and are taken once a day. Days of WAL cost far more than days of backups, so
-- spending the budget on a short precise window plus a long coarse one buys
-- more recovery than spreading it evenly.
--
-- Defaults chosen: WAL 5 days, backups 15 days.
-- ─────────────────────────────────────────────────────────────────────────────

BEGIN;

ALTER TABLE platform.backup_repositories
  ADD COLUMN IF NOT EXISTS "retentionWalDays"    INTEGER NOT NULL DEFAULT 5,
  ADD COLUMN IF NOT EXISTS "retentionBackupDays" INTEGER NOT NULL DEFAULT 15;

-- WAL retention shorter than backup retention is the intended shape, but the
-- reverse is not wrong either — it just means every backup is fully
-- replayable. What IS wrong is zero, which would delete everything on the next
-- sweep, so the floor is enforced here rather than trusted to the UI.
ALTER TABLE platform.backup_repositories
  DROP CONSTRAINT IF EXISTS "backup_repositories_retention_positive";
ALTER TABLE platform.backup_repositories
  ADD CONSTRAINT "backup_repositories_retention_positive"
  CHECK ("retentionWalDays" >= 1 AND "retentionBackupDays" >= 1);

UPDATE platform.backup_repositories
   SET "retentionWalDays"    = 5,
       "retentionBackupDays" = 15,
       "updatedOn"           = CURRENT_TIMESTAMP
 WHERE "code" = 'r2-primary';

-- The local pgBackRest spool is an outage buffer, not an archive: it holds
-- only enough to survive R2 being unreachable, and long retention there fills
-- the disk the database lives on.
UPDATE platform.backup_repositories
   SET "retentionWalDays"    = 2,
       "retentionBackupDays" = 3,
       "updatedOn"           = CURRENT_TIMESTAMP
 WHERE "code" = 'local';

COMMIT;