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 PERMISSIONS = Object.freeze([
  FEATURE_PERMISSIONS.MANAGE_FRAME,
  FEATURE_PERMISSIONS.MANAGE_WALKTHROUGH,
  FEATURE_PERMISSIONS.MANAGE_INTERNAL_COMM,
  FEATURE_PERMISSIONS.MANAGE_CONTENT_CATALOG,
  FEATURE_PERMISSIONS.READ_STAFF_ATTENDANCE_REPORTS,
  FEATURE_PERMISSIONS.READ_SAFETY_QUIZ_REPORTS,
  FEATURE_PERMISSIONS.READ_PERSONALITY_REPORTS,
  FEATURE_PERMISSIONS.READ_POLICY_ACKNOWLEDGMENT_REPORTS,
]);

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

const REPORT_PERMISSIONS = Object.freeze([
  FEATURE_PERMISSIONS.READ_STAFF_ATTENDANCE_REPORTS,
  FEATURE_PERMISSIONS.READ_SAFETY_QUIZ_REPORTS,
  FEATURE_PERMISSIONS.READ_PERSONALITY_REPORTS,
  FEATURE_PERMISSIONS.READ_POLICY_ACKNOWLEDGMENT_REPORTS,
]);

const ROLE_GRANTS: Readonly<Record<RoleName, readonly string[]>> = Object.freeze({
  [ROLE_NAMES.SUPER_ADMIN]: [],
  [ROLE_NAMES.SYSTEM_ADMIN]: [],
  [ROLE_NAMES.OWNER]: PERMISSIONS,
  [ROLE_NAMES.SUPERINTENDENT]: PERMISSIONS,
  [ROLE_NAMES.PRINCIPAL]: PERMISSIONS,
  [ROLE_NAMES.REGISTRAR]: REPORT_PERMISSIONS,
  [ROLE_NAMES.DIRECTOR]: PERMISSIONS,
  [ROLE_NAMES.OFFICE_MANAGER]: [],
  [ROLE_NAMES.TEACHER]: [],
  [ROLE_NAMES.SUPPORT_STAFF]: [],
  [ROLE_NAMES.STUDENT]: [],
  [ROLE_NAMES.GUARDIAN]: [],
  [ROLE_NAMES.GUEST]: [],
});

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

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

async function rowsByName(
  queryInterface: QueryInterface,
  table: string,
  names: readonly string[],
): Promise<Map<string, string>> {
  const [rows] = await queryInterface.sequelize.query(
    `SELECT "id", "name" FROM "${table}" WHERE "name" IN (:names)`,
    { replacements: { names } },
  );
  return new Map(
    resultRows(rows)
      .filter(isNamedIdRow)
      .map((row) => [row.name, row.id]),
  );
}

export default {
  up: async (queryInterface: QueryInterface) => {
    const now = new Date();
    const permissionIds = await rowsByName(queryInterface, 'permissions', PERMISSIONS);
    const missing = PERMISSIONS.filter((name) => !permissionIds.has(name));

    if (missing.length > 0) {
      const rows = missing.map((name) => {
        const id = uuid();
        permissionIds.set(name, id);
        return { id, name, createdAt: now, updatedAt: now };
      });
      await queryInterface.bulkInsert('permissions', rows);
    }

    const roleIds = await rowsByName(queryInterface, 'roles', Object.values(ROLE_NAMES));
    const links: Array<{
      createdAt: Date;
      updatedAt: Date;
      roles_permissionsId: string;
      permissionId: string;
    }> = [];

    for (const role of FULL_ACCESS_ROLES) {
      const roleId = roleIds.get(role);
      if (!roleId) continue;
      for (const permissionName of ROLE_GRANTS[role]) {
        const permissionId = permissionIds.get(permissionName);
        if (!permissionId) continue;
        const [existing] = await queryInterface.sequelize.query(
          `SELECT 1 FROM "rolesPermissionsPermissions"
           WHERE "roles_permissionsId" = :roleId AND "permissionId" = :permissionId
           LIMIT 1`,
          { replacements: { roleId, permissionId } },
        );
        if (resultRows(existing).length === 0) {
          links.push({
            createdAt: now,
            updatedAt: now,
            roles_permissionsId: roleId,
            permissionId,
          });
        }
      }
    }

    const registrarId = roleIds.get(ROLE_NAMES.REGISTRAR);
    if (registrarId) {
      for (const permissionName of REPORT_PERMISSIONS) {
        const permissionId = permissionIds.get(permissionName);
        if (!permissionId) continue;
        const [existing] = await queryInterface.sequelize.query(
          `SELECT 1 FROM "rolesPermissionsPermissions"
           WHERE "roles_permissionsId" = :roleId AND "permissionId" = :permissionId
           LIMIT 1`,
          { replacements: { roleId: registrarId, permissionId } },
        );
        if (resultRows(existing).length === 0) {
          links.push({
            createdAt: now,
            updatedAt: now,
            roles_permissionsId: registrarId,
            permissionId,
          });
        }
      }
    }

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

  down: async () => {
    // Intentionally no-op: permission rows are safe to keep and may be assigned
    // as per-user custom permissions.
  },
};
