/**
 * Vendor analytics queries — funnel, revenue series, category breakdown,
 * top deals, and period comparison.
 *
 * All queries are vendor-scoped: only data belonging to vendorId is returned.
 * No PII is returned — only aggregated metrics.
 */

import { executeRows } from '../execute-rows.js';
import { and, count, desc, eq, gte, inArray, isNotNull, lt, sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { deals, dealSkus, orderLine, reviews } from '../schema.js';
import { order, vendorSplit } from '@platform-modules/commerce-orders';

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

// ─── Shared helpers ───────────────────────────────────────────────────────────

function startOfDay(d: Date): Date {
  return new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate()));
}

// ─── Types ────────────────────────────────────────────────────────────────────

export interface VendorFunnelRow {
  /** Total deals ever created by vendor. */
  totalDeals: number;
  /** Deals currently in ACTIVE state. */
  activeDeals: number;
  /** Deals that sold out (quantitySold >= quantityTotal). */
  soldOutDeals: number;
  /** Total purchases across all vendor deals. */
  totalPurchases: number;
  /** Total quantity sold across all vendor deals. */
  totalQuantitySold: number;
  /** Average conversion rate (quantitySold / quantityTotal) across active deals. */
  avgConversionRate: number;
}

export interface RevenueSeriesPoint {
  date: string; // ISO date YYYY-MM-DD
  revenue: number;
  orders: number;
}

export interface CategoryBreakdownRow {
  category: string;
  dealCount: number;
  totalRevenue: number;
  totalOrders: number;
}

export interface TopDealRow {
  dealId: string;
  title: string;
  quantitySold: number;
  quantityTotal: number;
  totalRevenue: number;
  conversionRate: number;
}

export interface PeriodComparison {
  current: {
    revenue: number;
    orders: number;
    avgRating: number | null;
  };
  previous: {
    revenue: number;
    orders: number;
    avgRating: number | null;
  };
  deltas: {
    revenueDelta: number;
    ordersDelta: number;
    ratingDelta: number | null;
  };
}

// ─── Queries ──────────────────────────────────────────────────────────────────

/**
 * Conversion funnel metrics for a vendor.
 *
 * Returns deal counts by state + overall purchase totals.
 */
export async function getVendorFunnel(
  db: DrizzleClient,
  vendorId: string,
): Promise<VendorFunnelRow> {
  const [dealStats] = await db
    .select({
      totalDeals: count(deals.id),
      activeDeals: sql<number>`COUNT(*) FILTER (WHERE ${deals.dealState} = 'ACTIVE')`,
      soldOutDeals: sql<number>`COUNT(*) FILTER (WHERE ${deals.stockRemaining} IS NOT NULL AND ${deals.stockRemaining} = 0)`,
      totalQuantitySold: sql<number>`COALESCE((SELECT SUM(ds.quantity_sold) FROM deal_skus ds WHERE ds.deal_id = ANY(ARRAY(SELECT id FROM deals WHERE vendor_id = ${vendorId}))), 0)`,
      avgConversionRate: sql<number>`
        COALESCE(
          (
            SELECT SUM(ds.quantity_sold)::float / NULLIF(SUM(ds.quantity_total), 0)
            FROM deal_skus ds
            INNER JOIN deals d ON d.id = ds.deal_id
            WHERE d.vendor_id = ${vendorId} AND d.deal_state = 'ACTIVE'
          ),
          0
        )
      `,
    })
    .from(deals)
    .where(eq(deals.vendorId, vendorId));

  // T6 repoint: COUNT(DISTINCT order.id) via vendorSplit (all statuses = full funnel)
  const [purchaseStats] = await db
    .select({
      totalPurchases: sql<number>`COUNT(DISTINCT ${order.id})::int`,
    })
    .from(vendorSplit)
    .innerJoin(order, eq(vendorSplit.orderId, order.id))
    .where(and(eq(vendorSplit.vendorId, vendorId), isNotNull(vendorSplit.vendorId)));

  return {
    totalDeals: Number(dealStats?.totalDeals ?? 0),
    activeDeals: Number(dealStats?.activeDeals ?? 0),
    soldOutDeals: Number(dealStats?.soldOutDeals ?? 0),
    totalQuantitySold: Number(dealStats?.totalQuantitySold ?? 0),
    avgConversionRate: Number(dealStats?.avgConversionRate ?? 0),
    totalPurchases: Number(purchaseStats?.totalPurchases ?? 0),
  };
}

