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

const GRANTED_ROLES: readonly RoleName[] = Object.freeze([
  ROLE_NAMES.OWNER,
  ROLE_NAMES.SUPERINTENDENT,
  ROLE_NAMES.PRINCIPAL,
  ROLE_NAMES.REGISTRAR,
  ROLE_NAMES.DIRECTOR,
]);

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

function firstId(value: unknown): string | null {
  return Array.isArray(value) ? value.find(isIdRow)?.id ?? null : null;
}

function resultRows(value: unknown): unknown[] {
  return Array.isArray(value) ? value : [];
}

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.READ_ZONE_CHECKIN_REPORTS } },
    );
    let permissionId = firstId(permissionRows);

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

    const [roleRows] = await queryInterface.sequelize.query(
      'SELECT "id", "name" FROM "roles" WHERE "name" IN (:names)',
      { replacements: { names: GRANTED_ROLES } },
    );
    const links: Array<{
      createdAt: Date;
      updatedAt: Date;
      roles_permissionsId: string;
      permissionId: string;
    }> = [];

    for (const row of resultRows(roleRows)) {
      if (!isIdRow(row)) {
        continue;
      }

      const [existingRows] = await queryInterface.sequelize.query(
        `SELECT 1 FROM "rolesPermissionsPermissions"
         WHERE "roles_permissionsId" = :roleId AND "permissionId" = :permissionId
         LIMIT 1`,
        { replacements: { roleId: row.id, permissionId } },
      );

      if (resultRows(existingRows).length === 0) {
        links.push({
          createdAt: now,
          updatedAt: now,
          roles_permissionsId: row.id,
          permissionId,
        });
      }
    }

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

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