/**
 * Admin analytics time-series query.
 *
 * getAnalyticsTimeSeries: returns per-sub-period arrays for 8 metrics.
 * Granularity is derived from the requested bucket:
 *   d → hourly   w/m → daily   y/all → monthly
 *
 * Uses CTE + generate_series so every period slot is represented (gaps → 0).
 *
 * Cumulative totals (totalCustomers, totalVendors) are computed from a
 * baseline count (rows before range start) + running sum of new-per-period.
 */

import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import { TZ } from '@/lib/datetime';
import { bucketToRange } from './queries.js';
import type { BucketMode, BucketSize } from './queries.js';

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

export interface TimeSeriesPoint {
  period: string; // ISO-like: 'YYYY-MM-DDTHH:MI' or 'YYYY-MM-DDT00:MI'
  value: number;
}

export interface AnalyticsTimeSeriesResult {
  revenue: TimeSeriesPoint[];
  activeDeals: TimeSeriesPoint[]; // count of active deals at each period point
  purchasesCompleted: TimeSeriesPoint[];
  totalCustomers: TimeSeriesPoint[]; // cumulative running total
  totalVendors: TimeSeriesPoint[]; // cumulative running total
  newCustomers: TimeSeriesPoint[];
  newVendors: TimeSeriesPoint[];
  llmCostUsd: TimeSeriesPoint[];
}

// ─── Helpers ──────────────────────────────────────────────────────────────────

type Granularity = 'hour' | 'day' | 'month';

function bucketGranularity(bucket: BucketSize): Granularity {
  if (bucket === 'd') return 'hour';
  if (bucket === 'w' || bucket === 'm') return 'day';
  return 'month';
}

function granularityInterval(g: Granularity): string {
  if (g === 'hour') return '1 hour';
  if (g === 'day') return '1 day';
  return '1 month';
}

type RawRow = { period: string; value: string };

function toPoints(rows: Record<string, unknown>[]): TimeSeriesPoint[] {
  return (rows as RawRow[]).map((r) => ({
    period: r.period,
    value: parseFloat(r.value ?? '0') || 0,
  }));
}

function toCumulativePoints(rows: Record<string, unknown>[], baseline: number): TimeSeriesPoint[] {
  let running = baseline;
  return (rows as RawRow[]).map((r) => {
    running += parseFloat(r.value ?? '0') || 0;
    return { period: r.period, value: running };
  });
}

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

