/**
 * Vendor query helpers — vendors-suppliers (wave-11 leaf-E).
 */
import { and, desc, eq, ilike, isNull, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { vendors } from '../schema/vendors'
import { expenses } from '../schema/expenses'
import { auditLog } from './_audit-forward'

export type { VendorRow } from '../schema/vendors'

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

export interface VendorListItem {
  id: string
  name: string
  taxId: string | null
  withholdingRate: string | null
  withholdingCertExpiry: string | null
  isArchived: boolean
  ytdSpend: string
  certExpiringSoon: boolean
}

export interface VendorWithStats {
  id: string
  tenantId: string
  name: string
  taxId: string | null
  withholdingRate: string | null
  withholdingCertNumber: string | null
  withholdingCertExpiry: string | null
  withholdingCertR2Key: string | null
  defaultCategory: string | null
  defaultVatDeductible: boolean
  paymentTermsDays: number
  email: string | null
  phone: string | null
  address: string | null
  notes: string | null
  isArchived: boolean
  createdAt: Date
  updatedAt: Date
  ytdSpend: string
  expenseCount: number
  linkedExpensesTotal: string
  expenses: VendorLinkedExpense[]
}

export interface VendorLinkedExpense {
  id: string
  expenseDate: string | null
  amount: string | null
  currency: string
  category: string | null
  status: string
  vendorName: string | null
}

export interface CreateVendorInput {
  name: string
  taxId?: string
  withholdingRate?: number | null
  withholdingCertNumber?: string
  withholdingCertExpiry?: string
  defaultCategory?: string
  defaultVatDeductible?: boolean
  paymentTermsDays?: number
  email?: string
  phone?: string
  address?: string
  notes?: string
}

export type UpdateVendorInput = Partial<CreateVendorInput>

// ── listVendors ────────────────────────────────────────────────────────────────

export async function listVendors(args: {
  tenantId: string
  q?: string
  archived?: boolean
  limit?: number
  db: Db
}): Promise<{ items: VendorListItem[]; total: number }> {
  const { tenantId, q, archived = false, limit = 50, db } = args

  const now = new Date()
  const thirtyDaysFromNow = new Date(now.getTime() + 30 * 24 * 60 * 60 * 1000)

  const conditions = [
    eq(vendors.tenantId, tenantId),
    archived ? undefined : eq(vendors.isArchived, false),
    q ? ilike(vendors.name, `%${q}%`) : undefined,
  ].filter(Boolean)

  const rows = await db
    .select()
    .from(vendors)
    .where(and(...(conditions as Parameters<typeof and>)))
    .orderBy(vendors.name)
    .limit(limit)

  const countRow = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(vendors)
    .where(and(...(conditions as Parameters<typeof and>)))

  // Get YTD spend per vendor
  const currentYear = now.getFullYear()
  const ytdRows = await db
    .select({
      vendorId: expenses.vendorId,
      ytd: sql<string>`coalesce(sum(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)::text`,
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        sql`extract(year from ${expenses.expenseDate}) = ${currentYear}`,
        sql`${expenses.vendorId} IS NOT NULL`,
        isNull(expenses.deletedAt),
      ),
    )
    .groupBy(expenses.vendorId)

  const ytdByVendor = new Map(ytdRows.map((r) => [r.vendorId, r.ytd]))

  const items: VendorListItem[] = rows.map((v) => {
    const certExpiry = v.withholdingCertExpiry
      ? new Date(v.withholdingCertExpiry)
      : null
    return {
      id: v.id,
      name: v.name,
      taxId: v.taxId,
      withholdingRate: v.withholdingRate,
      withholdingCertExpiry: v.withholdingCertExpiry,
      isArchived: v.isArchived,
      ytdSpend: ytdByVendor.get(v.id) ?? '0',
      certExpiringSoon: certExpiry !== null && certExpiry <= thirtyDaysFromNow,
    }
  })

  return { items, total: countRow[0]?.count ?? 0 }
}

// ── getVendor ──────────────────────────────────────────────────────────────────

export async function getVendor(
  db: Db,
  tenantId: string,
  vendorId: string,
): Promise<VendorWithStats | null> {
  const rows = await db
    .select()
    .from(vendors)
    .where(and(eq(vendors.tenantId, tenantId), eq(vendors.id, vendorId)))
    .limit(1)

  if (!rows[0]) return null

  const [ytd, summary, linkedExpenses] = await Promise.all([
    getVendorYtdSpend(db, tenantId, vendorId),
    db
      .select({
        expenseCount: sql<number>`count(*)::int`,
        linkedExpensesTotal: sql<string>`coalesce(sum(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)::text`,
      })
      .from(expenses)
      .where(
        and(
          eq(expenses.tenantId, tenantId),
          eq(expenses.vendorId, vendorId),
          isNull(expenses.deletedAt),
        ),
      ),
    db
      .select({
        id: expenses.id,
        expenseDate: expenses.expenseDate,
        amount: sql<string | null>`${expenses.amount}::text`,
        currency: expenses.currency,
        category: expenses.expenseCategory,
        status: expenses.status,
        vendorName: expenses.vendorName,
      })
      .from(expenses)
      .where(
        and(
          eq(expenses.tenantId, tenantId),
          eq(expenses.vendorId, vendorId),
          isNull(expenses.deletedAt),
        ),
      )
      .orderBy(desc(expenses.expenseDate), desc(expenses.createdAt))
      .limit(100),
  ])
  const v = rows[0]
  const summaryRow = summary[0]

  return {
    id: v.id,
    tenantId: v.tenantId,
    name: v.name,
    taxId: v.taxId,
    withholdingRate: v.withholdingRate,
    withholdingCertNumber: v.withholdingCertNumber,
    withholdingCertExpiry: v.withholdingCertExpiry,
    withholdingCertR2Key: v.withholdingCertR2Key,
    defaultCategory: v.defaultCategory,
    defaultVatDeductible: v.defaultVatDeductible,
    paymentTermsDays: v.paymentTermsDays,
    email: v.email,
    phone: v.phone,
    address: v.address,
    notes: v.notes,
    isArchived: v.isArchived,
    createdAt: v.createdAt,
    updatedAt: v.updatedAt,
    ytdSpend: ytd,
    expenseCount: summaryRow?.expenseCount ?? 0,
    linkedExpensesTotal: summaryRow?.linkedExpensesTotal ?? '0',
    expenses: linkedExpenses,
  }
}

// ── getVendorYtdSpend ──────────────────────────────────────────────────────────

export async function getVendorYtdSpend(
  db: Db,
  tenantId: string,
  vendorId: string,
): Promise<string> {
  const currentYear = new Date().getFullYear()
  const rows = await db
    .select({ ytd: sql<string>`coalesce(sum(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)::text` })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        eq(expenses.vendorId, vendorId),
        sql`extract(year from ${expenses.expenseDate}) = ${currentYear}`,
        isNull(expenses.deletedAt),
      ),
    )
  return rows[0]?.ytd ?? '0'
}

