/**
 * Admin dashboard metrics query.
 *
 * getDashboardMetrics: runs aggregate queries across deals, vendors, users,
 * purchases, reports, and review removal queue to produce platform KPIs.
 *
 * No raw SQL. PII is never logged or returned.
 * All results are plain-serializable (numbers, strings).
 */

import { eq, inArray, gte, lt, and, sql, count } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  deals,
  vendors,
  users,
  reportTickets,
  reviewRemovalQueue,
  orderLine,
} from '@/server/db/schema.js';
import { order, vendorSplit } from '@platform-modules/commerce-orders';
import { voucher } from '@platform-modules/commerce-fulfillment';
import { moduleRefAsUuid } from '@/server/platform-seams/ids.js';

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

// ─── Return type ──────────────────────────────────────────────────────────────

export interface DashboardMetrics {
  /** Deals currently in ACTIVE state. */
  activeDeals: number;
  /** Vendors with accountState ACTIVE or VETERAN. */
  activeVendors: number;
  /** Total registered user accounts. */
  totalUsers: number;
  /** All-time purchase count (COMPLETED payments). */
  totalPurchases: number;
  /** All-time gross merchandise value (sum of amountPaid, COMPLETED). */
  totalGmv: string;
  /** All-time Multideal commission (sum of commissionAmount, COMPLETED). */
  totalCommission: string;
  /** Users whose account was created in the last 7 days. */
  newUsers7d: number;
  /** Users whose account was created in the last 30 days. */
  newUsers30d: number;
  /**
   * Total pending action items across three queues:
   *   deals with state PENDING_APPROVAL
   * + review removal queue entries with status PENDING
   * + report tickets with status OPEN or INVESTIGATING
   */
  pendingItems: number;
  /** Deals with state PENDING_APPROVAL. */
  pendingDeals: number;
  /** Review removal queue entries with status PENDING. */
  pendingRemovals: number;
  /** Report tickets with status OPEN or INVESTIGATING. */
  pendingReports: number;
  /**
   * Redemption rate as a percentage string (e.g. "72.4").
   * Computed as REDEEMED purchases / total COMPLETED purchases * 100.
   * Returns "0" when there are no purchases.
   */
  redemptionRate: string;
}

// ─── Metric deltas ────────────────────────────────────────────────────────────

/**
 * Percentage-change values vs the previous identical time window.
 *
 * Each field mirrors a numeric field in DashboardMetrics.
 * Value is null when the previous window had a count of 0 (division by zero
 * is undefined — callers should render "N/A" or a "new" badge instead).
 *
 * Fields excluded (no meaningful delta):
 *   - pendingItems (derived sum — deltas on sub-fields are enough)
 */
export interface DashboardMetricDeltas {
  /** Pct change in active deals. */
  activeDeals: number | null;
  /** Pct change in active vendors. */
  activeVendors: number | null;
  /** Pct change in total user count. */
  totalUsers: number | null;
  /** Pct change in completed purchase count. */
  totalPurchases: number | null;
  /** Pct change in gross merchandise value. */
  totalGmv: number | null;
  /** Pct change in platform commission. */
  totalCommission: number | null;
  /** Pct change in new users in current window vs previous window of same length. */
  newUsers: number | null;
  /** Pct change in pending deal approvals. */
  pendingDeals: number | null;
  /** Pct change in pending review removals. */
  pendingRemovals: number | null;
  /** Pct change in pending report tickets. */
  pendingReports: number | null;
}

/** Compute pct change. Returns null when previous is 0. */
function pct(current: number, previous: number): number | null {
  if (previous === 0) return null;
  return ((current - previous) / previous) * 100;
}

/**
 * Returns percentage-change metrics comparing the current window of `periodDays`
 * against the immediately preceding window of the same length.
 *
 * Example: periodDays=7 → compares last 7 days to the 7 days before that.
 */
