import { v4 as uuid } from 'uuid';
import type { QueryInterface } from 'sequelize';
import { ROLE_NAMES } from '@/shared/constants/roles';
import { FEATURE_PERMISSIONS } from '@/shared/constants/product-permissions';

const ESA_MANAGEMENT_ROLES = [
  ROLE_NAMES.SYSTEM_ADMIN,
  ROLE_NAMES.OWNER,
  ROLE_NAMES.SUPERINTENDENT,
  ROLE_NAMES.PRINCIPAL,
  ROLE_NAMES.DIRECTOR,
  ROLE_NAMES.OFFICE_MANAGER,
] as const;

function isIdRow(value: unknown): value is { id: string } {
  return (
    value !== null
    && typeof value === 'object'
    && 'id' in value
    && typeof value.id === 'string'
  );
}

function idRows(value: unknown): readonly { id: string }[] {
  return Array.isArray(value) ? value.filter(isIdRow) : [];
}

function firstId(value: unknown): string | null {
  return idRows(value)[0]?.id ?? null;
}

export default {
  up: async (queryInterface: QueryInterface) => {
    const now = new Date();
    const [permissionRows] = await queryInterface.sequelize.query(
      'SELECT "id" FROM "permissions" WHERE "name" = :name LIMIT 1',
      { replacements: { name: FEATURE_PERMISSIONS.MANAGE_ESA_FUNDING_CONTENT } },
    );
    let permissionId = firstId(permissionRows);

    if (!permissionId) {
      permissionId = uuid();
      await queryInterface.bulkInsert('permissions', [{
        id: permissionId,
        name: FEATURE_PERMISSIONS.MANAGE_ESA_FUNDING_CONTENT,
        createdAt: now,
        updatedAt: now,
      }]);
    }

    const [roleRows] = await queryInterface.sequelize.query(
      'SELECT "id" FROM "roles" WHERE "name" IN (:names)',
      { replacements: { names: ESA_MANAGEMENT_ROLES } },
    );
    const roleIds = idRows(roleRows).map((row) => row.id);

    if (roleIds.length === 0) {
      return;
    }

    const [existingRows] = await queryInterface.sequelize.query(
      `SELECT "roles_permissionsId" AS "id"
       FROM "rolesPermissionsPermissions"
       WHERE "roles_permissionsId" IN (:roleIds)
       AND "permissionId" = :permissionId`,
      { replacements: { roleIds, permissionId } },
    );
    const existingRoleIds = new Set(idRows(existingRows).map((row) => row.id));
    const missingRows = roleIds
      .filter((roleId) => !existingRoleIds.has(roleId))
      .map((roleId) => ({
        createdAt: now,
        updatedAt: now,
        roles_permissionsId: roleId,
        permissionId,
      }));

    if (missingRows.length > 0) {
      await queryInterface.bulkInsert('rolesPermissionsPermissions', missingRows);
    }
  },

  down: async () => {
    // Keep permission grants on rollback; permissions may be assigned manually.
  },
};
