/**
 * Invoice Approval Workflow query helpers — invoice-approval-workflow (P054).
 *
 * These helpers supplement invoices-core with approval-specific operations.
 * The existing approveInvoice / rejectInvoice in queries/invoices.ts are basic
 * (no approved_by, approval_note, rejection_reason columns). The integrator
 * should rewire POST /:id/approve and POST /:id/reject in routes/invoices/index.ts
 * to call approveInvoiceEnhanced / rejectInvoiceEnhanced from this module.
 *
 * Assumed schema columns (integrator adds to invoices.ts via 0006_wave7.sql):
 *   approved_by         UUID REFERENCES users(id) ON DELETE SET NULL
 *   approval_note       TEXT
 *   rejection_reason    TEXT
 *   rejection_notify_customer BOOLEAN NOT NULL DEFAULT false
 *
 * These columns are written via sql`` fragments to survive before integrator
 * applies the migration (compile-time safe; runtime fails until migration runs).
 *
 * Design notes:
 * - approved_on_behalf_of ('staff'|'customer') in the spec dialog is UI metadata
 *   only — we fold the label into approval_note if provided, not a separate column.
 * - Reject reason minimum 5 chars (spec dialog says "min 5 characters").
 *   The base rejectInvoiceSchema in invoices.ts uses min(1) — our schema is stricter.
 * - Reject transitions status to 'REJECTED' (not DRAFT). Spec dialog prose says
 *   "returns to DRAFT for editing" — that is UX phrasing; the API contract is REJECTED.
 */
import { and, eq, asc, desc, count, sql } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { ilikeSubstringPattern } from '../utils/escape-like'
import { invoices, invoiceLines } from '../schema/invoices'
import { customers } from '../schema/customers'
import { auditLog } from './_audit-forward'
import { appendInvoiceActivity } from '../activities/writers'
import { users } from '../schema/users'

// ── Zod schemas ───────────────────────────────────────────────────────────────

export const approveInvoiceEnhancedSchema = z.object({
  /**
   * Who is approving: 'staff' = current user; 'customer' = on behalf of customer.
   * UI only — folded into approval_note prefix, no separate column.
   */
  approvedOnBehalfOf: z.enum(['staff', 'customer']).optional().default('staff'),
  note: z.string().max(2000).optional(),
})

export const rejectInvoiceEnhancedSchema = z.object({
  /** Required; min 5 chars per spec (IL tax audit trail). */
  reason: z.string().min(5, 'Rejection reason must be at least 5 characters').max(2000),
  /** Whether to trigger a customer notification email (handled by comms module). */
  notifyCustomer: z.boolean().optional().default(false),
})

export const bulkApproveSchema = z.object({
  ids: z.array(z.string().uuid()).min(1).max(200),
})

export const listPendingApprovalsSchema = z.object({
  search: z.string().optional(),
  sort: z.enum(['oldest', 'newest', 'amount', 'customer']).optional().default('oldest'),
  page: z.coerce.number().int().min(1).optional().default(1),
  perPage: z.coerce.number().int().min(1).max(100).optional().default(50),
})

// ── Public interfaces ─────────────────────────────────────────────────────────

export interface PendingApprovalItem {
  id: string
  proformaNumber: string | null
  customerId: string | null
  customerName: string | null
  total: string
  currency: string
  sentAt: string | null
}

export interface PendingApprovalsPage {
  items: PendingApprovalItem[]
  total: number
  page: number
  perPage: number
}

export interface BulkApproveResult {
  approved: number
  skipped: number
  errors: Array<{ id: string; reason: string }>
}

// ── Query helpers ─────────────────────────────────────────────────────────────

/**
 * List SENT invoices awaiting staff approval with optional search + sort.
 * Joins customers table to surface customer_name.
 */
