/**
 * Data export & GDPR query helpers — data-export-gdpr (wave-10 leaf 8).
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Route files MUST NOT import raw Drizzle tables; they import from '@zync/db/queries'.
 *
 * Export flow:
 *   1. POST /data/export  → createExportJob (status: 'pending')
 *   2. Worker picks up    → marks 'processing', builds JSON, marks 'completed'
 *   3. GET /data/export/:jobId → poll status
 *   4. GET /data/export/:jobId/download → return JSON file
 *
 * GDPR right-to-erasure:
 *   anonymizeCustomer — replaces PII with anonymized placeholders in-place.
 */
import { and, eq, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { exportJobs } from '../schema/data-export'
import { customers } from '../schema/customers'
import { auditLog } from './_audit-forward'
import crypto from 'node:crypto'

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

export interface ExportJobObject {
  id: string
  tenantId: string
  createdBy: string | null
  status: 'pending' | 'processing' | 'completed' | 'failed'
  downloadUrl: string | null
  errorMessage: string | null
  createdAt: string
  completedAt: string | null
}

export interface TenantExportData {
  tenant: Record<string, unknown>
  members: unknown[]
  customers: unknown[]
  invoices: unknown[]
  expenses: unknown[]
  projects: unknown[]
  timeEntries: unknown[]
  communications: unknown[]
  contracts: unknown[]
  leads: unknown[]
  tickets: unknown[]
  settings: Record<string, unknown>
}

// ── Serializer ────────────────────────────────────────────────────────────────

function serializeJob(row: typeof exportJobs.$inferSelect): ExportJobObject {
  return {
    id: row.id,
    tenantId: row.tenantId,
    createdBy: row.createdBy,
    status: row.status as ExportJobObject['status'],
    downloadUrl: row.downloadUrl,
    errorMessage: row.errorMessage,
    createdAt: row.createdAt.toISOString(),
    completedAt: row.completedAt ? row.completedAt.toISOString() : null,
  }
}

// ── exportTenantData ──────────────────────────────────────────────────────────

export async function exportTenantData(
  db: Db,
  tenantId: string,
): Promise<TenantExportData> {
  // Collect all tenant data via raw SQL to avoid import cycles
  const [
    tenantRows,
    memberRows,
    customerRows,
    invoiceRows,
    expenseRows,
    projectRows,
    timeEntryRows,
    commRows,
    contractRows,
    leadRows,
    ticketRows,
    settingsRows,
  ] = await Promise.all([
    db.execute(sql`SELECT * FROM tenants WHERE id = ${tenantId} LIMIT 1`),
    db.execute(sql`
      SELECT u.id, u.name, u.email, u.status, u.created_at, tm.status AS membership_status
      FROM tenant_memberships tm
      JOIN users u ON u.id = tm.user_id
      WHERE tm.tenant_id = ${tenantId}
    `),
    db.execute(sql`SELECT * FROM customers WHERE tenant_id = ${tenantId} ORDER BY created_at`),
    db.execute(sql`SELECT * FROM invoices WHERE tenant_id = ${tenantId} ORDER BY created_at`),
    db.execute(sql`SELECT id, tenant_id, expense_category, amount, status, expense_date, created_at FROM expenses WHERE tenant_id = ${tenantId} ORDER BY created_at`),
    db.execute(sql`SELECT * FROM projects WHERE tenant_id = ${tenantId} ORDER BY created_at`),
    db.execute(sql`SELECT id, user_id, project_id, task_id, description, duration_seconds, billable, started_at, stopped_at FROM time_entries WHERE tenant_id = ${tenantId} ORDER BY started_at`),
    db.execute(sql`SELECT id, customer_id, direction, channel, subject, created_at FROM customer_communications WHERE tenant_id = ${tenantId} ORDER BY created_at`),
    db.execute(sql`SELECT id, customer_id, title, status, created_at FROM contracts WHERE tenant_id = ${tenantId} ORDER BY created_at`).catch(() => [] as unknown[]),
    db.execute(sql`SELECT id, email, name, status, created_at FROM leads WHERE tenant_id = ${tenantId} ORDER BY created_at`).catch(() => [] as unknown[]),
    db.execute(sql`SELECT id, customer_id, title, status, created_at FROM tickets WHERE tenant_id = ${tenantId} ORDER BY created_at`).catch(() => [] as unknown[]),
    db.execute(sql`SELECT * FROM tenant_settings WHERE tenant_id = ${tenantId} LIMIT 1`).catch(() => [] as unknown[]),
  ])

  return {
    tenant: (tenantRows[0] as Record<string, unknown>) ?? {},
    members: memberRows as unknown[],
    customers: customerRows as unknown[],
    invoices: invoiceRows as unknown[],
    expenses: expenseRows as unknown[],
    projects: projectRows as unknown[],
    timeEntries: timeEntryRows as unknown[],
    communications: commRows as unknown[],
    contracts: contractRows as unknown[],
    leads: leadRows as unknown[],
    tickets: ticketRows as unknown[],
    settings: (settingsRows[0] as Record<string, unknown>) ?? {},
  }
}

// ── anonymizeCustomer ─────────────────────────────────────────────────────────

export async function anonymizeCustomer(
  db: Db,
  tenantId: string,
  actorUserId: string,
  customerId: string,
): Promise<void> {
  // Verify customer belongs to tenant
  const [existing] = await db
    .select({ id: customers.id })
    .from(customers)
    .where(and(eq(customers.tenantId, tenantId), eq(customers.id, customerId)))
    .limit(1)

  if (!existing) {
    throw new Error('Customer not found or access denied')
  }

  const hash = crypto
    .createHash('sha256')
    .update(customerId)
    .digest('hex')
    .slice(0, 12)

  await db.transaction(async (tx) => {
    await tx
      .update(customers)
      .set({
        name: 'Anonymous',
        email: `anon-${hash}@deleted.local`,
        phone: null,
        notes: null,
        address: null,
        status: 'archived',
        updatedAt: new Date(),
      })
      .where(and(eq(customers.tenantId, tenantId), eq(customers.id, customerId)))

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorUserId,
      actorType: 'user',
      entityType: 'customer',
      entityId: customerId,
      action: 'customer.anonymize',
      changes: null,
    })
  })
}

