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

/**
 * Per-tenant safety-quiz content (WS-B). The safety-quiz `content_type` becomes
 * dedicated per tenant (org/school/campus/class); other content types stay
 * org-level. Adds nullable tenant columns and drops the `content_type` UNIQUE
 * constraint (a tenant-scoped type has one row per owning tenant). Idempotent.
 */
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;
}

const COLUMNS = ['organizationId', 'schoolId', 'campusId', 'classId'] as const;

export default {
  up: async (queryInterface: QueryInterface) => {
    for (const column of COLUMNS) {
      if (!(await columnExists(queryInterface, 'content_catalog', column))) {
        await queryInterface.addColumn('content_catalog', column, {
          type: DataTypes.UUID,
          allowNull: true,
        });
      }
    }
    // Drop the UNIQUE constraint on content_type if present (name may vary).
    await queryInterface.sequelize.query(`
      DO $$
      DECLARE c text;
      BEGIN
        FOR c IN
          SELECT conname FROM pg_constraint
          WHERE conrelid = 'content_catalog'::regclass AND contype = 'u'
            AND pg_get_constraintdef(oid) ILIKE '%(content_type)%'
        LOOP
          EXECUTE 'ALTER TABLE content_catalog DROP CONSTRAINT ' || quote_ident(c);
        END LOOP;
      END $$;
    `);
  },

  down: async (queryInterface: QueryInterface) => {
    for (const column of COLUMNS) {
      if (await columnExists(queryInterface, 'content_catalog', column)) {
        await queryInterface.removeColumn('content_catalog', column);
      }
    }
  },
};
