import { DataTypes, type QueryInterface } from 'sequelize';
import {
  ROLE_SCOPE_VALUES,
  ROLE_DEFINITIONS,
  ROLE_SCOPES,
} from '@/shared/constants/roles';

/**
 * Workstream 3 §3.1 foundation: add the authorization `scope` to `roles` (NOT
 * NULL — every role has a scope; the 11 first-class roles are seeded with one)
 * and a nullable `campusId` to `users` (campus scope for campus-bound roles;
 * null for system/organization scopes, so legitimately optional).
 *
 * Handles existing data: adds column as nullable, backfills scope based on
 * role name using ROLE_DEFINITIONS mapping, then makes it NOT NULL.
 *
 * `campusId` is a plain UUID (no DB-level FK), matching how
 * `users.organizationId` is modeled — associations use `constraints: false`.
 *
 * This migration is idempotent: columns/enums are 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 columnIsNullable(
  queryInterface: QueryInterface,
  table: string,
  column: string,
): Promise<boolean> {
  const [results] = await queryInterface.sequelize.query(`
    SELECT is_nullable FROM information_schema.columns
    WHERE table_name = '${table}' AND column_name = '${column}'
  `);
  const row = (results as { is_nullable: string }[])[0];
  return row?.is_nullable === 'YES';
}

export default {
  up: async (queryInterface: QueryInterface) => {
    // Create enum type if not exists, or add missing values to existing enum
    const [enumExists] = await queryInterface.sequelize.query(`
      SELECT 1 FROM pg_type WHERE typname = 'enum_roles_scope'
    `);

    if ((enumExists as unknown[]).length === 0) {
      // Enum doesn't exist, create it with all values
      await queryInterface.sequelize.query(`
        CREATE TYPE "public"."enum_roles_scope" AS ENUM(${ROLE_SCOPE_VALUES.map((v) => `'${v}'`).join(', ')});
      `);
    } else {
      // Enum exists, add any missing values
      for (const scope of ROLE_SCOPE_VALUES) {
        const [valueExists] = await queryInterface.sequelize.query(`
          SELECT 1 FROM pg_enum WHERE enumtypid = 'enum_roles_scope'::regtype AND enumlabel = '${scope}'
        `);
        if ((valueExists as unknown[]).length === 0) {
          await queryInterface.sequelize.query(`
            ALTER TYPE "public"."enum_roles_scope" ADD VALUE IF NOT EXISTS '${scope}'
          `);
        }
      }
    }

    const scopeExists = await columnExists(queryInterface, 'roles', 'scope');

    if (!scopeExists) {
      // Step 1: Add column as nullable first
      await queryInterface.addColumn('roles', 'scope', {
        type: DataTypes.ENUM(...ROLE_SCOPE_VALUES),
        allowNull: true,
      });
    }

    // Step 2: Backfill existing rows with correct scope based on role name
    for (const def of ROLE_DEFINITIONS) {
      await queryInterface.sequelize.query(
        `UPDATE roles SET scope = :scope WHERE name = :name AND scope IS NULL`,
        { replacements: { scope: def.scope, name: def.name } },
      );
    }
    // Fallback for any roles not in ROLE_DEFINITIONS (e.g., custom roles)
    await queryInterface.sequelize.query(
      `UPDATE roles SET scope = :scope WHERE scope IS NULL`,
      { replacements: { scope: ROLE_SCOPES.CAMPUS } },
    );

    // Step 3: Make column NOT NULL if still nullable
    if (!scopeExists || (await columnIsNullable(queryInterface, 'roles', 'scope'))) {
      await queryInterface.changeColumn('roles', 'scope', {
        type: DataTypes.ENUM(...ROLE_SCOPE_VALUES),
        allowNull: false,
      });
    }

    if (!(await columnExists(queryInterface, 'users', 'campusId'))) {
      await queryInterface.addColumn('users', 'campusId', {
        type: DataTypes.UUID,
        allowNull: true,
      });
    }
  },

  down: async (queryInterface: QueryInterface) => {
    await queryInterface.removeColumn('users', 'campusId');
    await queryInterface.removeColumn('roles', 'scope');
    // Postgres keeps the enum type after the column is dropped; remove it too.
    await queryInterface.sequelize.query(
      'DROP TYPE IF EXISTS "enum_roles_scope";',
    );
  },
};
