import {
  Op,
  QueryTypes,
  type CreationAttributes,
  type QueryInterface,
} from 'sequelize';
import {
  SEED_ORGANIZATION_ID,
  SEED_ORGANIZATION_NAME,
  SEED_ORGANIZATION_2_ID,
  SEED_ORGANIZATION_2_NAME,
  SEED_SECONDARY_CAMPUS_ID,
  SEED_SECONDARY_SCHOOL_ID,
  SEED_SECONDARY_SCHOOL_NAME,
  SEED_SECONDARY_USERS,
  SEED_CAMPUS_ID,
  SEED_SCHOOL_ID,
  SEED_SCHOOL_NAME,
  SEED_SCHOOL_2_ID,
  SEED_SCHOOL_2_NAME,
  SEED_SCHOOL_CAMPUS_IDS,
  SEED_FIXTURE_USERS,
} from '@/shared/constants/seed-fixtures';
import { PRODUCT_CAMPUS_SEED_ROWS } from '@/shared/constants/campuses';
import type { Organizations } from '@/db/models/organizations';
import type { Schools } from '@/db/models/schools';
import type { Campuses } from '@/db/models/campuses';

/**
 * RBAC fixture links (Workstream 4): the primary company owns two schools and
 * the product campuses; the secondary company owns one school/campus for
 * cross-tenant isolation and manual QA. Both tenants have per-role users tied
 * to org/school/campus. Runs after
 * the user and role seeders. Idempotent and reversible.
 */
const campusIds = PRODUCT_CAMPUS_SEED_ROWS.map((campus) => campus.id);
const tenantFixtures = [
  {
    users: SEED_FIXTURE_USERS,
    organizationId: SEED_ORGANIZATION_ID,
    schoolId: SEED_SCHOOL_ID,
    campusId: SEED_CAMPUS_ID,
  },
  {
    users: SEED_SECONDARY_USERS,
    organizationId: SEED_ORGANIZATION_2_ID,
    schoolId: SEED_SECONDARY_SCHOOL_ID,
    campusId: SEED_SECONDARY_CAMPUS_ID,
  },
] as const;

