import { type MigrationInterface, type QueryRunner } from 'typeorm';

/**
 * Extend booking_change_requests to support quote-scoped CRs.
 *
 * Why one table, not two: the lifecycle (pending → approved/rejected/
 * on_hold/withdrawn), the kind enum, the requester/decider audit
 * shape, and the approvals page UI all carry over identically. A
 * second table would duplicate every join and double the FE work.
 *
 * Schema changes:
 *   - booking_id              becomes nullable (quote-scoped CRs leave it NULL)
 *   - quote_id                new column, nullable, FK to query_quotes
 *   - requester_remarks       becomes nullable (auto-generated CRs from
 *                             zero-price / below-floor saves carry no
 *                             user-typed remark)
 *   - CHECK constraint        exactly one of (booking_id, quote_id) is set
 *
 * Idempotent: every change wrapped in a guard so re-runs no-op.
 */
export class QuoteChangeRequestSupport1777500000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // booking_id → nullable
    await queryRunner.query(`
      DO $$ BEGIN
        IF EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_name='booking_change_requests' AND column_name='booking_id' AND is_nullable='NO') THEN
          ALTER TABLE "booking_change_requests" ALTER COLUMN "booking_id" DROP NOT NULL;
        END IF;
      END $$;
    `);

    // requester_remarks → nullable
    await queryRunner.query(`
      DO $$ BEGIN
        IF EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_name='booking_change_requests' AND column_name='requester_remarks' AND is_nullable='NO') THEN
          ALTER TABLE "booking_change_requests" ALTER COLUMN "requester_remarks" DROP NOT NULL;
        END IF;
      END $$;
    `);

    // quote_id column + FK
    await queryRunner.query(
      `ALTER TABLE "booking_change_requests" ADD COLUMN IF NOT EXISTS "quote_id" uuid`,
    );
    await queryRunner.query(`
      DO $$ BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'FK_booking_change_requests_quote') THEN
          ALTER TABLE "booking_change_requests"
            ADD CONSTRAINT "FK_booking_change_requests_quote"
            FOREIGN KEY ("quote_id") REFERENCES "query_quotes"("id") ON DELETE CASCADE;
        END IF;
      END $$;
    `);
    await queryRunner.query(
      `CREATE INDEX IF NOT EXISTS "IDX_booking_change_requests_quote" ON "booking_change_requests" ("tenant_id", "quote_id")`,
    );

    // Exactly one of (booking_id, quote_id) must be set per row.
    await queryRunner.query(`
      DO $$ BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'CHK_change_request_target_xor') THEN
          ALTER TABLE "booking_change_requests"
            ADD CONSTRAINT "CHK_change_request_target_xor"
            CHECK (
              (booking_id IS NOT NULL)::int + (quote_id IS NOT NULL)::int = 1
            );
        END IF;
      END $$;
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "booking_change_requests" DROP CONSTRAINT IF EXISTS "CHK_change_request_target_xor"`,
    );
    await queryRunner.query(`DROP INDEX IF EXISTS "IDX_booking_change_requests_quote"`);
    await queryRunner.query(
      `ALTER TABLE "booking_change_requests" DROP CONSTRAINT IF EXISTS "FK_booking_change_requests_quote"`,
    );
    await queryRunner.query(
      `ALTER TABLE "booking_change_requests" DROP COLUMN IF EXISTS "quote_id"`,
    );
    // Don't restore NOT NULL on booking_id / requester_remarks — could
    // collide with rows the new code path produced. Manual fix-up only.
  }
}