// ── createVendor ───────────────────────────────────────────────────────────────

export async function createVendor(
  db: Db,
  tenantId: string,
  actorId: string,
  input: CreateVendorInput,
): Promise<VendorWithStats> {
  const rows = await db
    .insert(vendors)
    .values({
      tenantId,
      name: input.name,
      taxId: input.taxId ?? null,
      withholdingRate: input.withholdingRate !== undefined
        ? String(input.withholdingRate)
        : null,
      withholdingCertNumber: input.withholdingCertNumber ?? null,
      withholdingCertExpiry: input.withholdingCertExpiry ?? null,
      defaultCategory: input.defaultCategory ?? null,
      defaultVatDeductible: input.defaultVatDeductible ?? true,
      paymentTermsDays: input.paymentTermsDays ?? 30,
      email: input.email ?? null,
      phone: input.phone ?? null,
      address: input.address ?? null,
      notes: input.notes ?? null,
    })
    .returning()

  const v = rows[0]!

  await db.insert(auditLog).values({
    tenantId,
    actorId,
    actorType: 'user',
    entityType: 'vendor',
    entityId: v.id,
    action: 'create',
    changes: null,
  })

  return {
    ...v,
    ytdSpend: '0',
    expenseCount: 0,
    linkedExpensesTotal: '0',
    expenses: [],
  }
}

// ── updateVendor ───────────────────────────────────────────────────────────────

export async function updateVendor(
  db: Db,
  tenantId: string,
  actorId: string,
  vendorId: string,
  input: UpdateVendorInput,
): Promise<VendorWithStats | null> {
  const set: Record<string, unknown> = { updatedAt: new Date() }

  if (input.name !== undefined) set['name'] = input.name
  if (input.taxId !== undefined) set['taxId'] = input.taxId
  if (input.withholdingRate !== undefined) set['withholdingRate'] = input.withholdingRate !== null ? String(input.withholdingRate) : null
  if (input.withholdingCertNumber !== undefined) set['withholdingCertNumber'] = input.withholdingCertNumber
  if (input.withholdingCertExpiry !== undefined) set['withholdingCertExpiry'] = input.withholdingCertExpiry
  if (input.defaultCategory !== undefined) set['defaultCategory'] = input.defaultCategory
  if (input.defaultVatDeductible !== undefined) set['defaultVatDeductible'] = input.defaultVatDeductible
  if (input.paymentTermsDays !== undefined) set['paymentTermsDays'] = input.paymentTermsDays
  if (input.email !== undefined) set['email'] = input.email
  if (input.phone !== undefined) set['phone'] = input.phone
  if (input.address !== undefined) set['address'] = input.address
  if (input.notes !== undefined) set['notes'] = input.notes

  await db
    .update(vendors)
    .set(set as Partial<typeof vendors.$inferInsert>)
    .where(and(eq(vendors.tenantId, tenantId), eq(vendors.id, vendorId)))

  await db.insert(auditLog).values({
    tenantId,
    actorId,
    actorType: 'user',
    entityType: 'vendor',
    entityId: vendorId,
    action: 'update',
    changes: null,
  })

  return getVendor(db, tenantId, vendorId)
}

// ── archiveVendor ──────────────────────────────────────────────────────────────

export async function archiveVendor(
  db: Db,
  tenantId: string,
  actorId: string,
  vendorId: string,
): Promise<VendorWithStats | null> {
  await db
    .update(vendors)
    .set({ isArchived: true, updatedAt: new Date() })
    .where(and(eq(vendors.tenantId, tenantId), eq(vendors.id, vendorId)))

  await db.insert(auditLog).values({
    tenantId,
    actorId,
    actorType: 'user',
    entityType: 'vendor',
    entityId: vendorId,
    action: 'archive',
    changes: null,
  })

  return getVendor(db, tenantId, vendorId)
}

// ── suggestVendors ─────────────────────────────────────────────────────────────

export async function suggestVendors(
  db: Db,
  tenantId: string,
  q: string,
  limit = 10,
): Promise<Array<{ id: string; name: string; taxId: string | null }>> {
  const rows = await db
    .select({ id: vendors.id, name: vendors.name, taxId: vendors.taxId })
    .from(vendors)
    .where(
      and(
        eq(vendors.tenantId, tenantId),
        eq(vendors.isArchived, false),
        ilike(vendors.name, `%${q}%`),
      ),
    )
    .orderBy(vendors.name)
    .limit(limit)

  return rows
}