/**
 * Data import query helpers — data-import (P051).
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 *
 * Import flow:
 *   1. POST /api/imports         → createImportJob (status: pending)
 *   2. Queue dispatches job      → setImportJobProcessing (status: processing)
 *   3. Per-row processing        → insertImportJobResult (per row)
 *   4. Completion                → completeImportJob (status: completed | failed)
 */
import { and, desc, eq, sql, count, lt, or } from 'drizzle-orm'
import type { Db } from '../client'
import { importJobs, importJobResults } from '../schema/data-import'

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

export interface ImportJobObject {
  id: string
  tenant_id: string
  created_by: string
  type: string
  status: string
  r2_key: string
  original_filename: string
  mime_type: string
  file_size_bytes: number
  column_mapping: Record<string, string | null> | null
  total_rows: number | null
  rows_processed: number
  success_count: number
  skipped_count: number
  error_count: number
  started_at: string | null
  completed_at: string | null
  error_message: string | null
  created_at: string
  updated_at: string
}

export interface ImportJobResultObject {
  id: string
  import_job_id: string
  tenant_id: string
  row_number: number
  status: string
  message: string | null
  original_data: string | null
  entity_id: string | null
  created_at: string
}

export interface CreateImportJobInput {
  created_by: string
  type: 'customers' | 'invoices' | 'products' | 'time_entries' | 'bulk_action'
  r2_key: string
  original_filename: string
  mime_type: string
  file_size_bytes: number
  column_mapping?: Record<string, string | null>
}

export interface InsertImportJobResultInput {
  import_job_id: string
  tenant_id: string
  row_number: number
  status: 'success' | 'skipped' | 'error'
  message?: string
  original_data?: string
  entity_id?: string
}

// ── Serializers ───────────────────────────────────────────────────────────────

function serializeImportJob(row: typeof importJobs.$inferSelect): ImportJobObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    created_by: row.createdBy,
    type: row.type,
    status: row.status,
    r2_key: row.r2Key,
    original_filename: row.originalFilename,
    mime_type: row.mimeType,
    file_size_bytes: row.fileSizeBytes,
    column_mapping: (row.columnMapping ?? null) as Record<string, string | null> | null,
    total_rows: row.totalRows ?? null,
    rows_processed: row.rowsProcessed,
    success_count: row.successCount,
    skipped_count: row.skippedCount,
    error_count: row.errorCount,
    started_at: row.startedAt?.toISOString() ?? null,
    completed_at: row.completedAt?.toISOString() ?? null,
    error_message: row.errorMessage ?? null,
    created_at: row.createdAt.toISOString(),
    updated_at: row.updatedAt.toISOString(),
  }
}

function serializeImportJobResult(
  row: typeof importJobResults.$inferSelect,
): ImportJobResultObject {
  return {
    id: row.id,
    import_job_id: row.importJobId,
    tenant_id: row.tenantId,
    row_number: row.rowNumber,
    status: row.status,
    message: row.message ?? null,
    original_data: row.originalData ?? null,
    entity_id: row.entityId ?? null,
    created_at: row.createdAt.toISOString(),
  }
}

// ── listImportJobs ────────────────────────────────────────────────────────────

export async function listImportJobs(
  db: Db,
  tenantId: string,
  limit = 50,
  cursorId?: string,
): Promise<ImportJobObject[]> {
  const clampedLimit = Math.min(limit, 100)
  let cursorCreatedAt: Date | null = null

  if (cursorId) {
    const [cursorRow] = await db
      .select({ createdAt: importJobs.createdAt, id: importJobs.id })
      .from(importJobs)
      .where(and(eq(importJobs.tenantId, tenantId), eq(importJobs.id, cursorId)))
      .limit(1)

    cursorCreatedAt = cursorRow?.createdAt ?? null
  }

  const whereClause = cursorId && cursorCreatedAt
    ? and(
        eq(importJobs.tenantId, tenantId),
        or(
          lt(importJobs.createdAt, cursorCreatedAt),
          and(eq(importJobs.createdAt, cursorCreatedAt), lt(importJobs.id, cursorId)),
        ),
      )
    : eq(importJobs.tenantId, tenantId)

  const rows = await db
    .select()
    .from(importJobs)
    .where(whereClause)
    .orderBy(desc(importJobs.createdAt), desc(importJobs.id))
    .limit(clampedLimit)

  return rows.map(serializeImportJob)
}

// ── getImportJob ──────────────────────────────────────────────────────────────

export async function getImportJob(
  db: Db,
  tenantId: string,
  id: string,
): Promise<ImportJobObject | null> {
  const [row] = await db
    .select()
    .from(importJobs)
    .where(and(eq(importJobs.tenantId, tenantId), eq(importJobs.id, id)))
    .limit(1)

  return row ? serializeImportJob(row) : null
}

// ── createImportJob ───────────────────────────────────────────────────────────

