/**
 * Admin settlements resource — read-only queries.
 *
 * T6 repoint: switched from purchases table to order/vendorSplit model.
 * - getEarningsReport: aggregate settled orders by vendor + month via vendorSplit.
 *   The 90/10 split is reflected by vendorSplit.funder ('vendor' vs 'platform').
 * - listSettlements: derives settlement rows from the earnings aggregation
 *   joined against the payouts table for real status (pending/paid/disputed).
 *
 * Settled statuses: 'paid', 'fulfilled', 'completed'
 * Refund statuses: 'refunded', 'partially_refunded'
 *
 * No raw SQL outside parameterized Drizzle. PII never logged.
 */

import {
  and,
  asc,
  desc,
  eq,
  gte,
  ilike,
  inArray,
  isNotNull,
  lte,
  sql,
  type SQL,
} from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import { payouts, vendors } from '@/server/db/schema.js';
import { order, refundIntent, vendorSplit } from '@platform-modules/commerce-orders';
import { moduleRefAsUuid } from '@/server/platform-seams/ids.js';
import type {
  EarningsReportFilter,
  EarningsReportResult,
  EarningsSortBy,
  ListSettlementsFilter,
  ListSettlementsResult,
  SettlementListRow,
} from './types.js';

const EXPORT_ROW_CAP = 50_000;

/** Order statuses that count as settled (revenue realized). */
const SETTLED_ORDER_STATUSES = ['paid', 'fulfilled', 'completed'] as const;

/** Order statuses that count as refunded. */
const REFUND_ORDER_STATUSES = ['refunded', 'partially_refunded'] as const;

function buildEarningsConditions(filter: EarningsReportFilter): SQL[] {
  const conditions: SQL[] = [isNotNull(vendorSplit.vendorId)];

  if (filter.vendorId) {
    conditions.push(eq(vendorSplit.vendorId, filter.vendorId));
  }
  if (filter.from) {
    conditions.push(gte(order.createdAt, new Date(filter.from)));
  }
  if (filter.to) {
    const toDate = new Date(filter.to);
    toDate.setDate(toDate.getDate() + 1);
    conditions.push(lte(order.createdAt, toDate));
  }
  if (filter.search) {
    const term = `%${filter.search.replace(/[%_\\]/g, '\\$&')}%`;
    conditions.push(ilike(vendors.businessName, term));
  }

  return conditions;
}

function earningsOrderClause(
  filter: EarningsReportFilter,
  monthBucket: ReturnType<typeof sql<string>>,
  grossExpr: SQL,
  vendorExpr: SQL,
  feeExpr: SQL,
  refundedExpr: SQL,
  purchaseCountExpr: SQL,
) {
  const dir = filter.sortDir === 'asc' ? asc : desc;
  const sortBy: EarningsSortBy | undefined = filter.sortBy;

  switch (sortBy) {
    case 'vendorBusinessName':
      return [dir(vendors.businessName), desc(monthBucket)];
    case 'monthYearMonth':
      return [dir(monthBucket), asc(vendors.businessName)];
    case 'purchaseCount':
      return [dir(purchaseCountExpr), desc(monthBucket)];
    case 'grossAgorot':
      return [dir(grossExpr), desc(monthBucket)];
    case 'vendorAmountAgorot':
      return [dir(vendorExpr), desc(monthBucket)];
    case 'platformFeeAgorot':
      return [dir(feeExpr), desc(monthBucket)];
    case 'refundedAgorot':
      return [dir(refundedExpr), desc(monthBucket)];
    default:
      return [desc(monthBucket), asc(vendors.businessName)];
  }
}

// ─── getEarningsReport ────────────────────────────────────────────────────────

