import { DataTypes, type QueryInterface } from 'sequelize';

/**
 * Zone check-in (Workstream 16) — every campus carries a **required** IANA
 * `timezone`. "Today" for a user's zone check-in is computed server-side in this
 * timezone, so it is independent of the caller's device clock/zone and correct
 * across organizations, campuses, and DST.
 *
 * Adding a NOT NULL column to a populated table needs a backfill, so this runs
 * in three steps: add nullable → backfill existing rows → set NOT NULL. The
 * `'UTC'` backfill is a one-time migration value only (there is no app-level
 * default); seeders and the campus admin set the real zone. Idempotent: the
 * column is only added if missing.
 */
async function columnExists(
  queryInterface: QueryInterface,
  table: string,
  column: string,
): Promise<boolean> {
  const [results] = await queryInterface.sequelize.query(`
    SELECT 1 FROM information_schema.columns
    WHERE table_name = '${table}' AND column_name = '${column}'
  `);
  return (results as unknown[]).length > 0;
}

export default {
  up: async (queryInterface: QueryInterface) => {
    if (!(await columnExists(queryInterface, 'campuses', 'timezone'))) {
      await queryInterface.addColumn('campuses', 'timezone', {
        type: DataTypes.TEXT,
        allowNull: true,
      });
      await queryInterface.sequelize.query(
        `UPDATE "campuses" SET "timezone" = 'UTC' WHERE "timezone" IS NULL`,
      );
      await queryInterface.changeColumn('campuses', 'timezone', {
        type: DataTypes.TEXT,
        allowNull: false,
      });
    }
  },

  down: async (queryInterface: QueryInterface) => {
    await queryInterface.removeColumn('campuses', 'timezone');
  },
};
