TX/lib/db/scripts/pre-push-cleanup.ts

import pg from "pg";

const { Client } = pg;

if (!process.env.DATABASE_URL) {
  throw new Error(
    "DATABASE_URL must be set. Did you forget to provision a database?",
  );
}

async function tableExists(client: pg.Client, table: string): Promise<boolean> {
  const result = await client.query(
    `SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = $1`,
    [table],
  );
  return (result.rowCount ?? 0) > 0;
}

async function main(): Promise<void> {
  const client = new Client({ connectionString: process.env.DATABASE_URL });
  await client.connect();
  try {
    await client.query("BEGIN");

    if (await tableExists(client, "app_permissions")) {
      const dupes = await client.query(
        `SELECT
           count(*)::int AS dup_groups,
           coalesce(sum(c - 1), 0)::int AS extra_rows
         FROM (
           SELECT app_id, permission_id, count(*) AS c
             FROM app_permissions
            GROUP BY app_id, permission_id
           HAVING count(*) > 1
         ) d`,
      );
      const dupGroups = (dupes.rows[0]?.dup_groups ?? 0) as number;
      const extraRows = (dupes.rows[0]?.extra_rows ?? 0) as number;
      if (dupGroups > 0) {
        await client.query(
          `CREATE TEMP TABLE app_permissions_dedup ON COMMIT DROP AS
             SELECT DISTINCT app_id, permission_id FROM app_permissions`,
        );
        await client.query(`DELETE FROM app_permissions`);
        await client.query(
          `INSERT INTO app_permissions (app_id, permission_id)
             SELECT app_id, permission_id FROM app_permissions_dedup`,
        );
        console.log(
          `[pre-push] Removed ${extraRows} duplicate app_permissions row(s) across ${dupGroups} (app_id, permission_id) key(s).`,
        );
      } else {
        console.log("[pre-push] app_permissions has no duplicates.");
      }
    } else {
      console.log("[pre-push] app_permissions table not present yet — skipping dedup.");
    }

    if (
      (await tableExists(client, "executive_meeting_notifications")) &&
      (await tableExists(client, "executive_meetings"))
    ) {
      const orphan = await client.query(
        `DELETE FROM executive_meeting_notifications n
          WHERE n.meeting_id IS NOT NULL
            AND NOT EXISTS (
              SELECT 1 FROM executive_meetings m WHERE m.id = n.meeting_id
            )`,
      );
      const removed = orphan.rowCount ?? 0;
      if (removed > 0) {
        console.log(
          `[pre-push] Removed ${removed} orphan executive_meeting_notifications row(s).`,
        );
      } else {
        console.log("[pre-push] No orphan executive_meeting_notifications rows.");
      }
    } else {
      console.log(
        "[pre-push] executive_meeting_notifications/executive_meetings not both present — skipping orphan cleanup.",
      );
    }

    // Backfill booking references BEFORE the schema push enforces the
    // NOT NULL + unique constraint on protocol_room_bookings.booking_reference.
    // Without this, pushing the constraint against a populated table (any
    // pre-existing booking predates the column) would fail. Reference format
    // mirrors makeBookingReference() in the API: RB-{year}-{pad4(id)}, which
    // is unique because id is unique. Idempotent: only touches NULL rows.
    if (await tableExists(client, "protocol_room_bookings")) {
      const hasColumn = await client.query(
        `SELECT 1 FROM information_schema.columns
          WHERE table_schema = 'public'
            AND table_name = 'protocol_room_bookings'
            AND column_name = 'booking_reference'`,
      );
      if ((hasColumn.rowCount ?? 0) > 0) {
        const backfilled = await client.query(
          `UPDATE protocol_room_bookings
              SET booking_reference =
                'RB-' || EXTRACT(YEAR FROM created_at)::int
                     || '-' || LPAD(id::text, 4, '0')
            WHERE booking_reference IS NULL`,
        );
        const filled = backfilled.rowCount ?? 0;
        if (filled > 0) {
          console.log(
            `[pre-push] Backfilled ${filled} protocol_room_bookings booking_reference value(s).`,
          );
        } else {
          console.log(
            "[pre-push] No protocol_room_bookings booking_reference to backfill.",
          );
        }
      } else {
        console.log(
          "[pre-push] protocol_room_bookings.booking_reference column not present yet — skipping backfill.",
        );
      }
    } else {
      console.log(
        "[pre-push] protocol_room_bookings table not present yet — skipping booking_reference backfill.",
      );
    }

    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK").catch(() => {});
    throw err;
  } finally {
    await client.end();
  }
}

main().catch((err) => {
  console.error("[pre-push] cleanup failed:", err);
  process.exit(1);
});