export async function getEarningsReport(
  db: DrizzleClient,
  filter: EarningsReportFilter,
): Promise<EarningsReportResult> {
  const conditions = buildEarningsConditions(filter);
  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

  const monthBucket = sql<string>`to_char(date_trunc('month', ${order.createdAt} AT TIME ZONE 'UTC'), 'YYYY-MM')`;

  // vendorSplit.amount is bigint agorot — no ×100 needed.
  const grossExpr = sql<number>`COALESCE(SUM(CASE WHEN ${order.status} IN ('paid','fulfilled','completed') THEN ${vendorSplit.amount} ELSE 0 END), 0)::int`;
  const vendorExpr = sql<number>`COALESCE(SUM(CASE WHEN ${order.status} IN ('paid','fulfilled','completed') AND ${vendorSplit.funder} = 'vendor' THEN ${vendorSplit.amount} ELSE 0 END), 0)::int`;
  const feeExpr = sql<number>`COALESCE(SUM(CASE WHEN ${order.status} IN ('paid','fulfilled','completed') AND ${vendorSplit.funder} = 'platform' THEN ${vendorSplit.amount} ELSE 0 END), 0)::int`;
  // Executed refund_intent is the authoritative refunded-to-buyer total per order;
  // allocate it across vendorSplit rows by each row's share of order.total.
  const orderExecutedRefundAgorot = sql<number>`COALESCE((
    SELECT SUM(${refundIntent.amount})
    FROM ${refundIntent}
    WHERE ${refundIntent.orderId} = ${order.id}
      AND ${refundIntent.status} = 'executed'
  ), 0)`;
  const refundedExpr = sql<number>`COALESCE(SUM(
    CASE
      WHEN ${order.status} = 'refunded' THEN ${vendorSplit.amount}
      WHEN ${order.status} = 'partially_refunded' THEN (
        ROUND(
          ${vendorSplit.amount}::numeric * ${orderExecutedRefundAgorot}::numeric
          / NULLIF(${order.total}::numeric, 0)
        )
      )::int
      ELSE 0
    END
  ), 0)::int`;
  // COUNT(DISTINCT order.id) avoids double-counting: each order has vendor + platform split row.
  const purchaseCountExpr = sql<number>`COUNT(DISTINCT CASE WHEN ${order.status} IN ('paid','fulfilled','completed') THEN ${order.id} ELSE NULL END)::int`;

  const totalRows = await db
    .select({
      total: sql<number>`COUNT(*)::int`,
    })
    .from(
      db
        .select({
          v: vendorSplit.vendorId,
          m: monthBucket.as('m'),
        })
        .from(vendorSplit)
        .innerJoin(order, eq(vendorSplit.orderId, order.id))
        .innerJoin(vendors, sql`${moduleRefAsUuid(sql`${vendorSplit.vendorId}`)} = ${vendors.id}`)
        .where(whereClause)
        .groupBy(vendorSplit.vendorId, monthBucket)
        .as('buckets'),
    );

  const orderClause = earningsOrderClause(
    filter,
    monthBucket,
    grossExpr,
    vendorExpr,
    feeExpr,
    refundedExpr,
    purchaseCountExpr,
  );

  const rows = await db
    .select({
      vendorId: vendorSplit.vendorId,
      vendorBusinessName: vendors.businessName,
      monthYearMonth: monthBucket,
      purchaseCount: purchaseCountExpr,
      grossAgorot: grossExpr,
      vendorAmountAgorot: vendorExpr,
      platformFeeAgorot: feeExpr,
      refundedAgorot: refundedExpr,
    })
    .from(vendorSplit)
    .innerJoin(order, eq(vendorSplit.orderId, order.id))
    .innerJoin(vendors, sql`${moduleRefAsUuid(sql`${vendorSplit.vendorId}`)} = ${vendors.id}`)
    .where(whereClause)
    .groupBy(vendorSplit.vendorId, vendors.businessName, monthBucket)
    .orderBy(...orderClause)
    .limit(filter.limit)
    .offset(filter.offset);

  return {
    data: rows.map((r) => ({ ...r, vendorId: r.vendorId as string })),
    total: totalRows[0]?.total ?? 0,
  };
}

/** Full filtered export (ignores offset; capped). */
export async function getEarningsReportForExport(
  db: DrizzleClient,
  filter: Omit<EarningsReportFilter, 'limit' | 'offset'>,
): Promise<EarningsReportResult> {
  const result = await getEarningsReport(db, {
    ...filter,
    limit: EXPORT_ROW_CAP + 1,
    offset: 0,
  });
  if (result.data.length > EXPORT_ROW_CAP) {
    const err = new Error('EXPORT_TOO_LARGE') as Error & { code: string };
    err.code = 'EXPORT_TOO_LARGE';
    throw err;
  }
  return result;
}

// ─── listSettlements ──────────────────────────────────────────────────────────

export async function listSettlements(
  db: DrizzleClient,
  filter: ListSettlementsFilter,
): Promise<ListSettlementsResult> {
  const earningsResult = await getEarningsReport(db, {
    from: filter.from,
    to: filter.to,
    limit: 1000,
    offset: 0,
  });

  const earningsRows = earningsResult.data;
  const statusByKey = new Map<string, string>();
  if (earningsRows.length > 0) {
    const vendorIds = [...new Set(earningsRows.map((r) => r.vendorId))];
    const months = [...new Set(earningsRows.map((r) => r.monthYearMonth))];
    const payoutRows = await db
      .select({
        vendorId: payouts.vendorId,
        periodMonth: payouts.periodMonth,
        status: payouts.status,
      })
      .from(payouts)
      .where(and(inArray(payouts.vendorId, vendorIds), inArray(payouts.periodMonth, months)));
    for (const p of payoutRows) statusByKey.set(`${p.vendorId}:${p.periodMonth}`, p.status);
  }

  const allRows: SettlementListRow[] = earningsRows.map((row) => ({
    id: `${row.vendorId}:${row.monthYearMonth}`,
    vendorId: row.vendorId,
    vendorBusinessName: row.vendorBusinessName,
    periodLabel: row.monthYearMonth,
    totalAgorot: row.grossAgorot,
    vendorAmountAgorot: row.vendorAmountAgorot,
    platformFeeAgorot: row.platformFeeAgorot,
    status: (statusByKey.get(`${row.vendorId}:${row.monthYearMonth}`) ??
      'pending') as SettlementListRow['status'],
    createdAt: `${row.monthYearMonth}-01T00:00:00.000Z`,
  }));

  const filtered =
    filter.status === 'all' ? allRows : allRows.filter((r) => r.status === filter.status);
  const total = filtered.length;
  const data = filtered.slice(filter.offset, filter.offset + filter.limit);

  return { data, total };
}

// Silence unused-import lint for status constant arrays
void SETTLED_ORDER_STATUSES;
void REFUND_ORDER_STATUSES;