/**
 * OCR correction workflow query helpers — expense-ocr-correction-ux spec.
 *
 * Covers:
 *  - listReviewQueue: GET /api/expenses/review (NEEDS_REVIEW queue)
 *  - approveOcrExpense: POST /api/expenses/:id/approve (OCR review → COMPLETED)
 *  - correctExpense: POST /api/expenses/:id/correct (post-approval correction)
 *  - voidExpense: POST /api/expenses/:id/void (soft-delete via voided_at)
 *
 * All writes use db.transaction() with tx.insert(auditLog) per require-audit-in-transaction.
 * Route files import from '@zync/db/queries'.
 */
import { and, eq, isNull, desc, count, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { expenses, expenseCorrections } from '../schema/expenses'
import type { NewExpense } from '../schema/expenses'
import { users } from '../schema/users'
import { auditLog } from './_audit-forward'
import { serializeExpenseRow } from './expenses'
import type { Expense, ReviewQueueItem, ReviewQueueResponse } from '@zync/types'

// ── Cursor helpers ─────────────────────────────────────────────────────────────

function encodeCursor(createdAt: Date, id: string): string {
  const payload = JSON.stringify({ createdAt: createdAt.toISOString(), id })
  return Buffer.from(payload).toString('base64url')
}

function decodeCursor(cursor: string): { createdAt: string; id: string } | null {
  try {
    const raw = Buffer.from(cursor, 'base64url').toString('utf8')
    const parsed = JSON.parse(raw) as { createdAt: string; id: string }
    if (typeof parsed.createdAt !== 'string' || typeof parsed.id !== 'string') return null
    return parsed
  } catch {
    return null
  }
}

// ── listReviewQueue ────────────────────────────────────────────────────────────

export async function listReviewQueue(
  db: Db,
  tenantId: string,
  opts: { cursor?: string; limit?: number },
): Promise<ReviewQueueResponse> {
  const limit = Math.min(opts.limit ?? 20, 100)
  const cursor = opts.cursor ? decodeCursor(opts.cursor) : null

  const baseConds = [
    eq(expenses.tenantId, tenantId),
    eq(expenses.status, 'NEEDS_REVIEW'),
    isNull(expenses.voidedAt),
    isNull(expenses.deletedAt),
  ]

  const countResult = await db
    .select({ value: count() })
    .from(expenses)
    .where(and(...baseConds))
  const total = countResult[0]?.value ?? 0

  const pageConds = [...baseConds]
  if (cursor) {
    pageConds.push(
      sql`(${expenses.createdAt}, ${expenses.id}) < (${new Date(cursor.createdAt)}, ${cursor.id})`,
    )
  }

  const rows = await db
    .select({
      id: expenses.id,
      tenantId: expenses.tenantId,
      vendorName: expenses.vendorName,
      amount: expenses.amount,
      currency: expenses.currency,
      expenseDate: expenses.expenseDate,
      ocrConfidence: expenses.ocrConfidence,
      status: expenses.status,
      createdAt: expenses.createdAt,
    })
    .from(expenses)
    .where(and(...pageConds))
    .orderBy(desc(expenses.createdAt), desc(expenses.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const pageRows = hasMore ? rows.slice(0, limit) : rows
  const lastRow = pageRows.at(-1)
  const nextCursor = hasMore && lastRow ? encodeCursor(lastRow.createdAt, lastRow.id) : null

  return {
    items: pageRows.map((r) => ({
      id: r.id,
      tenantId: r.tenantId,
      vendorName: r.vendorName ?? null,
      amount: r.amount ?? null,
      currency: r.currency,
      expenseDate: r.expenseDate ?? null,
      ocrConfidence: r.ocrConfidence ?? null,
      status: r.status as ReviewQueueItem['status'],
      createdAt: r.createdAt.toISOString(),
    })),
    nextCursor,
    total,
  }
}

// ── approveOcrExpense ──────────────────────────────────────────────────────────

/** Editable field names allowed in OCR review approve corrections payload. */
const OCR_APPROVE_EDITABLE_FIELDS = new Set([
  'vendorName', 'expenseDate', 'amount', 'vatAmount',
  'expenseCategory', 'deductionPct', 'allocationNumber',
])

export interface ApproveOcrExpenseArgs {
  tenantId: string
  expenseId: string
  userId: string
  corrections: Record<string, string>
}

export async function approveOcrExpense(
  db: Db,
  args: ApproveOcrExpenseArgs,
): Promise<Expense> {
  const { tenantId, expenseId, userId, corrections } = args

  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(expenses)
      .where(
        and(
          eq(expenses.id, expenseId),
          eq(expenses.tenantId, tenantId),
          isNull(expenses.deletedAt),
        ),
      )

    if (!existing) throw new Error('Expense not found')
    if (existing.status !== 'NEEDS_REVIEW') {
      throw new Error('Expense is not in NEEDS_REVIEW status')
    }
    if (existing.voidedAt) throw new Error('Cannot approve a voided expense')

    // Write correction rows for changed fields
    const correctionRows = Object.entries(corrections)
      .filter(([field]) => OCR_APPROVE_EDITABLE_FIELDS.has(field))
      .map(([fieldName, newValue]) => ({
        expenseId,
        userId,
        fieldName,
        originalValue: (() => {
          const v = existing[fieldName as keyof typeof existing]
          return v == null ? null : String(v)
        })(),
        correctedValue: newValue,
        correctionSource: 'ocr' as const,
      }))

    if (correctionRows.length > 0) {
      await tx.insert(expenseCorrections).values(correctionRows)
    }

    // Build update payload from corrections
    const updatePayload: Partial<NewExpense> = {
      status: 'COMPLETED',
      updatedAt: new Date(),
    }
    for (const [field, value] of Object.entries(corrections)) {
      if (!OCR_APPROVE_EDITABLE_FIELDS.has(field)) continue
      if (field === 'deductionPct') {
        const n = parseInt(value, 10)
        if (!isNaN(n)) (updatePayload as Record<string, unknown>)['deductionPct'] = n
      } else {
        ;(updatePayload as Record<string, unknown>)[field] = value || null
      }
    }

    const [updated] = await tx
      .update(expenses)
      .set(updatePayload)
      .where(and(eq(expenses.id, expenseId), eq(expenses.tenantId, tenantId)))
      .returning()

    if (!updated) throw new Error('Expense update failed')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'expense',
      entityId: expenseId,
      action: 'expense.ocr_approved',
      changes: {
        status: ['NEEDS_REVIEW', 'COMPLETED'],
        correctionCount: [null, correctionRows.length],
      },
    })

    return serializeExpenseRow(updated)
  })
}

