/**
 * Vendor earnings domain queries.
 *
 * T6 repoint: switched from purchases table to order/vendorSplit model.
 * vendorSplit.amount is bigint agorot — no ×100 conversion needed.
 * COUNT(DISTINCT order.id) avoids double-counting (vendor + platform split rows per order).
 *
 * Deferred (T6-defer-1): redemption status not on order model → payout status still from payouts table.
 */

import { and, desc, eq, inArray, sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import { payouts } from '@/server/db/schema.js';
import { order, vendorSplit } from '@platform-modules/commerce-orders';

/** Settled order statuses (revenue realized). */
const SETTLED_ORDER_STATUSES = ['paid', 'fulfilled', 'completed'] as const;

export interface VendorEarningsRow {
  periodMonth: string;
  purchaseCount: number;
  grossAgorot: number;
  vendorAmountAgorot: number;
  platformFeeAgorot: number;
  status: 'pending' | 'paid' | 'disputed';
}

export interface VendorEarningsResult {
  data: VendorEarningsRow[];
  total: number;
}

export interface VendorEarningsSummary {
  totalVendorAgorot: number;
  currentMonthVendorAgorot: number;
  pendingVendorAgorot: number;
}

export async function getVendorEarnings(
  db: DrizzleClient,
  vendorId: string,
  opts: { limit: number; offset: number },
): Promise<VendorEarningsResult> {
  const monthBucket = sql<string>`to_char(${order.createdAt}, 'YYYY-MM')`;

  const completedFilter = and(
    eq(vendorSplit.vendorId, vendorId),
    sql`${order.status} IN ('paid','fulfilled','completed')`,
  );

  const totalResult = await db
    .select({ total: sql<number>`count(distinct ${monthBucket})` })
    .from(vendorSplit)
    .innerJoin(order, eq(vendorSplit.orderId, order.id))
    .where(completedFilter);
  const total = totalResult[0]?.total ?? 0;

  const rows = await db
    .select({
      periodMonth: monthBucket,
      purchaseCount: sql<number>`COUNT(DISTINCT ${order.id})::int`,
      grossAgorot: sql<number>`COALESCE(SUM(${vendorSplit.amount}), 0)::int`,
      vendorAmountAgorot: sql<number>`COALESCE(SUM(CASE WHEN ${vendorSplit.funder} = 'vendor' THEN ${vendorSplit.amount} ELSE 0 END), 0)::int`,
      platformFeeAgorot: sql<number>`COALESCE(SUM(CASE WHEN ${vendorSplit.funder} = 'platform' THEN ${vendorSplit.amount} ELSE 0 END), 0)::int`,
    })
    .from(vendorSplit)
    .innerJoin(order, eq(vendorSplit.orderId, order.id))
    .where(completedFilter)
    .groupBy(monthBucket)
    .orderBy(desc(monthBucket))
    .limit(opts.limit)
    .offset(opts.offset);

  const statusByMonth = new Map<string, string>();
  if (rows.length > 0) {
    const months = [...new Set(rows.map((r) => r.periodMonth))];
    const payoutRows = await db
      .select({ periodMonth: payouts.periodMonth, status: payouts.status })
      .from(payouts)
      .where(and(eq(payouts.vendorId, vendorId), inArray(payouts.periodMonth, months)));
    for (const p of payoutRows) statusByMonth.set(p.periodMonth, p.status);
  }

  const data: VendorEarningsRow[] = rows.map((row) => ({
    ...row,
    status: (statusByMonth.get(row.periodMonth) ?? 'pending') as VendorEarningsRow['status'],
  }));

  return { data, total: Number(total) };
}

export async function getVendorEarningsSummary(
  db: DrizzleClient,
  vendorId: string,
): Promise<VendorEarningsSummary> {
  const currentMonth = new Date().toISOString().slice(0, 7);
  const monthBucket = sql<string>`to_char(${order.createdAt}, 'YYYY-MM')`;

  const settledAndVendor = and(
    eq(vendorSplit.vendorId, vendorId),
    sql`${order.status} IN ('paid','fulfilled','completed')`,
    eq(vendorSplit.funder, 'vendor'),
  );

  // All-time vendor net (funder='vendor' splits only)
  const [allTime] = await db
    .select({
      totalVendorAgorot: sql<number>`COALESCE(SUM(${vendorSplit.amount}), 0)::int`,
    })
    .from(vendorSplit)
    .innerJoin(order, eq(vendorSplit.orderId, order.id))
    .where(settledAndVendor);

  // Current month vendor net
  const [currentMonthRow] = await db
    .select({
      vendorAgorot: sql<number>`COALESCE(SUM(${vendorSplit.amount}), 0)::int`,
    })
    .from(vendorSplit)
    .innerJoin(order, eq(vendorSplit.orderId, order.id))
    .where(and(settledAndVendor, sql`${monthBucket} = ${currentMonth}`));

  // Pending: months without a 'paid' payout record
  const pendingRows = await db
    .select({
      periodMonth: monthBucket,
      vendorAgorot: sql<number>`COALESCE(SUM(${vendorSplit.amount}), 0)::int`,
    })
    .from(vendorSplit)
    .innerJoin(order, eq(vendorSplit.orderId, order.id))
    .where(settledAndVendor)
    .groupBy(monthBucket);

  const paidStatusByMonth = new Map<string, string>();
  if (pendingRows.length > 0) {
    const months = [...new Set(pendingRows.map((r) => r.periodMonth))];
    const payoutRows = await db
      .select({ periodMonth: payouts.periodMonth, status: payouts.status })
      .from(payouts)
      .where(and(eq(payouts.vendorId, vendorId), inArray(payouts.periodMonth, months)));
    for (const p of payoutRows) paidStatusByMonth.set(p.periodMonth, p.status);
  }

  let pendingTotal = 0;
  for (const row of pendingRows) {
    if (paidStatusByMonth.get(row.periodMonth) !== 'paid') {
      pendingTotal += row.vendorAgorot;
    }
  }

  return {
    totalVendorAgorot: allTime?.totalVendorAgorot ?? 0,
    currentMonthVendorAgorot: currentMonthRow?.vendorAgorot ?? 0,
    pendingVendorAgorot: pendingTotal,
  };
}

// Silence unused-import lint for status array
void SETTLED_ORDER_STATUSES;