/**
 * Expense-to-invoice-line query helpers — expense-to-invoice-line (P052).
 *
 * These helpers implement the bridge that turns a completed, unbilled expense
 * into an invoice line. The key mutation is atomic:
 *   1. INSERT invoice_lines row with expense_id backlink
 *   2. UPDATE expenses.billed_at + expenses.invoice_id
 *
 * NOTE: expenses.billed_at, expenses.invoice_id and invoice_lines.expense_id
 * are columns added by the integrator (expenses.ts and invoices.ts have
 * multi-leaf collisions). This file uses raw column names in sql`` references
 * where the Drizzle type doesn't yet carry those fields, and documents the
 * exact Drizzle field definitions the integrator must add (see bottom of file).
 *
 * Once the integrator applies the alters, the `any` casts here can be narrowed.
 */
import { and, eq, sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { invoices } from '../schema/invoices'
import { recalculateInvoiceTotals } from './invoices'

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

export interface BillableExpense {
  id: string
  tenantId: string
  projectId: string | null
  customerId: string | null
  description: string           // suggested invoice line description
  amount: string                // numeric string (normalized expense amount)
  expenseDate: string | null    // ISO date
  vendorName: string | null
  billedAt: string | null       // ISO 8601 — null = unbilled
  invoiceId: string | null
  status: string
}

export interface AddExpenseToInvoiceInput {
  expenseId: string
  invoiceId: string
  description: string
  amount: string   // numeric string, validated by caller
  tenantId: string
}

export interface AddExpenseToInvoiceResult {
  invoiceLineId: string
  invoiceId: string
  expenseId: string
}

// ── Query: list billable (unbilled, COMPLETED, project-linked) expenses ───────

/**
 * Returns expenses that are candidates for invoicing:
 *   - status = 'COMPLETED'
 *   - billed_at IS NULL
 *   - project_id IS NOT NULL
 * Ordered by expense_date DESC.
 */
export async function listBillableExpenses(
  db: Db,
  tenantId: string,
  opts: { projectId?: string; cursor?: string; limit?: number } = {},
): Promise<{ expenses: BillableExpense[]; nextCursor: string | null }> {
  const limit = Math.min(opts.limit ?? 50, 200)

  // We use sql`` for billed_at / invoice_id because they are integrator-owned
  // columns not yet in the Drizzle schema type. The integrator must ADD these
  // columns to packages/db/src/schema/expenses.ts (see alters below).
  const rows = await db.execute(sql`
    SELECT
      e.id,
      e.tenant_id,
      e.project_id,
      p.customer_id,
      COALESCE(e.vendor_name, 'Expense') AS description,
      COALESCE(e.amount::text, '0') AS amount,
      e.expense_date::text AS expense_date,
      e.vendor_name,
      e.billed_at::text AS billed_at,
      e.invoice_id,
      e.status
    FROM expenses e
    LEFT JOIN projects p ON p.id = e.project_id
    WHERE
      e.tenant_id = ${tenantId}
      AND e.status = 'COMPLETED'
      AND e.deleted_at IS NULL
      AND e.project_id IS NOT NULL
      AND e.billed_at IS NULL
      ${opts.projectId ? sql`AND e.project_id = ${opts.projectId}` : sql``}
    ORDER BY e.expense_date DESC NULLS LAST, e.created_at DESC, e.id DESC
    LIMIT ${limit + 1}
  `)

  const allRows = rows as Array<Record<string, unknown>>
  const hasMore = allRows.length > limit
  const page = hasMore ? allRows.slice(0, limit) : allRows

  const mapped: BillableExpense[] = page.map((r) => ({
    id: r.id as string,
    tenantId: r.tenant_id as string,
    projectId: (r.project_id as string | null) ?? null,
    customerId: (r.customer_id as string | null) ?? null,
    description: r.description as string,
    amount: (r.amount as string | null) ?? '0',
    expenseDate: (r.expense_date as string | null) ?? null,
    vendorName: (r.vendor_name as string | null) ?? null,
    billedAt: null,  // filter guarantees billed_at IS NULL
    invoiceId: null,
    status: r.status as string,
  }))

  const nextCursor =
    hasMore && page.length > 0
      ? Buffer.from(JSON.stringify({ id: page[page.length - 1]!.id })).toString('base64url')
      : null

  return { expenses: mapped, nextCursor }
}

/**
 * Fetch a single expense, including its billing state.
 * Returns null if not found or soft-deleted.
 */
export async function getExpenseForInvoicing(
  db: Db,
  tenantId: string,
  expenseId: string,
): Promise<BillableExpense | null> {
  const rows = await db.execute(sql`
    SELECT
      e.id,
      e.tenant_id,
      e.project_id,
      p.customer_id,
      COALESCE(e.vendor_name, 'Expense') AS description,
      COALESCE(e.amount::text, '0') AS amount,
      e.expense_date::text AS expense_date,
      e.vendor_name,
      e.billed_at::text AS billed_at,
      e.invoice_id,
      e.status,
      e.deleted_at
    FROM expenses e
    LEFT JOIN projects p ON p.id = e.project_id
    WHERE e.id = ${expenseId} AND e.tenant_id = ${tenantId}
    LIMIT 1
  `)

  const row = (rows as Array<Record<string, unknown>>)[0]
  if (!row || row.deleted_at !== null) return null

  return {
    id: row.id as string,
    tenantId: row.tenant_id as string,
    projectId: (row.project_id as string | null) ?? null,
    customerId: (row.customer_id as string | null) ?? null,
    description: row.description as string,
    amount: (row.amount as string | null) ?? '0',
    expenseDate: (row.expense_date as string | null) ?? null,
    vendorName: (row.vendor_name as string | null) ?? null,
    billedAt: (row.billed_at as string | null) ?? null,
    invoiceId: (row.invoice_id as string | null) ?? null,
    status: (row.status as string | null) ?? 'PENDING',
  }
}

/**
 * List DRAFT invoices for a given customer (for the "Add to existing invoice"
 * modal selector). Returns lightweight invoice stubs.
 */
export async function listDraftInvoicesForCustomer(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<Array<{ id: string; invoiceNumber: string | null; proformaNumber: string | null; total: string; createdAt: string }>> {
  const rows = await db
    .select({
      id: invoices.id,
      invoiceNumber: invoices.invoiceNumber,
      proformaNumber: invoices.proformaNumber,
      total: invoices.total,
      createdAt: invoices.createdAt,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.customerId, customerId),
        eq(invoices.status, 'DRAFT'),
      ),
    )
    .orderBy(invoices.createdAt)

  return rows.map((r) => ({
    id: r.id,
    invoiceNumber: r.invoiceNumber ?? null,
    proformaNumber: r.proformaNumber ?? null,
    total: r.total,
    createdAt: r.createdAt.toISOString(),
  }))
}

// ── Mutation: add expense as invoice line ─────────────────────────────────────

/**
 * Atomically:
 *   1. Verifies the invoice is DRAFT and belongs to the tenant.
 *   2. Inserts an invoice_lines row with expense_id set.
 *   3. Stamps expenses.billed_at and expenses.invoice_id.
 *
 * Must be called inside a transaction. The route handler wraps in db.transaction().
 */
export async function addExpenseToInvoiceLine(
  tx: DbTx,
  input: AddExpenseToInvoiceInput,
): Promise<AddExpenseToInvoiceResult> {
  const now = new Date()

  // 1. Insert invoice line using raw SQL to:
  //    a) Compute position atomically as MAX(position) + 1 for this invoice
  //    b) Set expense_id (integrator-owned column not yet in Drizzle type)
  const insertResult = await tx.execute(sql`
    INSERT INTO invoice_lines (
      id,
      invoice_id,
      tenant_id,
      description,
      quantity,
      unit_price,
      discount_pct,
      line_total,
      taxable,
      position,
      expense_id,
      created_at
    )
    SELECT
      gen_random_uuid(),
      ${input.invoiceId},
      ${input.tenantId},
      ${input.description},
      1,
      ${input.amount}::numeric,
      0,
      ${input.amount}::numeric,
      true,
      COALESCE((SELECT MAX(position) FROM invoice_lines WHERE invoice_id = ${input.invoiceId}), 0) + 1,
      ${input.expenseId}::uuid,
      NOW()
    WHERE EXISTS (
      SELECT 1 FROM invoices
      WHERE id = ${input.invoiceId}
        AND tenant_id = ${input.tenantId}
        AND status = 'DRAFT'
    )
    RETURNING id
  `)

  const lineRow = (insertResult as unknown as Array<{ id: string }>)[0]
  if (!lineRow) {
    throw new Error('Invoice not found, not DRAFT, or tenant mismatch')
  }
  const line = { id: lineRow.id }

  // 2. Stamp expense as billed
  await tx.execute(sql`
    UPDATE expenses
    SET
      billed_at = ${now.toISOString()},
      invoice_id = ${input.invoiceId},
      updated_at = ${now.toISOString()}
    WHERE id = ${input.expenseId}
      AND tenant_id = ${input.tenantId}
      AND billed_at IS NULL
  `)

  await recalculateInvoiceTotals(tx, input.tenantId, input.invoiceId)

  return {
    invoiceLineId: line.id,
    invoiceId: input.invoiceId,
    expenseId: input.expenseId,
  }
}

/*
 * ══════════════════════════════════════════════════════════════════════════════
 * INTEGRATOR-OWNED COLUMN DEFINITIONS (exact Drizzle field names + types)
 * ══════════════════════════════════════════════════════════════════════════════
 *
 * The integrator must add these to the respective schema files BEFORE this
 * module's queries resolve without raw sql casts.
 *
 * packages/db/src/schema/invoices.ts — invoiceLines table:
 *   expenseId: uuid('expense_id').references(() => expenses.id, { onDelete: 'set null' }),
 *
 * packages/db/src/schema/expenses.ts — expenses table:
 *   billedAt: timestamp('billed_at', { withTimezone: true }),
 *   invoiceId: uuid('invoice_id').references(() => invoices.id, { onDelete: 'set null' }),
 *
 * SQL DDL (goes in integrator migration 0006_wave7.sql):
 *   ALTER TABLE invoice_lines ADD COLUMN expense_id UUID REFERENCES expenses(id) ON DELETE SET NULL;
 *   ALTER TABLE expenses ADD COLUMN billed_at TIMESTAMPTZ;
 *   ALTER TABLE expenses ADD COLUMN invoice_id UUID REFERENCES invoices(id) ON DELETE SET NULL;
 *   CREATE INDEX idx_expenses_invoice ON expenses (tenant_id) WHERE billed_at IS NULL AND project_id IS NOT NULL AND status = 'COMPLETED';
 * ══════════════════════════════════════════════════════════════════════════════
 */