-- ─────────────────────────────────────────────────────────────────────────────
-- 057 — Database backup, PITR, Time Travel and maintenance
--
-- Seventeen tables in `platform`, backing plan-database-backup.md. Grouped
-- here in one migration because they are one subsystem: a backup run is
-- meaningless without the repository it wrote to, the WAL segments that make it
-- restorable, and the recovery window computed from both.
--
-- Three constraints in here are load-bearing and were bugs in an earlier
-- design, so they are enforced by the database rather than by convention:
--
--   1. backup_targets.stanza and .pgDataPath are BOTH unique. A pgBackRest
--      stanza is keyed by pg1-path, and a Postgres cluster has exactly one
--      archive_command — so two stanzas over one data directory means the
--      second one silently never receives WAL. Its "backups" would restore
--      only to their own stop time, and nothing would report a problem. One
--      cluster, one stanza, one row.
--
--   2. backup_repositories carries quotaBytes. This deployment has a hard 1 GB
--      R2 budget, and a backup system that fills its own bucket stops
--      archiving WAL while still reporting success. The ceiling belongs in the
--      data, checked before each run, not in a comment someone reads later.
--
--   3. platform.schema_migrations exists at all. Right now `prisma/migrations`
--      holds 2 tracked migrations while `prisma/migrations/manual` holds 56
--      hand-applied files, and chat-service holds 9 more with no tracking —
--      so nothing records what schema version any database is at. A restored
--      database whose version cannot be identified cannot be rolled forward,
--      which is how a successful restore becomes a failed recovery. This table
--      is the ledger; 058 backfills it.
--
-- Idempotent throughout (IF NOT EXISTS) so a partial apply can be re-run.
-- ─────────────────────────────────────────────────────────────────────────────

BEGIN;

-- ── Configuration: what we back up, and where to ─────────────────────────────

