import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * DB Audit fix #2 — per-tenant, per-kind sequence table.
 *
 * WHY:
 *   quickBookingService (and the two repository helpers
 *   getNextBookingNumber / getNextQueryNumber) all used a race-prone
 *   `SELECT COUNT(*) + 1` pattern to pick the next `BK-XXX` / `QRY-XXX`
 *   number. Two concurrent bookings land on the same number, PG throws
 *   a unique-constraint error, and the caller sees a 500. Even if the
 *   UNIQUE index catches it, we still lose a booking creation.
 *
 * WHAT:
 *   A tiny (tenant_id, kind, last_value) table with a Postgres
 *   function that atomically returns the next value via UPSERT +
 *   RETURNING. Callers switch to `SELECT next_tenant_sequence(tenant,
 *   kind)` — safe under concurrency, one round-trip, no explicit lock.
 *
 * SEED:
 *   Backfill last_value = MAX(existing number suffix) per tenant per
 *   kind so the sequence picks up where COUNT(*) left off. First call
 *   after this migration returns MAX + 1 — no gap, no clash.
 */
export class TenantSequences1784200000000 implements MigrationInterface {
  name = 'TenantSequences1784200000000';

  public async up(qr: QueryRunner): Promise<void> {
    // ── Table ────────────────────────────────────────────────────
    await qr.query(`
      CREATE TABLE IF NOT EXISTS "tenant_sequences" (
        "tenant_id"  uuid    NOT NULL,
        "kind"       varchar(30) NOT NULL,
        "last_value" integer NOT NULL DEFAULT 0,
        PRIMARY KEY ("tenant_id", "kind")
      )
    `);

    // ── Atomic increment function ────────────────────────────────
    // INSERT ... ON CONFLICT DO UPDATE + RETURNING = one statement,
    // fully atomic. No FOR UPDATE / lock table needed — Postgres
    // handles concurrent upserts via row-level locks internally.
    await qr.query(`
      CREATE OR REPLACE FUNCTION next_tenant_sequence(p_tenant uuid, p_kind varchar)
      RETURNS integer AS $$
      DECLARE
        v_next integer;
      BEGIN
        INSERT INTO "tenant_sequences" ("tenant_id", "kind", "last_value")
        VALUES (p_tenant, p_kind, 1)
        ON CONFLICT ("tenant_id", "kind")
        DO UPDATE SET "last_value" = "tenant_sequences"."last_value" + 1
        RETURNING "last_value" INTO v_next;
        RETURN v_next;
      END;
      $$ LANGUAGE plpgsql;
    `);

    // ── Seed from existing MAX per tenant + kind ──────────────────
    // BK-042 / QRY-007 style — parse the numeric suffix out. Guard
    // with substring/regex so garbage entries don't blow up the seed.
    // NULLIF/coalesce keeps tenants with 0 existing rows at 0.
    await qr.query(`
      INSERT INTO "tenant_sequences" ("tenant_id", "kind", "last_value")
      SELECT
        tenant_id,
        'booking',
        COALESCE(MAX(NULLIF(regexp_replace(booking_number, '\\D+', '', 'g'), '')::int), 0)
      FROM "bookings"
      WHERE booking_number IS NOT NULL
      GROUP BY tenant_id
      ON CONFLICT ("tenant_id", "kind") DO UPDATE
        SET "last_value" = GREATEST("tenant_sequences"."last_value", EXCLUDED."last_value")
    `);

    await qr.query(`
      INSERT INTO "tenant_sequences" ("tenant_id", "kind", "last_value")
      SELECT
        tenant_id,
        'query',
        COALESCE(MAX(NULLIF(regexp_replace(query_number, '\\D+', '', 'g'), '')::int), 0)
      FROM "queries"
      WHERE query_number IS NOT NULL
      GROUP BY tenant_id
      ON CONFLICT ("tenant_id", "kind") DO UPDATE
        SET "last_value" = GREATEST("tenant_sequences"."last_value", EXCLUDED."last_value")
    `);
  }

  public async down(qr: QueryRunner): Promise<void> {
    await qr.query(`DROP FUNCTION IF EXISTS next_tenant_sequence(uuid, varchar)`);
    await qr.query(`DROP TABLE IF EXISTS "tenant_sequences"`);
  }
}