export default {
  up: async (queryInterface: QueryInterface) => {
    const createdAt = new Date();
    const updatedAt = new Date();

    // 1. The companies (idempotent): the primary tenant and the second tenant
    //    used to prove cross-tenant isolation (Workstream 8).
    const orgSeeds: CreationAttributes<Organizations>[] = [
      { id: SEED_ORGANIZATION_ID, name: SEED_ORGANIZATION_NAME, createdAt, updatedAt },
      { id: SEED_ORGANIZATION_2_ID, name: SEED_ORGANIZATION_2_NAME, createdAt, updatedAt },
    ];
    const existingOrgs = await queryInterface.sequelize.query<{ id: string }>(
      `SELECT id FROM organizations WHERE id IN (:ids)`,
      {
        replacements: { ids: orgSeeds.map((org) => org.id) },
        type: QueryTypes.SELECT,
      },
    );
    const existingOrgIds = new Set(existingOrgs.map((row) => row.id));
    const orgsToInsert = orgSeeds.filter((org) => !existingOrgIds.has(org.id as string));
    if (orgsToInsert.length > 0) {
      await queryInterface.bulkInsert('organizations', orgsToInsert);
    }

    // 2. Schools under the companies (idempotent).
    const schoolSeeds: CreationAttributes<Schools>[] = [
      { id: SEED_SCHOOL_ID, name: SEED_SCHOOL_NAME, code: 'north', active: true, organizationId: SEED_ORGANIZATION_ID, createdAt, updatedAt },
      { id: SEED_SCHOOL_2_ID, name: SEED_SCHOOL_2_NAME, code: 'south', active: true, organizationId: SEED_ORGANIZATION_ID, createdAt, updatedAt },
      { id: SEED_SECONDARY_SCHOOL_ID, name: SEED_SECONDARY_SCHOOL_NAME, code: 'rival-north', active: true, organizationId: SEED_ORGANIZATION_2_ID, createdAt, updatedAt },
    ];
    const existingSchools = await queryInterface.sequelize.query<{ id: string }>(
      `SELECT id FROM schools WHERE id IN (:ids)`,
      {
        replacements: { ids: schoolSeeds.map((s) => s.id) },
        type: QueryTypes.SELECT,
      },
    );
    const existingSchoolIds = new Set(existingSchools.map((row) => row.id));
    const schoolsToInsert = schoolSeeds.filter(
      (s) => !existingSchoolIds.has(s.id as string),
    );
    if (schoolsToInsert.length > 0) {
      await queryInterface.bulkInsert('schools', schoolsToInsert);
    }

    // 3. The primary company owns the product campuses; each belongs to one
    // school. The secondary company owns a separate campus so its scoped users
    // are not attached to primary-tenant campuses.
    await queryInterface.sequelize.query(
      `UPDATE "campuses" SET "organizationId" = :org WHERE "id" IN (:ids)`,
      { replacements: { org: SEED_ORGANIZATION_ID, ids: campusIds } },
    );
    for (const [schoolId, ids] of Object.entries(SEED_SCHOOL_CAMPUS_IDS)) {
      await queryInterface.sequelize.query(
        `UPDATE "campuses" SET "schoolId" = :school WHERE "id" IN (:ids)`,
        { replacements: { school: schoolId, ids: [...ids] } },
      );
    }
    const existingSecondaryCampus = await queryInterface.sequelize.query<{ id: string }>(
      `SELECT id FROM campuses WHERE id = :id`,
      {
        replacements: { id: SEED_SECONDARY_CAMPUS_ID },
        type: QueryTypes.SELECT,
      },
    );
    if (existingSecondaryCampus.length === 0) {
      const secondaryCampus: CreationAttributes<Campuses> = {
        id: SEED_SECONDARY_CAMPUS_ID,
        name: 'Rival Tigers Campus',
        code: 'rival-tigers',
        timezone: 'America/Phoenix',
        mascot: 'Rival Tigers',
        color: 'bg-orange-500',
        bgGradient: 'from-orange-500 to-amber-500',
        borderColor: 'border-orange-500/30',
        textColor: 'text-orange-400',
        bgLight: 'bg-orange-500/10',
        description: 'Cross-tenant isolation campus',
        isOnline: false,
        active: true,
        importHash: 'product-campus-rival-tigers',
        organizationId: SEED_ORGANIZATION_2_ID,
        schoolId: SEED_SECONDARY_SCHOOL_ID,
        createdAt,
        updatedAt,
      };
      await queryInterface.bulkInsert('campuses', [secondaryCampus]);
    }

    // 4. Tie each fixture user to its tenant org/school/campus per scope.
    for (const tenant of tenantFixtures) {
      for (const user of tenant.users) {
        await queryInterface.sequelize.query(
          `UPDATE "users" SET "organizationId" = :org, "schoolId" = :school, "campusId" = :campus WHERE "id" = :id`,
          {
            replacements: {
              org: user.organization ? tenant.organizationId : null,
              school: user.school ? tenant.schoolId : null,
              campus: user.campus ? tenant.campusId : null,
              id: user.id,
            },
          },
        );
      }
    }

  },

  down: async (queryInterface: QueryInterface) => {
    await queryInterface.sequelize.query(
      `UPDATE "users" SET "organizationId" = NULL, "schoolId" = NULL, "campusId" = NULL WHERE "id" IN (:ids)`,
      {
        replacements: {
          ids: [
            ...SEED_FIXTURE_USERS.map((user) => user.id),
            ...SEED_SECONDARY_USERS.map((user) => user.id),
          ],
        },
      },
    );
    await queryInterface.sequelize.query(
      `UPDATE "campuses" SET "organizationId" = NULL, "schoolId" = NULL WHERE "id" IN (:ids)`,
      { replacements: { ids: campusIds } },
    );
    await queryInterface.bulkDelete(
      'campuses',
      { id: SEED_SECONDARY_CAMPUS_ID },
      {},
    );
    await queryInterface.bulkDelete(
      'schools',
      { id: { [Op.in]: [SEED_SCHOOL_ID, SEED_SCHOOL_2_ID, SEED_SECONDARY_SCHOOL_ID] } },
      {},
    );
    await queryInterface.bulkDelete(
      'organizations',
      { id: { [Op.in]: [SEED_ORGANIZATION_ID, SEED_ORGANIZATION_2_ID] } },
      {},
    );
  },
};