export async function listPendingApprovals(
  db: Db,
  tenantId: string,
  opts: z.infer<typeof listPendingApprovalsSchema>,
): Promise<PendingApprovalsPage> {
  const page = opts.page ?? 1
  const perPage = opts.perPage ?? 50
  const offset = (page - 1) * perPage

  // Build sort expression
  const sortExpr = (() => {
    switch (opts.sort) {
      case 'oldest':
        return asc(invoices.sentAt)
      case 'newest':
        return desc(invoices.sentAt)
      case 'amount':
        return desc(sql`CAST(${invoices.total} AS NUMERIC)`)
      case 'customer':
        return asc(customers.name)
      default:
        return asc(invoices.sentAt)
    }
  })()

  const baseWhere = and(
    eq(invoices.tenantId, tenantId),
    eq(invoices.status, 'SENT'),
    sql`${invoices.source} <> 'credit_note'`,
    opts.search
      ? sql`${customers.name} ILIKE ${ilikeSubstringPattern(opts.search)}`
      : undefined,
  )

  const [countRow] = await db
    .select({ total: count() })
    .from(invoices)
    .leftJoin(customers, eq(invoices.customerId, customers.id))
    .where(baseWhere)

  const rows = await db
    .select({
      id: invoices.id,
      proformaNumber: invoices.proformaNumber,
      customerId: invoices.customerId,
      customerName: customers.name,
      total: invoices.total,
      currency: invoices.currency,
      sentAt: invoices.sentAt,
    })
    .from(invoices)
    .leftJoin(customers, eq(invoices.customerId, customers.id))
    .where(baseWhere)
    .orderBy(sortExpr)
    .limit(perPage)
    .offset(offset)

  const items: PendingApprovalItem[] = rows.map((r) => ({
    id: r.id,
    proformaNumber: r.proformaNumber ?? null,
    customerId: r.customerId,
    customerName: r.customerName ?? null,
    total: r.total,
    currency: r.currency,
    sentAt: r.sentAt?.toISOString() ?? null,
  }))

  return {
    items,
    total: countRow?.total ?? 0,
    page,
    perPage,
  }
}

/**
 * Enhanced SENT → APPROVED.
 * Writes approved_by and approval_note via raw SQL fragments
 * (columns added by integrator in 0006_wave7.sql).
 *
 * The integrator must rewire POST /api/invoices/:id/approve to call this
 * function instead of approveInvoice from queries/invoices.ts.
 */
export async function approveInvoiceEnhanced(
  db: Db,
  tenantId: string,
  id: string,
  actorId: string,
  input: z.infer<typeof approveInvoiceEnhancedSchema>,
): Promise<{ id: string; status: string; approvedAt: string }> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select({ id: invoices.id, status: invoices.status, source: invoices.source })
      .from(invoices)
      .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id)))
      .limit(1)
      .for('update')

    if (!existing) throw new Error('Invoice not found')
    if (existing.source === 'credit_note') {
      throw new Error('Cannot approve a credit note')
    }
    if (existing.status !== 'SENT') {
      throw new Error(`Cannot approve invoice in status ${existing.status}`)
    }

    // Build approval note (fold approvedOnBehalfOf into note prefix)
    const notePrefix =
      input.approvedOnBehalfOf === 'customer' ? '[On behalf of customer] ' : '[Staff approval] '
    const approvalNote = input.note ? `${notePrefix}${input.note}` : notePrefix.trim()

    const now = new Date()
    const nowIso = now.toISOString()

    // Write new columns via raw SQL to survive pre-migration typecheck
    await tx.execute(
      sql`UPDATE invoices
          SET status = 'APPROVED',
              approved_at = ${nowIso},
              approved_by = ${actorId}::uuid,
              approval_note = ${approvalNote},
              updated_at = ${nowIso}
          WHERE tenant_id = ${tenantId}::uuid AND id = ${id}::uuid`,
    )

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'invoice',
      entityId: id,
      action: 'invoice.approved',
      changes: {
        status: ['SENT', 'APPROVED'],
        approvedOnBehalfOf: [null, input.approvedOnBehalfOf],
      },
    })

    const [actorRow] = await tx
      .select({ name: users.name })
      .from(users)
      .where(eq(users.id, actorId))
      .limit(1)
    const actorName = actorRow?.name ?? 'Staff member'
    let activityNote =
      input.approvedOnBehalfOf === 'customer'
        ? `Approved on behalf of customer by ${actorName}`
        : `Approved by ${actorName}`
    if (input.note) {
      activityNote = `${activityNote}\n"${input.note}"`
    }
    await appendInvoiceActivity(tx, {
      tenantId,
      invoiceId: id,
      actorId,
      actorType: 'user',
      eventType: 'approved',
      note: activityNote,
    })

    return { id, status: 'APPROVED', approvedAt: now.toISOString() }
  })
}

