-- heartbeat-partition (2026-05-31) — PRD §7 deferred item #14.
--
-- WHY
-- ---
-- At the Evidence Action fleet size the Heartbeat table accrues ~21M
-- rows/month. RollupService.runRollup (src/lib/services/rollup.service.ts)
-- reclaims space with a per-monitor
--     DELETE FROM Heartbeat WHERE monitorId = ? AND createdAt < ?
-- which, at tens of millions of rows, holds a long row-by-row lock and
-- does not return the freed pages to the filesystem (InnoDB keeps the
-- tablespace high-water mark). RANGE partitioning by createdAt lets the
-- operator instead run
--     ALTER TABLE Heartbeat DROP PARTITION pYYYYMM;
-- which is an instant metadata operation that frees the disk immediately.
-- The monthly add/drop routine is documented in
-- docs/runbooks/prisma-migrations.md.
--
-- ⚠️ DESTRUCTIVE / STRUCTURAL — NOT AUTO-APPLIED ON cPanel
-- --------------------------------------------------------
-- The cPanel/Passenger deploy does NOT run `prisma migrate deploy`
-- (only the Docker entrypoint does). This migration rebuilds the
-- Heartbeat table in place (ALTER ... PARTITION BY rewrites the whole
-- table) and changes the primary key + drops a foreign key. It MUST be
-- applied by an operator in a maintenance window, on a restored-backup
-- test run first. See the runbook.
--
-- TWO MYSQL CONSTRAINTS THIS MIGRATION HAS TO SATISFY
-- ---------------------------------------------------
-- (a) A partitioned InnoDB table CANNOT carry a foreign key. The
--     Heartbeat -> Monitor FK (Heartbeat_monitorId_fkey, ON DELETE
--     CASCADE) is therefore dropped. This is safe in this codebase:
--     monitor deletion is SOFT-delete only (MonitorService.deleteMonitors
--     sets deletedAt; there is no hard prisma.monitor.delete in app
--     code), so the cascade was never exercised in production. Referential
--     integrity now rests on app logic. The Prisma-level relation is
--     retained (Prisma resolves it via the field map, not the DB FK), so
--     `monitor.findMany({ include: { heartbeats } })` keeps working.
--
-- (b) Every UNIQUE/PRIMARY key on a partitioned table must include the
--     partitioning column. The PK becomes composite (id, createdAt). id
--     stays the LEADING column so it can remain AUTO_INCREMENT (MySQL
--     requires an auto-increment column to be the leading column of some
--     key) and so existing lookups by id (e.g. the /api/dashboard/kpi
--     correlated subquery `h.id = (SELECT id ... LIMIT 1)`) keep their
--     unique semantics — id remains globally unique across partitions.
--
-- IDEMPOTENCE / SAFETY GUARDS
-- ---------------------------
-- The FK drop is wrapped so the migration does not abort if the
-- constraint name differs (older hand-built DBs) or was already dropped.
-- A table is only ALTER-partitioned if it is not already partitioned.

-- 1. Drop the Monitor foreign key (guarded — name may vary on legacy DBs).
SET @fk := (
    SELECT CONSTRAINT_NAME
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'Heartbeat'
      AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    LIMIT 1
);
SET @ddl := IF(
    @fk IS NOT NULL,
    CONCAT('ALTER TABLE `Heartbeat` DROP FOREIGN KEY `', @fk, '`'),
    'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- The canonical Prisma-generated name, asserted by the heartbeat-partition
-- smoke so the intent is greppable even when the guard above resolves a
-- differently-named constraint:
-- ALTER TABLE `Heartbeat` DROP FOREIGN KEY `Heartbeat_monitorId_fkey`;

-- 2. Repoint the primary key to the composite (id, createdAt). Dropping
--    and re-adding the PK in a single ALTER keeps id AUTO_INCREMENT valid
--    at every step (id stays the leading key column throughout).
ALTER TABLE `Heartbeat`
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (`id`, `createdAt`);

-- 3. RANGE-partition by day-number of createdAt. Monthly partitions cover
--    a window around the present plus a MAXVALUE catch-all so an insert
--    for any date (including future or pre-window backfill) always lands
--    somewhere and never errors. Boundaries use TO_DAYS('YYYY-MM-DD'),
--    which is a constant expression MySQL evaluates at DDL time.
--
--    Naming convention pYYYYMM = "rows with createdAt < first day of the
--    NEXT month", e.g. p202605 holds all of May 2026 (createdAt <
--    2026-06-01). pmax is the catch-all. The runbook's monthly routine
--    REORGANIZEs pmax to split off the next pYYYYMM, then DROPs the
--    oldest partition once it ages past raw retention.
ALTER TABLE `Heartbeat`
    PARTITION BY RANGE (TO_DAYS(`createdAt`)) (
        PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
        PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
        PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
        PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01')),
        PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
        PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
        PARTITION pmax VALUES LESS THAN (MAXVALUE)
    );