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

const BACKFILL_IMPORT_HASH_PREFIX = 'ei-quiz-content-org-backfill';

const orgQuizRows = [
  {
    content_type: EI_ASSESSMENT_CONTENT_TYPE,
    payload: CONTENT_CATALOG_SEED_PAYLOADS.emotionalIntelligenceAssessmentQuestions,
  },
  {
    content_type: PERSONALITY_QUIZ_CONTENT_TYPE,
    payload: CONTENT_CATALOG_SEED_PAYLOADS.emotionalIntelligencePersonalityQuiz,
  },
];

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

  return rows.length > 0;
}

export default {
  up: async (queryInterface: QueryInterface) => {
    const organizations = await queryInterface.sequelize.query<{ id: string }>(
      'SELECT id FROM organizations WHERE "deletedAt" IS NULL',
      { type: QueryTypes.SELECT },
    );

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

    for (const organization of organizations) {
      for (const contentRow of orgQuizRows) {
        if (await orgContentRowExists(queryInterface, organization.id, contentRow.content_type)) {
          continue;
        }

        rows.push({
          id: uuid(),
          content_type: contentRow.content_type,
          payload: JSON.stringify(contentRow.payload),
          active: true,
          importHash: [
            BACKFILL_IMPORT_HASH_PREFIX,
            contentRow.content_type,
            organization.id,
          ].join('-'),
          organizationId: organization.id,
          schoolId: null,
          campusId: null,
          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}%` },
    });
  },
};
