/**
 * Reports aggregation service — admin-reports-analytics.
 *
 * Three cross-tenant aggregation functions for the /admin/reports endpoint:
 *   - getRevenueReport  : MRR/ARR, trend, per-tier breakdown
 *   - getSignupsReport  : new signups, churn, trial conversion
 *   - getTenantsReport  : paginated tenant table with last-login
 *
 * All queries use the un-scoped Db handle (cross-tenant). Never call tenantQuery here.
 */
import { sql, count, and, gte, lte, lt, isNull, or, eq, desc, inArray } from '@zync/db'
import type { Db } from '@zync/db/queries'
import {
  TIER_MONTHLY_ILS,
  type DateRange,
  type RevenueReport,
  type SignupsReport,
  type TenantsReport,
  type MrrPoint,
  type PlanBreakdownRow,
  type SignupBar,
  type ChurnByTierRow,
  type TenantReportRow,
} from './types'

// ── Internal helpers ──────────────────────────────────────────────────────────

/** Compute monthly-equivalent ILS for a subscription row's tier + period. */
function monthlyRevenue(tier: string, _period: string | null): number {
  return TIER_MONTHLY_ILS[tier] ?? 0
}

/** Returns the first day of the month containing `date` as a Date. */
function monthStart(date: Date): Date {
  return new Date(Date.UTC(date.getUTCFullYear(), date.getUTCMonth(), 1))
}

/** Returns the last moment of the month containing `date`. */
function monthEnd(date: Date): Date {
  const d = new Date(Date.UTC(date.getUTCFullYear(), date.getUTCMonth() + 1, 0, 23, 59, 59, 999))
  return d
}

/** Format a Date as 'YYYY-MM'. */
function fmtMonth(date: Date): string {
  const y = date.getUTCFullYear()
  const m = String(date.getUTCMonth() + 1).padStart(2, '0')
  return `${y}-${m}`
}

/** Parse a YYYY-MM-DD string to a Date (UTC midnight). */
function parseDate(s: string): Date {
  return new Date(`${s}T00:00:00Z`)
}

/** Clamp pageSize to [1, 100]. */
function clampPageSize(ps: number): number {
  return Math.min(Math.max(1, ps), 100)
}

// ── getRevenueReport ──────────────────────────────────────────────────────────

export async function getRevenueReport(db: Db, range: DateRange): Promise<RevenueReport> {
  // We import the schema tables via raw SQL references to avoid barrel coupling issues
  // (barrel wiring is controller's job; we use drizzle's sql template here for cross-join).
  // All active subscription rows:
  const activeRows = await db.execute<{
    tier: string
    period: string | null
    tenant_id: string
  }>(sql`
    SELECT tier, period, tenant_id
    FROM zync_subscriptions
    WHERE status = 'active'
  `)

  const activeSubs = activeRows ?? []

  let totalMrr = 0
  const tierMap: Record<string, { tenants: number; mrr: number }> = {}

  for (const row of activeSubs) {
    const mrr = monthlyRevenue(row.tier, row.period)
    totalMrr += mrr
    if (!tierMap[row.tier]) tierMap[row.tier] = { tenants: 0, mrr: 0 }
    tierMap[row.tier]!.tenants++
    tierMap[row.tier]!.mrr += mrr
  }

  const breakdown: PlanBreakdownRow[] = Object.entries(tierMap)
    .map(([tier, { tenants, mrr: mrrIls }]) => ({
      tier,
      tenants,
      mrrIls,
      pctOfMrr: totalMrr > 0 ? Math.round((mrrIls / totalMrr) * 1000) / 10 : 0,
    }))
    .sort((a, b) => b.mrrIls - a.mrrIls)

  // MRR trend: 12 months ending at range.to
  const toDate = parseDate(range.to)
  const mrrTrend: MrrPoint[] = []

  for (let i = 11; i >= 0; i--) {
    const pivot = new Date(Date.UTC(toDate.getUTCFullYear(), toDate.getUTCMonth() - i, 1))
    const mEnd = monthEnd(pivot)

    const trendRows = await db.execute<{ tier: string; period: string | null; cnt: string }>(sql`
      SELECT tier, period, count(*) AS cnt
      FROM zync_subscriptions
      WHERE current_period_start <= ${mEnd.toISOString()}
        AND (canceled_at IS NULL OR canceled_at > ${mEnd.toISOString()})
        AND status IN ('active', 'trialing')
      GROUP BY tier, period
    `)

    let monthMrr = 0
    for (const r of (trendRows ?? [])) {
      monthMrr += monthlyRevenue(r.tier, r.period) * Number(r.cnt)
    }
    mrrTrend.push({ month: fmtMonth(pivot), mrrIls: monthMrr })
  }

  return {
    mrrIls: totalMrr,
    arrIls: totalMrr * 12,
    activeTenants: activeSubs.length,
    mrrTrend,
    breakdown,
  }
}