export async function getDashboardMetricDeltas(
  db: DrizzleClient,
  { periodDays }: { periodDays: number },
): Promise<DashboardMetricDeltas> {
  const windowMs = periodDays * 24 * 60 * 60 * 1000;
  const now = new Date();
  const currentStart = new Date(now.getTime() - windowMs);
  const previousStart = new Date(now.getTime() - 2 * windowMs);

  // ── Current window (10 queries) ──────────────────────────────────────────
  // T6 repoint: purchaseStats split into orderStats (count+gmv) + commission (vendorSplit).
  const [
    curActiveDeals,
    curActiveVendors,
    curTotalUsers,
    curOrderStats,
    curCommission,
    curNewUsers,
    _curNewUsers30d, // unused placeholder to keep parallel index alignment
    curPendingDeals,
    curPendingRemovals,
    curPendingReports,
  ] = await Promise.all([
    db.select({ n: count() }).from(deals).where(eq(deals.dealState, 'ACTIVE')),
    db
      .select({ n: count() })
      .from(vendors)
      .where(inArray(vendors.accountState, ['ACTIVE', 'VETERAN'])),
    db.select({ n: count() }).from(users),
    // order count + GMV for settled orders
    db
      .select({
        total: count(),
        gmv: sql<string>`COALESCE(SUM(${order.total}), 0)::text`,
      })
      .from(order)
      .where(inArray(order.status, [...SETTLED_ORDER_STATUSES])),
    // platform commission via vendorSplit funder='platform'
    db
      .select({
        commission: sql<string>`COALESCE(SUM(${vendorSplit.amount}), 0)::text`,
      })
      .from(vendorSplit)
      .innerJoin(order, eq(vendorSplit.orderId, order.id))
      .where(
        and(eq(vendorSplit.funder, 'platform'), inArray(order.status, [...SETTLED_ORDER_STATUSES])),
      ),
    db.select({ n: count() }).from(users).where(gte(users.createdAt, currentStart)),
    db.select({ n: count() }).from(users).where(gte(users.createdAt, currentStart)), // placeholder
    db.select({ n: count() }).from(deals).where(eq(deals.dealState, 'PENDING_APPROVAL')),
    db
      .select({ n: count() })
      .from(reviewRemovalQueue)
      .where(eq(reviewRemovalQueue.status, 'PENDING')),
    db
      .select({ n: count() })
      .from(reportTickets)
      .where(inArray(reportTickets.status, ['OPEN', 'INVESTIGATING'])),
  ]);

  // ── Previous window (10 queries) ─────────────────────────────────────────
  const [
    prevActiveDeals,
    prevActiveVendors,
    prevTotalUsers,
    prevOrderStats,
    prevCommission,
    prevNewUsers,
    _prevNewUsers30d,
    prevPendingDeals,
    prevPendingRemovals,
    prevPendingReports,
  ] = await Promise.all([
    db.select({ n: count() }).from(deals).where(eq(deals.dealState, 'ACTIVE')),
    db
      .select({ n: count() })
      .from(vendors)
      .where(inArray(vendors.accountState, ['ACTIVE', 'VETERAN'])),
    db.select({ n: count() }).from(users).where(lt(users.createdAt, currentStart)),
    // previous window settled orders
    db
      .select({
        total: count(),
        gmv: sql<string>`COALESCE(SUM(${order.total}), 0)::text`,
      })
      .from(order)
      .where(
        and(inArray(order.status, [...SETTLED_ORDER_STATUSES]), lt(order.createdAt, currentStart)),
      ),
    // previous window commission
    db
      .select({
        commission: sql<string>`COALESCE(SUM(${vendorSplit.amount}), 0)::text`,
      })
      .from(vendorSplit)
      .innerJoin(order, eq(vendorSplit.orderId, order.id))
      .where(
        and(
          eq(vendorSplit.funder, 'platform'),
          inArray(order.status, [...SETTLED_ORDER_STATUSES]),
          lt(order.createdAt, currentStart),
        ),
      ),
    db
      .select({ n: count() })
      .from(users)
      .where(and(gte(users.createdAt, previousStart), lt(users.createdAt, currentStart))),
    db
      .select({ n: count() })
      .from(users)
      .where(and(gte(users.createdAt, previousStart), lt(users.createdAt, currentStart))), // placeholder
    db.select({ n: count() }).from(deals).where(eq(deals.dealState, 'PENDING_APPROVAL')),
    db
      .select({ n: count() })
      .from(reviewRemovalQueue)
      .where(eq(reviewRemovalQueue.status, 'PENDING')),
    db
      .select({ n: count() })
      .from(reportTickets)
      .where(inArray(reportTickets.status, ['OPEN', 'INVESTIGATING'])),
  ]);

  const cActiveDealsCnt = Number(curActiveDeals[0]?.n ?? 0);
  const pActiveDealsCnt = Number(prevActiveDeals[0]?.n ?? 0);
  const cActiveVendorsCnt = Number(curActiveVendors[0]?.n ?? 0);
  const pActiveVendorsCnt = Number(prevActiveVendors[0]?.n ?? 0);
  const cTotalUsersCnt = Number(curTotalUsers[0]?.n ?? 0);
  const pTotalUsersCnt = Number(prevTotalUsers[0]?.n ?? 0);

  const cOrderRow = curOrderStats[0];
  const pOrderRow = prevOrderStats[0];
  const cTotalPurchases = Number(cOrderRow?.total ?? 0);
  const pTotalPurchases = Number(pOrderRow?.total ?? 0);
  const cGmv = parseFloat(cOrderRow?.gmv?.toString() ?? '0');
  const pGmv = parseFloat(pOrderRow?.gmv?.toString() ?? '0');
  const cCommission = parseFloat(curCommission[0]?.commission?.toString() ?? '0');
  const pCommission = parseFloat(prevCommission[0]?.commission?.toString() ?? '0');

  const cNewUsers = Number(curNewUsers[0]?.n ?? 0);
  const pNewUsers = Number(prevNewUsers[0]?.n ?? 0);
  const cPendingDeals = Number(curPendingDeals[0]?.n ?? 0);
  const pPendingDeals = Number(prevPendingDeals[0]?.n ?? 0);
  const cPendingRemovals = Number(curPendingRemovals[0]?.n ?? 0);
  const pPendingRemovals = Number(prevPendingRemovals[0]?.n ?? 0);
  const cPendingReports = Number(curPendingReports[0]?.n ?? 0);
  const pPendingReports = Number(prevPendingReports[0]?.n ?? 0);

  return {
    activeDeals: pct(cActiveDealsCnt, pActiveDealsCnt),
    activeVendors: pct(cActiveVendorsCnt, pActiveVendorsCnt),
    totalUsers: pct(cTotalUsersCnt, pTotalUsersCnt),
    totalPurchases: pct(cTotalPurchases, pTotalPurchases),
    totalGmv: pct(cGmv, pGmv),
    totalCommission: pct(cCommission, pCommission),
    newUsers: pct(cNewUsers, pNewUsers),
    pendingDeals: pct(cPendingDeals, pPendingDeals),
    pendingRemovals: pct(cPendingRemovals, pPendingRemovals),
    pendingReports: pct(cPendingReports, pPendingReports),
  };
}

