-- Schema Synchronization Script (zero-downtime safe form)
-- =========================================================

-- Dangerous operations have been rewritten into lock-friendly, zero-downtime equivalents.

-- Generated: 2026-11-13T16:58:27.657574

-- ===== PHASE: EXPAND — add the target-type column and mirror writes with a trigger =====
--   Note: The new column is nullable during the migration; the trigger casts every insert/update of the old column into it.
--   Note: This makes the change lock-friendly, NOT loss-proof — confirm the cast preserves every existing value before the cutover.

-- ALTER COLUMN: widen_demo.readings.id
ALTER TABLE widen_demo.readings ADD COLUMN "id_new" bigint;


-- CREATE FUNCTION: widen_demo.readings.id
CREATE OR REPLACE FUNCTION "readings_id_sync"() RETURNS trigger AS $$
BEGIN
  NEW."id_new" := CAST(NEW."id" AS bigint);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- CREATE TRIGGER: widen_demo.readings.id
CREATE TRIGGER "readings_id_sync_trg" BEFORE INSERT OR UPDATE ON widen_demo.readings FOR EACH ROW EXECUTE FUNCTION "readings_id_sync"();


-- ===== PHASE: BACKFILL — cast existing rows into the new column =====
--   Note: Converts every row in this one statement: the CONTRACT below drops the old column in this same file, so a bounded pass would leave rows unconverted. On a very large table, run this UPDATE in batches of your own before deploying the CONTRACT.

-- UPDATE DATA: widen_demo.readings.id
UPDATE widen_demo.readings SET "id_new" = CAST("id" AS bigint) WHERE "id_new" IS DISTINCT FROM CAST("id" AS bigint);



-- ============================================================
-- DEPLOY THE STATEMENTS BELOW IN A LATER RELEASE
-- Run them once the application no longer depends on what they
-- remove or change. Where the backfill above is a bounded batch,
-- run it until it reports 0 rows first. On a very large table,
-- split this section into a separate migration.
-- ============================================================

-- ===== PHASE: CONTRACT — cut over: drop the trigger and old column, rename the new one =====
--   Note: Deploy this only after the backfill is complete AND the application no longer reads or writes the old column's type.
--   Note: If the original column was NOT NULL, add an ALTER COLUMN … SET NOT NULL on the new column before the rename.

-- DROP TRIGGER: widen_demo.readings.id
DROP TRIGGER "readings_id_sync_trg" ON widen_demo.readings;


-- DROP FUNCTION: widen_demo.readings.id
DROP FUNCTION "readings_id_sync"();


-- ALTER TABLE: widen_demo.readings.id
ALTER TABLE widen_demo.readings DROP COLUMN "id";


-- ALTER COLUMN: widen_demo.readings.id
ALTER TABLE widen_demo.readings RENAME COLUMN "id_new" TO "id";