/**
 * Platform stats, admin audit queries, and tenant audit log — admin-dashboard.
 */
import { count, eq, sql, and, desc, inArray } from 'drizzle-orm'
import type { Db } from '../client'
import { users } from '../schema/users'
import { tenants } from '../schema/tenants'
import { tenantMemberships } from '../schema/rbac'
import { invitations } from '../schema/auth-tokens'
import { auditLog } from './_audit-forward'

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

export interface PlatformStats {
  totalActiveUsers: number
  pendingApprovals: number
  activeUsers30d: number
  frozenUsers: number
  openInvitations: number
  tenantsByTier: Record<string, number>
}

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

export interface TenantAuditLogParams {
  section?: string
  from?: string
  to?: string
  page?: number
  limit?: number
}

// ── Platform stats ────────────────────────────────────────────────────────────

export async function getPlatformStats(db: Db): Promise<PlatformStats> {
  const [activeUsersRow] = await db
    .select({ cnt: count(users.id) })
    .from(users)
    .where(eq(users.status, 'active'))

  const [pendingRow] = await db
    .select({ cnt: count(tenantMemberships.userId) })
    .from(tenantMemberships)
    .where(eq(tenantMemberships.status, 'pending_approval'))

  const [frozenRow] = await db
    .select({ cnt: count(tenantMemberships.userId) })
    .from(tenantMemberships)
    .where(eq(tenantMemberships.status, 'frozen'))

  const [openInvitesRow] = await db
    .select({ cnt: count(invitations.id) })
    .from(invitations)
    .where(
      sql`${invitations.acceptedAt} IS NULL AND ${invitations.expiresAt} > NOW()`,
    )

  const tierRows = await db
    .select({
      tier: tenants.tier,
      cnt: count(tenants.id),
    })
    .from(tenants)
    .groupBy(tenants.tier)

  const tenantsByTier: Record<string, number> = {}
  for (const row of tierRows) {
    tenantsByTier[row.tier] = Number(row.cnt)
  }

  const [active30dRow] = await db
    .select({ cnt: count(users.id) })
    .from(users)
    .where(
      sql`${users.status} = 'active' AND ${users.emailVerifiedAt} IS NOT NULL AND ${users.createdAt} > NOW() - INTERVAL '30 days'`,
    )

  return {
    totalActiveUsers: Number(activeUsersRow?.cnt ?? 0),
    pendingApprovals: Number(pendingRow?.cnt ?? 0),
    activeUsers30d: Number(active30dRow?.cnt ?? 0),
    frozenUsers: Number(frozenRow?.cnt ?? 0),
    openInvitations: Number(openInvitesRow?.cnt ?? 0),
    tenantsByTier,
  }
}

// ── Recent admin activity ─────────────────────────────────────────────────────

export async function getRecentAdminActivity(
  db: Db,
  limit = 20,
): Promise<AdminAuditRow[]> {
  const rows = await db
    .select({
      id: auditLog.id,
      tenantId: auditLog.tenantId,
      actorId: auditLog.actorId,
      actorType: auditLog.actorType,
      entityType: auditLog.entityType,
      entityId: auditLog.entityId,
      action: auditLog.action,
      changes: auditLog.changes,
      ip: auditLog.ip,
      createdAt: auditLog.createdAt,
    })
    .from(auditLog)
    .orderBy(desc(auditLog.createdAt))
    .limit(limit)

  return rows as AdminAuditRow[]
}

// ── Tenant audit log ──────────────────────────────────────────────────────────

const SECTION_ACTIONS: Record<string, string[]> = {
  payments: [
    'invoice.paid',
    'subscription.created',
    'subscription.cancelled',
    'subscription.changed',
    'refund.issued',
  ],
  tier_history: ['tenant.tier_changed'],
  services: ['usage.ocr', 'usage.chat', 'usage.mail', 'usage.storage', 'usage.email'],
}

export async function getTenantAuditLog(
  db: Db,
  tenantId: string,
  params: TenantAuditLogParams = {},
): Promise<{ items: AdminAuditRow[]; page: number; limit: number }> {
  const { section = 'all', from, to, page = 1, limit = 50 } = params
  const offset = (page - 1) * limit

  const actionFilter = section !== 'all' ? SECTION_ACTIONS[section] : null

  // Build where clause
  let whereClause = eq(auditLog.tenantId, tenantId)

  if (actionFilter && actionFilter.length > 0) {
    whereClause = and(
      whereClause,
      inArray(auditLog.action, actionFilter),
    ) as typeof whereClause
  }

  if (from) {
    whereClause = and(
      whereClause,
      sql`${auditLog.createdAt} >= ${from}::timestamptz`,
    ) as typeof whereClause
  }
  if (to) {
    whereClause = and(
      whereClause,
      sql`${auditLog.createdAt} <= ${to}::timestamptz`,
    ) as typeof whereClause
  }

  const rows = await db
    .select({
      id: auditLog.id,
      tenantId: auditLog.tenantId,
      actorId: auditLog.actorId,
      actorType: auditLog.actorType,
      entityType: auditLog.entityType,
      entityId: auditLog.entityId,
      action: auditLog.action,
      changes: auditLog.changes,
      ip: auditLog.ip,
      createdAt: auditLog.createdAt,
    })
    .from(auditLog)
    .where(whereClause)
    .orderBy(desc(auditLog.createdAt))
    .limit(limit)
    .offset(offset)

  return { items: rows as AdminAuditRow[], page, limit }
}