import { sql } from 'drizzle-orm';
import type { TxDrizzleClient } from '@/server/db/client.js';

interface RawSqlResult {
  rows: unknown[];
}

export async function runIdempotentDealDuplicate<T extends { id: string }>(
  db: TxDrizzleClient,
  input: { vendorId: string; sourceDealId: string; idempotencyKey: string },
  loadResult: (db: TxDrizzleClient, id: string) => Promise<T | null>,
  createResult: (db: TxDrizzleClient) => Promise<T>,
): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(
      sql`SELECT pg_advisory_xact_lock(hashtextextended(${`${input.vendorId}:${input.sourceDealId}:${input.idempotencyKey}`}, 0))`,
    );
    const prior = (await tx.execute(sql`
      SELECT result_draft_id
      FROM deal_duplicate_requests
      WHERE vendor_id = ${input.vendorId} AND source_deal_id = ${input.sourceDealId}
        AND idempotency_key = ${input.idempotencyKey}
    `)) as unknown as RawSqlResult;
    const priorDraftId = (prior.rows[0] as { result_draft_id?: string } | undefined)
      ?.result_draft_id;
    if (priorDraftId) {
      const priorDraft = await loadResult(tx, priorDraftId);
      if (!priorDraft) throw new Error('Idempotent duplicate result is missing');
      return priorDraft;
    }

    const draft = await createResult(tx);
    await tx.execute(sql`
      INSERT INTO deal_duplicate_requests
        (vendor_id, source_deal_id, idempotency_key, result_draft_id)
      VALUES (${input.vendorId}, ${input.sourceDealId}, ${input.idempotencyKey}, ${draft.id})
    `);
    return draft;
  });
}