/**
 * Audit log read helpers — tenant-audit-log spec.
 *
 * listAuditLog:  cursor-paginated tenant-scoped read with filters.
 * deleteOldAuditLogs: retention purge (called by nightly cron; tier-scoped).
 *
 * Cursor shape: base64url of JSON { created_at: string (ISO), id: string }.
 * Default order: created_at DESC, id DESC (newest-first, stable).
 */
import { and, desc, eq, gte, lt, lte, or, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { ilikeSubstringPattern } from '../utils/escape-like'
import { tenantAuditLog } from '../schema/audit'
import { tenants } from '../schema/tenants'
import type { AuditLogItem } from '@zync/types'

// ── Cursor helpers ─────────────────────────────────────────────────────────────

export function encodeCursor(createdAt: Date | string, id: string): string {
  const payload = JSON.stringify({ created_at: String(createdAt), id })
  return Buffer.from(payload).toString('base64url')
}

export function decodeCursor(cursor: string): { created_at: string; id: string } | null {
  try {
    const raw = Buffer.from(cursor, 'base64url').toString('utf8')
    const parsed = JSON.parse(raw) as { created_at: string; id: string }
    if (typeof parsed.created_at !== 'string' || typeof parsed.id !== 'string') return null
    return parsed
  } catch {
    return null
  }
}

// ── Row mapper ─────────────────────────────────────────────────────────────────

function mapRow(row: typeof tenantAuditLog.$inferSelect): AuditLogItem {
  return {
    id: row.id,
    event_type: row.eventType,
    entity_type: row.entityType ?? null,
    entity_id: row.entityId ?? null,
    entity_label: row.entityLabel ?? null,
    actor_name: row.actorName ?? null,
    actor_email: row.actorEmail ?? null,
    metadata: (row.metadata as Record<string, unknown> | null) ?? null,
    ip_address: row.ipAddress ?? null,
    created_at: Math.floor(row.createdAt.getTime() / 1000),
  }
}

// ── List filters ──────────────────────────────────────────────────────────────

export interface AuditLogListOptions {
  /** Filter by user_id (required for MEMBER scope) */
  userId?: string
  eventType?: string
  entityType?: string
  /** ISO date string or Unix timestamp string */
  from?: string
  to?: string
  cursor?: string
  limit: number
  /** Free-text ILIKE across actor_name, actor_email, entity_label, event_type */
  q?: string
  /** ILIKE on entity_label */
  entityQ?: string
}

export interface AuditLogPage {
  items: AuditLogItem[]
  next_cursor: string | null
  has_more: boolean
}

export async function listAuditLog(
  db: Db,
  tenantId: string,
  opts: AuditLogListOptions,
): Promise<AuditLogPage> {
  const limit = Math.min(opts.limit, 200)

  // Parse date range — accept both ISO strings and Unix timestamp strings
  let fromDate: Date | undefined
  let toDate: Date | undefined
  if (opts.from) {
    const ts = Number(opts.from)
    fromDate = isNaN(ts) ? new Date(opts.from) : new Date(ts * 1000)
  }
  if (opts.to) {
    const ts = Number(opts.to)
    toDate = isNaN(ts) ? new Date(opts.to) : new Date(ts * 1000)
  }

  // Cursor predicate
  let cursorCondition: ReturnType<typeof and> | undefined
  if (opts.cursor) {
    const decoded = decodeCursor(opts.cursor)
    if (decoded) {
      const cursorDate = new Date(decoded.created_at)
      cursorCondition = or(
        lt(tenantAuditLog.createdAt, cursorDate),
        and(
          eq(tenantAuditLog.createdAt, cursorDate),
          lt(tenantAuditLog.id, decoded.id),
        ),
      )
    }
  }

  // Full-text ILIKE predicate
  let ftsCondition: ReturnType<typeof sql> | undefined
  if (opts.q) {
    const pattern = ilikeSubstringPattern(opts.q)
    ftsCondition = sql`(
      ${tenantAuditLog.actorName} ILIKE ${pattern}
      OR ${tenantAuditLog.actorEmail} ILIKE ${pattern}
      OR ${tenantAuditLog.entityLabel} ILIKE ${pattern}
      OR ${tenantAuditLog.eventType} ILIKE ${pattern}
    )`
  }

  const rows = await db
    .select()
    .from(tenantAuditLog)
    .where(
      and(
        eq(tenantAuditLog.tenantId, tenantId),
        opts.userId ? eq(tenantAuditLog.userId, opts.userId) : undefined,
        opts.eventType ? eq(tenantAuditLog.eventType, opts.eventType) : undefined,
        opts.entityType ? eq(tenantAuditLog.entityType, opts.entityType) : undefined,
        fromDate ? gte(tenantAuditLog.createdAt, fromDate) : undefined,
        toDate ? lte(tenantAuditLog.createdAt, toDate) : undefined,
        cursorCondition,
        ftsCondition,
        opts.entityQ
          ? sql`${tenantAuditLog.entityLabel} ILIKE ${ilikeSubstringPattern(opts.entityQ)}`
          : undefined,
      ),
    )
    .orderBy(desc(tenantAuditLog.createdAt), desc(tenantAuditLog.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const items = hasMore ? rows.slice(0, limit) : rows
  const lastItem = items[items.length - 1]
  const nextCursor =
    hasMore && lastItem ? encodeCursor(lastItem.createdAt, lastItem.id) : null

  return {
    items: items.map(mapRow),
    next_cursor: nextCursor,
    has_more: hasMore,
  }
}

// ── Export helpers ─────────────────────────────────────────────────────────────

export interface AuditLogExportOptions {
  userId?: string
  eventType?: string
  entityType?: string
  from?: string
  to?: string
  q?: string
  entityQ?: string
}

/**
 * Fetch up to 10,000 rows for CSV export. Same filter logic as listAuditLog.
 */
export async function fetchAuditLogForExport(
  db: Db,
  tenantId: string,
  opts: AuditLogExportOptions,
): Promise<AuditLogItem[]> {
  const EXPORT_CAP = 10_000

  let fromDate: Date | undefined
  let toDate: Date | undefined
  if (opts.from) {
    const ts = Number(opts.from)
    fromDate = isNaN(ts) ? new Date(opts.from) : new Date(ts * 1000)
  }
  if (opts.to) {
    const ts = Number(opts.to)
    toDate = isNaN(ts) ? new Date(opts.to) : new Date(ts * 1000)
  }

  let ftsCondition: ReturnType<typeof sql> | undefined
  if (opts.q) {
    const pattern = ilikeSubstringPattern(opts.q)
    ftsCondition = sql`(
      ${tenantAuditLog.actorName} ILIKE ${pattern}
      OR ${tenantAuditLog.actorEmail} ILIKE ${pattern}
      OR ${tenantAuditLog.entityLabel} ILIKE ${pattern}
      OR ${tenantAuditLog.eventType} ILIKE ${pattern}
    )`
  }

  const rows = await db
    .select()
    .from(tenantAuditLog)
    .where(
      and(
        eq(tenantAuditLog.tenantId, tenantId),
        opts.userId ? eq(tenantAuditLog.userId, opts.userId) : undefined,
        opts.eventType ? eq(tenantAuditLog.eventType, opts.eventType) : undefined,
        opts.entityType ? eq(tenantAuditLog.entityType, opts.entityType) : undefined,
        fromDate ? gte(tenantAuditLog.createdAt, fromDate) : undefined,
        toDate ? lte(tenantAuditLog.createdAt, toDate) : undefined,
        ftsCondition,
        opts.entityQ
          ? sql`${tenantAuditLog.entityLabel} ILIKE ${ilikeSubstringPattern(opts.entityQ)}`
          : undefined,
      ),
    )
    .orderBy(desc(tenantAuditLog.createdAt), desc(tenantAuditLog.id))
    .limit(EXPORT_CAP)

  return rows.map(mapRow)
}

// ── Retention purge ───────────────────────────────────────────────────────────

/**
 * Nightly retention purge — deletes rows past each tier's retention window.
 * business: 90 days, enterprise: 365 days. freelancer/white_label: no purge.
 * Called only by the CRON_SECRET-guarded cron endpoint.
 */
export async function purgeAuditLogByRetention(db: Db): Promise<{ business: number; enterprise: number }> {
  const now = new Date()

  const businessCutoff = new Date(now.getTime() - 90 * 24 * 60 * 60 * 1000)
  const enterpriseCutoff = new Date(now.getTime() - 365 * 24 * 60 * 60 * 1000)

  const businessResult = await db
    .delete(tenantAuditLog)
    .where(
      and(
        sql`${tenantAuditLog.tenantId} IN (SELECT id FROM ${tenants} WHERE ${tenants.tier} = 'business')`,
        lt(tenantAuditLog.createdAt, businessCutoff),
      ),
    )
    .returning({ id: tenantAuditLog.id })

  const enterpriseResult = await db
    .delete(tenantAuditLog)
    .where(
      and(
        sql`${tenantAuditLog.tenantId} IN (SELECT id FROM ${tenants} WHERE ${tenants.tier} = 'enterprise')`,
        lt(tenantAuditLog.createdAt, enterpriseCutoff),
      ),
    )
    .returning({ id: tenantAuditLog.id })

  return {
    business: businessResult.length,
    enterprise: enterpriseResult.length,
  }
}