/**
 * Daily revenue series for a vendor over a date range.
 *
 * @param fromDate - Inclusive start (UTC).
 * @param toDate   - Exclusive end (UTC). Defaults to today + 1.
 */
export async function getVendorRevenueSeries(
  db: DrizzleClient,
  vendorId: string,
  fromDate: Date,
  toDate?: Date,
): Promise<RevenueSeriesPoint[]> {
  const to = toDate ?? new Date(Date.now() + 86_400_000);

  // T6 repoint: vendorSplit.amount bigint agorot ÷ 100 = shekels (callers expect shekels)
  const rows = await db
    .select({
      date: sql<string>`DATE(${order.createdAt} AT TIME ZONE 'UTC')`,
      revenue: sql<number>`COALESCE(SUM(CASE WHEN ${vendorSplit.funder} = 'vendor' THEN ${vendorSplit.amount} ELSE 0 END) / 100.0, 0)`,
      orders: sql<number>`COUNT(DISTINCT ${order.id})::int`,
    })
    .from(vendorSplit)
    .innerJoin(order, eq(vendorSplit.orderId, order.id))
    .where(
      and(
        eq(vendorSplit.vendorId, vendorId),
        isNotNull(vendorSplit.vendorId),
        inArray(order.status, [...SETTLED_ORDER_STATUSES]),
        gte(order.createdAt, fromDate),
        lt(order.createdAt, to),
      ),
    )
    .groupBy(sql`DATE(${order.createdAt} AT TIME ZONE 'UTC')`)
    .orderBy(sql`DATE(${order.createdAt} AT TIME ZONE 'UTC')`);

  return rows.map((r) => ({
    date: r.date,
    revenue: Number(r.revenue),
    orders: Number(r.orders),
  }));
}

/**
 * Revenue + deal count broken down by deal category for a vendor.
 */
export async function getVendorCategoryBreakdown(
  db: DrizzleClient,
  vendorId: string,
): Promise<CategoryBreakdownRow[]> {
  const rows = await db
    .select({
      category: sql<string>`COALESCE(dc.name, 'Uncategorized')`,
      dealCount: sql<number>`COUNT(DISTINCT ${deals.id})`,
      totalRevenue: sql<number>`COALESCE(SUM(${orderLine.lineTotal}) / 100.0, 0)`,
      totalOrders: count(orderLine.id),
    })
    .from(deals)
    .leftJoin(dealSkus, eq(dealSkus.dealId, deals.id))
    .leftJoin(orderLine, eq(orderLine.variantId, dealSkus.id))
    .leftJoin(
      order,
      and(eq(order.id, orderLine.orderId), inArray(order.status, [...SETTLED_ORDER_STATUSES])),
    )
    .leftJoin(sql`deal_categories dc`, sql`dc.id = ${deals.categoryId}`)
    .where(eq(deals.vendorId, vendorId))
    .groupBy(sql`COALESCE(dc.name, 'Uncategorized')`)
    .orderBy(desc(sql`COALESCE(SUM(${orderLine.lineTotal}) / 100.0, 0)`));

  return rows.map((r) => ({
    category: r.category,
    dealCount: Number(r.dealCount),
    totalRevenue: Number(r.totalRevenue),
    totalOrders: Number(r.totalOrders),
  }));
}

/**
 * Top N deals by revenue for a vendor.
 *
 * @param limit - Number of deals to return. Default: 5.
 */
