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

/**
 * School tier (American Organization → School → Campus hierarchy). Adds the
 * `schools` table and a nullable `schoolId` foreign key on `campuses` and
 * `users`. Like every other relation in this codebase the link is an
 * app-level UUID column with no DB-level constraint (`constraints: false` in the
 * models). `schoolId` is left nullable here; the reseed assigns every campus to
 * a school (campus belongs to exactly one school). Idempotent: the table and
 * each column are only created if missing.
 */
async function tableExists(
  queryInterface: QueryInterface,
  table: string,
): Promise<boolean> {
  const [results] = await queryInterface.sequelize.query(`
    SELECT 1 FROM information_schema.tables
    WHERE table_name = '${table}'
  `);
  return (results as unknown[]).length > 0;
}

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 addSchoolIdColumn(
  queryInterface: QueryInterface,
  table: string,
): Promise<void> {
  if (!(await columnExists(queryInterface, table, 'schoolId'))) {
    await queryInterface.addColumn(table, 'schoolId', {
      type: DataTypes.UUID,
      allowNull: true,
    });
  }
}

export default {
  up: async (queryInterface: QueryInterface) => {
    if (!(await tableExists(queryInterface, 'schools'))) {
      await queryInterface.createTable('schools', {
        id: {
          type: DataTypes.UUID,
          defaultValue: DataTypes.UUIDV4,
          primaryKey: true,
        },
        name: { type: DataTypes.TEXT, allowNull: false },
        code: { type: DataTypes.TEXT, allowNull: false },
        address: { type: DataTypes.TEXT, allowNull: true },
        phone: { type: DataTypes.TEXT, allowNull: true },
        email: { type: DataTypes.TEXT, allowNull: true },
        description: { type: DataTypes.TEXT, allowNull: true },
        active: {
          type: DataTypes.BOOLEAN,
          allowNull: false,
          defaultValue: false,
        },
        importHash: {
          type: DataTypes.STRING(255),
          allowNull: true,
          unique: true,
        },
        organizationId: { type: DataTypes.UUID, allowNull: true },
        createdById: { type: DataTypes.UUID, allowNull: true },
        updatedById: { type: DataTypes.UUID, allowNull: true },
        createdAt: { type: DataTypes.DATE, allowNull: false },
        updatedAt: { type: DataTypes.DATE, allowNull: false },
        deletedAt: { type: DataTypes.DATE, allowNull: true },
      });
    }

    await addSchoolIdColumn(queryInterface, 'campuses');
    await addSchoolIdColumn(queryInterface, 'users');
  },

  down: async (queryInterface: QueryInterface) => {
    if (await columnExists(queryInterface, 'users', 'schoolId')) {
      await queryInterface.removeColumn('users', 'schoolId');
    }
    if (await columnExists(queryInterface, 'campuses', 'schoolId')) {
      await queryInterface.removeColumn('campuses', 'schoolId');
    }
    if (await tableExists(queryInterface, 'schools')) {
      await queryInterface.dropTable('schools');
    }
  },
};
