/**
 * Bulk invoice generation query helpers — bulk-invoice-generation (P048).
 *
 * Architecture:
 *   ≤ 20 customers → inline generation, returns created invoice summaries.
 *   > 20 customers → dispatches to CF Queue (bulk_action), returns job_id.
 *                    Job tracked in import_jobs (type='bulk_action',
 *                    columnMappings contains job meta/params).
 *
 * The job consumer (spec 42 / bulk-operations) processes the queue message and
 * updates import_jobs progress columns (successCount, errorCount, skippedCount).
 *
 * Per-customer results live in import_job_results rows:
 *   status   = 'success' | 'skipped' | 'error'
 *   entityId = created invoice UUID (success only)
 *   rawRow   = { customerId, customerName, reason?, invoiceNumber? }
 *
 * Invoice content rules (spec §"Invoice Content Rules"):
 *   Time entries:  grouped by project→task, one line item per group.
 *   Expenses:      one line item per approved billable expense.
 *   Milestones:    one line item per completed uninvoiced milestone.
 *   Defaults:      payment_terms_days, tax_rate, prefix from tenant_settings.
 *
 * NOTE: Expense queries reference customer_id, billable, invoice_id columns on
 * expenses table — these are wave-7 COLLISION-2 ALTERs applied by the integrator.
 * Until those columns land, expense lines are omitted gracefully via raw SQL.
 */
import { eq, and, sql, inArray } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { customers } from '../schema/customers'
import { invoices } from '../schema/invoices'
import { importJobs, importJobResults } from '../schema/data-import'
import { projectMilestones } from '../schema/project-milestones'
import { createInvoiceInTx } from './invoices'
import { createNotification } from './notifications'

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

export const bulkGeneratePreviewSchema = z.object({
  periodStart: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  periodEnd: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  includeTime: z.boolean().default(true),
  includeExpenses: z.boolean().default(true),
  includeMilestones: z.boolean().default(false),
})

export const bulkGenerateSchema = bulkGeneratePreviewSchema.extend({
  customerIds: z.array(z.string().uuid()).min(1).max(200),
})

export type BulkGeneratePreviewInput = z.infer<typeof bulkGeneratePreviewSchema>
export type BulkGenerateInput = z.infer<typeof bulkGenerateSchema>

// ── Preview types ─────────────────────────────────────────────────────────────

export interface CustomerBillingPreview {
  customerId: string
  customerName: string
  timeHours: number
  expenseTotal: number
  milestoneTotal: number
  invoiceTotal: number
  hasOpenInvoice: boolean
  linesPreview?: Array<{ description: string; amount: number }>
}

export interface BulkPreviewResult {
  customers: CustomerBillingPreview[]
}

// ── Preview query ─────────────────────────────────────────────────────────────

/**
 * Compute what invoices would be generated: aggregate billable items per
 * customer for the given period. Marks customers that already have an
 * open/unpaid invoice (pre-deselection hint for the UI).
 *
 * Time entries: uses time_entries.billable + time_entries.invoice_id (wave-7 leaf 3 alter).
 * Expenses: uses expenses.customer_id + expenses.billable + expenses.invoice_id
 *   (wave-7 integrator COLLISION-2 alter). Uses raw SQL to avoid Drizzle schema mismatch
 *   before those columns land.
 */