/**
 * Enhanced SENT → REJECTED.
 * Writes rejection_reason and rejection_notify_customer via raw SQL fragments.
 *
 * The integrator must rewire POST /api/invoices/:id/reject to call this
 * function instead of rejectInvoice from queries/invoices.ts.
 */
export async function rejectInvoiceEnhanced(
  db: Db,
  tenantId: string,
  id: string,
  actorId: string,
  input: z.infer<typeof rejectInvoiceEnhancedSchema>,
): Promise<{ id: string; status: string }> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select({ id: invoices.id, status: invoices.status, source: invoices.source })
      .from(invoices)
      .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id)))
      .limit(1)
      .for('update')

    if (!existing) throw new Error('Invoice not found')
    if (existing.source === 'credit_note') {
      throw new Error('Cannot reject a credit note')
    }
    if (existing.status !== 'SENT') {
      throw new Error(`Cannot reject invoice in status ${existing.status}`)
    }

    const now = new Date()
    const nowIso = now.toISOString()

    // Write new columns via raw SQL
    await tx.execute(
      sql`UPDATE invoices
          SET status = 'REJECTED',
              rejection_reason = ${input.reason},
              rejection_notify_customer = ${input.notifyCustomer ?? false},
              updated_at = ${nowIso}
          WHERE tenant_id = ${tenantId}::uuid AND id = ${id}::uuid`,
    )

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'invoice',
      entityId: id,
      action: 'invoice.rejected',
      changes: {
        status: ['SENT', 'REJECTED'],
        rejectionReason: [null, input.reason],
        notifyCustomer: [null, input.notifyCustomer ?? false],
      },
    })

    await appendInvoiceActivity(tx, {
      tenantId,
      invoiceId: id,
      actorId,
      actorType: 'user',
      eventType: 'rejected',
      note: input.reason,
    })

    return { id, status: 'REJECTED' }
  })
}

/**
 * Bulk approve: process each invoice in its own transaction.
 * Partial failure is acceptable — returns summary of approved/skipped/errors.
 * Max 200 IDs per call (validated by schema).
 */
export async function bulkApproveInvoices(
  db: Db,
  tenantId: string,
  actorId: string,
  ids: string[],
): Promise<BulkApproveResult> {
  let approved = 0
  let skipped = 0
  const errors: Array<{ id: string; reason: string }> = []

  for (const id of ids) {
    try {
      const result = await approveInvoiceEnhanced(db, tenantId, id, actorId, {
        approvedOnBehalfOf: 'staff',
        note: undefined,
      })
      if (result.status === 'APPROVED') {
        approved++
      } else {
        skipped++
      }
    } catch (err) {
      const reason = err instanceof Error ? err.message : 'Unknown error'
      // If already not SENT, count as skipped; otherwise as error
      if (reason.includes('Cannot approve invoice in status')) {
        skipped++
      } else {
        errors.push({ id, reason })
      }
    }
  }

  return { approved, skipped, errors }
}

/**
 * Count of SENT invoices awaiting approval for a tenant.
 * Used for the sidebar badge.
 */
export async function countPendingApprovals(db: Db, tenantId: string): Promise<number> {
  const [row] = await db
    .select({ total: count() })
    .from(invoices)
    .where(and(eq(invoices.tenantId, tenantId), eq(invoices.status, 'SENT')))
  return row?.total ?? 0
}

/**
 * Get invoice lines for a given invoice (used for template copy).
 * Exported so invoice-drafts.ts can import without duplicating the query.
 */
export async function getInvoiceLinesForCopy(
  db: Db,
  invoiceId: string,
): Promise<typeof invoiceLines.$inferSelect[]> {
  return db
    .select()
    .from(invoiceLines)
    .where(eq(invoiceLines.invoiceId, invoiceId))
    .orderBy(invoiceLines.position)
}