/**
 * LTV query helpers — single source of truth for customer / vendor / affiliate LTV.
 *
 * Money is agorot (integer) end to end. UI formats via formatCurrency.
 * Settled orders only; net-of-refunds per tmp/specs/ltv-dashboards.md.
 *
 * T7: purchases table removed. Queries now join orderLine → order.
 *   - refAttributionId not yet on orderLine; affiliate purchase metrics return zeros.
 */

import { and, asc, eq, ilike, inArray, isNotNull, sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  affiliateEntriesTable,
  affiliatePayouts,
  ledgerEntries,
  orderLine,
  order,
  vendorSplit,
  returns,
  users,
} from '@/server/db/schema.js';
import { minorForApi } from '@/server/referrals/ledger.js';

/** Settled order statuses — matches order.status (lowercase). */
export const SETTLED_PURCHASE_STATUSES = ['completed', 'refunded', 'partially_refunded'] as const;

export type CustomerLtv = {
  netAgorot: number;
  orderCount: number;
  firstAt: Date | null;
  lastAt: Date | null;
};

export type VendorCustomerLtvRow = {
  userId: string;
  displayName: string | null;
  netAgorot: number;
  orderCount: number;
  lastAt: Date | null;
};

export type VendorLtv = {
  platformNetAgorot: number;
  gmvAgorot: number;
  orderCount: number;
};

export type AffiliateLtv = {
  marginAgorot: number;
  stripeFeeKnown: boolean;
  totalPayoutsAgorot: number;
};

/** Gross spend per order-line row (agorot). lineTotal is already in agorot as bigint. */
const purchaseGrossAgorot = sql<number>`${orderLine.lineTotal}::int`;

/** Cash refunded to buyer via completed returns (partial or full). */
function purchaseCashRefundedAgorot() {
  return sql<number>`(
    SELECT COALESCE(SUM(${returns.cashRefundAgorot}), 0)::int
    FROM ${returns}
    WHERE ${returns.orderLineId} = ${orderLine.id}
      AND ${returns.status} = 'refunded'
      AND ${returns.cashRefundAgorot} IS NOT NULL
  )`;
}

/**
 * Buyer net spend per order-line row (agorot).
 * Requires order + orderLine to be in the query FROM clause.
 */
export const purchaseNetCustomerAgorot = sql<number>`CASE
  WHEN ${order.status} = 'refunded' THEN 0
  ELSE GREATEST(0, ${purchaseGrossAgorot} - COALESCE(${purchaseCashRefundedAgorot()}, 0))
END`;

function settledPurchaseFilter(
  ...extra: Parameters<typeof and> extends (infer U)[] ? U[] : never[]
) {
  return and(inArray(order.status, [...SETTLED_PURCHASE_STATUSES]), ...extra);
}

const emptyCustomerLtv: CustomerLtv = {
  netAgorot: 0,
  orderCount: 0,
  firstAt: null,
  lastAt: null,
};

/** Buyer's cumulative net spend across all vendors (settled orders, net of refunds). */
export async function customerLtvGlobal(db: DrizzleClient, userId: string): Promise<CustomerLtv> {
  const [row] = await db
    .select({
      netAgorot: sql<number>`COALESCE(SUM(${purchaseNetCustomerAgorot}), 0)::int`,
      orderCount: sql<number>`COUNT(*)::int`,
      firstAt: sql<Date | null>`MIN(${orderLine.createdAt})`,
      lastAt: sql<Date | null>`MAX(${orderLine.createdAt})`,
    })
    .from(orderLine)
    .innerJoin(order, eq(orderLine.orderId, order.id))
    .where(settledPurchaseFilter(eq(order.buyerUserId, userId), isNotNull(order.buyerUserId)));

  if (!row) return emptyCustomerLtv;

  return {
    netAgorot: Number(row.netAgorot ?? 0),
    orderCount: Number(row.orderCount ?? 0),
    firstAt: row.firstAt ?? null,
    lastAt: row.lastAt ?? null,
  };
}

/** Buyer's cumulative net spend on this vendor's deals only. */
export async function customerLtvForVendor(
  db: DrizzleClient,
  vendorId: string,
  userId: string,
): Promise<CustomerLtv> {
  const [row] = await db
    .select({
      netAgorot: sql<number>`COALESCE(SUM(${purchaseNetCustomerAgorot}), 0)::int`,
      orderCount: sql<number>`COUNT(*)::int`,
      firstAt: sql<Date | null>`MIN(${orderLine.createdAt})`,
      lastAt: sql<Date | null>`MAX(${orderLine.createdAt})`,
    })
    .from(orderLine)
    .innerJoin(order, eq(orderLine.orderId, order.id))
    .where(
      settledPurchaseFilter(
        eq(orderLine.vendorId, vendorId),
        eq(order.buyerUserId, userId),
        isNotNull(order.buyerUserId),
      ),
    );

  if (!row) return emptyCustomerLtv;

  return {
    netAgorot: Number(row.netAgorot ?? 0),
    orderCount: Number(row.orderCount ?? 0),
    firstAt: row.firstAt ?? null,
    lastAt: row.lastAt ?? null,
  };
}

