/**
 * Billing plans query helpers — billing-plans-management-ui (P083, wave-9 leaf 1).
 *
 * System-wide plan catalogue CRUD. All functions are admin-plane only;
 * no tenant-scoped filtering. No raw Drizzle in route files.
 *
 * Tenant count per plan is derived via zync_subscriptions.tier join.
 * Plans are soft-deleted (archivedAt set); hard deletes are not exposed.
 */
import { eq, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { billingPlans } from '../schema/billing-plans'
import { zyncSubscriptions } from '../schema/zync-subscriptions'

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

export interface BillingPlanListRow {
  id: string
  name: string
  tier: string
  priceMonthly: number
  priceAnnual: number
  features: string[]
  maxUsers: number | null
  maxProjects: number | null
  archivedAt: string | null
  createdAt: string
  updatedAt: string
  tenantCount: number
}

export interface CreateBillingPlanInput {
  name: string
  tier: string
  priceMonthly: number
  priceAnnual: number
  features: string[]
  maxUsers: number | null
  maxProjects: number | null
}

export interface UpdateBillingPlanInput {
  name?: string
  tier?: string
  priceMonthly?: number
  priceAnnual?: number
  features?: string[]
  maxUsers?: number | null
  maxProjects?: number | null
}

// ── Read helpers ──────────────────────────────────────────────────────────────

/**
 * List all billing plans (including archived), with tenant counts.
 * Tenant count = number of active zync_subscriptions rows matching the plan tier.
 */
export async function listBillingPlans(db: Db): Promise<BillingPlanListRow[]> {
  // Aggregate tenant counts per tier from zync_subscriptions
  const tierCounts = await db
    .select({
      tier: zyncSubscriptions.tier,
      count: sql<number>`count(*)::int`,
    })
    .from(zyncSubscriptions)
    .groupBy(zyncSubscriptions.tier)

  const countByTier = new Map<string, number>(
    tierCounts.map((r) => [r.tier, r.count]),
  )

  const rows = await db
    .select()
    .from(billingPlans)
    .orderBy(billingPlans.createdAt)

  return rows.map((r) => ({
    id: r.id,
    name: r.name,
    tier: r.tier,
    priceMonthly: r.priceMonthly,
    priceAnnual: r.priceAnnual,
    features: r.features ?? [],
    maxUsers: r.maxUsers,
    maxProjects: r.maxProjects,
    archivedAt: r.archivedAt?.toISOString() ?? null,
    createdAt: r.createdAt.toISOString(),
    updatedAt: r.updatedAt.toISOString(),
    tenantCount: countByTier.get(r.tier) ?? 0,
  }))
}

/**
 * Get a single billing plan by ID. Returns null if not found.
 */
export async function getBillingPlan(db: Db, planId: string): Promise<BillingPlanListRow | null> {
  const [row] = await db
    .select()
    .from(billingPlans)
    .where(eq(billingPlans.id, planId))
    .limit(1)

  if (!row) return null

  const [tierCount] = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(zyncSubscriptions)
    .where(eq(zyncSubscriptions.tier, row.tier))

  return {
    id: row.id,
    name: row.name,
    tier: row.tier,
    priceMonthly: row.priceMonthly,
    priceAnnual: row.priceAnnual,
    features: row.features ?? [],
    maxUsers: row.maxUsers,
    maxProjects: row.maxProjects,
    archivedAt: row.archivedAt?.toISOString() ?? null,
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
    tenantCount: tierCount?.count ?? 0,
  }
}

// ── Write helpers ─────────────────────────────────────────────────────────────

/**
 * Create a new billing plan. Returns the created row.
 */
export async function createBillingPlan(
  db: Db,
  adminUserId: string,
  data: CreateBillingPlanInput,
): Promise<BillingPlanListRow> {
  const rows = await db
    .insert(billingPlans)
    .values({
      name: data.name,
      tier: data.tier,
      priceMonthly: data.priceMonthly,
      priceAnnual: data.priceAnnual,
      features: data.features,
      maxUsers: data.maxUsers,
      maxProjects: data.maxProjects,
      createdBy: adminUserId,
    })
    .returning()

  const row = rows[0]
  if (!row) throw new Error('Insert returned no row')

  return {
    id: row.id,
    name: row.name,
    tier: row.tier,
    priceMonthly: row.priceMonthly,
    priceAnnual: row.priceAnnual,
    features: row.features ?? [],
    maxUsers: row.maxUsers,
    maxProjects: row.maxProjects,
    archivedAt: row.archivedAt?.toISOString() ?? null,
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
    tenantCount: 0,
  }
}

/**
 * Update an existing billing plan. Partial update — only supplied fields change.
 * Throws BillingPlanNotFoundError if plan does not exist.
 * Throws BillingPlanArchivedError if plan is archived.
 */
export class BillingPlanNotFoundError extends Error {
  constructor(planId: string) {
    super(`Billing plan not found: ${planId}`)
    this.name = 'BillingPlanNotFoundError'
  }
}

export class BillingPlanArchivedError extends Error {
  constructor(planId: string) {
    super(`Billing plan is archived: ${planId}`)
    this.name = 'BillingPlanArchivedError'
  }
}

export async function updateBillingPlan(
  db: Db,
  _adminUserId: string,
  planId: string,
  data: UpdateBillingPlanInput,
): Promise<BillingPlanListRow> {
  const existing = await getBillingPlan(db, planId)
  if (!existing) throw new BillingPlanNotFoundError(planId)
  if (existing.archivedAt) throw new BillingPlanArchivedError(planId)

  const updates: Partial<typeof billingPlans.$inferInsert> = {
    updatedAt: new Date(),
  }
  if (data.name !== undefined) updates.name = data.name
  if (data.tier !== undefined) updates.tier = data.tier
  if (data.priceMonthly !== undefined) updates.priceMonthly = data.priceMonthly
  if (data.priceAnnual !== undefined) updates.priceAnnual = data.priceAnnual
  if (data.features !== undefined) updates.features = data.features
  if ('maxUsers' in data) updates.maxUsers = data.maxUsers ?? undefined
  if ('maxProjects' in data) updates.maxProjects = data.maxProjects ?? undefined

  const updatedRows = await db
    .update(billingPlans)
    .set(updates)
    .where(eq(billingPlans.id, planId))
    .returning()

  const row = updatedRows[0]
  if (!row) throw new BillingPlanNotFoundError(planId)

  const tierCounts = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(zyncSubscriptions)
    .where(eq(zyncSubscriptions.tier, row.tier))
  const tierCount = tierCounts[0]

  return {
    id: row.id,
    name: row.name,
    tier: row.tier,
    priceMonthly: row.priceMonthly,
    priceAnnual: row.priceAnnual,
    features: row.features ?? [],
    maxUsers: row.maxUsers,
    maxProjects: row.maxProjects,
    archivedAt: row.archivedAt?.toISOString() ?? null,
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
    tenantCount: tierCount?.count ?? 0,
  }
}

/**
 * Soft-archive a billing plan. Sets archivedAt = now.
 * Throws BillingPlanNotFoundError if plan does not exist.
 * Throws BillingPlanArchivedError if already archived.
 */
export async function archiveBillingPlan(
  db: Db,
  _adminUserId: string,
  planId: string,
): Promise<void> {
  const existing = await getBillingPlan(db, planId)
  if (!existing) throw new BillingPlanNotFoundError(planId)
  if (existing.archivedAt) throw new BillingPlanArchivedError(planId)

  await db
    .update(billingPlans)
    .set({ archivedAt: new Date(), updatedAt: new Date() })
    .where(eq(billingPlans.id, planId))
}