-- ─────────────────────────────────────────────────────────────────────────────
-- 064 — Align the enabled policies with what the host can actually execute
--
-- ── WHAT WENT WRONG ──────────────────────────────────────────────────────────
--
-- The 058 seed enabled `full`, `diff` and `incr` by column default. All three
-- are pgBackRest backup types. The deployed server has no pgBackRest and no
-- privileged agent, so `runPolicy` refused them — correctly — on every tick.
--
-- A refusal deliberately does not advance `nextRunAt`, so each stayed
-- permanently due: refused once a minute, silently, for as long as the server
-- has been up. Meanwhile the two policies this host CAN run — the logical
-- pg_dump and the physical base backup — shipped `isEnabled = false`, because
-- they were framed as "dev only". The server is the same kind of host as a
-- developer machine in this respect, so nothing that could run was on, and
-- everything that was on could not run.
--
-- The visible symptom was an hourly `sla_breach` alert per policy and no
-- backups at all. The invisible one was worse: the screen said "enabled".
--
-- ── WHAT THIS DOES ───────────────────────────────────────────────────────────
--
--   1. Turns off the three pgBackRest-only policies. They are not deleted —
--      when pgBackRest is deployed, set isEnabled = true and they resume.
--   2. Turns on the logical dump, which needs nothing but pg_dump and
--      DATABASE_URL, and drops "(dev)" from its name because it is now the
--      primary backup on every host.
--   3. Clears the alert rows the old state produced, including the duplicate
--      that has been failing `backupAlert.create()` with P2002 every hour.
--
-- `bkp_basebackup` is deliberately left DISABLED. pg_basebackup needs a role
-- with REPLICATION and `wal_level >= replica` on the server; neither is
-- verified there yet. Enabling it blind would swap an honest "not running" for
-- a failing run, which is a worse trade. See docs/27-local-pitr-guide.md.

BEGIN;

-- 1. The three that cannot run here.
UPDATE platform.backup_policies
   SET "isEnabled" = false,
       "updatedOn" = CURRENT_TIMESTAMP
 WHERE "id" IN ('bkp_full', 'bkp_diff', 'bkp_incr');

-- 2. The one that can. nextRunAt is cleared so initialiseSchedules() computes
--    it from the cron expression on the next tick rather than inheriting a
--    stale instant.
UPDATE platform.backup_policies
   SET "isEnabled"   = true,
       "name"        = 'Logical dump → R2',
       "nextRunAt"   = NULL,
       "updatedOn"   = CURRENT_TIMESTAMP
 WHERE "id" = 'bkp_local_dump';

-- 3a. Normalise the key skew. '' and NULL are the same key to
--     `backup_alerts_one_open_per_kind` (it indexes COALESCE("targetId", ''))
--     but different to a Prisma query, which is how a row could exist in the
--     index while the dedupe lookup failed to see it — and then how
--     `create()` hit a duplicate key. Resolve the older of any such pair
--     first, so the normalising UPDATE cannot itself violate the index.
WITH ranked AS (
  SELECT "id",
         ROW_NUMBER() OVER (
           PARTITION BY "kind", COALESCE("targetId", '')
           ORDER BY "lastSeenAt" DESC, "firstSeenAt" DESC
         ) AS rn
    FROM platform.backup_alerts
   WHERE "status" <> 'resolved'
)
UPDATE platform.backup_alerts a
   SET "status"     = 'resolved',
       "resolvedAt" = CURRENT_TIMESTAMP
  FROM ranked
 WHERE a."id" = ranked."id"
   AND ranked.rn > 1;

UPDATE platform.backup_alerts
   SET "targetId" = NULL
 WHERE "targetId" = '';

-- 3b. Close the alerts describing the state this migration just fixed. They
--     re-raise within the hour if the condition somehow survives, which is the
--     property that makes auto-resolution safe here.
UPDATE platform.backup_alerts
   SET "status"     = 'resolved',
       "resolvedAt" = CURRENT_TIMESTAMP
 WHERE "status" <> 'resolved'
   AND "kind" IN ('sla_breach', 'policy_unsupported');

COMMIT;