// ─── Query ────────────────────────────────────────────────────────────────────

export async function getDashboardMetrics(db: DrizzleClient): Promise<DashboardMetrics> {
  const sevenDaysAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);
  const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);

  // Run all aggregates in parallel - Neon HTTP has no persistent connection
  // so each is an independent round-trip; parallelism maximises speed.
  // T6 repoint: purchaseStats split into 3 parallel queries.
  // T7: redemptionRate reads platform voucher rows joined to settled orders.
  const [
    activeDealsResult,
    activeVendorsResult,
    totalUsersResult,
    orderCountGmvResult,
    commissionResult,
    redemptionResult,
    newUsers7dResult,
    newUsers30dResult,
    pendingDealsResult,
    pendingRemovalsResult,
    pendingReportsResult,
  ] = await Promise.all([
    // Active deals
    db.select({ n: count() }).from(deals).where(eq(deals.dealState, 'ACTIVE')),

    // Active vendors (ACTIVE or VETERAN)
    db
      .select({ n: count() })
      .from(vendors)
      .where(inArray(vendors.accountState, ['ACTIVE', 'VETERAN'])),

    // Total users
    db.select({ n: count() }).from(users),

    // Settled order count + GMV (T6 repoint)
    db
      .select({
        total: count(),
        gmv: sql<string>`COALESCE(SUM(${order.total}), 0)::text`,
      })
      .from(order)
      .where(inArray(order.status, [...SETTLED_ORDER_STATUSES])),

    // Platform commission via vendorSplit funder='platform' (T6 repoint)
    db
      .select({
        commission: sql<string>`COALESCE(SUM(${vendorSplit.amount}), 0)::text`,
      })
      .from(vendorSplit)
      .innerJoin(order, eq(vendorSplit.orderId, order.id))
      .where(
        and(eq(vendorSplit.funder, 'platform'), inArray(order.status, [...SETTLED_ORDER_STATUSES])),
      ),

    // Redemption rate via platform voucher table (T7: purchases table removed)
    db
      .select({
        total: count(),
        redeemed: sql<number>`COUNT(*) FILTER (WHERE ${voucher.state} = 'REDEEMED')`,
      })
      .from(voucher)
      .innerJoin(orderLine, eq(orderLine.id, moduleRefAsUuid(sql`${voucher.lineId}`)))
      .innerJoin(order, eq(orderLine.orderId, order.id))
      .where(inArray(order.status, [...SETTLED_ORDER_STATUSES])),

    // New users last 7 days
    db.select({ n: count() }).from(users).where(gte(users.createdAt, sevenDaysAgo)),

    // New users last 30 days
    db.select({ n: count() }).from(users).where(gte(users.createdAt, thirtyDaysAgo)),

    // Pending deal approvals
    db.select({ n: count() }).from(deals).where(eq(deals.dealState, 'PENDING_APPROVAL')),

    // Pending review removals
    db
      .select({ n: count() })
      .from(reviewRemovalQueue)
      .where(eq(reviewRemovalQueue.status, 'PENDING')),

    // Open or investigating reports
    db
      .select({ n: count() })
      .from(reportTickets)
      .where(inArray(reportTickets.status, ['OPEN', 'INVESTIGATING'])),
  ]);

  const activeDeals = activeDealsResult[0]?.n ?? 0;
  const activeVendors = activeVendorsResult[0]?.n ?? 0;
  const totalUsers = totalUsersResult[0]?.n ?? 0;

  const orderRow = orderCountGmvResult[0];
  const totalPurchases = orderRow?.total ?? 0;
  const totalGmv = orderRow?.gmv ?? '0';
  const totalCommission = commissionResult[0]?.commission ?? '0';
  const redemptionRow = redemptionResult[0];
  const redeemedCount = Number(redemptionRow?.redeemed ?? 0);

  const newUsers7d = newUsers7dResult[0]?.n ?? 0;
  const newUsers30d = newUsers30dResult[0]?.n ?? 0;

  const pendingItems =
    (pendingDealsResult[0]?.n ?? 0) +
    (pendingRemovalsResult[0]?.n ?? 0) +
    (pendingReportsResult[0]?.n ?? 0);

  // Redemption rate uses voucher counts (T7 migration)
  const redemptionBase = Number(redemptionRow?.total ?? 0);
  const redemptionRate =
    redemptionBase > 0 ? ((redeemedCount / redemptionBase) * 100).toFixed(1) : '0';

  return {
    activeDeals: Number(activeDeals),
    activeVendors: Number(activeVendors),
    totalUsers: Number(totalUsers),
    totalPurchases: Number(totalPurchases),
    totalGmv: totalGmv?.toString() ?? '0',
    totalCommission: totalCommission?.toString() ?? '0',
    newUsers7d: Number(newUsers7d),
    newUsers30d: Number(newUsers30d),
    pendingItems: Number(pendingItems),
    pendingDeals: Number(pendingDealsResult[0]?.n ?? 0),
    pendingRemovals: Number(pendingRemovalsResult[0]?.n ?? 0),
    pendingReports: Number(pendingReportsResult[0]?.n ?? 0),
    redemptionRate,
  };
}