/**
 * Invoice Draft Library query helpers — invoice-draft-library (P055).
 *
 * Covers:
 * - Listing DRAFT invoices (non-template) for the drafts library
 * - Listing invoice templates (is_template = true)
 * - Creating a new invoice template (is_template=true, customer_id nullable)
 * - Creating a draft invoice from a template (copies all line items)
 * - Counting DRAFT invoices for the main list "X drafts" pill
 *
 * Assumed schema delta (integrator applies to invoices.ts via 0006_wave7.sql):
 *   is_template BOOLEAN NOT NULL DEFAULT false
 *   customer_id DROP NOT NULL (was NOT NULL)
 *   ADD CONSTRAINT chk_invoice_requires_customer
 *     CHECK (is_template = true OR customer_id IS NOT NULL)
 *
 * is_template is read/written via sql`` fragments to survive pre-migration
 * typecheck; customer_id is already on the schema object so it can be written
 * normally (we pass NULL for template creation).
 *
 * Design notes:
 * - Templates are DRAFT invoices with is_template=true; they are never sent.
 * - DELETE /api/invoices/:id for DRAFTs is handled by deleteDraftInvoice in
 *   queries/invoices.ts — not duplicated here.
 * - The "from-template" route at POST /api/invoices/drafts/from-template/:templateId
 *   bypasses core createInvoice (which requires customer_id NOT NULL in its zod schema).
 */
