import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Movement Dashboard — Payment Collection
 *
 * Adds a "cash collected from guest at the service" workflow to every
 * MD-visible line item (transport / activity / special). Each item
 * grows 8 columns:
 *
 *   payment_collection_required          boolean NOT NULL default false
 *   payment_collection_expected_amount   decimal(12,2)  null
 *   payment_collection_currency_id       uuid           null → currencies(id)
 *   payment_collection_status            varchar(20)    null  IN ('PENDING','COLLECTED')
 *   payment_collection_actual_amount     decimal(12,2)  null
 *   payment_collection_collected_at      timestamptz    null
 *   payment_collection_collected_by      uuid           null → users(id)
 *   payment_collection_difference_reason varchar(500)   null
 *
 * Each table also gets a partial composite index for the "Pending /
 * Collected" filter pills on the Movement Chart:
 *
 *   (tenant_id, payment_collection_status)
 *   WHERE is_deleted = false AND payment_collection_required = true
 *
 * Also seeds the new PAYMENT_COLLECTION permission module on every
 * existing role at `none` so it appears on the Roles → Permissions
 * dialog immediately; admins upgrade to `edit`/`delete` from Settings.
 *
 * Design guardrails:
 * - No hard-coded rate — currency is per row so a tenant can collect
 *   in a currency different from the booking's selling currency.
 * - Status CHECK enforces the finite lifecycle; app layer enforces
 *   status transitions.
 * - Difference reason left nullable — the service layer requires it
 *   only when actual != expected. Not a DB constraint because the
 *   check spans two rows-in-flight fields.
 */
export class MDPaymentCollection1785100000000 implements MigrationInterface {
  name = 'MDPaymentCollection1785100000000';

  public async up(qr: QueryRunner): Promise<void> {
    const tables = [
      { name: 'booking_transport_items', idxPrefix: 'bti' },
      { name: 'booking_activity_items', idxPrefix: 'bai' },
      { name: 'booking_special_items', idxPrefix: 'bsi' },
    ] as const;

    for (const t of tables) {
      await qr.query(`
        ALTER TABLE "${t.name}"
          ADD COLUMN IF NOT EXISTS "payment_collection_required" boolean NOT NULL DEFAULT false,
          ADD COLUMN IF NOT EXISTS "payment_collection_expected_amount" numeric(12,2) NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_currency_id" uuid NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_status" varchar(20) NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_actual_amount" numeric(12,2) NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_collected_at" timestamptz NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_collected_by" uuid NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_difference_reason" varchar(500) NULL
      `);

      await qr.query(`
        DO $$ BEGIN
          IF NOT EXISTS (
            SELECT 1 FROM pg_constraint
            WHERE conname = 'chk_${t.name}_payment_collection_status'
          ) THEN
            ALTER TABLE "${t.name}"
              ADD CONSTRAINT "chk_${t.name}_payment_collection_status"
              CHECK ("payment_collection_status" IS NULL OR "payment_collection_status" IN ('PENDING','COLLECTED'));
          END IF;
        END $$;
      `);

      await qr.query(`
        DO $$ BEGIN
          IF NOT EXISTS (
            SELECT 1 FROM pg_constraint
            WHERE conname = 'fk_${t.name}_payment_collection_currency'
          ) THEN
            ALTER TABLE "${t.name}"
              ADD CONSTRAINT "fk_${t.name}_payment_collection_currency"
              FOREIGN KEY ("payment_collection_currency_id") REFERENCES "currencies"("id")
              ON DELETE SET NULL;
          END IF;
        END $$;
      `);

      await qr.query(`
        DO $$ BEGIN
          IF NOT EXISTS (
            SELECT 1 FROM pg_constraint
            WHERE conname = 'fk_${t.name}_payment_collection_collected_by'
          ) THEN
            ALTER TABLE "${t.name}"
              ADD CONSTRAINT "fk_${t.name}_payment_collection_collected_by"
              FOREIGN KEY ("payment_collection_collected_by") REFERENCES "users"("id")
              ON DELETE SET NULL;
          END IF;
        END $$;
      `);

      await qr.query(`
        CREATE INDEX IF NOT EXISTS "ix_${t.idxPrefix}_tenant_payment_collection"
        ON "${t.name}" ("tenant_id", "payment_collection_status")
        WHERE "is_deleted" = false AND "payment_collection_required" = true
      `);
    }

    // ── 2. Seed PAYMENT_COLLECTION permission on every existing role ─
    // New Roles that get created after this migration pick up the
    // module automatically via the Settings → Roles flow (roles.service
    // builds the full ModuleName list). This backfill catches roles
    // seeded before the migration was written.
    await qr.query(`
      INSERT INTO "role_permissions" ("id", "tenant_id", "role_id", "module", "access_level", "created_at", "updated_at", "is_deleted")
      SELECT
        gen_random_uuid(),
        r."tenant_id",
        r."id",
        'payment_collection',
        'none',
        NOW(),
        NOW(),
        false
      FROM "roles" r
      WHERE r."is_deleted" = false
        AND NOT EXISTS (
          SELECT 1 FROM "role_permissions" rp
          WHERE rp."role_id" = r."id"
            AND rp."module" = 'payment_collection'
        )
    `);

    // Grant EDIT to Admin role on every tenant so the feature is
    // usable immediately after deploy without a manual Settings pass.
    await qr.query(`
      UPDATE "role_permissions"
      SET "access_level" = 'delete', "updated_at" = NOW()
      WHERE "module" = 'payment_collection'
        AND "role_id" IN (
          SELECT "id" FROM "roles"
          WHERE "is_deleted" = false
            AND "is_system" = true
            AND LOWER("name") = 'admin'
        )
    `);
  }

  public async down(qr: QueryRunner): Promise<void> {
    // Rollback: drop the seeded permission rows first so the module
    // string stops appearing in the Roles dialog; then drop the
    // columns + indexes + constraints on each item table.
    await qr.query(`
      DELETE FROM "role_permissions"
      WHERE "module" = 'payment_collection'
    `);

    for (const t of ['booking_transport_items', 'booking_activity_items', 'booking_special_items'] as const) {
      const idxPrefix =
        t === 'booking_transport_items' ? 'bti' :
        t === 'booking_activity_items' ? 'bai' : 'bsi';

      await qr.query(`DROP INDEX IF EXISTS "ix_${idxPrefix}_tenant_payment_collection"`);
      await qr.query(`ALTER TABLE "${t}" DROP CONSTRAINT IF EXISTS "chk_${t}_payment_collection_status"`);
      await qr.query(`ALTER TABLE "${t}" DROP CONSTRAINT IF EXISTS "fk_${t}_payment_collection_currency"`);
      await qr.query(`ALTER TABLE "${t}" DROP CONSTRAINT IF EXISTS "fk_${t}_payment_collection_collected_by"`);
      await qr.query(`
        ALTER TABLE "${t}"
          DROP COLUMN IF EXISTS "payment_collection_required",
          DROP COLUMN IF EXISTS "payment_collection_expected_amount",
          DROP COLUMN IF EXISTS "payment_collection_currency_id",
          DROP COLUMN IF EXISTS "payment_collection_status",
          DROP COLUMN IF EXISTS "payment_collection_actual_amount",
          DROP COLUMN IF EXISTS "payment_collection_collected_at",
          DROP COLUMN IF EXISTS "payment_collection_collected_by",
          DROP COLUMN IF EXISTS "payment_collection_difference_reason"
      `);
    }
  }
}