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

/**
 * Tenant branding: a `logo` URL on every tenant level (organizations, schools,
 * campuses, classes) for the dynamic sidebar/top-bar badge and the tenant
 * creation forms. Nullable text (a stored file `privateUrl`/download URL or an
 * external URL). Idempotent: 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 addLogoColumn(
  queryInterface: QueryInterface,
  table: string,
): Promise<void> {
  if (!(await columnExists(queryInterface, table, 'logo'))) {
    await queryInterface.addColumn(table, 'logo', {
      type: DataTypes.TEXT,
      allowNull: true,
    });
  }
}

const TABLES = ['organizations', 'schools', 'campuses', 'classes'] as const;

export default {
  up: async (queryInterface: QueryInterface) => {
    for (const table of TABLES) {
      await addLogoColumn(queryInterface, table);
    }
  },

  down: async (queryInterface: QueryInterface) => {
    for (const table of TABLES) {
      if (await columnExists(queryInterface, table, 'logo')) {
        await queryInterface.removeColumn(table, 'logo');
      }
    }
  },
};