import { and, eq, 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'

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

export const createTemplateSchema = z.object({
  /** Template name — stored in notes field since invoices has no name column. */
  name: z.string().min(1).max(500),
  currency: z.string().length(3).optional().default('ILS'),
  notes: z.string().max(5000).optional().nullable(),
  lines: z
    .array(
      z.object({
        description: z.string().min(1).max(2000),
        quantity: z.number().positive(),
        unitPrice: z.number(),
        discountPct: z.number().min(0).max(100).optional().default(0),
        taxable: z.boolean().optional().default(true),
        position: z.number().int().min(0),
      }),
    )
    .min(1),
})

export const createFromTemplateSchema = z.object({
  /** Customer to assign the new draft to. */
  customerId: z.string().uuid(),
})

export const listDraftsSchema = z.object({
  search: z.string().optional(),
  page: z.coerce.number().int().min(1).optional().default(1),
  perPage: z.coerce.number().int().min(1).max(100).optional().default(50),
})

export const listTemplatesSchema = z.object({
  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 DraftItem {
  id: string
  customerId: string | null
  customerName: string | null
  total: string
  currency: string
  notes: string | null
  createdAt: string
  updatedAt: string
}

export interface DraftListPage {
  items: DraftItem[]
  total: number
  page: number
  perPage: number
}

export interface TemplateItem {
  id: string
  name: string
  currency: string
  notes: string | null
  lineCount: number
  createdAt: string
  updatedAt: string
}

export interface TemplateListPage {
  items: TemplateItem[]
  total: number
  page: number
  perPage: number
}

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

/**
 * List DRAFT invoices (non-template) for the drafts library.
 * is_template column read via raw SQL since it may not be in Drizzle schema yet.
 */
export async function listDraftInvoices(
  db: Db,
  tenantId: string,
  opts: z.infer<typeof listDraftsSchema>,
): Promise<DraftListPage> {
  const page = opts.page ?? 1
  const perPage = opts.perPage ?? 50
  const offset = (page - 1) * perPage

  const baseWhere = and(
    eq(invoices.tenantId, tenantId),
    eq(invoices.status, 'DRAFT'),
    sql`(${invoices.id} IN (
      SELECT id FROM invoices
      WHERE tenant_id = ${tenantId}::uuid
        AND status = 'DRAFT'
        AND (is_template IS NULL OR is_template = false)
    ))`,
    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,
      customerId: invoices.customerId,
      customerName: customers.name,
      total: invoices.total,
      currency: invoices.currency,
      notes: invoices.notes,
      createdAt: invoices.createdAt,
      updatedAt: invoices.updatedAt,
    })
    .from(invoices)
    .leftJoin(customers, eq(invoices.customerId, customers.id))
    .where(baseWhere)
    .orderBy(desc(invoices.updatedAt))
    .limit(perPage)
    .offset(offset)

  return {
    items: rows.map((r) => ({
      id: r.id,
      customerId: r.customerId ?? null,
      customerName: r.customerName ?? null,
      total: r.total,
      currency: r.currency,
      notes: r.notes ?? null,
      createdAt: r.createdAt.toISOString(),
      updatedAt: r.updatedAt.toISOString(),
    })),
    total: countRow?.total ?? 0,
    page,
    perPage,
  }
}

/**
 * List invoice templates (is_template = true, status = DRAFT).
 * Returns template name (from notes field), currency, and line count.
 */
export async function listTemplates(
  db: Db,
  tenantId: string,
  opts: z.infer<typeof listTemplatesSchema>,
): Promise<TemplateListPage> {
  const page = opts.page ?? 1
  const perPage = opts.perPage ?? 50
  const offset = (page - 1) * perPage

  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM invoices
        WHERE tenant_id = ${tenantId}::uuid
          AND status = 'DRAFT'
          AND is_template = true`,
  )
  const total = (countResult[0] as { total: number } | undefined)?.total ?? 0

  const rows = await db.execute(
    sql`SELECT
          i.id,
          i.notes,
          i.currency,
          i.created_at,
          i.updated_at,
          COUNT(il.id)::int AS line_count
        FROM invoices i
        LEFT JOIN invoice_lines il ON il.invoice_id = i.id
        WHERE i.tenant_id = ${tenantId}::uuid
          AND i.status = 'DRAFT'
          AND i.is_template = true
        GROUP BY i.id, i.notes, i.currency, i.created_at, i.updated_at
        ORDER BY i.updated_at DESC
        LIMIT ${perPage} OFFSET ${offset}`,
  )

  const items: TemplateItem[] = (rows as unknown as Array<{
    id: string
    notes: string | null
    currency: string
    created_at: string
    updated_at: string
    line_count: number
  }>).map((r) => ({
    id: r.id,
    // Template name is the first line of notes (set by createTemplate)
    name: r.notes?.split('\n')[0] ?? 'Unnamed Template',
    currency: r.currency,
    notes: r.notes ?? null,
    lineCount: r.line_count,
    createdAt: new Date(r.created_at).toISOString(),
    updatedAt: new Date(r.updated_at).toISOString(),
  }))

  return { items, total, page, perPage }
}

/**
 * Create an invoice template (is_template=true, customer_id=NULL).
 * Template name is stored as the first line of notes with a marker prefix.
 * customer_id is NULL (schema allows this after integrator drops NOT NULL).
 */
export async function createTemplate(
  db: Db,
  tenantId: string,
  actorId: string,
  input: z.infer<typeof createTemplateSchema>,
): Promise<{ id: string; name: string; currency: string; lineCount: number }> {
  return db.transaction(async (tx) => {
    // Store template name as first line of notes (invoices has no name column)
    const notes = input.notes
      ? `${input.name}\n${input.notes}`
      : input.name

    // Calculate line totals
    const parsedLines = input.lines.map((l) => ({
      ...l,
      lineTotal:
        Math.round(l.quantity * l.unitPrice * (1 - (l.discountPct ?? 0) / 100) * 100) / 100,
    }))
    const subtotal = parsedLines.reduce((sum, l) => sum + l.lineTotal, 0)

    // Insert via raw SQL to set is_template=true and customer_id=NULL
    // (bypasses Drizzle's schema-level NOT NULL check on customerId pre-migration)
    const result = await tx.execute(
      sql`INSERT INTO invoices (
            id, tenant_id, customer_id, status, currency, subtotal, vat_amount,
            total, amount_paid, notes, source, is_template, created_by,
            created_at, updated_at
          ) VALUES (
            gen_random_uuid(),
            ${tenantId}::uuid,
            NULL,
            'DRAFT',
            ${input.currency ?? 'ILS'},
            ${String(Math.round(subtotal * 100) / 100)},
            '0',
            ${String(Math.round(subtotal * 100) / 100)},
            '0',
            ${notes},
            'manual',
            true,
            ${actorId}::uuid,
            NOW(),
            NOW()
          )
          RETURNING id`,
    )

    const invoiceId = (result[0] as { id: string }).id

    // Insert lines
    if (parsedLines.length > 0) {
      for (const l of parsedLines) {
        await tx.insert(invoiceLines).values({
          invoiceId,
          tenantId,
          description: l.description,
          quantity: String(l.quantity),
          unitPrice: String(l.unitPrice),
          discountPct: String(l.discountPct ?? 0),
          lineTotal: String(l.lineTotal),
          taxable: l.taxable ?? true,
          position: l.position,
        })
      }
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'invoice',
      entityId: invoiceId,
      action: 'invoice.template_created',
      changes: null,
    })

    return {
      id: invoiceId,
      name: input.name,
      currency: input.currency ?? 'ILS',
      lineCount: parsedLines.length,
    }
  })
}

/**
 * Create a DRAFT invoice from a template.
 * Copies all line items; sets customer_id from input; is_template=false.
 * Does NOT assign invoice_number (assigned at send time per IL tax law).
 */
export async function createInvoiceFromTemplate(
  db: Db,
  tenantId: string,
  actorId: string,
  templateId: string,
  input: z.infer<typeof createFromTemplateSchema>,
): Promise<{ id: string; customerId: string; currency: string; total: string }> {
  return db.transaction(async (tx) => {
    // Verify template exists and belongs to this tenant
    const templateResult = await tx.execute(
      sql`SELECT id, currency, subtotal, total, notes
          FROM invoices
          WHERE id = ${templateId}::uuid
            AND tenant_id = ${tenantId}::uuid
            AND status = 'DRAFT'
            AND is_template = true
          LIMIT 1`,
    )

    const template = templateResult[0] as {
      id: string
      currency: string
      subtotal: string
      total: string
      notes: string | null
    } | undefined

    if (!template) {
      throw new Error('Template not found')
    }

    // Strip template name (first line) from notes for the new draft
    const originalNotes = template.notes ?? ''
    const notesLines = originalNotes.split('\n')
    const draftNotes = notesLines.length > 1 ? notesLines.slice(1).join('\n') || null : null

    // Create new draft invoice (customer_id required for non-template)
    const newInvoiceResult = await tx.execute(
      sql`INSERT INTO invoices (
            id, tenant_id, customer_id, status, currency, subtotal, vat_amount,
            total, amount_paid, notes, source, is_template, created_by,
            created_at, updated_at
          ) VALUES (
            gen_random_uuid(),
            ${tenantId}::uuid,
            ${input.customerId}::uuid,
            'DRAFT',
            ${template.currency},
            ${template.subtotal},
            '0',
            ${template.subtotal},
            '0',
            ${draftNotes},
            'manual',
            false,
            ${actorId}::uuid,
            NOW(),
            NOW()
          )
          RETURNING id`,
    )

    const newInvoiceId = (newInvoiceResult[0] as { id: string }).id

    // Copy lines from template
    const templateLines = await tx
      .select()
      .from(invoiceLines)
      .where(eq(invoiceLines.invoiceId, templateId))
      .orderBy(invoiceLines.position)

    if (templateLines.length > 0) {
      await tx.insert(invoiceLines).values(
        templateLines.map((l) => ({
          invoiceId: newInvoiceId,
          tenantId,
          description: l.description,
          quantity: l.quantity,
          unitPrice: l.unitPrice,
          discountPct: l.discountPct,
          lineTotal: l.lineTotal,
          taxable: l.taxable,
          position: l.position,
        })),
      )
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'invoice',
      entityId: newInvoiceId,
      action: 'invoice.created_from_template',
      changes: { templateId: [null, templateId] },
    })

    return {
      id: newInvoiceId,
      customerId: input.customerId,
      currency: template.currency,
      total: template.subtotal,
    }
  })
}

/**
 * Count DRAFT (non-template) invoices for a tenant.
 * Used for the main invoice list "X drafts" pill.
 */
export async function countDraftInvoices(db: Db, tenantId: string): Promise<number> {
  const result = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM invoices
        WHERE tenant_id = ${tenantId}::uuid
          AND status = 'DRAFT'
          AND (is_template IS NULL OR is_template = false)`,
  )
  return (result[0] as { total: number } | undefined)?.total ?? 0
}

/**
 * Count invoice templates for a tenant.
 */
export async function countTemplates(db: Db, tenantId: string): Promise<number> {
  const result = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM invoices
        WHERE tenant_id = ${tenantId}::uuid
          AND status = 'DRAFT'
          AND is_template = true`,
  )
  return (result[0] as { total: number } | undefined)?.total ?? 0
}

/**
 * Get a single template by ID (tenant-scoped).
 * Returns null if not found or not a template.
 */
export async function getTemplate(
  db: Db,
  tenantId: string,
  templateId: string,
): Promise<TemplateItem | null> {
  const result = await db.execute(
    sql`SELECT
          i.id,
          i.notes,
          i.currency,
          i.created_at,
          i.updated_at,
          COUNT(il.id)::int AS line_count
        FROM invoices i
        LEFT JOIN invoice_lines il ON il.invoice_id = i.id
        WHERE i.id = ${templateId}::uuid
          AND i.tenant_id = ${tenantId}::uuid
          AND i.status = 'DRAFT'
          AND i.is_template = true
        GROUP BY i.id, i.notes, i.currency, i.created_at, i.updated_at
        LIMIT 1`,
  )

  const row = result[0] as {
    id: string
    notes: string | null
    currency: string
    created_at: string
    updated_at: string
    line_count: number
  } | undefined

  if (!row) return null

  return {
    id: row.id,
    name: row.notes?.split('\n')[0] ?? 'Unnamed Template',
    currency: row.currency,
    notes: row.notes ?? null,
    lineCount: row.line_count,
    createdAt: new Date(row.created_at).toISOString(),
    updatedAt: new Date(row.updated_at).toISOString(),
  }
}

/**
 * Delete a template (DRAFT + is_template=true only).
 * Throws if not found, not a template, or not DRAFT.
 */
export async function deleteTemplate(
  db: Db,
  tenantId: string,
  templateId: string,
  actorId: string,
): Promise<void> {
  return db.transaction(async (tx) => {
    const result = await tx.execute(
      sql`SELECT id, status
          FROM invoices
          WHERE id = ${templateId}::uuid
            AND tenant_id = ${tenantId}::uuid
            AND is_template = true
          LIMIT 1`,
    )
    const row = result[0] as { id: string; status: string } | undefined

    if (!row) throw new Error('Template not found')
    if (row.status !== 'DRAFT') throw new Error('Only DRAFT templates can be deleted')

    await tx.delete(invoiceLines).where(eq(invoiceLines.invoiceId, templateId))
    await tx.execute(
      sql`DELETE FROM invoices
          WHERE id = ${templateId}::uuid
            AND tenant_id = ${tenantId}::uuid
            AND is_template = true`,
    )

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'invoice',
      entityId: templateId,
      action: 'invoice.template_deleted',
      changes: null,
    })
  })
}