export type VendorCustomerLtvListOptions = {
  search?: string;
  page?: number;
  pageSize?: number;
};

export type VendorCustomerLtvListResult = {
  rows: VendorCustomerLtvRow[];
  total: number;
};

/** All buyers of a vendor with per-buyer scoped LTV (single aggregate query). */
export async function vendorCustomerLtvList(
  db: DrizzleClient,
  vendorId: string,
  opts: VendorCustomerLtvListOptions = {},
): Promise<VendorCustomerLtvListResult> {
  const pageSize = opts.pageSize ?? 50;
  const page = opts.page ?? 1;
  const offset = (page - 1) * pageSize;

  const filters = [
    eq(orderLine.vendorId, vendorId),
    isNotNull(order.buyerUserId),
    ...(opts.search?.trim() ? [ilike(users.displayName, `%${opts.search.trim()}%`)] : []),
  ];

  const whereClause = settledPurchaseFilter(...filters);

  const [countRow, rows] = await Promise.all([
    db
      .select({ total: sql<number>`COUNT(DISTINCT ${order.buyerUserId})::int` })
      .from(orderLine)
      .innerJoin(order, eq(orderLine.orderId, order.id))
      .innerJoin(users, eq(order.buyerUserId, users.id))
      .where(whereClause),
    db
      .select({
        userId: order.buyerUserId,
        displayName: users.displayName,
        netAgorot: sql<number>`COALESCE(SUM(${purchaseNetCustomerAgorot}), 0)::int`,
        orderCount: sql<number>`COUNT(*)::int`,
        lastAt: sql<Date | null>`MAX(${orderLine.createdAt})`,
      })
      .from(orderLine)
      .innerJoin(order, eq(orderLine.orderId, order.id))
      .innerJoin(users, eq(order.buyerUserId, users.id))
      .where(whereClause)
      .groupBy(order.buyerUserId, users.displayName)
      .orderBy(sql`SUM(${purchaseNetCustomerAgorot}) DESC`, asc(order.buyerUserId))
      .limit(pageSize)
      .offset(offset),
  ]);

  return {
    total: Number(countRow[0]?.total ?? 0),
    rows: rows
      .filter((row): row is typeof row & { userId: string } => row.userId != null)
      .map((row) => ({
        userId: row.userId,
        displayName: row.displayName ?? null,
        netAgorot: Number(row.netAgorot ?? 0),
        orderCount: Number(row.orderCount ?? 0),
        lastAt: row.lastAt ?? null,
      })),
  };
}

/** Vendor value to the platform: platform fee net (lead) + GMV gross (secondary). */
export async function vendorLtv(db: DrizzleClient, vendorId: string): Promise<VendorLtv> {
  const settled = [...SETTLED_PURCHASE_STATUSES];

  const [gmvRow, platformRow] = await Promise.all([
    // GMV + order count via orderLine
    db
      .select({
        gmvAgorot: sql<number>`COALESCE(SUM(${orderLine.lineTotal})::int, 0)`,
        orderCount: sql<number>`COUNT(*)::int`,
      })
      .from(orderLine)
      .innerJoin(order, eq(orderLine.orderId, order.id))
      .where(and(eq(orderLine.vendorId, vendorId), inArray(order.status, settled))),

    // Platform commission via vendorSplit (funder='platform') on orders from this vendor
    db
      .select({
        platformNetAgorot: sql<number>`COALESCE(SUM(${vendorSplit.amount})::int, 0)`,
      })
      .from(vendorSplit)
      .innerJoin(order, eq(vendorSplit.orderId, order.id))
      .where(
        and(
          eq(vendorSplit.funder, 'platform'),
          inArray(order.status, settled),
          sql`EXISTS (
            SELECT 1 FROM ${orderLine} ol
            WHERE ol.order_id = ${order.id}
              AND ol.vendor_id = ${vendorId}
          )`,
        ),
      ),
  ]);

  return {
    platformNetAgorot: Number(platformRow[0]?.platformNetAgorot ?? 0),
    gmvAgorot: Number(gmvRow[0]?.gmvAgorot ?? 0),
    orderCount: Number(gmvRow[0]?.orderCount ?? 0),
  };
}

const emptyAffiliateLtv: AffiliateLtv = {
  marginAgorot: 0,
  stripeFeeKnown: false,
  totalPayoutsAgorot: 0,
};

/**
 * T7: refAttributionId removed with purchases table; not yet on orderLine.
 * Returns zero platform metrics until orderLine.refAttributionId is added.
 */
async function affiliatePurchaseMetrics(
  _db: DrizzleClient,
  _affiliateUserId: string,
): Promise<{ platformNetAgorot: number; stripeFeesAgorot: number; stripeFeeKnown: boolean }> {
  return { platformNetAgorot: 0, stripeFeesAgorot: 0, stripeFeeKnown: false };
}

