/**
 * Stock reservation primitives.
 *
 * All functions accept `env: { DATABASE_URL: string }` and derive the DB
 * connection via getDb() — consistent with the env-driven factory pattern used
 * throughout the server layer.
 *
 * dealSkus has no `reserved_qty` column. Available stock is computed as:
 *   quantity_total - quantity_sold - SUM(active reservations)
 * where "active" = consumed_at IS NULL AND released_at IS NULL.
 *
 * Atomicity: reserveStock locks deal_skus via `SELECT … FOR UPDATE`, then in a
 * separate statement re-reads active holds so concurrent reservers observe
 * committed child rows under READ COMMITTED (MVCC-correct multi-statement-in-tx).
 */

import { sql, and, eq, inArray, isNull } from 'drizzle-orm';
import { getDb } from '@/server/db/client.js';
import { dealSkus } from '@/server/db/schema.js';
import { refreshDealCache } from '@/server/domain/variants/cache.js';
import { captureCaught } from '@/server/observability/capture.server';
import { consume, stockReservation, toInventoryTx } from '@/server/stock/inventory-platform.js';
import {
  insertReservation,
  releaseReservation as releaseReservationQuery,
  type SqlExecutor,
} from '@/server/db/queries/stock/reservations.js';

const DEFAULT_TTL_MS = 15 * 60 * 1000;

export interface ReserveStockArgs {
  skuId: string;
  userId: string;
  qty: number;
  paymentIntentId: string;
  /** Milliseconds until reservation expires (default 15 minutes). */
  ttlMs?: number;
  cartTokenHash?: string;
}

export interface ReserveStockInTxArgs {
  skuId: string;
  userId: string;
  qty: number;
  paymentIntentId: string;
  expiresAt: Date;
  cartTokenHash?: string;
}

function firstRow<T>(res: unknown): T | undefined {
  const rows = Array.isArray(res) ? res : (res as { rows?: T[] }).rows;
  return rows?.[0] as T | undefined;
}

/**
 * Reserve `qty` units inside the caller's transaction.
 *
 * Three statements: lock parent → fresh SUM of holds → guard → insert.
 *
 * @throws `oversold:${skuId}` when insufficient stock is available.
 */
export async function reserveStockInTx(
  tx: SqlExecutor,
  args: ReserveStockInTxArgs,
): Promise<{ reservationId: string }> {
  const { skuId, userId, qty, paymentIntentId, expiresAt, cartTokenHash } = args;

  if (!Number.isInteger(args.qty) || args.qty <= 0) {
    throw new Error('invalid_qty');
  }

  const lockedRes = await tx.execute(sql`
    SELECT id, quantity_total, quantity_sold
    FROM deal_skus
    WHERE id = ${skuId}::uuid
    FOR UPDATE
  `);
  const locked = firstRow<{
    id: string;
    quantity_total: number;
    quantity_sold: number;
  }>(lockedRes);

  if (!locked) {
    throw new Error(`oversold:${skuId}`);
  }

  const sumRes = await tx.execute(sql`
    SELECT COALESCE(SUM(qty), 0)::int AS reserved
    FROM stock_reservations
    WHERE sku_id = ${skuId}::uuid
      AND consumed_at IS NULL
      AND released_at IS NULL
  `);
  const sumRow = firstRow<{ reserved: number }>(sumRes);
  const reserved = Number(sumRow?.reserved ?? 0);

  const available = locked.quantity_total - locked.quantity_sold - reserved;
  if (available < qty) {
    throw new Error(`oversold:${skuId}`);
  }

  const reservationId = await insertReservation(tx, {
    skuId,
    userId,
    qty,
    paymentIntentId,
    expiresAt,
    cartTokenHash,
  });

  return { reservationId };
}

/**
 * Atomically reserve `qty` units of a SKU.
 *
 * @throws `oversold:${skuId}` when insufficient stock is available.
 */
export async function reserveStock(
  env: { DATABASE_URL: string },
  args: ReserveStockArgs,
): Promise<{ reservationId: string }> {
  const { skuId, userId, qty, paymentIntentId, ttlMs = DEFAULT_TTL_MS, cartTokenHash } = args;
  const db = getDb({ DATABASE_URL: env.DATABASE_URL });
  const expiresAt = new Date(Date.now() + ttlMs);

  return db.transaction((tx) =>
    reserveStockInTx(tx, { skuId, userId, qty, paymentIntentId, expiresAt, cartTokenHash }),
  );
}

/**
 * Idempotently release a reservation (payment cancelled / expired).
 * Noop if already released or consumed.
 */