// ── reprocessExpense ───────────────────────────────────────────────────────────

export async function reprocessExpense(
  db: Db,
  tenantId: string,
  expenseId: string,
  userId: string,
): Promise<{ r2Key: string; fileType: string }> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(expenses)
      .where(
        and(
          eq(expenses.id, expenseId),
          eq(expenses.tenantId, tenantId),
          isNull(expenses.deletedAt),
        ),
      )

    if (!existing) throw new Error('Expense not found')
    if (existing.voidedAt) throw new Error('Cannot reprocess a voided expense')

    await tx
      .update(expenses)
      .set({
        status: 'NEEDS_REVIEW',
        ocrConfidence: null,
        rawOcrText: null,
        vendorName: null,
        vendorTaxId: null,
        invoiceNumber: null,
        invoiceTotal: null,
        vatAmount: null,
        expenseDate: null,
        amount: null,
        processingStartedAt: null,
        processedAt: null,
        processingError: null,
        updatedAt: new Date(),
      })
      .where(and(eq(expenses.id, expenseId), eq(expenses.tenantId, tenantId)))

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'expense',
      entityId: expenseId,
      action: 'expense.reprocessed',
    })

    return { r2Key: existing.r2Key, fileType: existing.fileType }
  })
}

// ── correctExpense ─────────────────────────────────────────────────────────────

export interface CorrectExpenseArgs {
  tenantId: string
  expenseId: string
  userId: string
  reason: string
  amount?: string
  category?: string
  date?: string
  receiptR2Key?: string
}