/** Net affiliate commission owed (affiliate_commission netting purchase-scoped refund_clawback). */
async function affiliateCommissionNetAgorot(
  db: DrizzleClient,
  affiliateUserId: string,
): Promise<number> {
  const [row] = await db
    .select({
      netAgorot: sql<string>`COALESCE(SUM(${ledgerEntries.delta}), 0)::bigint`,
    })
    .from(affiliateEntriesTable)
    .innerJoin(ledgerEntries, eq(ledgerEntries.id, affiliateEntriesTable.entryId))
    .where(
      and(
        eq(affiliateEntriesTable.ownerId, affiliateUserId),
        sql`(
          ${affiliateEntriesTable.entryType} = 'affiliate_commission'
          OR (${affiliateEntriesTable.entryType} = 'refund_clawback' AND ${affiliateEntriesTable.sourceType} = 'purchase')
        )`,
      ),
    );

  return minorForApi(BigInt(row?.netAgorot ?? 0));
}

/** Cash disbursed to affiliate (paid / marked-paid payouts only). */
async function affiliateTotalPayoutsAgorot(
  db: DrizzleClient,
  affiliateUserId: string,
): Promise<number> {
  const [row] = await db
    .select({
      total: sql<string>`COALESCE(SUM(${affiliatePayouts.amountAgorot}), 0)::bigint`,
    })
    .from(affiliatePayouts)
    .where(and(eq(affiliatePayouts.userId, affiliateUserId), eq(affiliatePayouts.status, 'paid')));

  return minorForApi(BigInt(row?.total ?? 0));
}

/**
 * Net platform margin from an affiliate's referred sales:
 * platform fees − Stripe fees − affiliate commission (netted clawbacks).
 *
 * T7: platform fees and Stripe fees return 0 until refAttributionId is restored on orderLine.
 */
export async function affiliateLtv(
  db: DrizzleClient,
  affiliateUserId: string,
): Promise<AffiliateLtv> {
  const [purchaseMetrics, commissionNetAgorot, totalPayoutsAgorot] = await Promise.all([
    affiliatePurchaseMetrics(db, affiliateUserId),
    affiliateCommissionNetAgorot(db, affiliateUserId),
    affiliateTotalPayoutsAgorot(db, affiliateUserId),
  ]);

  const marginAgorot =
    purchaseMetrics.platformNetAgorot - purchaseMetrics.stripeFeesAgorot - commissionNetAgorot;

  return {
    marginAgorot,
    stripeFeeKnown: purchaseMetrics.stripeFeeKnown,
    totalPayoutsAgorot,
  };
}

/** Batch affiliate LTV for list views — no N+1. */
export async function affiliateLtvBatch(
  db: DrizzleClient,
  userIds: string[],
): Promise<Map<string, AffiliateLtv>> {
  const result = new Map<string, AffiliateLtv>();
  if (userIds.length === 0) return result;

  for (const userId of userIds) {
    result.set(userId, { ...emptyAffiliateLtv });
  }

  // T7: purchaseRows removed — refAttributionId not yet on orderLine.
  // platformNetAgorot and stripeFeesAgorot are zero until migration completes.

  const commissionRows = await db
    .select({
      userId: affiliateEntriesTable.ownerId,
      netAgorot: sql<string>`COALESCE(SUM(${ledgerEntries.delta}), 0)::bigint`,
    })
    .from(affiliateEntriesTable)
    .innerJoin(ledgerEntries, eq(ledgerEntries.id, affiliateEntriesTable.entryId))
    .where(
      and(
        inArray(affiliateEntriesTable.ownerId, userIds),
        sql`(
          ${affiliateEntriesTable.entryType} = 'affiliate_commission'
          OR (${affiliateEntriesTable.entryType} = 'refund_clawback' AND ${affiliateEntriesTable.sourceType} = 'purchase')
        )`,
      ),
    )
    .groupBy(affiliateEntriesTable.ownerId);

  const payoutRows = await db
    .select({
      userId: affiliatePayouts.userId,
      total: sql<string>`COALESCE(SUM(${affiliatePayouts.amountAgorot}), 0)::bigint`,
    })
    .from(affiliatePayouts)
    .where(and(inArray(affiliatePayouts.userId, userIds), eq(affiliatePayouts.status, 'paid')))
    .groupBy(affiliatePayouts.userId);

  const commissionByUser = new Map(
    commissionRows.map((row) => [row.userId, minorForApi(BigInt(row.netAgorot ?? 0))]),
  );

  const payoutByUser = new Map(
    payoutRows.map((row) => [row.userId, minorForApi(BigInt(row.total ?? 0))]),
  );

  for (const userId of userIds) {
    const commissionNet = commissionByUser.get(userId) ?? 0;
    const totalPayouts = payoutByUser.get(userId) ?? 0;

    result.set(userId, {
      // T7: platformNetAgorot = 0; margin shows only commission side
      marginAgorot: 0 - commissionNet,
      stripeFeeKnown: false,
      totalPayoutsAgorot: totalPayouts,
    });
  }

  return result;
}