// src/lib/admin/billing-queries.ts — God-Mode READ loaders for the Financial
// Supervision surface (Slice 4). Gated on `billing:read` (assertSysPermission):
// Admin SYS always, Staff SYS if granted — the 2FA wall lives inside the gate
// itself. These bridge the local DB ↔ Stripe mapping so an operator sees each
// tenant's customer id, plan/status and an MRR estimate WITHOUT the Stripe
// dashboard.
import "server-only";
import { asc, eq } from "drizzle-orm";
import { assertSysPermission } from "@/lib/auth/sys-permissions";
import { getPlan, type PlanName } from "@/lib/billing/plans";
import {
  type BillingStatus,
  computeBillingStatus,
  pickRelevantSubscription,
} from "@/lib/billing/status";
import { isStripeConfigured } from "@/lib/billing/stripe-client";
import { organization, subscription } from "@/lib/db/auth-schema";
import { db } from "@/lib/db/client";

const PAGE_SIZE = 20;

/** Coarse subscription buckets used for the KPI counts + the status filter. */
export type BillingStatusBucket =
  | "active"
  | "trialing"
  | "past_due"
  | "canceled"
  | "none";

/** Map a raw Stripe/local subscription status → a coarse bucket. */
function statusBucket(status: string | null): BillingStatusBucket {
  if (!status) return "none";
  if (status === "active") return "active";
  if (status === "trialing") return "trialing";
  if (status === "past_due") return "past_due";
  if (
    status === "canceled" ||
    status === "incomplete_expired" ||
    status === "unpaid"
  ) {
    return "canceled";
  }
  return "none";
}

/** Plan list price as a monthly figure in cents (annual amortised to /12). */
function planMonthlyCents(
  plan: string | null,
  interval: string | null,
): number {
  if (!plan) return 0;
  const def = (["solo", "starter", "pro", "agency"] as PlanName[]).includes(
    plan as PlanName,
  )
    ? getPlan(plan as PlanName)
    : null;
  if (!def) return 0;
  return interval === "year" ? Math.round(def.annualUsd / 12) : def.monthlyUsd;
}

export interface BillingOverviewRow {
  orgId: string;
  orgName: string;
  stripeCustomerId: string | null;
  plan: string | null;
  status: BillingStatusBucket;
  periodEnd: Date | null;
  seats: number | null;
  trialEndsAt: Date | null;
  stripeSubscriptionId: string | null;
}

export interface BillingKpis {
  counts: Record<BillingStatusBucket, number> & { total: number };
  /** Approximate MRR in cents from active subs × plan list price. ESTIMATE
   *  ONLY — not real billed revenue (no invoice/proration data locally). */
  mrrEstimateCents: number;
  mrrIsEstimate: true;
}

export interface ListBillingOverviewResult {
  rows: BillingOverviewRow[];
  kpis: BillingKpis;
  /** Total rows AFTER the status/search filter (for pagination). */
  total: number;
  limit: number;
  offset: number;
  status: BillingStatusBucket | "all";
  search: string;
  stripeConfigured: boolean;
}

export interface ListBillingOverviewInput {
  status?: BillingStatusBucket | "all";
  search?: string;
  offset?: number;
}

/**
 * Per-tenant billing overview + aggregate KPIs (super-admin only). Loads every
 * org and its relevant subscription, builds one row per org, computes the KPIs
 * across ALL tenants (so the cards are global, not page-local), then filters by
 * status/search and paginates the rows. Acceptable for a light admin tool.
 */
