import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Scope MD cash-at-service collections to a specific supplier junction
 * row instead of the whole service item. Enables independent "+ Cash"
 * per supplier on multi-supplier transport/activity/special lines.
 */
export class SupplierScopedPaymentCollection1785600000000 implements MigrationInterface {
  name = 'SupplierScopedPaymentCollection1785600000000';

  public async up(qr: QueryRunner): Promise<void> {
    await qr.query(`
      ALTER TABLE "service_payment_collections"
        ADD COLUMN IF NOT EXISTS "transport_item_supplier_id" uuid NULL,
        ADD COLUMN IF NOT EXISTS "activity_item_supplier_id"  uuid NULL,
        ADD COLUMN IF NOT EXISTS "special_item_supplier_id"   uuid NULL
    `);

    await qr.query(`
      DO $$ BEGIN
        IF NOT EXISTS (
          SELECT 1 FROM pg_constraint WHERE conname = 'fk_spc_transport_item_supplier'
        ) THEN
          ALTER TABLE "service_payment_collections"
            ADD CONSTRAINT "fk_spc_transport_item_supplier"
            FOREIGN KEY ("transport_item_supplier_id")
            REFERENCES "booking_transport_item_suppliers"("id");
        END IF;
      END $$;
    `);
    await qr.query(`
      DO $$ BEGIN
        IF NOT EXISTS (
          SELECT 1 FROM pg_constraint WHERE conname = 'fk_spc_activity_item_supplier'
        ) THEN
          ALTER TABLE "service_payment_collections"
            ADD CONSTRAINT "fk_spc_activity_item_supplier"
            FOREIGN KEY ("activity_item_supplier_id")
            REFERENCES "booking_activity_item_suppliers"("id");
        END IF;
      END $$;
    `);
    await qr.query(`
      DO $$ BEGIN
        IF NOT EXISTS (
          SELECT 1 FROM pg_constraint WHERE conname = 'fk_spc_special_item_supplier'
        ) THEN
          ALTER TABLE "service_payment_collections"
            ADD CONSTRAINT "fk_spc_special_item_supplier"
            FOREIGN KEY ("special_item_supplier_id")
            REFERENCES "booking_special_item_suppliers"("id");
        END IF;
      END $$;
    `);

    // Existing dev rows were item-scoped (no junction FK). Link each
    // live collection to the primary supplier junction before the
    // per-junction unique index + NOT NULL CHECK land.
    await this.ensurePrimaryJunctionsForCollections(qr);
    await this.backfillCollectionJunctionFks(qr);
    await this.deduplicateJunctionCollections(qr);
    await this.normalizeJunctionFkColumns(qr);
    await this.softDeleteUnlinkableCollections(qr);

    // Drop per-item uniqueness — replaced by per-junction uniqueness.
    await qr.query(`DROP INDEX IF EXISTS "ux_spc_transport_item"`);
    await qr.query(`DROP INDEX IF EXISTS "ux_spc_activity_item"`);
    await qr.query(`DROP INDEX IF EXISTS "ux_spc_special_item"`);

    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "ux_spc_transport_item_supplier"
      ON "service_payment_collections" ("transport_item_supplier_id")
      WHERE "is_deleted" = false AND "status" <> 'CANCELLED' AND "transport_item_supplier_id" IS NOT NULL
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "ux_spc_activity_item_supplier"
      ON "service_payment_collections" ("activity_item_supplier_id")
      WHERE "is_deleted" = false AND "status" <> 'CANCELLED' AND "activity_item_supplier_id" IS NOT NULL
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "ux_spc_special_item_supplier"
      ON "service_payment_collections" ("special_item_supplier_id")
      WHERE "is_deleted" = false AND "status" <> 'CANCELLED' AND "special_item_supplier_id" IS NOT NULL
    `);

    await qr.query(`
      ALTER TABLE "service_payment_collections"
        DROP CONSTRAINT IF EXISTS "chk_spc_junction_fk_matches_type"
    `);
    await qr.query(`
      ALTER TABLE "service_payment_collections"
        ADD CONSTRAINT "chk_spc_junction_fk_matches_type" CHECK (
          (
            "service_type" = 'TRANSPORT'
            AND "transport_item_supplier_id" IS NOT NULL
            AND "activity_item_supplier_id"  IS NULL
            AND "special_item_supplier_id"   IS NULL
          ) OR (
            "service_type" = 'ACTIVITY'
            AND "activity_item_supplier_id"  IS NOT NULL
            AND "transport_item_supplier_id" IS NULL
            AND "special_item_supplier_id"   IS NULL
          ) OR (
            "service_type" = 'SPECIAL'
            AND "special_item_supplier_id"   IS NOT NULL
            AND "transport_item_supplier_id" IS NULL
            AND "activity_item_supplier_id"  IS NULL
          )
        )
    `);
  }

  public async down(qr: QueryRunner): Promise<void> {
    await qr.query(`
      ALTER TABLE "service_payment_collections"
        DROP CONSTRAINT IF EXISTS "chk_spc_junction_fk_matches_type"
    `);
    await qr.query(`DROP INDEX IF EXISTS "ux_spc_special_item_supplier"`);
    await qr.query(`DROP INDEX IF EXISTS "ux_spc_activity_item_supplier"`);
    await qr.query(`DROP INDEX IF EXISTS "ux_spc_transport_item_supplier"`);

    // Revert restores one-collection-per-item indexes. Dev rows may
    // have several live collections on the same item (supplier-scoped)
    // — keep the primary junction row, soft-delete the rest.
    await this.deduplicateItemCollectionsForRevert(
      qr,
      'transport_item_id',
      'transport_item_supplier_id',
      'booking_transport_item_suppliers',
    );
    await this.deduplicateItemCollectionsForRevert(
      qr,
      'activity_item_id',
      'activity_item_supplier_id',
      'booking_activity_item_suppliers',
    );
    await this.deduplicateItemCollectionsForRevert(
      qr,
      'special_item_id',
      'special_item_supplier_id',
      'booking_special_item_suppliers',
    );

    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "ux_spc_transport_item"
      ON "service_payment_collections" ("transport_item_id")
      WHERE "is_deleted" = false AND "transport_item_id" IS NOT NULL
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "ux_spc_activity_item"
      ON "service_payment_collections" ("activity_item_id")
      WHERE "is_deleted" = false AND "activity_item_id" IS NOT NULL
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "ux_spc_special_item"
      ON "service_payment_collections" ("special_item_id")
      WHERE "is_deleted" = false AND "special_item_id" IS NOT NULL
    `);

