import { Op, QueryTypes, type QueryInterface } from 'sequelize';
import { v4 as uuid } from 'uuid';
import type { ContentCatalog } from '@/db/models/content_catalog';
import type { CreationAttributes } from 'sequelize';
import { PER_TENANT_CONTENT_TYPES } from '@/shared/constants/content-catalog';
import { CONTENT_CATALOG_DEFAULT_ROWS } from '@/db/seeders/content-catalog-data/content-catalog-seed-payloads';

const BACKFILL_IMPORT_HASH_PREFIX = 'dashboard-content-scope-backfill';

interface TenantRow {
  readonly id: string;
  readonly organizationId?: string | null;
}

interface ContentStamp {
  readonly level: 'organization' | 'school' | 'campus';
  readonly organizationId: string;
  readonly schoolId: string | null;
  readonly campusId: string | null;
}

const perTenantDefaultRows = CONTENT_CATALOG_DEFAULT_ROWS.filter((row) =>
  PER_TENANT_CONTENT_TYPES.has(row.content_type),
);

async function exactContentRowExists(
  queryInterface: QueryInterface,
  contentType: string,
  stamp: ContentStamp,
): Promise<boolean> {
  const rows = await queryInterface.sequelize.query<{ id: string }>(
    `
      SELECT id
      FROM content_catalog
      WHERE content_type = :contentType
        AND active = true
        AND "deletedAt" IS NULL
        AND "organizationId" = :organizationId
        AND "schoolId" ${stamp.schoolId ? '= :schoolId' : 'IS NULL'}
        AND "campusId" ${stamp.campusId ? '= :campusId' : 'IS NULL'}
        AND "classId" IS NULL
      LIMIT 1
    `,
    {
      replacements: {
        contentType,
        organizationId: stamp.organizationId,
        schoolId: stamp.schoolId,
        campusId: stamp.campusId,
      },
      type: QueryTypes.SELECT,
    },
  );

  return rows.length > 0;
}

function importHash(contentType: string, stamp: ContentStamp): string {
  return [
    BACKFILL_IMPORT_HASH_PREFIX,
    contentType,
    stamp.level,
    stamp.campusId ?? stamp.schoolId ?? stamp.organizationId,
  ].join('-');
}

export default {
  up: async (queryInterface: QueryInterface) => {
    const [organizations, schools, campuses] = await Promise.all([
      queryInterface.sequelize.query<TenantRow>(
        'SELECT id FROM organizations WHERE "deletedAt" IS NULL',
        { type: QueryTypes.SELECT },
      ),
      queryInterface.sequelize.query<TenantRow>(
        'SELECT id, "organizationId" FROM schools WHERE "deletedAt" IS NULL',
        { type: QueryTypes.SELECT },
      ),
      queryInterface.sequelize.query<TenantRow>(
        'SELECT id, "organizationId" FROM campuses WHERE "deletedAt" IS NULL',
        { type: QueryTypes.SELECT },
      ),
    ]);

    const stamps: ContentStamp[] = [
      ...organizations.map((organization) => ({
        level: 'organization' as const,
        organizationId: organization.id,
        schoolId: null,
        campusId: null,
      })),
      ...schools
        .filter((school): school is TenantRow & { readonly organizationId: string } =>
          typeof school.organizationId === 'string',
        )
        .map((school) => ({
          level: 'school' as const,
          organizationId: school.organizationId,
          schoolId: school.id,
          campusId: null,
        })),
      ...campuses
        .filter((campus): campus is TenantRow & { readonly organizationId: string } =>
          typeof campus.organizationId === 'string',
        )
        .map((campus) => ({
          level: 'campus' as const,
          organizationId: campus.organizationId,
          schoolId: null,
          campusId: campus.id,
        })),
    ];

    const now = new Date();
    const rows: CreationAttributes<ContentCatalog>[] = [];

    for (const contentRow of perTenantDefaultRows) {
      for (const stamp of stamps) {
        if (await exactContentRowExists(queryInterface, contentRow.content_type, stamp)) {
          continue;
        }
        rows.push({
          id: uuid(),
          content_type: contentRow.content_type,
          payload: JSON.stringify(contentRow.payload),
          active: true,
          importHash: importHash(contentRow.content_type, stamp),
          organizationId: stamp.organizationId,
          schoolId: stamp.schoolId,
          campusId: stamp.campusId,
          classId: null,
          createdAt: now,
          updatedAt: now,
        });
      }
    }

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

  down: async (queryInterface: QueryInterface) => {
    await queryInterface.bulkDelete('content_catalog', {
      importHash: { [Op.like]: `${BACKFILL_IMPORT_HASH_PREFIX}%` },
    });
  },
};