export async function releaseReservation(
  env: { DATABASE_URL: string },
  reservationId: string,
): Promise<void> {
  const db = getDb({ DATABASE_URL: env.DATABASE_URL });

  await releaseReservationQuery(db, reservationId);
}

/**
 * Idempotently consume a reservation on successful payment.
 * Marks consumed_at AND increments deal_skus.quantity_sold in one CTE round-trip.
 */
export async function consumeReservation(
  env: { DATABASE_URL: string },
  reservationId: string,
): Promise<
  | {
      consumed: true;
      reservationId: string;
      skuId: string;
      dealId: string;
      reservationQty: number;
      quantitySold: number;
    }
  | { consumed: false; reservationId: string }
> {
  const db = getDb({ DATABASE_URL: env.DATABASE_URL });

  const result = await db.execute(sql`
    WITH mark AS (
      UPDATE stock_reservations
      SET consumed_at = NOW()
      WHERE id = ${reservationId}::uuid
        AND consumed_at IS NULL
        AND released_at IS NULL
      RETURNING sku_id, qty
    ),
    updated AS (
      UPDATE deal_skus ds
      SET quantity_sold = ds.quantity_sold + mark.qty
      FROM mark
      WHERE ds.id = mark.sku_id
      RETURNING
        ds.id AS sku_id,
        ds.deal_id,
        ds.quantity_sold,
        mark.qty AS reservation_qty
    )
    SELECT sku_id, deal_id, quantity_sold, reservation_qty
    FROM updated
  `);

  const row = firstRow<{
    sku_id: string;
    deal_id: string;
    quantity_sold: number | string;
    reservation_qty: number | string;
  }>(result);

  if (!row) {
    return { consumed: false, reservationId };
  }

  try {
    await refreshDealCache(db, row.deal_id);
  } catch (err) {
    captureCaught(err, {
      scope: 'server.stock.consumeReservation.refreshDealCache',
      severity: 'warning',
      extra: { reservationId, skuId: row.sku_id, dealId: row.deal_id },
    });
  }

  return {
    consumed: true,
    reservationId,
    skuId: row.sku_id,
    dealId: row.deal_id,
    reservationQty: Number(row.reservation_qty),
    quantitySold: Number(row.quantity_sold),
  };
}

/**
 * Consume every module inventory reservation held by a successful payment.
 * Each reservation atomically updates the canonical inventory module and the
 * legacy SKU/cache read model. Replay is a no-op because module consume is CAS.
 */
export async function consumeInventoryReservationsForHolders(
  env: { DATABASE_URL: string },
  holderRefs: readonly string[],
): Promise<{ consumed: number }> {
  const db = getDb({ DATABASE_URL: env.DATABASE_URL });
  const reservations = await db
    .select({ id: stockReservation.id, skuId: stockReservation.skuId, qty: stockReservation.qty })
    .from(stockReservation)
    .where(
      and(
        inArray(stockReservation.holderRef, [...new Set(holderRefs)]),
        isNull(stockReservation.consumedAt),
        isNull(stockReservation.releasedAt),
      ),
    );

  let consumedCount = 0;
  for (const reservation of reservations) {
    await db.transaction(async (tx) => {
      const consumed = await consume(toInventoryTx(tx), reservation.id);
      if (!consumed) return;

      const [updated] = await tx
        .update(dealSkus)
        .set({ quantitySold: sql`${dealSkus.quantitySold} + ${reservation.qty}` })
        .where(
          and(
            eq(dealSkus.id, reservation.skuId),
            sql`${dealSkus.quantitySold} + ${reservation.qty} <= ${dealSkus.quantityTotal}`,
          ),
        )
        .returning({ dealId: dealSkus.dealId });
      if (!updated) throw new Error(`oversold:${reservation.skuId}`);
      await refreshDealCache(tx, updated.dealId);
      consumedCount += 1;
    });
  }

  return { consumed: consumedCount };
}

/**
 * Release all expired un-consumed reservations.
 * Intended to run from a CF Cron Trigger (wired to the every-30-minute sweep schedule).
 *
 * @returns Number of reservations released in this sweep.
 */
export async function sweepStaleReservations(env: {
  DATABASE_URL: string;
}): Promise<{ released: number }> {
  const db = getDb({ DATABASE_URL: env.DATABASE_URL });

  const result = await db.execute(sql`
    UPDATE stock_reservations
    SET released_at = NOW()
    WHERE expires_at < NOW()
      AND consumed_at IS NULL
      AND released_at IS NULL
    RETURNING id
  `);

  const resultRows = (result as unknown as { rows: Array<{ id: string }> }).rows;
  return { released: resultRows?.length ?? 0 };
}