// ── createExportJob ───────────────────────────────────────────────────────────

export async function createExportJob(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<string> {
  const rows = await db
    .insert(exportJobs)
    .values({
      tenantId,
      createdBy: userId,
      status: 'pending',
    })
    .returning({ id: exportJobs.id })

  const jobId = rows[0]!.id

  await db.insert(auditLog).values({
    tenantId,
    actorId: userId,
    actorType: 'user',
    entityType: 'export_job',
    entityId: jobId,
    action: 'data_export.create',
    changes: null,
  })

  return jobId
}

// ── getExportJobStatus ────────────────────────────────────────────────────────

export async function getExportJobStatus(
  db: Db,
  tenantId: string,
  jobId: string,
): Promise<ExportJobObject | null> {
  const [row] = await db
    .select({
      id: exportJobs.id,
      tenantId: exportJobs.tenantId,
      createdBy: exportJobs.createdBy,
      status: exportJobs.status,
      downloadUrl: exportJobs.downloadUrl,
      errorMessage: exportJobs.errorMessage,
      createdAt: exportJobs.createdAt,
      completedAt: exportJobs.completedAt,
    })
    .from(exportJobs)
    .where(and(eq(exportJobs.tenantId, tenantId), eq(exportJobs.id, jobId)))
    .limit(1)

  if (!row) return null
  return serializeJob(row)
}

// ── setExportJobProcessing ────────────────────────────────────────────────────

export async function setExportJobProcessing(
  db: Db,
  tenantId: string,
  jobId: string,
): Promise<void> {
  await db
    .update(exportJobs)
    .set({ status: 'processing' })
    .where(and(eq(exportJobs.tenantId, tenantId), eq(exportJobs.id, jobId)))
}

// ── completeExportJob ─────────────────────────────────────────────────────────

export async function completeExportJob(
  db: Db,
  tenantId: string,
  jobId: string,
  downloadUrl: string,
): Promise<void> {
  await db
    .update(exportJobs)
    .set({ status: 'completed', downloadUrl, completedAt: new Date() })
    .where(and(eq(exportJobs.tenantId, tenantId), eq(exportJobs.id, jobId)))
}

// ── failExportJob ─────────────────────────────────────────────────────────────

export async function failExportJob(
  db: Db,
  tenantId: string,
  jobId: string,
  errorMessage: string,
): Promise<void> {
  await db
    .update(exportJobs)
    .set({ status: 'failed', errorMessage, completedAt: new Date() })
    .where(and(eq(exportJobs.tenantId, tenantId), eq(exportJobs.id, jobId)))
}