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

/**
 * Guardian ↔ student link (many-to-many). Students and guardians are users
 * (roles); this junction relates two `users` rows so a student can have several
 * guardians and a guardian several students. App-level UUIDs, no DB-level FK
 * constraints (consistent with the rest of the schema). Idempotent.
 */
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;
}

export default {
  up: async (queryInterface: QueryInterface) => {
    if (!(await tableExists(queryInterface, 'guardian_students'))) {
      await queryInterface.createTable('guardian_students', {
        id: {
          type: DataTypes.UUID,
          defaultValue: DataTypes.UUIDV4,
          primaryKey: true,
        },
        guardianId: { type: DataTypes.UUID, allowNull: false },
        studentId: { type: DataTypes.UUID, allowNull: false },
        relationship: { type: DataTypes.TEXT, allowNull: 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 },
      });
    }
  },

  down: async (queryInterface: QueryInterface) => {
    if (await tableExists(queryInterface, 'guardian_students')) {
      await queryInterface.dropTable('guardian_students');
    }
  },
};