export async function listTopDealsByVendor(
  db: DrizzleClient,
  vendorId: string,
  limit = 5,
): Promise<TopDealRow[]> {
  const rows = await db
    .select({
      dealId: deals.id,
      title: deals.title,
      quantitySold: sql<number>`COALESCE((SELECT SUM(ds.quantity_sold) FROM deal_skus ds WHERE ds.deal_id = ${deals.id}), 0)`,
      quantityTotal: sql<number>`COALESCE((SELECT SUM(ds.quantity_total) FROM deal_skus ds WHERE ds.deal_id = ${deals.id}), 0)`,
      totalRevenue: sql<number>`COALESCE(SUM(${orderLine.lineTotal}) / 100.0, 0)`,
    })
    .from(deals)
    .leftJoin(dealSkus, eq(dealSkus.dealId, deals.id))
    .leftJoin(orderLine, eq(orderLine.variantId, dealSkus.id))
    .leftJoin(
      order,
      and(eq(order.id, orderLine.orderId), inArray(order.status, [...SETTLED_ORDER_STATUSES])),
    )
    .where(eq(deals.vendorId, vendorId))
    .groupBy(deals.id, deals.title)
    .orderBy(desc(sql`COALESCE(SUM(${orderLine.lineTotal}) / 100.0, 0)`))
    .limit(limit);

  return rows.map((r) => ({
    dealId: r.dealId,
    title: r.title,
    quantitySold: Number(r.quantitySold),
    quantityTotal: Number(r.quantityTotal),
    totalRevenue: Number(r.totalRevenue),
    conversionRate:
      Number(r.quantityTotal) > 0 ? Number(r.quantitySold) / Number(r.quantityTotal) : 0,
  }));
}

/**
 * Compare current period vs previous period for key vendor metrics.
 *
 * @param periodDays - Length of each period in days. Default: 30.
 */
export async function getPeriodComparison(
  db: DrizzleClient,
  vendorId: string,
  periodDays = 30,
): Promise<PeriodComparison> {
  const now = new Date();
  const currentStart = startOfDay(new Date(now.getTime() - periodDays * 86_400_000));
  const previousStart = startOfDay(new Date(currentStart.getTime() - periodDays * 86_400_000));

  // T6 repoint: vendorSplit.amount bigint agorot ÷ 100 = shekels (caller expects shekels)
  async function getPeriodMetrics(from: Date, to: Date) {
    const [purchaseRow] = await db
      .select({
        revenue: sql<number>`COALESCE(SUM(CASE WHEN ${vendorSplit.funder} = 'vendor' THEN ${vendorSplit.amount} ELSE 0 END) / 100.0, 0)`,
        orders: sql<number>`COUNT(DISTINCT ${order.id})::int`,
      })
      .from(vendorSplit)
      .innerJoin(order, eq(vendorSplit.orderId, order.id))
      .where(
        and(
          eq(vendorSplit.vendorId, vendorId),
          isNotNull(vendorSplit.vendorId),
          inArray(order.status, [...SETTLED_ORDER_STATUSES]),
          gte(order.createdAt, from),
          lt(order.createdAt, to),
        ),
      );

    const [ratingRow] = await db
      .select({
        avgRating: sql<number | null>`AVG(${reviews.rating}::float)`,
      })
      .from(reviews)
      .where(
        and(
          eq(reviews.vendorId, vendorId),
          eq(reviews.isVisible, true),
          gte(reviews.createdAt, from),
          lt(reviews.createdAt, to),
        ),
      );

    return {
      revenue: Number(purchaseRow?.revenue ?? 0),
      orders: Number(purchaseRow?.orders ?? 0),
      avgRating: ratingRow?.avgRating != null ? Number(ratingRow.avgRating) : null,
    };
  }

  const [current, previous] = await Promise.all([
    getPeriodMetrics(currentStart, now),
    getPeriodMetrics(previousStart, currentStart),
  ]);

  const revenueDelta = current.revenue - previous.revenue;
  const ordersDelta = current.orders - previous.orders;
  const ratingDelta =
    current.avgRating != null && previous.avgRating != null
      ? current.avgRating - previous.avgRating
      : null;

  return {
    current,
    previous,
    deltas: { revenueDelta, ordersDelta, ratingDelta },
  };
}

