import { v4 as uuid } from 'uuid';
import { QueryTypes, type QueryInterface } from 'sequelize';
import { FEATURE_PERMISSIONS } from '@/shared/constants/product-permissions';
import { POLICY_DOCUMENT_CATEGORIES } from '@/shared/constants/policy-documents';
import { CONTENT_CATALOG_SEED_PAYLOADS } from '@/db/seeders/content-catalog-data/content-catalog-seed-payloads';

const ESA_CONTENT_TYPE = 'esa-funding-content';
const ESA_POLICY_TAG = 'ESA Funding';
const ESA_POLICY_TITLE = 'ESA Funding Information';

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;
}

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 campuses = await queryInterface.sequelize.query<{
      id: string;
      organizationId: string;
    }>(
      `SELECT "id", "organizationId" FROM "campuses" WHERE "deletedAt" IS NULL`,
      { type: QueryTypes.SELECT },
    );

    const contentRows = campuses.map((campus) => ({
      id: uuid(),
      content_type: ESA_CONTENT_TYPE,
      payload: JSON.stringify(CONTENT_CATALOG_SEED_PAYLOADS.esaFundingContent),
      active: true,
      importHash: `content-catalog-${ESA_CONTENT_TYPE}-${campus.id}`,
      organizationId: campus.organizationId,
      schoolId: null,
      campusId: campus.id,
      classId: null,
      createdAt: now,
      updatedAt: now,
    }));

    if (contentRows.length > 0) {
      const existingContentRows = await queryInterface.sequelize.query<{ importHash: string }>(
        `SELECT "importHash" FROM content_catalog WHERE "importHash" IN (:importHashes)`,
        {
          replacements: { importHashes: contentRows.map((row) => row.importHash) },
          type: QueryTypes.SELECT,
        },
      );
      const existingContentHashes = new Set(existingContentRows.map((row) => row.importHash));
      const missingContentRows = contentRows.filter((row) => !existingContentHashes.has(row.importHash));

      if (missingContentRows.length > 0) {
        await queryInterface.bulkInsert('content_catalog', missingContentRows);
      }
    }

    const policyRows = campuses.map((campus) => ({
      id: uuid(),
      title: ESA_POLICY_TITLE,
      body: 'Staff must review the current ESA Funding Information page and acknowledge understanding of the campus guidance.',
      category: POLICY_DOCUMENT_CATEGORIES.HANDBOOK_POLICY,
      tag: ESA_POLICY_TAG,
      author: null,
      steps: null,
      autism_considerations: null,
      version: 1,
      active: true,
      importHash: `policy-doc-esa-funding-${campus.id}`,
      organizationId: campus.organizationId,
      schoolId: null,
      campusId: campus.id,
      classId: null,
      createdById: null,
      updatedById: null,
      createdAt: now,
      updatedAt: now,
    }));

    if (policyRows.length > 0) {
      const existingPolicyRows = await queryInterface.sequelize.query<{ importHash: string }>(
        `SELECT "importHash" FROM policy_documents WHERE "importHash" IN (:importHashes)`,
        {
          replacements: { importHashes: policyRows.map((row) => row.importHash) },
          type: QueryTypes.SELECT,
        },
      );
      const existingPolicyHashes = new Set(existingPolicyRows.map((row) => row.importHash));
      const missingPolicyRows = policyRows.filter((row) => !existingPolicyHashes.has(row.importHash));

      if (missingPolicyRows.length > 0) {
        await queryInterface.bulkInsert('policy_documents', missingPolicyRows);
      }
    }
  },

  down: async () => {
    // Keep permission and seeded ESA rows on rollback; campus content may be edited.
  },
};