    await qr.query(`
      ALTER TABLE "service_payment_collections"
        DROP CONSTRAINT IF EXISTS "fk_spc_special_item_supplier",
        DROP CONSTRAINT IF EXISTS "fk_spc_activity_item_supplier",
        DROP CONSTRAINT IF EXISTS "fk_spc_transport_item_supplier"
    `);
    await qr.query(`
      ALTER TABLE "service_payment_collections"
        DROP COLUMN IF EXISTS "special_item_supplier_id",
        DROP COLUMN IF EXISTS "activity_item_supplier_id",
        DROP COLUMN IF EXISTS "transport_item_supplier_id"
    `);
  }

  /** Ensure a primary junction row exists for items with collections. */
  private async ensurePrimaryJunctionsForCollections(qr: QueryRunner): Promise<void> {
    await qr.query(`
      INSERT INTO "booking_transport_item_suppliers"
        (tenant_id, transport_item_id, supplier_id, supplier_name_text,
         role, sort_order, actual_supplier_cost, created_at, updated_at)
      SELECT DISTINCT ON (ti.id)
             ti.tenant_id, ti.id, ti.supplier_id, ti.supplier_name_text,
             'primary', 0, ti.actual_supplier_cost, now(), now()
        FROM "service_payment_collections" pc
        JOIN "booking_transport_items" ti
          ON ti.id = pc.transport_item_id AND ti.is_deleted = false
       WHERE pc.service_type = 'TRANSPORT'
         AND pc.transport_item_id IS NOT NULL
         AND (ti.supplier_id IS NOT NULL OR ti.supplier_name_text IS NOT NULL)
         AND NOT EXISTS (
           SELECT 1 FROM "booking_transport_item_suppliers" j
            WHERE j.transport_item_id = ti.id AND j.is_deleted = false
         )
       ORDER BY ti.id
    `);
    await qr.query(`
      INSERT INTO "booking_activity_item_suppliers"
        (tenant_id, activity_item_id, supplier_id, supplier_name_text,
         supplier_phone_text, role, sort_order, actual_supplier_cost,
         created_at, updated_at)
      SELECT DISTINCT ON (ai.id)
             ai.tenant_id, ai.id, ai.supplier_id, ai.supplier_name_text,
             ai.supplier_phone_text, 'primary', 0, ai.actual_supplier_cost,
             now(), now()
        FROM "service_payment_collections" pc
        JOIN "booking_activity_items" ai
          ON ai.id = pc.activity_item_id AND ai.is_deleted = false
       WHERE pc.service_type = 'ACTIVITY'
         AND pc.activity_item_id IS NOT NULL
         AND (ai.supplier_id IS NOT NULL OR ai.supplier_name_text IS NOT NULL)
         AND NOT EXISTS (
           SELECT 1 FROM "booking_activity_item_suppliers" j
            WHERE j.activity_item_id = ai.id AND j.is_deleted = false
         )
       ORDER BY ai.id
    `);
    await qr.query(`
      INSERT INTO "booking_special_item_suppliers"
        (tenant_id, special_item_id, supplier_id, supplier_name_text,
         supplier_phone_text, role, sort_order, actual_supplier_cost,
         created_at, updated_at)
      SELECT DISTINCT ON (si.id)
             si.tenant_id, si.id, si.supplier_id, si.supplier_name_text,
             si.supplier_phone_text, 'primary', 0, si.actual_supplier_cost,
             now(), now()
        FROM "service_payment_collections" pc
        JOIN "booking_special_items" si
          ON si.id = pc.special_item_id AND si.is_deleted = false
       WHERE pc.service_type = 'SPECIAL'
         AND pc.special_item_id IS NOT NULL
         AND (si.supplier_id IS NOT NULL OR si.supplier_name_text IS NOT NULL)
         AND NOT EXISTS (
           SELECT 1 FROM "booking_special_item_suppliers" j
            WHERE j.special_item_id = si.id AND j.is_deleted = false
         )
       ORDER BY si.id
    `);
  }

  /** Point legacy item-scoped collections at the primary junction row. */
  private async backfillCollectionJunctionFks(qr: QueryRunner): Promise<void> {
    await qr.query(`
      UPDATE "service_payment_collections" pc
         SET transport_item_supplier_id = pick.junction_id,
             updated_at = now()
        FROM (
          SELECT pc2.id,
                 (
                   SELECT j.id
                     FROM "booking_transport_item_suppliers" j
                    WHERE j.transport_item_id = pc2.transport_item_id
                      AND j.is_deleted = false
                    ORDER BY j.sort_order ASC, j.created_at ASC
                    LIMIT 1
                 ) AS junction_id
            FROM "service_payment_collections" pc2
           WHERE pc2.service_type = 'TRANSPORT'
             AND pc2.transport_item_supplier_id IS NULL
             AND pc2.transport_item_id IS NOT NULL
        ) pick
       WHERE pc.id = pick.id
         AND pick.junction_id IS NOT NULL
    `);
    await qr.query(`
      UPDATE "service_payment_collections" pc
         SET activity_item_supplier_id = pick.junction_id,
             updated_at = now()
        FROM (
          SELECT pc2.id,
                 (
                   SELECT j.id
                     FROM "booking_activity_item_suppliers" j
                    WHERE j.activity_item_id = pc2.activity_item_id
                      AND j.is_deleted = false
                    ORDER BY j.sort_order ASC, j.created_at ASC
                    LIMIT 1
                 ) AS junction_id
            FROM "service_payment_collections" pc2
           WHERE pc2.service_type = 'ACTIVITY'
             AND pc2.activity_item_supplier_id IS NULL
             AND pc2.activity_item_id IS NOT NULL
        ) pick
       WHERE pc.id = pick.id
         AND pick.junction_id IS NOT NULL
    `);
    await qr.query(`
      UPDATE "service_payment_collections" pc
         SET special_item_supplier_id = pick.junction_id,
             updated_at = now()
        FROM (
          SELECT pc2.id,
                 (
                   SELECT j.id
                     FROM "booking_special_item_suppliers" j
                    WHERE j.special_item_id = pc2.special_item_id
                      AND j.is_deleted = false
                    ORDER BY j.sort_order ASC, j.created_at ASC
                    LIMIT 1
                 ) AS junction_id
            FROM "service_payment_collections" pc2
           WHERE pc2.service_type = 'SPECIAL'
             AND pc2.special_item_supplier_id IS NULL
             AND pc2.special_item_id IS NOT NULL
        ) pick
       WHERE pc.id = pick.id
         AND pick.junction_id IS NOT NULL
    `);
  }

  /** One live collection per junction — keep the earliest row. */
  private async deduplicateJunctionCollections(qr: QueryRunner): Promise<void> {
    for (const junctionFk of [
      'transport_item_supplier_id',
      'activity_item_supplier_id',
      'special_item_supplier_id',
    ]) {
      await qr.query(`
        UPDATE "service_payment_collections" pc
           SET is_deleted = true,
               updated_at = now()
          FROM (
            SELECT id,
                   ROW_NUMBER() OVER (
                     PARTITION BY "${junctionFk}"
                     ORDER BY created_at ASC
                   ) AS rn
              FROM "service_payment_collections"
             WHERE is_deleted = false
               AND status <> 'CANCELLED'
               AND "${junctionFk}" IS NOT NULL
          ) ranked
         WHERE pc.id = ranked.id
           AND ranked.rn > 1
      `);
    }
  }

  /** Each row may only carry the junction FK matching its service_type. */
  private async normalizeJunctionFkColumns(qr: QueryRunner): Promise<void> {
    await qr.query(`
      UPDATE "service_payment_collections"
         SET activity_item_supplier_id = NULL,
             special_item_supplier_id = NULL,
             updated_at = now()
       WHERE service_type = 'TRANSPORT'
    `);
    await qr.query(`
      UPDATE "service_payment_collections"
         SET transport_item_supplier_id = NULL,
             special_item_supplier_id = NULL,
             updated_at = now()
       WHERE service_type = 'ACTIVITY'
    `);
    await qr.query(`
      UPDATE "service_payment_collections"
         SET transport_item_supplier_id = NULL,
             activity_item_supplier_id = NULL,
             updated_at = now()
       WHERE service_type = 'SPECIAL'
    `);
  }

  /** Rows with no junction to attach to cannot satisfy the new CHECK. */
  private async softDeleteUnlinkableCollections(qr: QueryRunner): Promise<void> {
    await qr.query(`
      DELETE FROM "service_payment_collections"
       WHERE (
           (service_type = 'TRANSPORT' AND transport_item_supplier_id IS NULL) OR
           (service_type = 'ACTIVITY'  AND activity_item_supplier_id  IS NULL) OR
           (service_type = 'SPECIAL'   AND special_item_supplier_id   IS NULL)
         )
    `);
  }

  /** Rollback helper — old schema allowed one live row per item. */
  private async deduplicateItemCollectionsForRevert(
    qr: QueryRunner,
    itemCol: string,
    junctionFk: string,
    junctionTable: string,
  ): Promise<void> {
    await qr.query(`
      UPDATE "service_payment_collections" pc
         SET is_deleted = true,
             updated_at = now()
        FROM (
          SELECT pc2.id,
                 ROW_NUMBER() OVER (
                   PARTITION BY pc2."${itemCol}"
                   ORDER BY COALESCE(j.sort_order, 999) ASC,
                            pc2.created_at ASC
                 ) AS rn
            FROM "service_payment_collections" pc2
            LEFT JOIN "${junctionTable}" j
              ON j.id = pc2."${junctionFk}"
             AND j.is_deleted = false
           WHERE pc2.is_deleted = false
             AND pc2."${itemCol}" IS NOT NULL
        ) ranked
       WHERE pc.id = ranked.id
         AND ranked.rn > 1
    `);
  }
}