/**
 * Billing query helpers — billing-module (wave-8 leaf 1).
 *
 * Provides tenant self-service billing read/write helpers.
 * All helpers are tenant-filtered; no raw Drizzle in route files.
 *
 * Storage strategy for payment methods and billing history:
 * - Payment methods: stored in tenants.settings JSONB under key `billing`
 * - Billing history: pulled from zync_subscriptions + invoices
 * - Billing email: stored in tenants.settings JSONB under key `billing.email`
 *
 * No schema migration needed: uses existing JSONB `settings` column on `tenants`.
 */
import { eq } from 'drizzle-orm'
import type { Db } from '../client'
import { tenants } from '../schema/tenants'
import { zyncSubscriptions } from '../schema/zync-subscriptions'
import { invoices } from '../schema/invoices'

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

export interface PaymentMethod {
  id: string
  type: 'card' | 'bank_transfer' | 'other'
  last4: string | null
  brand: string | null
  expMonth: number | null
  expYear: number | null
  isDefault: boolean
  label: string | null
}

export interface BillingInfo {
  tier: string
  status: string
  period: string | null
  currentPeriodStart: string | null
  currentPeriodEnd: string | null
  trialEndsAt: string | null
  adapterCustomerId: string | null
  billingEmail: string | null
  usage: {
    invoiceCount: number
  }
}

export interface BillingHistoryItem {
  id: string
  number: string | null
  amount: string
  currency: string
  status: string
  issuedAt: string | null
  dueDate: string | null
}

export interface BillingHistoryPage {
  items: BillingHistoryItem[]
  total: number
}

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

/**
 * Get current plan details + usage stats for a tenant.
 * Joins zync_subscriptions with invoices count for usage metering.
 */
export async function getTenantBilling(db: Db, tenantId: string): Promise<BillingInfo | null> {
  const [tenant] = await db
    .select({
      settings: tenants.settings,
    })
    .from(tenants)
    .where(eq(tenants.id, tenantId))
    .limit(1)

  if (!tenant) return null

  const [sub] = await db
    .select()
    .from(zyncSubscriptions)
    .where(eq(zyncSubscriptions.tenantId, tenantId))
    .limit(1)

  // Count invoices for usage metering
  const allInvoices = await db
    .select({ id: invoices.id })
    .from(invoices)
    .where(eq(invoices.tenantId, tenantId))

  const settings = (tenant.settings as Record<string, unknown>) ?? {}
  const billing = (settings['billing'] as Record<string, unknown>) ?? {}
  const billingEmail = typeof billing['email'] === 'string' ? billing['email'] : null

  if (!sub) {
    return {
      tier: 'freelancer',
      status: 'active',
      period: null,
      currentPeriodStart: null,
      currentPeriodEnd: null,
      trialEndsAt: null,
      adapterCustomerId: null,
      billingEmail,
      usage: { invoiceCount: allInvoices.length },
    }
  }

  return {
    tier: sub.tier,
    status: sub.status,
    period: sub.period ?? null,
    currentPeriodStart: sub.currentPeriodStart?.toISOString() ?? null,
    currentPeriodEnd: sub.currentPeriodEnd?.toISOString() ?? null,
    trialEndsAt: sub.trialEndsAt?.toISOString() ?? null,
    adapterCustomerId: sub.adapterCustomerId ?? null,
    billingEmail,
    usage: { invoiceCount: allInvoices.length },
  }
}

/**
 * Get saved payment methods for a tenant.
 * Payment methods are stored in tenants.settings JSONB under key `billing.paymentMethods`.
 * In production these would be fetched from the payment adapter (Stripe, etc.);
 * here we return what's stored in the settings blob (adapter-agnostic shape).
 */
export async function getPaymentMethods(db: Db, tenantId: string): Promise<PaymentMethod[]> {
  const [tenant] = await db
    .select({ settings: tenants.settings })
    .from(tenants)
    .where(eq(tenants.id, tenantId))
    .limit(1)

  if (!tenant) return []

  const settings = (tenant.settings as Record<string, unknown>) ?? {}
  const billing = (settings['billing'] as Record<string, unknown>) ?? {}
  const methods = billing['paymentMethods']

  if (!Array.isArray(methods)) return []
  return methods as PaymentMethod[]
}

/**
 * Get billing history (past invoices) for a tenant.
 * Returns up to `limit` most recent invoices.
 */
export async function getBillingHistory(
  db: Db,
  tenantId: string,
  limit = 20,
): Promise<BillingHistoryPage> {
  const rows = await db
    .select({
      id: invoices.id,
      invoiceNumber: invoices.invoiceNumber,
      proformaNumber: invoices.proformaNumber,
      total: invoices.total,
      currency: invoices.currency,
      status: invoices.status,
      taxIssuedAt: invoices.taxIssuedAt,
      dueDate: invoices.dueDate,
    })
    .from(invoices)
    .where(eq(invoices.tenantId, tenantId))
    .orderBy(invoices.createdAt)
    .limit(limit)

  // count total
  const all = await db
    .select({ id: invoices.id })
    .from(invoices)
    .where(eq(invoices.tenantId, tenantId))

  const items: BillingHistoryItem[] = rows.map((r) => ({
    id: r.id,
    number: r.invoiceNumber ?? r.proformaNumber ?? null,
    amount: r.total ?? '0',
    currency: r.currency ?? 'ILS',
    status: r.status,
    issuedAt: r.taxIssuedAt?.toISOString() ?? null,
    dueDate: r.dueDate ?? null,
  }))

  return { items, total: all.length }
}

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

/**
 * Update the billing contact email stored in tenants.settings JSONB.
 * Merges into the existing `billing` sub-key to avoid overwriting other billing data.
 */
export async function updateBillingEmail(
  db: Db,
  tenantId: string,
  email: string,
): Promise<void> {
  // Read current settings first to merge
  const [tenant] = await db
    .select({ settings: tenants.settings })
    .from(tenants)
    .where(eq(tenants.id, tenantId))
    .limit(1)

  if (!tenant) throw new Error('Tenant not found')

  const current = (tenant.settings as Record<string, unknown>) ?? {}
  const currentBilling = (current['billing'] as Record<string, unknown>) ?? {}

  const next = {
    ...current,
    billing: {
      ...currentBilling,
      email,
    },
  }

  await db
    .update(tenants)
    .set({ settings: next })
    .where(eq(tenants.id, tenantId))
}