/**
 * Admin analytics query.
 *
 * getAnalytics: runs aggregate queries scoped to a time bucket window,
 * returning platform KPIs plus top-10 leaderboards.
 *
 * bucketToRange: converts a (mode, bucket) pair to { start, end } using
 * Asia/Jerusalem timezone for calendar-mode buckets (no external deps).
 *
 * No raw SQL beyond Drizzle helpers. PII never logged or returned.
 * All results are plain-serializable.
 */

import { eq, inArray, gte, lte, and, isNotNull, sql, count, sum, desc } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  deals,
  vendors,
  users,
  reportTickets,
  reviewRemovalQueue,
  dealImages,
  llmJobs,
  vendorTranslations,
  dealSkus,
  orderLine,
} from '@/server/db/schema.js';
import { VENDOR_DEFAULT_LOCALE } from '@/server/db/queries/vendors.js';
import { moduleRefAsUuid } from '@/server/platform-seams/ids.js';
import { purchaseNetCustomerAgorot, SETTLED_PURCHASE_STATUSES } from '@/server/analytics/ltv.js';
import { formatAgorotPlain } from '@/lib/money.js';
import { TZ } from '@/lib/datetime';
import { order, vendorSplit } from '@platform-modules/commerce-orders';

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

// ─── Bucket helpers ───────────────────────────────────────────────────────────

export type BucketMode = 'rolling' | 'calendar';
export type BucketSize = 'd' | 'w' | 'm' | 'y' | 'all';

/**
 * Compute { start, end } for a given (mode, bucket) pair.
 *
 * Rolling: subtract a fixed duration from `now`.
 * Calendar: snap to the most recent period boundary in Asia/Jerusalem.
 * All: start = epoch, end = now.
 *
 * Israel TZ is `Asia/Jerusalem`. DST is handled correctly because we derive
 * the wall-clock date parts via Intl.DateTimeFormat and then back-compute the
 * UTC epoch for that midnight, rather than assuming a fixed offset.
 */
export function bucketToRange(mode: BucketMode, bucket: BucketSize): { start: Date; end: Date } {
  const now = new Date();

  if (bucket === 'all') {
    return { start: new Date(0), end: now };
  }

  if (mode === 'rolling') {
    const msMap: Record<Exclude<BucketSize, 'all'>, number> = {
      d: 24 * 60 * 60 * 1000,
      w: 7 * 24 * 60 * 60 * 1000,
      m: 30 * 24 * 60 * 60 * 1000,
      y: 365 * 24 * 60 * 60 * 1000,
    };
    return {
      start: new Date(now.getTime() - msMap[bucket as Exclude<BucketSize, 'all'>]),
      end: now,
    };
  }

  // Calendar mode — compute the local midnight in Asia/Jerusalem.
  // Strategy: format the current instant into its constituent date parts using
  // the Israel TZ, then parse those parts as a "local" Date in Israel's offset-
  // at-that-moment by reconstructing the ISO string and compensating for offset.
  const jeParts = new Intl.DateTimeFormat('en-CA', {
    timeZone: TZ,
    year: 'numeric',
    month: '2-digit',
    day: '2-digit',
  }).formatToParts(now);

  const year = parseInt(jeParts.find((p) => p.type === 'year')!.value, 10);
  const month = parseInt(jeParts.find((p) => p.type === 'month')!.value, 10); // 1-based
  const day = parseInt(jeParts.find((p) => p.type === 'day')!.value, 10);

  // Helper: given a Y/M/D in Israel TZ, return the UTC Date corresponding to
  // midnight of that day in Israel (i.e., the local 00:00:00 wall-clock).
  function jeLocalMidnight(y: number, mo: number, d: number): Date {
    // Build an ISO datetime string that Intl can parse in the Israel offset.
    // We use the "trick": we know the Israel offset at `now` via comparing
    // UTC toString with localeString, but DST-safe approach is:
    // 1. Start from an approximation (UTC midnight of that date).
    // 2. Adjust once by the offset observed at that approximation.
    const approxUtcMs = Date.UTC(y, mo - 1, d, 0, 0, 0, 0);
    const approxDate = new Date(approxUtcMs);

    // Get the wall-clock hour in Israel for this approximate midnight.
    const hourParts = new Intl.DateTimeFormat('en-CA', {
      timeZone: TZ,
      hour: '2-digit',
      minute: '2-digit',
      hour12: false,
    }).formatToParts(approxDate);
    const localHour = parseInt(hourParts.find((p) => p.type === 'hour')!.value, 10);
    const localMinute = parseInt(hourParts.find((p) => p.type === 'minute')!.value, 10);

    // Israel midnight in UTC = approx UTC midnight minus (localHour * 60 + localMinute) minutes.
    const offsetMs = (localHour * 60 + localMinute) * 60 * 1000;
    return new Date(approxUtcMs - offsetMs);
  }

  let start: Date;

  if (bucket === 'd') {
    start = jeLocalMidnight(year, month, day);
  } else if (bucket === 'w') {
    // Israeli calendar: week starts on Sunday (getDay() === 0 in local terms).
    // We need the most recent Sunday (inclusive of today if today is Sunday).
    // Compute day-of-week in Israel.
    const dowParts = new Intl.DateTimeFormat('en-CA', {
      timeZone: TZ,
      weekday: 'short',
    }).format(now);
    const dowMap: Record<string, number> = {
      Sun: 0,
      Mon: 1,
      Tue: 2,
      Wed: 3,
      Thu: 4,
      Fri: 5,
      Sat: 6,
    };
    const dow = dowMap[dowParts] ?? 0;
    // Subtract `dow` days to reach the most recent Sunday.
    const sundayDate = new Date(jeLocalMidnight(year, month, day).getTime() - dow * 86400000);
    start = sundayDate;
  } else if (bucket === 'm') {
    start = jeLocalMidnight(year, month, 1);
  } else {
    // bucket === 'y'
    start = jeLocalMidnight(year, 1, 1);
  }

  return { start, end: now };
}