// ── getSignupsReport ──────────────────────────────────────────────────────────

export async function getSignupsReport(db: Db, range: DateRange): Promise<SignupsReport> {
  const fromDate = parseDate(range.from)
  const toDate = parseDate(range.to)
  // inclusive end
  const toEnd = new Date(toDate.getTime() + 86400000 - 1)

  // New signups in range (by subscription created_at)
  const newRows = await db.execute<{ cnt: string }>(sql`
    SELECT count(*) AS cnt
    FROM zync_subscriptions
    WHERE created_at >= ${fromDate.toISOString()}
      AND created_at <= ${toEnd.toISOString()}
  `)
  const newSignups = Number((newRows?.[0] as { cnt?: string } | undefined)?.cnt ?? 0)

  // Churned = status IN ('canceled','past_due') AND canceled_at falls within range
  const churnRows = await db.execute<{ cnt: string }>(sql`
    SELECT count(*) AS cnt
    FROM zync_subscriptions
    WHERE status IN ('canceled', 'past_due')
      AND canceled_at >= ${fromDate.toISOString()}
      AND canceled_at <= ${toEnd.toISOString()}
  `)
  const churned = Number((churnRows?.[0] as { cnt?: string } | undefined)?.cnt ?? 0)

  // Signups by month (last 12 months) grouped by tier
  const signupMonthRows = await db.execute<{
    month: string
    tier: string
    cnt: string
  }>(sql`
    SELECT
      to_char(created_at, 'YYYY-MM') AS month,
      tier,
      count(*) AS cnt
    FROM zync_subscriptions
    WHERE created_at >= now() - INTERVAL '12 months'
    GROUP BY month, tier
    ORDER BY month ASC
  `)

  const monthMap: Record<string, Record<string, number>> = {}
  for (const r of (signupMonthRows ?? [])) {
    if (!monthMap[r.month]) monthMap[r.month] = {}
    monthMap[r.month]![r.tier] = (monthMap[r.month]![r.tier] ?? 0) + Number(r.cnt)
  }
  const signupsByMonth: SignupBar[] = Object.entries(monthMap)
    .sort(([a], [b]) => a.localeCompare(b))
    .map(([month, byTier]) => ({ month, byTier }))

  // Churn by tier
  const churnByTierRows = await db.execute<{ tier: string; cnt: string }>(sql`
    SELECT tier, count(*) AS cnt
    FROM zync_subscriptions
    WHERE status IN ('canceled', 'past_due')
      AND canceled_at >= ${fromDate.toISOString()}
      AND canceled_at <= ${toEnd.toISOString()}
    GROUP BY tier
  `)
  const churnByTier: ChurnByTierRow[] = (churnByTierRows ?? []).map((r) => ({
    tier: r.tier,
    churned: Number(r.cnt),
  }))

  // Trial conversion: tenants that entered trialing and reached 'active' within 30 days — last 90d
  const ninetyDaysAgo = new Date(Date.now() - 90 * 86400000)
  const convRows = await db.execute<{ total_trials: string; converted: string }>(sql`
    SELECT
      count(*) AS total_trials,
      sum(CASE
        WHEN status = 'active'
          AND current_period_start IS NOT NULL
          AND trial_ends_at IS NOT NULL
          AND current_period_start <= trial_ends_at + INTERVAL '30 days'
        THEN 1 ELSE 0
      END) AS converted
    FROM zync_subscriptions
    WHERE trial_ends_at IS NOT NULL
      AND trial_ends_at >= ${ninetyDaysAgo.toISOString()}
  `)
  const cr = (convRows?.[0] as { total_trials?: string; converted?: string } | undefined)
  const totalTrials = Number(cr?.total_trials ?? 0)
  const converted = Number(cr?.converted ?? 0)
  const trialConversionPct = totalTrials > 0
    ? Math.round((converted / totalTrials) * 1000) / 10
    : 0

  // Avg trial duration: approximate using current_period_start - (trial_ends_at - 14 days)
  const trialDurRows = await db.execute<{ avg_days: string | null }>(sql`
    SELECT avg(
      EXTRACT(EPOCH FROM (current_period_start - (trial_ends_at - INTERVAL '14 days'))) / 86400
    )::text AS avg_days
    FROM zync_subscriptions
    WHERE status = 'active'
      AND trial_ends_at IS NOT NULL
      AND current_period_start IS NOT NULL
      AND current_period_start <= trial_ends_at + INTERVAL '30 days'
      AND trial_ends_at >= ${ninetyDaysAgo.toISOString()}
  `)
  const avgTrialDurationDays = Math.round(
    Number((trialDurRows?.[0] as { avg_days?: string | null } | undefined)?.avg_days ?? 0) * 10,
  ) / 10

  return {
    signupsByMonth,
    newSignups,
    churned,
    net: newSignups - churned,
    trialConversionPct,
    avgTrialDurationDays,
    churnByTier,
  }
}