export async function correctExpense(
  db: Db,
  args: CorrectExpenseArgs,
): Promise<Expense> {
  const { tenantId, expenseId, userId, reason, amount, category, date, receiptR2Key } = args

  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(expenses)
      .where(
        and(
          eq(expenses.id, expenseId),
          eq(expenses.tenantId, tenantId),
          isNull(expenses.deletedAt),
        ),
      )

    if (!existing) throw new Error('Expense not found')
    if (existing.approvalStatus !== 'approved') {
      throw new Error('Only approved expenses can be corrected')
    }
    if (existing.voidedAt) throw new Error('Cannot correct a voided expense')

    // Write correction rows
    const correctionRows: Array<{
      expenseId: string
      userId: string
      fieldName: string
      originalValue: string | null
      correctedValue: string
      correctionSource: 'manual'
    }> = []

    if (amount !== undefined) {
      correctionRows.push({
        expenseId, userId,
        fieldName: 'amount',
        originalValue: existing.amount ?? null,
        correctedValue: amount,
        correctionSource: 'manual',
      })
    }
    if (category !== undefined) {
      correctionRows.push({
        expenseId, userId,
        fieldName: 'expenseCategory',
        originalValue: existing.expenseCategory ?? null,
        correctedValue: category,
        correctionSource: 'manual',
      })
    }
    if (date !== undefined) {
      correctionRows.push({
        expenseId, userId,
        fieldName: 'expenseDate',
        originalValue: existing.expenseDate ?? null,
        correctedValue: date,
        correctionSource: 'manual',
      })
    }
    if (receiptR2Key !== undefined) {
      correctionRows.push({
        expenseId, userId,
        fieldName: 'r2Key',
        originalValue: existing.r2Key,
        correctedValue: receiptR2Key,
        correctionSource: 'manual',
      })
    }

    if (correctionRows.length > 0) {
      await tx.insert(expenseCorrections).values(correctionRows)
    }

    // Reset approval status and apply field changes
    const updatePayload: Partial<NewExpense> & Record<string, unknown> = {
      approvalStatus: 'pending',
      approvedBy: null,
      approvedAt: null,
      correctionNote: reason,
      updatedAt: new Date(),
    }
    if (amount !== undefined) updatePayload['amount'] = amount
    if (category !== undefined) updatePayload['expenseCategory'] = category
    if (date !== undefined) updatePayload['expenseDate'] = date
    if (receiptR2Key !== undefined) updatePayload['r2Key'] = receiptR2Key

    const [updated] = await tx
      .update(expenses)
      .set(updatePayload)
      .where(and(eq(expenses.id, expenseId), eq(expenses.tenantId, tenantId)))
      .returning()

    if (!updated) throw new Error('Expense update failed')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'expense',
      entityId: expenseId,
      action: 'expense.corrected',
      changes: {
        approvalStatus: ['approved', 'pending'],
        correctionNote: [null, reason],
      },
    })

    return serializeExpenseRow(updated)
  })
}

// ── voidExpense ────────────────────────────────────────────────────────────────

export async function voidExpense(
  db: Db,
  tenantId: string,
  expenseId: string,
  userId: string,
  reason: string,
): Promise<void> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(expenses)
      .where(
        and(
          eq(expenses.id, expenseId),
          eq(expenses.tenantId, tenantId),
          isNull(expenses.deletedAt),
        ),
      )

    if (!existing) throw new Error('Expense not found')
    if (existing.voidedAt) throw new Error('Expense is already voided')

    const now = new Date()

    await tx
      .update(expenses)
      .set({ voidedAt: now, voidedReason: reason, updatedAt: now })
      .where(and(eq(expenses.id, expenseId), eq(expenses.tenantId, tenantId)))

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'expense',
      entityId: expenseId,
      action: 'expense.voided',
      changes: {
        voidedAt: [null, now.toISOString()],
        voidedReason: [null, reason],
      },
    })
  })
}

// ── getExpenseApproverInfo ─────────────────────────────────────────────────────
// Used by correctExpense notification — fetch approver user info

export async function getExpenseApproverInfo(
  db: Db,
  approverId: string,
): Promise<{ email: string; name: string | null } | null> {
  const [row] = await db
    .select({ email: users.email, name: users.name })
    .from(users)
    .where(eq(users.id, approverId))
    .limit(1)
  return row ?? null
}