// ─── Return types ─────────────────────────────────────────────────────────────

export interface VendorLeaderboardRow {
  vendorId: string;
  businessName: string;
  revenue: string;
}

export interface CustomerLeaderboardRow {
  userId: string;
  displayName: string;
  totalSpend: string;
}

export interface DealLeaderboardRow {
  dealId: string;
  title: string;
  purchaseCount: number;
}

export interface AnalyticsResult {
  // Unbucketed KPIs
  totalCustomers: number;
  totalVendors: number;
  itemsPendingModeration: number;

  // Bucketed KPIs
  revenue: string;
  dealsCreated: number;
  purchasesCompleted: number;
  newCustomers: number;
  newVendors: number;
  llmCostUsd: string;

  // Leaderboards
  topVendors: VendorLeaderboardRow[];
  topCustomers: CustomerLeaderboardRow[];
  topDeals: DealLeaderboardRow[];
}

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

export async function getAnalytics(
  db: DrizzleClient,
  opts: { mode: BucketMode; bucket: BucketSize },
): Promise<AnalyticsResult> {
  const { start, end } = bucketToRange(opts.mode, opts.bucket);

  const [
    totalCustomersResult,
    totalVendorsResult,
    pendingDealsResult,
    pendingRemovalsResult,
    pendingReportsResult,
    pendingImagesResult,
    revenueResult,
    dealsCreatedResult,
    purchasesResult,
    newCustomersResult,
    newVendorsResult,
    llmCostResult,
    topVendorsResult,
    topCustomersResult,
    topDealsResult,
  ] = await Promise.all([
    // Total customers (all registered users)
    db.select({ n: count() }).from(users),

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

    // 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 report tickets
    db
      .select({ n: count() })
      .from(reportTickets)
      .where(inArray(reportTickets.status, ['OPEN', 'INVESTIGATING'])),

    // Pending image approvals
    db.select({ n: count() }).from(dealImages).where(eq(dealImages.approvalStatus, 'PENDING')),

    // Revenue: sum of order.total for settled orders in range (T6 repoint — bigint agorot)
    db
      .select({ total: sql<string>`COALESCE(SUM(${order.total}), 0)::text` })
      .from(order)
      .where(
        and(
          inArray(order.status, [...SETTLED_ORDER_STATUSES]),
          gte(order.createdAt, start),
          lte(order.createdAt, end),
        ),
      ),

    // Deals created in range
    db
      .select({ n: count() })
      .from(deals)
      .where(and(gte(deals.createdAt, start), lte(deals.createdAt, end))),

    // Settled orders in range (T6 repoint)
    db
      .select({ n: count() })
      .from(order)
      .where(
        and(
          inArray(order.status, [...SETTLED_ORDER_STATUSES]),
          gte(order.createdAt, start),
          lte(order.createdAt, end),
        ),
      ),

    // New customers (users) in range
    db
      .select({ n: count() })
      .from(users)
      .where(and(gte(users.createdAt, start), lte(users.createdAt, end))),

    // New vendors in range
    db
      .select({ n: count() })
      .from(vendors)
      .where(and(gte(vendors.createdAt, start), lte(vendors.createdAt, end))),

    // LLM cost in range (only rows with costUsd not null)
    db
      .select({ total: sum(llmJobs.costUsd) })
      .from(llmJobs)
      .where(
        and(isNotNull(llmJobs.costUsd), gte(llmJobs.createdAt, start), lte(llmJobs.createdAt, end)),
      ),

    // Top vendors by vendor payout — settled orders in range (T6 repoint via vendorSplit)
    db
      .select({
        vendorId: vendorSplit.vendorId,
        businessName: vendors.businessName,
        revenue: sql<string>`COALESCE(SUM(CASE WHEN ${vendorSplit.funder} = 'vendor' THEN ${vendorSplit.amount} ELSE 0 END), 0)::text`,
      })
      .from(vendorSplit)
      .innerJoin(order, eq(vendorSplit.orderId, order.id))
      .innerJoin(vendors, sql`${moduleRefAsUuid(sql`${vendorSplit.vendorId}`)} = ${vendors.id}`)
      .where(
        and(
          isNotNull(vendorSplit.vendorId),
          inArray(order.status, [...SETTLED_ORDER_STATUSES]),
          gte(order.createdAt, start),
          lte(order.createdAt, end),
        ),
      )
      .groupBy(vendorSplit.vendorId, vendors.businessName)
      .orderBy(
        desc(
          sql`SUM(CASE WHEN ${vendorSplit.funder} = 'vendor' THEN ${vendorSplit.amount} ELSE 0 END)`,
        ),
      )
      .limit(10),

    // Top customers by net spend (settled orders, net-of-refunds) — same definition as ltv.ts
    db
      .select({
        userId: order.buyerUserId,
        displayName: vendorTranslations.displayName,
        totalSpend: sql<number>`COALESCE(SUM(${purchaseNetCustomerAgorot}), 0)::int`,
      })
      .from(orderLine)
      .innerJoin(order, eq(orderLine.orderId, order.id))
      .innerJoin(users, eq(order.buyerUserId, users.id))
      .leftJoin(vendors, eq(vendors.ownerUserId, users.id))
      .leftJoin(
        vendorTranslations,
        and(
          eq(vendorTranslations.vendorId, vendors.id),
          eq(vendorTranslations.locale, VENDOR_DEFAULT_LOCALE),
        ),
      )
      .where(
        and(
          inArray(order.status, [...SETTLED_PURCHASE_STATUSES]),
          isNotNull(order.buyerUserId),
          gte(orderLine.createdAt, start),
          lte(orderLine.createdAt, end),
        ),
      )
      .groupBy(order.buyerUserId, vendorTranslations.displayName)
      .orderBy(desc(sql`SUM(${purchaseNetCustomerAgorot})`))
      .limit(10),

    // Top deals by order-line count — completed orders in range
    db
      .select({
        dealId: dealSkus.dealId,
        title: deals.title,
        purchaseCount: count(),
      })
      .from(orderLine)
      .innerJoin(order, eq(orderLine.orderId, order.id))
      .innerJoin(dealSkus, eq(orderLine.variantId, dealSkus.id))
      .innerJoin(deals, eq(dealSkus.dealId, deals.id))
      .where(
        and(
          eq(order.status, 'completed'),
          gte(orderLine.createdAt, start),
          lte(orderLine.createdAt, end),
        ),
      )
      .groupBy(dealSkus.dealId, deals.title)
      .orderBy(desc(count()))
      .limit(10),
  ]);

  const itemsPendingModeration =
    Number(pendingDealsResult[0]?.n ?? 0) +
    Number(pendingRemovalsResult[0]?.n ?? 0) +
    Number(pendingReportsResult[0]?.n ?? 0) +
    Number(pendingImagesResult[0]?.n ?? 0);

  return {
    totalCustomers: Number(totalCustomersResult[0]?.n ?? 0),
    totalVendors: Number(totalVendorsResult[0]?.n ?? 0),
    itemsPendingModeration,

    revenue: revenueResult[0]?.total ?? '0',
    dealsCreated: Number(dealsCreatedResult[0]?.n ?? 0),
    purchasesCompleted: Number(purchasesResult[0]?.n ?? 0),
    newCustomers: Number(newCustomersResult[0]?.n ?? 0),
    newVendors: Number(newVendorsResult[0]?.n ?? 0),
    llmCostUsd: llmCostResult[0]?.total?.toString() ?? '0',

    topVendors: (topVendorsResult ?? []).map((r) => ({
      vendorId: r.vendorId as string,
      businessName: r.businessName,
      revenue: r.revenue ?? '0',
    })),

    topCustomers: (topCustomersResult ?? []).map((r) => ({
      userId: r.userId ?? '',
      displayName: r.displayName ?? '',
      totalSpend: formatAgorotPlain(Number(r.totalSpend ?? 0)),
    })),

    topDeals: (topDealsResult ?? []).map((r) => ({
      dealId: r.dealId as string,
      title: r.title,
      purchaseCount: Number(r.purchaseCount),
    })),
  };
}