export async function getAnalyticsTimeSeries(
  db: DrizzleClient,
  opts: { mode: BucketMode; bucket: BucketSize },
): Promise<AnalyticsTimeSeriesResult> {
  const { start, end } = bucketToRange(opts.mode, opts.bucket);
  const gran = bucketGranularity(opts.bucket);
  const interval = granularityInterval(gran);
  const startIso = start.toISOString();
  const endIso = end.toISOString();

  const [
    revenueRes,
    dealsRes,
    purchasesRes,
    newCustomersRes,
    newVendorsRes,
    llmRes,
    baselineCustomersRes,
    baselineVendorsRes,
  ] = await Promise.all([
    db.execute(sql`
      WITH periods AS (
        SELECT generate_series(
          date_trunc(${gran}, ${startIso}::timestamptz AT TIME ZONE ${TZ}),
          date_trunc(${gran}, ${endIso}::timestamptz AT TIME ZONE ${TZ}),
          ${interval}::interval
        ) AS period
      ),
      data AS (
        SELECT
          date_trunc(${gran}, created_at AT TIME ZONE ${TZ}) AS period,
          SUM(total)::numeric AS value
        FROM "order"
        WHERE status = 'paid'
          AND created_at >= ${startIso}::timestamptz
          AND created_at <= ${endIso}::timestamptz
        GROUP BY 1
      )
      SELECT
        to_char(p.period, 'YYYY-MM-DD"T"HH24:MI') AS period,
        COALESCE(d.value, 0)::text AS value
      FROM periods p
      LEFT JOIN data d ON d.period = p.period
      ORDER BY p.period
    `),

    // Active deals at each period point: approved before period end, not yet
    // sold-out or window-ended at period start, and in a live state.
    db.execute(sql`
      WITH periods AS (
        SELECT generate_series(
          date_trunc(${gran}, ${startIso}::timestamptz AT TIME ZONE ${TZ}),
          date_trunc(${gran}, ${endIso}::timestamptz AT TIME ZONE ${TZ}),
          ${interval}::interval
        ) AS period
      )
      SELECT
        to_char(p.period, 'YYYY-MM-DD"T"HH24:MI') AS period,
        (
          SELECT COUNT(*)
          FROM deals
          WHERE deal_state IN ('ACTIVE', 'PAUSED', 'SOLD_OUT')
            AND COALESCE(approved_at, created_at) <= p.period + ${interval}::interval
            AND (window_end IS NULL OR window_end >= p.period)
            AND (sold_out_at IS NULL OR sold_out_at >= p.period)
        )::text AS value
      FROM periods p
      ORDER BY p.period
    `),

    db.execute(sql`
      WITH periods AS (
        SELECT generate_series(
          date_trunc(${gran}, ${startIso}::timestamptz AT TIME ZONE ${TZ}),
          date_trunc(${gran}, ${endIso}::timestamptz AT TIME ZONE ${TZ}),
          ${interval}::interval
        ) AS period
      ),
      data AS (
        SELECT
          date_trunc(${gran}, created_at AT TIME ZONE ${TZ}) AS period,
          COUNT(*)::numeric AS value
        FROM "order"
        WHERE status = 'paid'
          AND created_at >= ${startIso}::timestamptz
          AND created_at <= ${endIso}::timestamptz
        GROUP BY 1
      )
      SELECT
        to_char(p.period, 'YYYY-MM-DD"T"HH24:MI') AS period,
        COALESCE(d.value, 0)::text AS value
      FROM periods p
      LEFT JOIN data d ON d.period = p.period
      ORDER BY p.period
    `),

    db.execute(sql`
      WITH periods AS (
        SELECT generate_series(
          date_trunc(${gran}, ${startIso}::timestamptz AT TIME ZONE ${TZ}),
          date_trunc(${gran}, ${endIso}::timestamptz AT TIME ZONE ${TZ}),
          ${interval}::interval
        ) AS period
      ),
      data AS (
        SELECT
          date_trunc(${gran}, created_at AT TIME ZONE ${TZ}) AS period,
          COUNT(*)::numeric AS value
        FROM users
        WHERE created_at >= ${startIso}::timestamptz
          AND created_at <= ${endIso}::timestamptz
        GROUP BY 1
      )
      SELECT
        to_char(p.period, 'YYYY-MM-DD"T"HH24:MI') AS period,
        COALESCE(d.value, 0)::text AS value
      FROM periods p
      LEFT JOIN data d ON d.period = p.period
      ORDER BY p.period
    `),

    db.execute(sql`
      WITH periods AS (
        SELECT generate_series(
          date_trunc(${gran}, ${startIso}::timestamptz AT TIME ZONE ${TZ}),
          date_trunc(${gran}, ${endIso}::timestamptz AT TIME ZONE ${TZ}),
          ${interval}::interval
        ) AS period
      ),
      data AS (
        SELECT
          date_trunc(${gran}, created_at AT TIME ZONE ${TZ}) AS period,
          COUNT(*)::numeric AS value
        FROM vendors
        WHERE created_at >= ${startIso}::timestamptz
          AND created_at <= ${endIso}::timestamptz
        GROUP BY 1
      )
      SELECT
        to_char(p.period, 'YYYY-MM-DD"T"HH24:MI') AS period,
        COALESCE(d.value, 0)::text AS value
      FROM periods p
      LEFT JOIN data d ON d.period = p.period
      ORDER BY p.period
    `),

    db.execute(sql`
      WITH periods AS (
        SELECT generate_series(
          date_trunc(${gran}, ${startIso}::timestamptz AT TIME ZONE ${TZ}),
          date_trunc(${gran}, ${endIso}::timestamptz AT TIME ZONE ${TZ}),
          ${interval}::interval
        ) AS period
      ),
      data AS (
        SELECT
          date_trunc(${gran}, created_at AT TIME ZONE ${TZ}) AS period,
          SUM(cost_usd)::numeric AS value
        FROM llm_jobs
        WHERE cost_usd IS NOT NULL
          AND created_at >= ${startIso}::timestamptz
          AND created_at <= ${endIso}::timestamptz
        GROUP BY 1
      )
      SELECT
        to_char(p.period, 'YYYY-MM-DD"T"HH24:MI') AS period,
        COALESCE(d.value, 0)::text AS value
      FROM periods p
      LEFT JOIN data d ON d.period = p.period
      ORDER BY p.period
    `),

    db.execute(sql`
      SELECT COUNT(*)::text AS value FROM users
      WHERE created_at < ${startIso}::timestamptz
    `),

    db.execute(sql`
      SELECT COUNT(*)::text AS value FROM vendors
      WHERE created_at < ${startIso}::timestamptz
    `),
  ]);

  const baselineCustomers = parseInt(
    String(
      ((baselineCustomersRes as { rows: RawRow[] }).rows[0] as { value: string } | undefined)
        ?.value ?? '0',
    ),
    10,
  );
  const baselineVendors = parseInt(
    String(
      ((baselineVendorsRes as { rows: RawRow[] }).rows[0] as { value: string } | undefined)
        ?.value ?? '0',
    ),
    10,
  );

  return {
    revenue: toPoints((revenueRes as { rows: RawRow[] }).rows),
    activeDeals: toPoints((dealsRes as { rows: RawRow[] }).rows),
    purchasesCompleted: toPoints((purchasesRes as { rows: RawRow[] }).rows),
    totalCustomers: toCumulativePoints(
      (newCustomersRes as { rows: RawRow[] }).rows,
      baselineCustomers,
    ),
    totalVendors: toCumulativePoints((newVendorsRes as { rows: RawRow[] }).rows, baselineVendors),
    newCustomers: toPoints((newCustomersRes as { rows: RawRow[] }).rows),
    newVendors: toPoints((newVendorsRes as { rows: RawRow[] }).rows),
    llmCostUsd: toPoints((llmRes as { rows: RawRow[] }).rows),
  };
}