export async function createImportJob(
  db: Db,
  tenantId: string,
  input: CreateImportJobInput,
): Promise<ImportJobObject> {
  const [row] = await db
    .insert(importJobs)
    .values({
      tenantId,
      createdBy: input.created_by,
      type: input.type,
      status: 'pending',
      r2Key: input.r2_key,
      originalFilename: input.original_filename,
      mimeType: input.mime_type,
      fileSizeBytes: input.file_size_bytes,
      columnMapping: (input.column_mapping ?? null) as Record<string, string | null> | null,
    })
    .returning()

  if (!row) throw new Error('Failed to insert import job')
  return serializeImportJob(row)
}

// ── setImportJobProcessing ────────────────────────────────────────────────────

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

// ── updateImportJobMappings ───────────────────────────────────────────────────

/**
 * Update the column_mapping after upload / mapping UI confirmation.
 * Only allowed when status = 'pending'.
 */
export async function updateImportJobMappings(
  db: Db,
  tenantId: string,
  id: string,
  columnMapping: Record<string, string | null>,
): Promise<ImportJobObject | null> {
  const [row] = await db
    .update(importJobs)
    .set({
      columnMapping: columnMapping as Record<string, string | null>,
      updatedAt: new Date(),
    })
    .where(
      and(
        eq(importJobs.tenantId, tenantId),
        eq(importJobs.id, id),
        eq(importJobs.status, 'pending'),
      ),
    )
    .returning()

  return row ? serializeImportJob(row) : null
}

// ── completeImportJob ─────────────────────────────────────────────────────────

export async function completeImportJob(
  db: Db,
  tenantId: string,
  id: string,
  outcome: {
    status: 'completed' | 'failed'
    successCount: number
    skippedCount: number
    errorCount: number
    rowsProcessed: number
    errorMessage?: string
  },
): Promise<void> {
  await db
    .update(importJobs)
    .set({
      status: outcome.status,
      successCount: outcome.successCount,
      skippedCount: outcome.skippedCount,
      errorCount: outcome.errorCount,
      rowsProcessed: outcome.rowsProcessed,
      errorMessage: outcome.errorMessage ?? null,
      completedAt: new Date(),
      updatedAt: new Date(),
    })
    .where(and(eq(importJobs.tenantId, tenantId), eq(importJobs.id, id)))
}

// ── insertImportJobResult ─────────────────────────────────────────────────────

export async function insertImportJobResult(
  db: Db,
  input: InsertImportJobResultInput,
): Promise<ImportJobResultObject> {
  const [row] = await db
    .insert(importJobResults)
    .values({
      importJobId: input.import_job_id,
      tenantId: input.tenant_id,
      rowNumber: input.row_number,
      status: input.status,
      message: input.message ?? null,
      originalData: input.original_data ?? null,
      entityId: input.entity_id ?? null,
    })
    .returning()

  if (!row) throw new Error('Failed to insert import job result')
  return serializeImportJobResult(row)
}

// ── listImportJobResults ──────────────────────────────────────────────────────

export async function listImportJobResults(
  db: Db,
  importJobId: string,
  statusFilter?: 'success' | 'skipped' | 'error',
  limit = 100,
): Promise<ImportJobResultObject[]> {
  const clampedLimit = Math.min(limit, 500)

  const conditions = statusFilter
    ? [eq(importJobResults.importJobId, importJobId), eq(importJobResults.status, statusFilter)]
    : [eq(importJobResults.importJobId, importJobId)]

  const rows = await db
    .select()
    .from(importJobResults)
    .where(and(...conditions))
    .orderBy(importJobResults.rowNumber)
    .limit(clampedLimit)

  return rows.map(serializeImportJobResult)
}

// ── countImportJobResults ─────────────────────────────────────────────────────

export async function countImportJobResultsByStatus(
  db: Db,
  importJobId: string,
): Promise<{ success: number; skipped: number; error: number }> {
  const rows = await db
    .select({
      status: importJobResults.status,
      cnt: count(),
    })
    .from(importJobResults)
    .where(eq(importJobResults.importJobId, importJobId))
    .groupBy(importJobResults.status)

  const result = { success: 0, skipped: 0, error: 0 }
  for (const row of rows) {
    if (row.status === 'success') result.success = Number(row.cnt)
    else if (row.status === 'skipped') result.skipped = Number(row.cnt)
    else if (row.status === 'error') result.error = Number(row.cnt)
  }
  return result
}

// ── deleteImportJob ───────────────────────────────────────────────────────────

/**
 * Hard-delete an import job and cascade-delete all its results.
 * Only allowed when status is 'pending', 'completed', or 'failed'.
 */
export async function deleteImportJob(
  db: Db,
  tenantId: string,
  id: string,
): Promise<boolean> {
  const result = await db
    .delete(importJobs)
    .where(
      and(
        eq(importJobs.tenantId, tenantId),
        eq(importJobs.id, id),
        sql`${importJobs.status} IN ('pending','completed','failed')`,
      ),
    )
    .returning({ id: importJobs.id })

  return result.length > 0
}