/**
 * Audit log query helpers — audit-compliance (wave-11 leaf-D).
 *
 * Standalone functions scoped to the new partitioned `audit_log` table.
 * Route files import from '@zync/db/queries' only.
 */
import { and, desc, eq, gte, lt, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { auditLog } from '../schema/audit-log'

export interface AuditListOptions {
  tenantId: string
  entityType?: string
  entityId?: string
  from?: Date
  to?: Date
  limit?: number
  offset?: number
}

export interface AuditLogRow {
  id: string
  tenantId: string
  actorId: string | null
  actorType: string
  apiKeyId: string | null
  entityType: string
  entityId: string
  action: string
  changes: Record<string, [unknown, unknown]> | null
  requestId: string | null
  ip: string | null
  createdAt: string
}

function serializeRow(row: typeof auditLog.$inferSelect): AuditLogRow {
  return {
    id: row.id,
    tenantId: row.tenantId,
    actorId: row.actorId ?? null,
    actorType: row.actorType,
    apiKeyId: row.apiKeyId ?? null,
    entityType: row.entityType,
    entityId: row.entityId,
    action: row.action,
    changes: (row.changes as Record<string, [unknown, unknown]> | null) ?? null,
    requestId: row.requestId ?? null,
    ip: row.ip ?? null,
    createdAt: row.createdAt.toISOString(),
  }
}

function buildWhereConditions(opts: AuditListOptions) {
  const conditions = [eq(auditLog.tenantId, opts.tenantId)]
  if (opts.entityType) conditions.push(eq(auditLog.entityType, opts.entityType))
  if (opts.entityId) conditions.push(eq(auditLog.entityId, opts.entityId))
  if (opts.from) conditions.push(gte(auditLog.createdAt, opts.from))
  if (opts.to) conditions.push(lt(auditLog.createdAt, opts.to))
  return conditions
}

export async function listAuditLog(
  db: Db,
  opts: AuditListOptions,
): Promise<{ rows: AuditLogRow[]; total: number }> {
  const limit = opts.limit ?? 50
  const offset = opts.offset ?? 0
  const conditions = buildWhereConditions(opts)

  const [rows, countResult] = await Promise.all([
    db
      .select()
      .from(auditLog)
      .where(and(...conditions))
      .orderBy(desc(auditLog.createdAt))
      .limit(limit)
      .offset(offset),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(auditLog)
      .where(and(...conditions)),
  ])

  return {
    rows: rows.map(serializeRow),
    total: countResult[0]?.count ?? 0,
  }
}

export async function listAuditLogSystem(
  db: Db,
  opts: Omit<AuditListOptions, 'tenantId'> & { tenantId?: string },
): Promise<{ rows: AuditLogRow[]; total: number }> {
  const limit = opts.limit ?? 50
  const offset = opts.offset ?? 0

  const conditions = []
  if (opts.tenantId) conditions.push(eq(auditLog.tenantId, opts.tenantId))
  if (opts.entityType) conditions.push(eq(auditLog.entityType, opts.entityType))
  if (opts.entityId) conditions.push(eq(auditLog.entityId, opts.entityId))
  if (opts.from) conditions.push(gte(auditLog.createdAt, opts.from))
  if (opts.to) conditions.push(lt(auditLog.createdAt, opts.to))

  const whereClause = conditions.length > 0 ? and(...conditions) : undefined

  const [rows, countResult] = await Promise.all([
    db
      .select()
      .from(auditLog)
      .where(whereClause)
      .orderBy(desc(auditLog.createdAt))
      .limit(limit)
      .offset(offset),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(auditLog)
      .where(whereClause),
  ])

  return {
    rows: rows.map(serializeRow),
    total: countResult[0]?.count ?? 0,
  }
}

export async function writeAuditLog(
  db: Db,
  values: {
    tenantId: string
    actorId?: string | null
    actorType: 'user' | 'system' | 'api_key' | 'portal_customer'
    apiKeyId?: string | null
    entityType: string
    entityId: string
    action: string
    changes?: Record<string, [unknown, unknown]> | null
    requestId?: string | null
    ip?: string | null
  },
): Promise<void> {
  await db.insert(auditLog).values({
    tenantId: values.tenantId,
    actorId: values.actorId ?? null,
    actorType: values.actorType,
    apiKeyId: values.apiKeyId ?? null,
    entityType: values.entityType,
    entityId: values.entityId,
    action: values.action,
    changes: values.changes ?? null,
    requestId: values.requestId ?? null,
    ip: values.ip ?? null,
  })
}

/**
 * Delete audit_log rows older than retentionDays.
 * Used by the monthly data-retention-purge cron.
 */
export async function deleteOldAuditLogs(db: Db, retentionDays: number): Promise<number> {
  const cutoff = new Date(Date.now() - retentionDays * 24 * 60 * 60 * 1000)
  const result = await db
    .delete(auditLog)
    .where(lt(auditLog.createdAt, cutoff))
  return (result as unknown[]).length ?? 0
}