// ─── Calendar-month overload ───────────────────────────────────────────────────

export interface CalendarMonthComparison {
  /** MTD revenue (current calendar month, UTC). */
  currentMonthRevenue: number;
  /** Full previous calendar month revenue. */
  previousMonthRevenue: number;
  /** MTD orders. */
  currentMonthOrders: number;
  /** Full previous calendar month orders. */
  previousMonthOrders: number;
  /** MTD avg rating (null if no reviews). */
  currentMonthRating: number | null;
  /** Previous month avg rating (null if no reviews). */
  previousMonthRating: number | null;
  revenueDelta: number;
  ordersDelta: number;
  ratingDelta: number | null;
}

/**
 * Compare current calendar month (MTD) vs the full previous calendar month.
 *
 * @param mode — must be `'calendarMonth'` to differentiate from rolling overload.
 */
export async function getPeriodComparisonCalendarMonth(
  db: DrizzleClient,
  vendorId: string,
): Promise<CalendarMonthComparison> {
  const now = new Date();
  const monthStart = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), 1));
  const prevMonthStart = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth() - 1, 1));

  const [current, previous] = await Promise.all([
    // Current MTD (T6 repoint: vendorSplit.amount agorot ÷ 100 → shekels)
    (async () => {
      const [pr] = await db
        .select({
          revenue: sql<number>`COALESCE(SUM(CASE WHEN ${vendorSplit.funder} = 'vendor' THEN ${vendorSplit.amount} ELSE 0 END) / 100.0, 0)`,
          orders: sql<number>`COUNT(DISTINCT ${order.id})::int`,
        })
        .from(vendorSplit)
        .innerJoin(order, eq(vendorSplit.orderId, order.id))
        .where(
          and(
            eq(vendorSplit.vendorId, vendorId),
            isNotNull(vendorSplit.vendorId),
            inArray(order.status, [...SETTLED_ORDER_STATUSES]),
            gte(order.createdAt, monthStart),
            lt(order.createdAt, now),
          ),
        );
      const [rr] = await db
        .select({ avgRating: sql<number | null>`AVG(${reviews.rating}::float)` })
        .from(reviews)
        .where(
          and(
            eq(reviews.vendorId, vendorId),
            eq(reviews.isVisible, true),
            gte(reviews.createdAt, monthStart),
            lt(reviews.createdAt, now),
          ),
        );
      return {
        revenue: Number(pr?.revenue ?? 0),
        orders: Number(pr?.orders ?? 0),
        avgRating: rr?.avgRating != null ? Number(rr.avgRating) : null,
      };
    })(),
    // Previous full month (T6 repoint)
    (async () => {
      const [pr] = await db
        .select({
          revenue: sql<number>`COALESCE(SUM(CASE WHEN ${vendorSplit.funder} = 'vendor' THEN ${vendorSplit.amount} ELSE 0 END) / 100.0, 0)`,
          orders: sql<number>`COUNT(DISTINCT ${order.id})::int`,
        })
        .from(vendorSplit)
        .innerJoin(order, eq(vendorSplit.orderId, order.id))
        .where(
          and(
            eq(vendorSplit.vendorId, vendorId),
            isNotNull(vendorSplit.vendorId),
            inArray(order.status, [...SETTLED_ORDER_STATUSES]),
            gte(order.createdAt, prevMonthStart),
            lt(order.createdAt, monthStart),
          ),
        );
      const [rr] = await db
        .select({ avgRating: sql<number | null>`AVG(${reviews.rating}::float)` })
        .from(reviews)
        .where(
          and(
            eq(reviews.vendorId, vendorId),
            eq(reviews.isVisible, true),
            gte(reviews.createdAt, prevMonthStart),
            lt(reviews.createdAt, monthStart),
          ),
        );
      return {
        revenue: Number(pr?.revenue ?? 0),
        orders: Number(pr?.orders ?? 0),
        avgRating: rr?.avgRating != null ? Number(rr.avgRating) : null,
      };
    })(),
  ]);

  return {
    currentMonthRevenue: current.revenue,
    previousMonthRevenue: previous.revenue,
    currentMonthOrders: current.orders,
    previousMonthOrders: previous.orders,
    currentMonthRating: current.avgRating,
    previousMonthRating: previous.avgRating,
    revenueDelta: current.revenue - previous.revenue,
    ordersDelta: current.orders - previous.orders,
    ratingDelta:
      current.avgRating != null && previous.avgRating != null
        ? current.avgRating - previous.avgRating
        : null,
  };
}