CREATE TABLE IF NOT EXISTS platform.backup_targets (
  "id"           TEXT PRIMARY KEY,
  "code"         TEXT NOT NULL,
  "name"         TEXT NOT NULL,
  "engine"       TEXT NOT NULL DEFAULT 'postgres',
  "stanza"       TEXT NOT NULL,
  "host"         TEXT NOT NULL DEFAULT 'localhost',
  "port"         INTEGER NOT NULL DEFAULT 5432,
  "databases"    TEXT[] NOT NULL DEFAULT '{}',
  "dsnRef"       TEXT NOT NULL DEFAULT 'DATABASE_URL',
  "pgDataPath"   TEXT NOT NULL,
  "sizeBytes"    BIGINT NOT NULL DEFAULT 0,
  "isActive"     BOOLEAN NOT NULL DEFAULT true,
  "sortOrder"    INTEGER NOT NULL DEFAULT 0,
  "createdBy"    TEXT NOT NULL DEFAULT 'system',
  "createdOn"    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedBy"    TEXT NOT NULL DEFAULT 'system',
  "updatedOn"    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deletedAt"    TIMESTAMP(3),
  "deletedBy"    TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS "backup_targets_code_key"       ON platform.backup_targets("code");
-- Constraint 1. Not decoration: these two uniques are what make the
-- one-cluster-one-stanza rule unrepresentable-if-violated.
CREATE UNIQUE INDEX IF NOT EXISTS "backup_targets_stanza_key"     ON platform.backup_targets("stanza");
CREATE UNIQUE INDEX IF NOT EXISTS "backup_targets_pgDataPath_key" ON platform.backup_targets("pgDataPath");

CREATE TABLE IF NOT EXISTS platform.backup_repositories (
  "id"            TEXT PRIMARY KEY,
  "code"          TEXT NOT NULL,
  "name"          TEXT NOT NULL,
  "kind"          TEXT NOT NULL,
  "repoIndex"     INTEGER NOT NULL,
  "endpoint"      TEXT,
  "bucket"        TEXT,
  "region"        TEXT DEFAULT 'auto',
  "pathPrefix"    TEXT,
  -- An env key such as 'R2_BACKUP_WRITE'. Never a credential value: there is
  -- no read path for a secret anywhere in the API, and adding one here would
  -- create the first.
  "credentialRef" TEXT,
  "encrypted"     BOOLEAN NOT NULL DEFAULT true,
  "immutable"     BOOLEAN NOT NULL DEFAULT false,
  "appendOnly"    BOOLEAN NOT NULL DEFAULT false,
  "retentionFull" INTEGER NOT NULL DEFAULT 2,
  "retentionDiff" INTEGER NOT NULL DEFAULT 4,
  "retentionDays" INTEGER NOT NULL DEFAULT 7,
  -- Constraint 2. 1 GB = 1073741824.
  "quotaBytes"    BIGINT NOT NULL DEFAULT 1073741824,
  "softLimitPct"  INTEGER NOT NULL DEFAULT 70,
  "hardLimitPct"  INTEGER NOT NULL DEFAULT 90,
  "usedBytes"     BIGINT NOT NULL DEFAULT 0,
  "backupBytes"   BIGINT NOT NULL DEFAULT 0,
  "walBytes"      BIGINT NOT NULL DEFAULT 0,
  "objectCount"   INTEGER NOT NULL DEFAULT 0,
  "status"        TEXT NOT NULL DEFAULT 'ok',
  "lastCheckedAt" TIMESTAMP(3),
  "lastError"     TEXT,
  "isActive"      BOOLEAN NOT NULL DEFAULT true,
  "sortOrder"     INTEGER NOT NULL DEFAULT 0,
  "createdBy"     TEXT NOT NULL DEFAULT 'system',
  "createdOn"     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedBy"     TEXT NOT NULL DEFAULT 'system',
  "updatedOn"     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deletedAt"     TIMESTAMP(3),
  "deletedBy"     TEXT,
  -- A soft limit at or above the hard limit means the warning never fires
  -- before the refusal, which defeats the point of having two.
  CONSTRAINT "backup_repositories_limits_ordered" CHECK ("softLimitPct" < "hardLimitPct"),
  CONSTRAINT "backup_repositories_limits_sane"    CHECK ("hardLimitPct" <= 100)
);

CREATE UNIQUE INDEX IF NOT EXISTS "backup_repositories_code_key"      ON platform.backup_repositories("code");
CREATE UNIQUE INDEX IF NOT EXISTS "backup_repositories_repoIndex_key" ON platform.backup_repositories("repoIndex");

CREATE TABLE IF NOT EXISTS platform.backup_policies (
  "id"                  TEXT PRIMARY KEY,
  "targetId"            TEXT NOT NULL REFERENCES platform.backup_targets("id") ON DELETE CASCADE,
  "name"                TEXT NOT NULL,
  "backupType"          TEXT NOT NULL,
  "cronExpression"      TEXT NOT NULL,
  "timezone"            TEXT NOT NULL DEFAULT 'Asia/Kolkata',
  "windowMinutes"       INTEGER NOT NULL DEFAULT 240,
  "retentionCount"      INTEGER,
  "retentionDays"       INTEGER,
  "skipIfRunning"       BOOLEAN NOT NULL DEFAULT true,
  "slaHours"            INTEGER NOT NULL DEFAULT 30,
  "estimatedBytes"      BIGINT NOT NULL DEFAULT 0,
  "isEnabled"           BOOLEAN NOT NULL DEFAULT true,
  "lastRunAt"           TIMESTAMP(3),
  "nextRunAt"           TIMESTAMP(3),
  "consecutiveFailures" INTEGER NOT NULL DEFAULT 0,
  "isActive"            BOOLEAN NOT NULL DEFAULT true,
  "sortOrder"           INTEGER NOT NULL DEFAULT 0,
  "createdBy"           TEXT NOT NULL DEFAULT 'system',
  "createdOn"           TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedBy"           TEXT NOT NULL DEFAULT 'system',
  "updatedOn"           TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deletedAt"           TIMESTAMP(3),
  "deletedBy"           TEXT
);

CREATE INDEX IF NOT EXISTS "backup_policies_targetId_isEnabled_idx" ON platform.backup_policies("targetId", "isEnabled");
-- The scheduler's hot path: "which policies are due?" every minute.
CREATE INDEX IF NOT EXISTS "backup_policies_nextRunAt_idx"          ON platform.backup_policies("nextRunAt");

-- ── Events: what actually happened ───────────────────────────────────────────

CREATE TABLE IF NOT EXISTS platform.backup_runs (
  "id"            TEXT PRIMARY KEY,
  "targetId"      TEXT NOT NULL REFERENCES platform.backup_targets("id") ON DELETE CASCADE,
  "policyId"      TEXT REFERENCES platform.backup_policies("id") ON DELETE SET NULL,
  "repositoryId"  TEXT REFERENCES platform.backup_repositories("id") ON DELETE SET NULL,
  "backupType"    TEXT NOT NULL,
  "trigger"       TEXT NOT NULL DEFAULT 'scheduled',
  "status"        TEXT NOT NULL DEFAULT 'running',
  "label"         TEXT,
  "lsnStart"      TEXT,
  "lsnStop"       TEXT,
  "timeline"      INTEGER,
  "walStart"      TEXT,
  "walStop"       TEXT,
  "sizeBytes"     BIGINT NOT NULL DEFAULT 0,
  "repoSizeBytes" BIGINT NOT NULL DEFAULT 0,
  "fileCount"     INTEGER NOT NULL DEFAULT 0,
  "checksum"      TEXT,
  "manifestKey"   TEXT,
  -- The ledger head at backup time. A backup that does not know its own schema
  -- version cannot be rolled forward on restore.
  "schemaVersion" TEXT,
  "verifyStatus"  TEXT NOT NULL DEFAULT 'unverified',
  "verifiedAt"    TIMESTAMP(3),
  "durationMs"    INTEGER,
  "error"         TEXT,
  "startedAt"     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "finishedAt"    TIMESTAMP(3),
  "expiresAt"     TIMESTAMP(3),
  "actorId"       TEXT,
  "actorEmail"    TEXT
);

CREATE INDEX IF NOT EXISTS "backup_runs_targetId_startedAt_idx" ON platform.backup_runs("targetId", "startedAt");
CREATE INDEX IF NOT EXISTS "backup_runs_status_startedAt_idx"   ON platform.backup_runs("status", "startedAt");

CREATE TABLE IF NOT EXISTS platform.wal_archive_segments (
  "id"              TEXT PRIMARY KEY,
  "targetId"        TEXT NOT NULL REFERENCES platform.backup_targets("id") ON DELETE CASCADE,
  "repositoryId"    TEXT REFERENCES platform.backup_repositories("id") ON DELETE SET NULL,
  "segmentName"     TEXT NOT NULL,
  "timeline"        INTEGER NOT NULL,
  "sizeBytes"       INTEGER NOT NULL DEFAULT 0,
  "compressedBytes" INTEGER NOT NULL DEFAULT 0,
  "checksum"        TEXT,
  "status"          TEXT NOT NULL DEFAULT 'pending',
  "lagSeconds"      INTEGER,
  "archivedAt"      TIMESTAMP(3),
  "uploadedAt"      TIMESTAMP(3),
  "error"           TEXT,
  "createdAt"       TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS "wal_archive_segments_unique"
  ON platform.wal_archive_segments("targetId", "segmentName", "timeline");
CREATE INDEX IF NOT EXISTS "wal_archive_segments_targetId_archivedAt_idx"
  ON platform.wal_archive_segments("targetId", "archivedAt");
CREATE INDEX IF NOT EXISTS "wal_archive_segments_status_idx"
  ON platform.wal_archive_segments("status");
-- The measured-RPO query — max(uploadedAt) per target — runs every two minutes
-- and is the number the Overview tile shows. Worth its own index.
CREATE INDEX IF NOT EXISTS "wal_archive_segments_uploadedAt_idx"
  ON platform.wal_archive_segments("targetId", "uploadedAt" DESC NULLS LAST);

CREATE TABLE IF NOT EXISTS platform.recovery_points (
  "id"             TEXT PRIMARY KEY,
  "targetId"       TEXT NOT NULL REFERENCES platform.backup_targets("id") ON DELETE CASCADE,
  "timeline"       INTEGER NOT NULL,
  "windowStart"    TIMESTAMP(3) NOT NULL,
  "windowEnd"      TIMESTAMP(3) NOT NULL,
  "anchorBackupId" TEXT,
  "segmentCount"   INTEGER NOT NULL DEFAULT 0,
  "isCurrent"      BOOLEAN NOT NULL DEFAULT false,
  "continuity"     TEXT NOT NULL DEFAULT 'continuous',
  "computedAt"     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS "recovery_points_targetId_windowEnd_idx"
  ON platform.recovery_points("targetId", "windowEnd");

CREATE TABLE IF NOT EXISTS platform.database_health_snapshots (
  "id"                TEXT PRIMARY KEY,
  "targetId"          TEXT NOT NULL REFERENCES platform.backup_targets("id") ON DELETE CASCADE,
  "databaseName"      TEXT,
  "databaseBytes"     BIGINT NOT NULL DEFAULT 0,
  "indexBytes"        BIGINT NOT NULL DEFAULT 0,
  "bloatBytes"        BIGINT NOT NULL DEFAULT 0,
  "tableCount"        INTEGER NOT NULL DEFAULT 0,
  "connectionCount"   INTEGER NOT NULL DEFAULT 0,
  "maxConnections"    INTEGER NOT NULL DEFAULT 0,
  "cacheHitRatio"     DOUBLE PRECISION,
  "longestTxnSeconds" INTEGER,
  "deadTupleCount"    BIGINT NOT NULL DEFAULT 0,
  "oldestXidAge"      INTEGER,
  "walBytesPerHour"   BIGINT NOT NULL DEFAULT 0,
  "diskFreeBytes"     BIGINT NOT NULL DEFAULT 0,
  -- "Can we still restore ourselves onto this disk?" crosses well before
  -- "is the disk full?" — a sandbox restore needs roughly 2x the database.
  "restoreHeadroomOk" BOOLEAN NOT NULL DEFAULT true,
  "status"            TEXT NOT NULL DEFAULT 'healthy',
  "collectedAt"       TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS "database_health_snapshots_targetId_collectedAt_idx"
  ON platform.database_health_snapshots("targetId", "collectedAt");

-- ── Restore, verification, drills ────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS platform.restore_jobs (
  "id"                    TEXT PRIMARY KEY,
  "targetId"              TEXT NOT NULL REFERENCES platform.backup_targets("id") ON DELETE CASCADE,
  "sourceRunId"           TEXT,
  "scope"                 TEXT NOT NULL,
  "databaseName"          TEXT,
  "organizationId"        TEXT,
  "tableList"             TEXT[] NOT NULL DEFAULT '{}',
  "targetTime"            TIMESTAMP(3),
  "targetLsn"             TEXT,
  "targetTimeline"        INTEGER,
  "environment"           TEXT NOT NULL DEFAULT 'sandbox',
  "sandboxName"           TEXT,
  "sandboxPort"           INTEGER,
  "sandboxDsnRef"         TEXT,
  -- Configurable from day one: once the database outgrows the VPS disk the
  -- sandbox has to move off-box, and that must be a setting, not a rewrite.
  "sandboxHost"           TEXT NOT NULL DEFAULT 'localhost',
  "status"                TEXT NOT NULL DEFAULT 'requested',
  "phase"                 TEXT,
  "progressPct"           INTEGER NOT NULL DEFAULT 0,
  "bytesDownloaded"       BIGINT NOT NULL DEFAULT 0,
  "rowsRestored"          BIGINT NOT NULL DEFAULT 0,
  "schemaVersionRestored" TEXT,
  "schemaVersionTarget"   TEXT,
  "migrationsReplayed"    TEXT[] NOT NULL DEFAULT '{}',
  -- Mandatory, and deliberately not nullable: a restore with no stated reason
  -- does not get approved, so there is no legitimate row without one.
  "reason"                TEXT NOT NULL,
  "requestedBy"           TEXT NOT NULL,
  "requestedByEmail"      TEXT,
  "approvedBy"            TEXT,
  "approvedAt"            TIMESTAMP(3),
  "rejectedReason"        TEXT,
  "preRestoreRunId"       TEXT,
  "error"                 TEXT,
  "startedAt"             TIMESTAMP(3),
  "finishedAt"            TIMESTAMP(3),
  "expiresAt"             TIMESTAMP(3),
  "createdOn"             TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedOn"             TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- Two-person rule, at the storage layer. The service enforces it too, but a
  -- self-approved promotion should be impossible to represent, not merely
  -- rejected by whichever code path happens to be in front of it.
  CONSTRAINT "restore_jobs_no_self_approval" CHECK ("approvedBy" IS NULL OR "approvedBy" <> "requestedBy")
);

CREATE INDEX IF NOT EXISTS "restore_jobs_targetId_createdOn_idx"  ON platform.restore_jobs("targetId", "createdOn");
CREATE INDEX IF NOT EXISTS "restore_jobs_status_idx"              ON platform.restore_jobs("status");
CREATE INDEX IF NOT EXISTS "restore_jobs_organizationId_idx"      ON platform.restore_jobs("organizationId");

CREATE TABLE IF NOT EXISTS platform.restore_verifications (
  "id"           TEXT PRIMARY KEY,
  "restoreJobId" TEXT NOT NULL REFERENCES platform.restore_jobs("id") ON DELETE CASCADE,
  "checkType"    TEXT NOT NULL,
  "name"         TEXT NOT NULL,
  "expected"     TEXT,
  "actual"       TEXT,
  "status"       TEXT NOT NULL DEFAULT 'passed',
  "detail"       JSONB,
  "durationMs"   INTEGER,
  "createdAt"    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS "restore_verifications_restoreJobId_idx"
  ON platform.restore_verifications("restoreJobId");

CREATE TABLE IF NOT EXISTS platform.backup_drills (
  "id"               TEXT PRIMARY KEY,
  "targetId"         TEXT NOT NULL REFERENCES platform.backup_targets("id") ON DELETE CASCADE,
  "repositoryId"     TEXT REFERENCES platform.backup_repositories("id") ON DELETE SET NULL,
  "restoreJobId"     TEXT,
  "trigger"          TEXT NOT NULL DEFAULT 'scheduled',
  -- The rotation. Always drilling the newest full proves only the newest full;
  -- deep_window is what substantiates the retention claim.
  "scenario"         TEXT NOT NULL DEFAULT 'recent_full',
  "targetInstantAge" INTEGER,
  "status"           TEXT NOT NULL DEFAULT 'running',
  "rtoSeconds"       INTEGER,
  "rpoSeconds"       INTEGER,
  "restoredBytes"    BIGINT NOT NULL DEFAULT 0,
  "checksPassed"     INTEGER NOT NULL DEFAULT 0,
  "checksFailed"     INTEGER NOT NULL DEFAULT 0,
  "findings"         JSONB,
  "error"            TEXT,
  "startedAt"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "finishedAt"       TIMESTAMP(3)
);

CREATE INDEX IF NOT EXISTS "backup_drills_targetId_startedAt_idx" ON platform.backup_drills("targetId", "startedAt");
CREATE INDEX IF NOT EXISTS "backup_drills_scenario_startedAt_idx" ON platform.backup_drills("scenario", "startedAt");

-- ── Merchant-facing: snapshots, checkpoints, Time Travel ─────────────────────

CREATE TABLE IF NOT EXISTS platform.merchant_snapshots (
  "id"              TEXT PRIMARY KEY,
  "organizationId"  TEXT NOT NULL,
  "storeId"         TEXT,
  "trigger"         TEXT NOT NULL DEFAULT 'scheduled',
  -- Which admission rule let this expensive thing happen. Recorded so the
  -- thresholds can be tuned from evidence rather than from intuition.
  "admissionReason" TEXT NOT NULL DEFAULT 'scheduled',
  "checkpointId"    TEXT,
  "status"          TEXT NOT NULL DEFAULT 'running',
  "snapshotLsn"     TEXT,
  "snapshotAt"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "repositoryId"    TEXT,
  "storageKey"      TEXT,
  "sizeBytes"       BIGINT NOT NULL DEFAULT 0,
  "checksum"        TEXT,
  "encrypted"       BOOLEAN NOT NULL DEFAULT true,
  "schemaVersion"   TEXT,
  "rowCounts"       JSONB,
  "tableCount"      INTEGER NOT NULL DEFAULT 0,
  "durationMs"      INTEGER,
  "error"           TEXT,
  "expiresAt"       TIMESTAMP(3),
  "createdBy"       TEXT NOT NULL DEFAULT 'system',
  "createdOn"       TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS "merchant_snapshots_organizationId_snapshotAt_idx"
  ON platform.merchant_snapshots("organizationId", "snapshotAt");
CREATE INDEX IF NOT EXISTS "merchant_snapshots_status_idx" ON platform.merchant_snapshots("status");

-- The cheap protection that runs for EVERY bulk operation, instead of a dump.
-- ~2KB per row. It stores no data — audit.change_log already holds every
-- before-image — only the handle needed to find and undo them.
CREATE TABLE IF NOT EXISTS platform.bulk_operation_checkpoints (
  "id"             TEXT PRIMARY KEY,
  "organizationId" TEXT NOT NULL,
  "storeId"        TEXT,
  "correlationId"  TEXT NOT NULL,
  "operationType"  TEXT NOT NULL,
  "label"          TEXT,
  "status"         TEXT NOT NULL DEFAULT 'running',
  "affectedTables" TEXT[] NOT NULL DEFAULT '{}',
  "affectedRows"   INTEGER NOT NULL DEFAULT 0,
  "deleteCount"    INTEGER NOT NULL DEFAULT 0,
  "isDestructive"  BOOLEAN NOT NULL DEFAULT false,
  "snapshotId"     TEXT,
  "undoneByOpId"   TEXT,
  "startedAt"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "finishedAt"     TIMESTAMP(3),
  -- Must track change_log retention. An undo handle that outlives the
  -- before-images it points at is a button that fails when pressed.
  "expiresAt"      TIMESTAMP(3),
  "actorId"        TEXT,
  "actorEmail"     TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS "bulk_operation_checkpoints_correlationId_key"
  ON platform.bulk_operation_checkpoints("correlationId");
CREATE INDEX IF NOT EXISTS "bulk_operation_checkpoints_organizationId_startedAt_idx"
  ON platform.bulk_operation_checkpoints("organizationId", "startedAt");
CREATE INDEX IF NOT EXISTS "bulk_operation_checkpoints_status_idx"
  ON platform.bulk_operation_checkpoints("status");

CREATE TABLE IF NOT EXISTS platform.time_travel_operations (
  "id"               TEXT PRIMARY KEY,
  "organizationId"   TEXT NOT NULL,
  "storeId"          TEXT,
  "scope"            TEXT NOT NULL,
  "entityType"       TEXT,
  "entityIds"        TEXT[] NOT NULL DEFAULT '{}',
  "expandedTables"   TEXT[] NOT NULL DEFAULT '{}',
  "correlationId"    TEXT,
  "checkpointId"     TEXT,
  "restoreToAt"      TIMESTAMP(3) NOT NULL,
  "status"           TEXT NOT NULL DEFAULT 'previewed',
  "affectedRows"     INTEGER NOT NULL DEFAULT 0,
  "insertCount"      INTEGER NOT NULL DEFAULT 0,
  "updateCount"      INTEGER NOT NULL DEFAULT 0,
  "deleteCount"      INTEGER NOT NULL DEFAULT 0,
  -- Rows a parent DELETE removes automatically through onDelete: Cascade.
  -- Counted separately because a preview that understates the blast radius is
  -- worse than no preview: the merchant approved what they were shown.
  "cascadeRows"      INTEGER NOT NULL DEFAULT 0,
  "preview"          JSONB,
  "conflicts"        JSONB,
  "conflictCount"    INTEGER NOT NULL DEFAULT 0,
  "protectedSkips"   JSONB,
  "protectedCount"   INTEGER NOT NULL DEFAULT 0,
  "reverseOfId"      TEXT,
  "appliedAt"        TIMESTAMP(3),
  "durationMs"       INTEGER,
  "error"            TEXT,
  "requestedBy"      TEXT NOT NULL,
  "requestedByEmail" TEXT,
  "approvedBy"       TEXT,
  "createdOn"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS "time_travel_operations_organizationId_createdOn_idx"
  ON platform.time_travel_operations("organizationId", "createdOn");
CREATE INDEX IF NOT EXISTS "time_travel_operations_correlationId_idx"
  ON platform.time_travel_operations("correlationId");
CREATE INDEX IF NOT EXISTS "time_travel_operations_status_idx"
  ON platform.time_travel_operations("status");

-- ── Maintenance and alerting ─────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS platform.maintenance_tasks (
  "id"               TEXT PRIMARY KEY,
  "targetId"         TEXT NOT NULL REFERENCES platform.backup_targets("id") ON DELETE CASCADE,
  "code"             TEXT NOT NULL,
  "name"             TEXT NOT NULL,
  "taskType"         TEXT NOT NULL,
  -- Unlike backups, VACUUM/ANALYZE/REINDEX genuinely are per-database, so this
  -- is required rather than optional.
  "databaseName"     TEXT NOT NULL,
  "tableList"        TEXT[] NOT NULL DEFAULT '{}',
  "cronExpression"   TEXT,
  "timezone"         TEXT NOT NULL DEFAULT 'Asia/Kolkata',
  "windowMinutes"    INTEGER NOT NULL DEFAULT 120,
  "maxLoadPct"       INTEGER NOT NULL DEFAULT 70,
  "requiresLock"     BOOLEAN NOT NULL DEFAULT false,
  "requiresApproval" BOOLEAN NOT NULL DEFAULT false,
  "isEnabled"        BOOLEAN NOT NULL DEFAULT true,
  "lastRunAt"        TIMESTAMP(3),
  "nextRunAt"        TIMESTAMP(3),
  "isActive"         BOOLEAN NOT NULL DEFAULT true,
  "sortOrder"        INTEGER NOT NULL DEFAULT 0,
  "createdBy"        TEXT NOT NULL DEFAULT 'system',
  "createdOn"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedBy"        TEXT NOT NULL DEFAULT 'system',
  "updatedOn"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deletedAt"        TIMESTAMP(3),
  "deletedBy"        TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS "maintenance_tasks_code_key" ON platform.maintenance_tasks("code");
CREATE INDEX IF NOT EXISTS "maintenance_tasks_nextRunAt_idx"   ON platform.maintenance_tasks("nextRunAt");

CREATE TABLE IF NOT EXISTS platform.maintenance_runs (
  "id"             TEXT PRIMARY KEY,
  "taskId"         TEXT NOT NULL REFERENCES platform.maintenance_tasks("id") ON DELETE CASCADE,
  "trigger"        TEXT NOT NULL DEFAULT 'scheduled',
  "status"         TEXT NOT NULL DEFAULT 'running',
  -- "load 82% > 70%" is a useful thing for an operator to be able to read
  -- later, so a skip is recorded rather than silently not happening.
  "skipReason"     TEXT,
  "tablesAffected" INTEGER NOT NULL DEFAULT 0,
  "bytesReclaimed" BIGINT NOT NULL DEFAULT 0,
  "rowsAffected"   BIGINT NOT NULL DEFAULT 0,
  "result"         JSONB,
  "durationMs"     INTEGER,
  "error"          TEXT,
  "startedAt"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "finishedAt"     TIMESTAMP(3),
  "actorId"        TEXT
);

CREATE INDEX IF NOT EXISTS "maintenance_runs_taskId_startedAt_idx"
  ON platform.maintenance_runs("taskId", "startedAt");

CREATE TABLE IF NOT EXISTS platform.backup_alerts (
  "id"             TEXT PRIMARY KEY,
  "targetId"       TEXT,
  "kind"           TEXT NOT NULL,
  "severity"       TEXT NOT NULL DEFAULT 'warning',
  "title"          TEXT NOT NULL,
  "detail"         JSONB,
  "status"         TEXT NOT NULL DEFAULT 'open',
  "firstSeenAt"    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "lastSeenAt"     TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "occurrences"    INTEGER NOT NULL DEFAULT 1,
  "acknowledgedBy" TEXT,
  "acknowledgedAt" TIMESTAMP(3),
  "resolvedAt"     TIMESTAMP(3),
  "notifiedAt"     TIMESTAMP(3)
);

CREATE INDEX IF NOT EXISTS "backup_alerts_status_severity_idx"     ON platform.backup_alerts("status", "severity");
CREATE INDEX IF NOT EXISTS "backup_alerts_kind_targetId_status_idx" ON platform.backup_alerts("kind", "targetId", "status");

-- Dedupe at the storage layer: one OPEN alert per (kind, target). Without
-- this a failing archive_command raises an alert every two minutes and the
-- inbox becomes unreadable exactly when it matters most. Partial unique index
-- because resolved history must still accumulate.
-- COALESCE because a NULL targetId (platform-wide alerts) would otherwise
-- bypass the constraint entirely — NULLs are distinct in a unique index.
CREATE UNIQUE INDEX IF NOT EXISTS "backup_alerts_one_open_per_kind"
  ON platform.backup_alerts("kind", COALESCE("targetId", ''))
  WHERE "status" <> 'resolved';

-- ── The migration ledger ─────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS platform.schema_migrations (
  "id"          TEXT PRIMARY KEY,
  "filename"    TEXT NOT NULL,
  "database"    TEXT NOT NULL DEFAULT 'shopora',
  "checksum"    TEXT NOT NULL,
  "sequence"    INTEGER NOT NULL,
  -- Migrations that cannot be safely replayed forward over older data (a
  -- dropped column, a destructive backfill) are flagged when written. The
  -- deep_window drill is what exercises this in practice.
  "forwardSafe" BOOLEAN NOT NULL DEFAULT true,
  "appliedAt"   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "appliedBy"   TEXT NOT NULL DEFAULT 'system',
  "durationMs"  INTEGER,
  -- Distinguishes "we ran this" from "we inferred this had already been run",
  -- which matters when reconciling a database nobody has a clean record of.
  "backfilled"  BOOLEAN NOT NULL DEFAULT false
);

CREATE UNIQUE INDEX IF NOT EXISTS "schema_migrations_filename_database_key"
  ON platform.schema_migrations("filename", "database");
CREATE INDEX IF NOT EXISTS "schema_migrations_database_sequence_idx"
  ON platform.schema_migrations("database", "sequence");

COMMIT;