export async function listBillingOverviewAction(
  input: ListBillingOverviewInput = {},
): Promise<ListBillingOverviewResult> {
  await assertSysPermission("billing:read");
  const search = (input.search ?? "").trim().toLowerCase();
  const offset = Math.max(0, input.offset ?? 0);
  const statusFilter = input.status ?? "all";

  const [orgs, subs] = await Promise.all([
    db
      .select({
        id: organization.id,
        name: organization.name,
        stripeCustomerId: organization.stripeCustomerId,
        trialEndsAt: organization.trialEndsAt,
      })
      .from(organization)
      .orderBy(asc(organization.name)),
    db
      .select({
        referenceId: subscription.referenceId,
        plan: subscription.plan,
        status: subscription.status,
        periodStart: subscription.periodStart,
        periodEnd: subscription.periodEnd,
        seats: subscription.seats,
        billingInterval: subscription.billingInterval,
        stripeCustomerId: subscription.stripeCustomerId,
        stripeSubscriptionId: subscription.stripeSubscriptionId,
      })
      .from(subscription),
  ]);

  // Group subs by org, then pick the access-granting / most-recent one.
  const byOrg = new Map<string, (typeof subs)[number][]>();
  for (const s of subs) {
    const list = byOrg.get(s.referenceId) ?? [];
    list.push(s);
    byOrg.set(s.referenceId, list);
  }

  const counts: Record<BillingStatusBucket, number> = {
    active: 0,
    trialing: 0,
    past_due: 0,
    canceled: 0,
    none: 0,
  };
  let mrrEstimateCents = 0;

  const allRows: BillingOverviewRow[] = orgs.map((org) => {
    const sub = pickRelevantSubscription(byOrg.get(org.id) ?? []);
    const bucket = statusBucket(sub?.status ?? null);
    counts[bucket] += 1;
    if (bucket === "active") {
      mrrEstimateCents += planMonthlyCents(
        sub?.plan ?? null,
        sub?.billingInterval ?? null,
      );
    }
    return {
      orgId: org.id,
      orgName: org.name,
      stripeCustomerId: org.stripeCustomerId,
      plan: sub?.plan ?? null,
      status: bucket,
      periodEnd: sub?.periodEnd ?? null,
      seats: sub?.seats ?? null,
      trialEndsAt: org.trialEndsAt,
      stripeSubscriptionId: sub?.stripeSubscriptionId ?? null,
    };
  });

  // Filter (status bucket + name search), then paginate.
  const filtered = allRows.filter((r) => {
    if (statusFilter !== "all" && r.status !== statusFilter) return false;
    if (search && !r.orgName.toLowerCase().includes(search)) return false;
    return true;
  });
  const rows = filtered.slice(offset, offset + PAGE_SIZE);

  return {
    rows,
    kpis: {
      counts: { ...counts, total: orgs.length },
      mrrEstimateCents,
      mrrIsEstimate: true,
    },
    total: filtered.length,
    limit: PAGE_SIZE,
    offset,
    status: statusFilter,
    search: input.search?.trim() ?? "",
    stripeConfigured: isStripeConfigured(),
  };
}

export interface OrgBillingSubscription {
  id: string;
  plan: string;
  status: string | null;
  periodStart: Date | null;
  periodEnd: Date | null;
  trialStart: Date | null;
  trialEnd: Date | null;
  cancelAtPeriodEnd: boolean | null;
  cancelAt: Date | null;
  canceledAt: Date | null;
  endedAt: Date | null;
  seats: number | null;
  billingInterval: string | null;
  stripeSubscriptionId: string | null;
  stripeCustomerId: string | null;
}

export interface OrgBillingDetail {
  orgId: string;
  orgName: string;
  stripeCustomerId: string | null;
  trialEndsAt: Date | null;
  /** Resolved app-level billing status (trial + sub). */
  status: BillingStatus;
  /** The local subscription row (all relevant fields) or null. */
  subscription: OrgBillingSubscription | null;
  /** True when the relevant subscription grants access right now. */
  hasActiveSubscription: boolean;
  stripeConfigured: boolean;
}

/**
 * Read-only billing detail for one org (super-admin only): the org↔customer
 * mapping, the local subscription row, the resolved billing status and the
 * Stripe-configured flag. No last-synced timestamp is tracked, so the local
 * row IS the sync state. Null when the org doesn't exist.
 */
export async function getOrgBillingDetailAction(
  orgId: string,
): Promise<OrgBillingDetail | null> {
  await assertSysPermission("billing:read");

  const [org] = await db
    .select({
      id: organization.id,
      name: organization.name,
      stripeCustomerId: organization.stripeCustomerId,
      trialEndsAt: organization.trialEndsAt,
    })
    .from(organization)
    .where(eq(organization.id, orgId))
    .limit(1);
  if (!org) return null;

  const subs = await db
    .select({
      id: subscription.id,
      plan: subscription.plan,
      status: subscription.status,
      periodStart: subscription.periodStart,
      periodEnd: subscription.periodEnd,
      trialStart: subscription.trialStart,
      trialEnd: subscription.trialEnd,
      cancelAtPeriodEnd: subscription.cancelAtPeriodEnd,
      cancelAt: subscription.cancelAt,
      canceledAt: subscription.canceledAt,
      endedAt: subscription.endedAt,
      seats: subscription.seats,
      billingInterval: subscription.billingInterval,
      stripeSubscriptionId: subscription.stripeSubscriptionId,
      stripeCustomerId: subscription.stripeCustomerId,
    })
    .from(subscription)
    .where(eq(subscription.referenceId, orgId));

  const sub = pickRelevantSubscription(subs);
  const status = computeBillingStatus(
    {
      trialEndsAt: org.trialEndsAt ?? null,
      subscriptionStatus: sub?.status ?? null,
    },
    new Date(),
  );
  // "Active" here = a Stripe-linked sub whose status grants access (the coupon
  // target). Trial-only orgs have no sub to attach a coupon to.
  const hasActiveSubscription = Boolean(
    sub?.stripeSubscriptionId &&
      (sub.status === "active" ||
        sub.status === "trialing" ||
        sub.status === "past_due"),
  );

  return {
    orgId: org.id,
    orgName: org.name,
    stripeCustomerId: org.stripeCustomerId,
    trialEndsAt: org.trialEndsAt,
    status,
    subscription: sub ?? null,
    hasActiveSubscription,
    stripeConfigured: isStripeConfigured(),
  };
}