/**
 * Line-level redemption reads derived from platform voucher rows (1 row per unit).
 *
 * Money-safe rule: ANY unit REDEEMED → line reads REDEEMED (blocks refund).
 */

import { eq, inArray, sql } from 'drizzle-orm';
import { voucher } from '@platform-modules/commerce-fulfillment';
import type { PgColumn } from 'drizzle-orm/pg-core';
import type { DrizzleClient } from '@/server/db/client.js';
import { orderLineVoucherExt } from '@/server/db/schema.js';
import {
  orderLineIdFromModuleRef,
  toModuleRef,
  voucherIdFromModuleRef,
} from '@/server/platform-seams/ids.js';

/** One platform voucher (unit), joined with its app-owned QR extension row. */
export interface LineVoucherSummary {
  voucherId: string;
  unitIndex: number;
  state: string;
  qrPngUrl: string | null;
  expiresAt: Date | null;
  redeemedAt: Date | null;
}

/**
 * Load every voucher (one per purchased unit) for a set of order lines,
 * grouped by lineId and ordered by unitIndex ASC — matches the platform's
 * own loadVoucherIds ordering. Powers per-unit QR rendering for qty>1 lines.
 */
export async function loadVouchersForLines(
  db: DrizzleClient,
  lineIds: string[],
): Promise<Map<string, LineVoucherSummary[]>> {
  const result = new Map<string, LineVoucherSummary[]>();
  if (lineIds.length === 0) return result;

  const rows = await db
    .select({
      lineId: voucher.lineId,
      voucherId: voucher.id,
      unitIndex: voucher.unitIndex,
      state: voucher.state,
      expiresAt: voucher.expiresAt,
      redeemedAt: voucher.redeemedAt,
      qrPngUrl: orderLineVoucherExt.qrPngUrl,
    })
    .from(voucher)
    // voucher.id is uuid; order_line_voucher_ext.voucher_id is text (app-owned
    // ext table's PK) — cast the join key, matching the `::text` idiom used
    // elsewhere in this file for uuid/text column-type mismatches.
    .leftJoin(orderLineVoucherExt, eq(orderLineVoucherExt.voucherId, sql`${voucher.id}::text`))
    .where(inArray(voucher.lineId, lineIds))
    .orderBy(voucher.lineId, voucher.unitIndex);

  for (const row of rows) {
    const lineId = toModuleRef(orderLineIdFromModuleRef(row.lineId));
    const list = result.get(lineId) ?? [];
    list.push({
      voucherId: toModuleRef(voucherIdFromModuleRef(row.voucherId)),
      unitIndex: row.unitIndex,
      state: row.state,
      qrPngUrl: row.qrPngUrl ?? null,
      expiresAt: row.expiresAt ?? null,
      redeemedAt: row.redeemedAt ?? null,
    });
    result.set(lineId, list);
  }

  return result;
}

/** Load vouchers for a single line — convenience wrapper over loadVouchersForLines. */
export async function loadVouchersForLine(
  db: DrizzleClient,
  lineId: string,
): Promise<LineVoucherSummary[]> {
  const map = await loadVouchersForLines(db, [lineId]);
  return map.get(lineId) ?? [];
}

/** Correlated subquery: aggregate voucher.state for one order_line.id (uuid). */
export function lineRedemptionStatusSql(lineIdColumn: PgColumn) {
  return sql<string>`(
    SELECT CASE
      WHEN COUNT(*) FILTER (WHERE ${voucher.state} = 'REDEEMED') > 0 THEN 'REDEEMED'
      WHEN COUNT(*) > 0
        AND COUNT(*) FILTER (WHERE ${voucher.state} = 'CANCELLED') = COUNT(*) THEN 'CANCELLED'
      WHEN COUNT(*) > 0
        AND COUNT(*) FILTER (WHERE ${voucher.state} = 'EXPIRED') = COUNT(*) THEN 'EXPIRED'
      ELSE 'UNREDEEMED'
    END
    FROM ${voucher}
    WHERE ${voucher.lineId} = ${lineIdColumn}::text
  )`;
}

export function lineRedeemedAtSql(lineIdColumn: PgColumn) {
  return sql<Date | null>`(
    SELECT MAX(${voucher.redeemedAt})
    FROM ${voucher}
    WHERE ${voucher.lineId} = ${lineIdColumn}::text
      AND ${voucher.state} = 'REDEEMED'
  )`.mapWith(voucher.redeemedAt);
}

export function lineExpiresAtSql(lineIdColumn: PgColumn) {
  return sql<Date | null>`(
    SELECT MIN(${voucher.expiresAt})
    FROM ${voucher}
    WHERE ${voucher.lineId} = ${lineIdColumn}::text
  )`.mapWith(voucher.expiresAt);
}

/** True when every voucher on the line is in a terminal non-UNREDEEMED state. */
export function lineAllVouchersTerminalSql(lineIdColumn: PgColumn) {
  return sql<number>`(
    SELECT CASE
      WHEN COUNT(*) = 0 THEN 0
      WHEN COUNT(*) FILTER (WHERE ${voucher.state} = 'UNREDEEMED') = 0 THEN 1
      ELSE 0
    END
    FROM ${voucher}
    WHERE ${voucher.lineId} = ${lineIdColumn}::text
  )`;
}

/** True when at least one voucher on the line is REDEEMED. */
export function lineAnyVoucherRedeemedSql(lineIdColumn: PgColumn) {
  return sql<number>`(
    SELECT CASE
      WHEN COUNT(*) FILTER (WHERE ${voucher.state} = 'REDEEMED') > 0 THEN 1
      ELSE 0
    END
    FROM ${voucher}
    WHERE ${voucher.lineId} = ${lineIdColumn}::text
  )`;
}