// ─── Returned buyer count ─────────────────────────────────────────────────────

/**
 * Count of distinct buyers who made more than one COMPLETED purchase from this
 * vendor (i.e. returned to buy again).
 */
export async function getReturnedBuyerCount(db: DrizzleClient, vendorId: string): Promise<number> {
  // T6 repoint: buyers with >1 distinct settled order from this vendor
  const result = await db.execute(
    sql`
      SELECT COUNT(*) AS n
      FROM (
        SELECT ${order.buyerUserId}
        FROM ${vendorSplit}
        INNER JOIN ${order} ON ${vendorSplit.orderId} = ${order.id}
        WHERE ${vendorSplit.vendorId} = ${vendorId}
          AND ${vendorSplit.vendorId} IS NOT NULL
          AND ${order.status} IN ('paid','fulfilled','completed')
        GROUP BY ${order.buyerUserId}
        HAVING COUNT(DISTINCT ${order.id}) > 1
      ) sub
    `,
  );
  const rows = executeRows<{ n: number | string }>(result);
  return Number(rows[0]?.n ?? 0);
}

// ─── Distinct buyers this month ───────────────────────────────────────────────

/**
 * Count of distinct buyers with at least one COMPLETED purchase from this vendor
 * in the current calendar month (UTC).
 */
export async function countDistinctBuyersThisMonth(
  db: DrizzleClient,
  vendorId: string,
): Promise<number> {
  const now = new Date();
  const monthStart = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), 1));

  // T6 repoint: distinct buyerUserId from settled orders via vendorSplit
  const [row] = await db
    .select({
      n: sql<number>`COUNT(DISTINCT ${order.buyerUserId})`,
    })
    .from(vendorSplit)
    .innerJoin(order, eq(vendorSplit.orderId, order.id))
    .where(
      and(
        eq(vendorSplit.vendorId, vendorId),
        isNotNull(vendorSplit.vendorId),
        inArray(order.status, [...SETTLED_ORDER_STATUSES]),
        gte(order.createdAt, monthStart),
        lt(order.createdAt, now),
      ),
    );

  return Number(row?.n ?? 0);
}

// ─── Redemption peak hour ─────────────────────────────────────────────────────

export interface RedemptionPeakHour {
  /** UTC hour (0-23) with most redemptions, or null if no redemptions. */
  peakHour: number | null;
  /** Redemption count in that hour. */
  count: number;
}

/**
 * Find the UTC hour of day with the highest number of REDEEMED purchases
 * for this vendor. Used to build the daily insight string.
 */
export async function getRedemptionPeakHour(
  db: DrizzleClient,
  vendorId: string,
): Promise<RedemptionPeakHour> {
  const result = await db.execute(
    sql`
      SELECT EXTRACT(HOUR FROM v.redeemed_at AT TIME ZONE 'UTC')::int AS hour,
             COUNT(*) AS n
      FROM order_line ol
      INNER JOIN voucher v ON v.line_id = ol.id::text
      WHERE ol.vendor_id = ${vendorId}
        AND v.state = 'REDEEMED'
        AND v.redeemed_at IS NOT NULL
      GROUP BY EXTRACT(HOUR FROM v.redeemed_at AT TIME ZONE 'UTC')
      ORDER BY n DESC
      LIMIT 1
    `,
  );
  const rows = executeRows<{ hour: number | string; n: number | string }>(result) ?? [];
  if (!rows[0]) return { peakHour: null, count: 0 };
  return { peakHour: Number(rows[0].hour), count: Number(rows[0].n) };
}