export async function getBulkGeneratePreview(
  db: Db,
  tenantId: string,
  input: BulkGeneratePreviewInput,
): Promise<BulkPreviewResult> {
  const { periodStart, periodEnd, includeTime, includeExpenses, includeMilestones } = input

  const periodStartDate = new Date(`${periodStart}T00:00:00Z`)
  const periodEndDate = new Date(`${periodEnd}T23:59:59Z`)

  // 1. All customers for this tenant
  const allCustomers = await db
    .select({ id: customers.id, name: customers.name })
    .from(customers)
    .where(
      and(
        eq(customers.tenantId, tenantId),
        sql`${customers.status} != 'archived'`,
      ),
    )

  const customerMap = new Map(allCustomers.map((c) => [c.id, c.name]))

  // 2. Time entries: group by project to get customer association.
  //    time_entries.invoice_id added by leaf 3 alter; use raw sql for safety.
  const timeByCustomer = new Map<string, number>()
  const timeValueByCustomer = new Map<string, number>()
  const linesPreviewByCustomer = new Map<string, Array<{ description: string; amount: number }>>()
  if (includeTime) {
    const timeRows = await db.execute<{
      customer_id: string
      project_name: string
      task_name: string | null
      total_seconds: string
      line_amount: string
    }>(sql`
      SELECT
        p.customer_id,
        p.name AS project_name,
        t.title AS task_name,
        sum(te.duration_seconds)::bigint AS total_seconds,
        sum(
          (te.duration_seconds::numeric / 3600)
          * COALESCE(p.hourly_rate, (p.billing_config->>'rate_per_hour')::numeric, 0)
        )::numeric AS line_amount
      FROM time_entries te
      JOIN projects p ON p.id = te.project_id
      LEFT JOIN tasks t ON t.id = te.task_id
      WHERE te.tenant_id = ${tenantId}::uuid
        AND te.billable = true
        AND (te.invoice_id IS NULL)
        AND te.stopped_at IS NOT NULL
        AND te.started_at >= ${periodStartDate}::timestamptz
        AND te.started_at <= ${periodEndDate}::timestamptz
        AND p.customer_id IS NOT NULL
      GROUP BY p.customer_id, p.name, t.title
    `)

    for (const row of timeRows) {
      if (row.customer_id) {
        const existing = timeByCustomer.get(row.customer_id) ?? 0
        timeByCustomer.set(row.customer_id, existing + Number(row.total_seconds ?? 0) / 3600)
        timeValueByCustomer.set(
          row.customer_id,
          (timeValueByCustomer.get(row.customer_id) ?? 0) + Number(row.line_amount ?? 0),
        )
        const lines = linesPreviewByCustomer.get(row.customer_id) ?? []
        lines.push({
          description: row.task_name
            ? `Time — ${row.project_name} — ${row.task_name}`
            : `Time — ${row.project_name}`,
          amount: Number(row.line_amount ?? 0),
        })
        linesPreviewByCustomer.set(row.customer_id, lines)
      }
    }
  }

  // 3. Approved billable expenses aggregated by customer.
  //    customer_id, billable, invoice_id on expenses table are wave-7 COLLISION-2 alters.
  //    Using raw SQL to avoid Drizzle snapshot mismatch before those columns land.
  const expensesByCustomer = new Map<string, number>()
  if (includeExpenses) {
    const expenseRows = await db.execute<{
      customer_id: string
      total: string
    }>(sql`
      SELECT customer_id, sum(amount)::numeric AS total
      FROM expenses
      WHERE tenant_id = ${tenantId}::uuid
        AND status = 'COMPLETED'
        AND billable = true
        AND (invoice_id IS NULL)
        AND expense_date >= ${periodStart}::date
        AND expense_date <= ${periodEnd}::date
        AND customer_id IS NOT NULL
        AND project_id IS NOT NULL
      GROUP BY customer_id
    `)

    for (const row of expenseRows) {
      if (row.customer_id) {
        expensesByCustomer.set(row.customer_id, Number(row.total ?? 0))
        const lines = linesPreviewByCustomer.get(row.customer_id) ?? []
        lines.push({ description: 'Expenses', amount: Number(row.total ?? 0) })
        linesPreviewByCustomer.set(row.customer_id, lines)
      }
    }
  }

  const milestonesByCustomer = new Map<string, number>()
  if (includeMilestones) {
    const milestoneRows = await db.execute<{ customer_id: string; total: string }>(sql`
      SELECT p.customer_id, sum(pm.amount)::numeric AS total
      FROM project_milestones pm
      JOIN projects p ON p.id = pm.project_id
      WHERE pm.tenant_id = ${tenantId}::uuid
        AND pm.status = 'completed'
        AND pm.invoice_id IS NULL
        AND pm.completed_at IS NOT NULL
        AND pm.completed_at >= ${periodStartDate}::timestamptz
        AND pm.completed_at <= ${periodEndDate}::timestamptz
        AND p.customer_id IS NOT NULL
      GROUP BY p.customer_id
    `)
    for (const row of milestoneRows) {
      milestonesByCustomer.set(row.customer_id, Number(row.total ?? 0))
      const lines = linesPreviewByCustomer.get(row.customer_id) ?? []
      lines.push({ description: 'Milestones', amount: Number(row.total ?? 0) })
      linesPreviewByCustomer.set(row.customer_id, lines)
    }
  }

  // 4. Open invoices per customer (pre-deselection hint)
  const openInvoiceCustomers = new Set<string>()
  const openInvoiceRows = await db
    .select({ customerId: invoices.customerId })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.status} IN ('DRAFT','SENT','APPROVED','TAX_ISSUED','PARTIALLY_PAID')`,
        sql`COALESCE(${invoices.issueDate}, ${invoices.createdAt}::date) >= ${periodStart}::date`,
        sql`COALESCE(${invoices.issueDate}, ${invoices.createdAt}::date) <= ${periodEnd}::date`,
      ),
    )

  for (const row of openInvoiceRows) {
    openInvoiceCustomers.add(row.customerId ?? '')
  }

  // 5. Build preview per customer (include only those with billable items)
  const result: CustomerBillingPreview[] = []
  for (const [customerId, customerName] of customerMap) {
    const timeHours = timeByCustomer.get(customerId) ?? 0
    const expenseTotal = expensesByCustomer.get(customerId) ?? 0
    const milestoneTotal = milestonesByCustomer.get(customerId) ?? 0
    const invoiceTotal = (timeValueByCustomer.get(customerId) ?? 0) + expenseTotal + milestoneTotal

    if (timeHours > 0 || expenseTotal > 0 || milestoneTotal > 0) {
      result.push({
        customerId,
        customerName,
        timeHours,
        expenseTotal,
        milestoneTotal,
        invoiceTotal,
        hasOpenInvoice: openInvoiceCustomers.has(customerId),
        linesPreview: linesPreviewByCustomer.get(customerId) ?? [],
      })
    }
  }

  return { customers: result }
}

// ── Job creation (large batch path: > 20 customers) ──────────────────────────

export interface CreateBulkGenerationJobInput {
  tenantId: string
  actorId: string
  customerIds: string[]
  periodStart: string
  periodEnd: string
  includeTime: boolean
  includeExpenses: boolean
  includeMilestones: boolean
}

/**
 * Create an import_jobs row for a large bulk-generation run (>20 customers).
 * Returns the job ID to return to the caller immediately.
 * The actual generation is handled by the Queue consumer (bulk-operations, spec 42).
 * Job parameters are stored in columnMappings JSONB.
 */
export async function createBulkGenerationJob(
  db: Db,
  input: CreateBulkGenerationJobInput,
): Promise<string> {
  const meta = {
    action: 'invoice_generate',
    periodStart: input.periodStart,
    periodEnd: input.periodEnd,
    customerIds: input.customerIds,
    includeTime: input.includeTime,
    includeExpenses: input.includeExpenses,
    includeMilestones: input.includeMilestones,
  }

  const [row] = await db
    .insert(importJobs)
    .values({
      tenantId: input.tenantId,
      createdBy: input.actorId,
      type: 'bulk_action',
      status: 'pending',
      // r2Key, originalFilename, mimeType, fileSizeBytes are NOT NULL in import_jobs
      // (it was designed for file uploads). Bulk-generation has no file; use sentinel values.
      r2Key: '',
      originalFilename: `bulk-invoice-generate-${input.periodStart}-to-${input.periodEnd}`,
      mimeType: 'application/json',
      fileSizeBytes: 0,
      columnMapping: meta,
      totalRows: input.customerIds.length,
      successCount: 0,
      errorCount: 0,
      skippedCount: 0,
    })
    .returning({ id: importJobs.id })

  if (!row) throw new Error('Failed to create bulk generation job')
  return row.id
}

// ── Job status read ───────────────────────────────────────────────────────────

export interface BulkJobResult {
  customerId: string
  customerName: string
  status: 'created' | 'skipped' | 'error'
  invoiceNumber?: string
  reason?: string
}

export interface BulkJobStatus {
  jobId: string
  status: 'pending' | 'processing' | 'completed' | 'failed'
  total: number
  processed: number
  created: number
  skipped: number
  failed: number
  results: BulkJobResult[]
  periodStart?: string
  periodEnd?: string
}

/**
 * Read the status of a bulk generation job.
 * Verifies tenant ownership before returning.
 */
export async function getBulkGenerationJobStatus(
  db: Db,
  tenantId: string,
  jobId: string,
): Promise<BulkJobStatus | null> {
  const [job] = await db
    .select()
    .from(importJobs)
    .where(
      and(
        eq(importJobs.id, jobId),
        eq(importJobs.tenantId, tenantId),
        eq(importJobs.type, 'bulk_action'),
      ),
    )
    .limit(1)

  if (!job) return null

  // Read per-customer result rows
  const resultRows = await db
    .select()
    .from(importJobResults)
    .where(eq(importJobResults.importJobId, jobId))
    .orderBy(importJobResults.rowNumber)

  const results: BulkJobResult[] = resultRows.map((r) => {
    const raw = (r.originalData ? JSON.parse(r.originalData) : null) as Record<string, string> | null
    return {
      customerId: raw?.customerId ?? '',
      customerName: raw?.customerName ?? '',
      status: r.status === 'success' ? 'created' : r.status === 'skipped' ? 'skipped' : 'error',
      invoiceNumber: raw?.invoiceNumber,
      reason: r.status !== 'success' ? (r.message ?? undefined) : undefined,
    }
  })

  const meta = job.columnMapping as Record<string, unknown> | null

  return {
    jobId: job.id,
    status: job.status as BulkJobStatus['status'],
    total: job.totalRows ?? 0,
    processed: (job.successCount ?? 0) + (job.errorCount ?? 0) + (job.skippedCount ?? 0),
    created: job.successCount ?? 0,
    skipped: job.skippedCount ?? 0,
    failed: job.errorCount ?? 0,
    results,
    periodStart: meta?.periodStart as string | undefined,
    periodEnd: meta?.periodEnd as string | undefined,
  }
}

// ── Billable item fetch (inline generation, ≤20 customers) ───────────────────

export interface LineItemDraft {
  description: string
  quantity: number
  unitPrice: number
  discountPct: number
  taxable: boolean
  position: number
}

export interface CustomerBillableItems {
  timeLines: LineItemDraft[]
  expenseLines: LineItemDraft[]
  milestoneLines: LineItemDraft[]
  timeEntryIds: string[]
  expenseIds: string[]
  milestoneIds: string[]
  isEmpty: boolean
}

/**
 * Fetch billable time + expense line items for a single customer in a period.
 * Used by the inline generation path (≤20 customers).
 * Uses raw SQL for expense columns that are wave-7 integrator alters.
 */
export async function getCustomerBillableItems(
  db: Db,
  tenantId: string,
  customerId: string,
  periodStart: string,
  periodEnd: string,
  includeTime: boolean,
  includeExpenses: boolean,
  includeMilestones = false,
): Promise<CustomerBillableItems> {
  const periodStartDate = new Date(`${periodStart}T00:00:00Z`)
  const periodEndDate = new Date(`${periodEnd}T23:59:59Z`)
  const timeLines: LineItemDraft[] = []
  const expenseLines: LineItemDraft[] = []
  const milestoneLines: LineItemDraft[] = []
  const timeEntryIds: string[] = []
  const expenseIds: string[] = []
  const milestoneIds: string[] = []

  if (includeTime) {
    const timeRows = await db.execute<{
      entry_ids: string[]
      project_id: string
      project_name: string
      task_name: string | null
      total_seconds: string
      hourly_rate: string
    }>(sql`
      SELECT
        array_agg(te.id ORDER BY te.started_at) AS entry_ids,
        p.id       AS project_id,
        p.name     AS project_name,
        t.title    AS task_name,
        sum(te.duration_seconds)::bigint AS total_seconds,
        COALESCE(max(p.hourly_rate), max((p.billing_config->>'rate_per_hour')::numeric), 0)::numeric AS hourly_rate
      FROM time_entries te
      JOIN projects p ON p.id = te.project_id
      LEFT JOIN tasks t ON t.id = te.task_id
      WHERE te.tenant_id = ${tenantId}::uuid
        AND p.customer_id = ${customerId}::uuid
        AND te.billable = true
        AND (te.invoice_id IS NULL)
        AND te.stopped_at IS NOT NULL
        AND te.started_at >= ${periodStartDate}::timestamptz
        AND te.started_at <= ${periodEndDate}::timestamptz
      GROUP BY p.id, p.name, t.title
      ORDER BY p.name, t.title NULLS FIRST
    `)

    let position = 0
    for (const row of timeRows) {
      const hours = Number(row.total_seconds ?? 0) / 3600
      if (hours > 0) {
        timeEntryIds.push(...((row.entry_ids ?? []) as string[]))
        timeLines.push({
          description: row.task_name
            ? `Time — ${row.project_name ?? row.project_id} — ${row.task_name}`
            : `Time — ${row.project_name ?? row.project_id}`,
          quantity: Math.round(hours * 100) / 100,
          unitPrice: Number(row.hourly_rate ?? 0),
          discountPct: 0,
          taxable: true,
          position: position++,
        })
      }
    }
  }

  if (includeExpenses) {
    // Expense columns customer_id, billable, invoice_id are wave-7 COLLISION-2 alters.
    const expenseRows = await db.execute<{
      id: string
      description: string
      amount: string
    }>(sql`
      SELECT id, description, amount::numeric
      FROM expenses
      WHERE tenant_id = ${tenantId}::uuid
        AND customer_id = ${customerId}::uuid
        AND status = 'COMPLETED'
        AND billable = true
        AND (invoice_id IS NULL)
        AND expense_date >= ${periodStart}::date
        AND expense_date <= ${periodEnd}::date
        AND project_id IS NOT NULL
      ORDER BY expense_date
    `)

    let position = timeLines.length
    for (const row of expenseRows) {
      expenseIds.push(row.id)
      expenseLines.push({
        description: row.description ?? 'Expense',
        quantity: 1,
        unitPrice: Number(row.amount ?? 0),
        discountPct: 0,
        taxable: true,
        position: position++,
      })
    }
  }

  if (includeMilestones) {
    const milestoneRows = await db.execute<{
      id: string
      name: string
      amount: string
    }>(sql`
      SELECT pm.id, pm.name, pm.amount::text AS amount
      FROM project_milestones pm
      JOIN projects p ON p.id = pm.project_id
      WHERE pm.tenant_id = ${tenantId}::uuid
        AND p.customer_id = ${customerId}::uuid
        AND pm.status = 'completed'
        AND pm.invoice_id IS NULL
        AND pm.completed_at IS NOT NULL
        AND pm.completed_at >= ${periodStartDate}::timestamptz
        AND pm.completed_at <= ${periodEndDate}::timestamptz
    `)

    let position = timeLines.length + expenseLines.length
    for (const row of milestoneRows) {
      milestoneIds.push(row.id)
      milestoneLines.push({
        description: row.name,
        quantity: 1,
        unitPrice: Number(row.amount ?? 0),
        discountPct: 0,
        taxable: true,
        position: position++,
      })
    }
  }

  return {
    timeLines,
    expenseLines,
    milestoneLines,
    timeEntryIds,
    expenseIds,
    milestoneIds,
    isEmpty: timeLines.length === 0 && expenseLines.length === 0 && milestoneLines.length === 0,
  }
}

export interface BulkInvoiceQueueJob {
  type: 'bulk_action'
  action: 'invoice_generate'
  jobId: string
  tenantId: string
  actorId: string
  params: BulkGenerateInput
}

export async function listScopedCustomers(
  db: Db,
  tenantId: string,
  customerIds: string[],
): Promise<Array<{ id: string; name: string }>> {
  if (customerIds.length === 0) return []

  return db.execute<{ id: string; name: string }>(sql`
    SELECT id, name
    FROM customers
    WHERE tenant_id = ${tenantId}::uuid
      AND id = ANY(${customerIds}::uuid[])
  `) as Promise<Array<{ id: string; name: string }>>
}

export async function generateInvoicesForCustomers(
  db: Db,
  input: {
    tenantId: string
    actorId: string
    customerIds: string[]
    periodStart: string
    periodEnd: string
    includeTime: boolean
    includeExpenses: boolean
    includeMilestones: boolean
    source?: 'hourly_auto'
  },
): Promise<{
  invoices: Array<{ id: string; customerId: string; customerName: string; number: string | null }>
  skipped: Array<{ customerId: string; customerName: string; reason: string }>
  failed: Array<{ customerId: string; customerName: string; reason: string }>
}> {
  const scopedCustomers = await listScopedCustomers(db, input.tenantId, input.customerIds)
  const customerMap = new Map(scopedCustomers.map((row) => [row.id, row.name]))

  if (scopedCustomers.length !== input.customerIds.length) {
    throw new Error('One or more customer_ids do not belong to the current tenant')
  }

  const invoicesCreated: Array<{ id: string; customerId: string; customerName: string; number: string | null }> = []
  const skipped: Array<{ customerId: string; customerName: string; reason: string }> = []
  const failed: Array<{ customerId: string; customerName: string; reason: string }> = []

  for (const customerId of input.customerIds) {
    const customerName = customerMap.get(customerId) ?? customerId
    try {
      const items = await getCustomerBillableItems(
        db,
        input.tenantId,
        customerId,
        input.periodStart,
        input.periodEnd,
        input.includeTime,
        input.includeExpenses,
        input.includeMilestones,
      )

      if (items.isEmpty) {
        skipped.push({ customerId, customerName, reason: 'No billable items in period' })
        continue
      }

      const invoice = await db.transaction(async (tx) => {
        const created = await createInvoiceInTx(
          tx,
          input.tenantId,
          input.actorId,
          {
            customerId,
            currency: 'ILS',
            source: input.source ?? 'hourly_auto',
            billedEntryIds: items.timeEntryIds,
            lines: [...items.timeLines, ...items.expenseLines, ...items.milestoneLines],
          },
          'IL',
        )

        if (items.expenseIds.length > 0) {
          await tx.execute(sql`
            UPDATE expenses
            SET billed_at = NOW(),
                invoice_id = ${created.id}::uuid,
                updated_at = NOW()
            WHERE tenant_id = ${input.tenantId}::uuid
              AND id = ANY(${items.expenseIds}::uuid[])
              AND invoice_id IS NULL
          `)
        }

        if (items.milestoneIds.length > 0) {
          await tx
            .update(projectMilestones)
            .set({ invoiceId: created.id, updatedAt: new Date() })
            .where(
              and(
                eq(projectMilestones.tenantId, input.tenantId),
                inArray(projectMilestones.id, items.milestoneIds),
                sql`${projectMilestones.invoiceId} IS NULL`,
              ),
            )
        }

        return created
      })

      invoicesCreated.push({
        id: invoice.id,
        customerId,
        customerName,
        number: invoice.invoiceNumber,
      })
    } catch (error) {
      failed.push({
        customerId,
        customerName,
        reason: error instanceof Error ? error.message : 'Unknown error',
      })
    }
  }

  return { invoices: invoicesCreated, skipped, failed }
}

export async function processBulkInvoiceGenerationJob(
  db: Db,
  job: BulkInvoiceQueueJob,
): Promise<void> {
  await db
    .update(importJobs)
    .set({ status: 'processing', startedAt: new Date(), updatedAt: new Date() })
    .where(and(eq(importJobs.id, job.jobId), eq(importJobs.tenantId, job.tenantId)))

  const result = await generateInvoicesForCustomers(db, {
    tenantId: job.tenantId,
    actorId: job.actorId,
    customerIds: job.params.customerIds,
    periodStart: job.params.periodStart,
    periodEnd: job.params.periodEnd,
    includeTime: job.params.includeTime,
    includeExpenses: job.params.includeExpenses,
    includeMilestones: job.params.includeMilestones,
  })

  const allRows = [
    ...result.invoices.map((invoice, index) => ({
      importJobId: job.jobId,
      tenantId: job.tenantId,
      rowNumber: index + 1,
      status: 'success' as const,
      message: null,
      entityId: invoice.id,
      originalData: JSON.stringify({
        customerId: invoice.customerId,
        customerName: invoice.customerName,
        invoiceNumber: invoice.number,
      }),
    })),
    ...result.skipped.map((row, index) => ({
      importJobId: job.jobId,
      tenantId: job.tenantId,
      rowNumber: result.invoices.length + index + 1,
      status: 'skipped' as const,
      message: row.reason,
      entityId: null,
      originalData: JSON.stringify({
        customerId: row.customerId,
        customerName: row.customerName,
      }),
    })),
    ...result.failed.map((row, index) => ({
      importJobId: job.jobId,
      tenantId: job.tenantId,
      rowNumber: result.invoices.length + result.skipped.length + index + 1,
      status: 'error' as const,
      message: row.reason,
      entityId: null,
      originalData: JSON.stringify({
        customerId: row.customerId,
        customerName: row.customerName,
      }),
    })),
  ]

  if (allRows.length > 0) {
    await db.insert(importJobResults).values(allRows)
  }

  await db
    .update(importJobs)
    .set({
      status: 'completed',
      completedAt: new Date(),
      updatedAt: new Date(),
      successCount: result.invoices.length,
      skippedCount: result.skipped.length,
      errorCount: result.failed.length,
      rowsProcessed: result.invoices.length + result.skipped.length + result.failed.length,
      errorMessage: result.failed.length > 0 ? `${result.failed.length} customer(s) failed` : null,
    })
    .where(and(eq(importJobs.id, job.jobId), eq(importJobs.tenantId, job.tenantId)))

  await createNotification(db, {
    tenantId: job.tenantId,
    userId: job.actorId,
    type: 'bulk_action_complete',
    titleKey: 'notifications.bulk_invoice_generation_complete.title',
    bodyKey: 'notifications.bulk_invoice_generation_complete.body',
    params: {
      count: result.invoices.length,
      periodStart: job.params.periodStart,
      periodEnd: job.params.periodEnd,
      jobId: job.jobId,
    },
    entityType: 'import_job',
    entityId: job.jobId,
  })
}