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

/**
 * Class tier (Organization → School → Campus → Class). Adds a nullable `classId`
 * foreign key on `users` for class-bound roles (Teacher / Support Staff).
 * Like every other relation here the link is an app-level UUID with no
 * DB-level constraint. Idempotent: each 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;
}

async function addClassIdColumn(
  queryInterface: QueryInterface,
  table: string,
): Promise<void> {
  if (!(await columnExists(queryInterface, table, 'classId'))) {
    await queryInterface.addColumn(table, 'classId', {
      type: DataTypes.UUID,
      allowNull: true,
    });
  }
}

export default {
  up: async (queryInterface: QueryInterface) => {
    await addClassIdColumn(queryInterface, 'users');
  },

  down: async (queryInterface: QueryInterface) => {
    if (await columnExists(queryInterface, 'users', 'classId')) {
      await queryInterface.removeColumn('users', 'classId');
    }
  },
};