// ── getTenantsReport ──────────────────────────────────────────────────────────

export async function getTenantsReport(
  db: Db,
  _range: DateRange,
  page: number,
  pageSize: number,
): Promise<TenantsReport> {
  const ps = clampPageSize(pageSize)
  const offset = (Math.max(1, page) - 1) * ps

  // Count total
  const countRow = await db.execute<{ cnt: string }>(sql`SELECT count(*) AS cnt FROM tenants`)
  const total = Number((countRow?.[0] as { cnt?: string } | undefined)?.cnt ?? 0)

  // Tenant rows with subscription info and last-login
  const rows = await db.execute<{
    slug: string
    name: string
    status: string | null
    tier: string | null
    created_at: string
    last_login_at: string | null
  }>(sql`
    SELECT
      t.slug,
      t.name,
      s.status,
      COALESCE(s.tier, t.tier) AS tier,
      t.created_at,
      (
        SELECT MAX(u.last_login_at)
        FROM users u
        INNER JOIN tenant_memberships tm ON tm.user_id = u.id
        WHERE tm.tenant_id = t.id
      ) AS last_login_at
    FROM tenants t
    LEFT JOIN zync_subscriptions s ON s.tenant_id = t.id
    ORDER BY t.created_at DESC
    LIMIT ${ps} OFFSET ${offset}
  `)

  const result: TenantReportRow[] = (rows ?? []).map((r) => {
    const tier = r.tier ?? 'freelancer'
    const status = r.status ?? 'active'
    return {
      slug: r.slug,
      name: r.name,
      status,
      tier,
      mrrIls: status === 'active' ? (TIER_MONTHLY_ILS[tier] ?? 0) : 0,
      signupDate: r.created_at ? r.created_at.slice(0, 10) : '',
      lastLoginAt: r.last_login_at ?? null,
    }
  })

  return {
    rows: result,
    total,
    page: Math.max(1, page),
    pageSize: ps,
  }
}