/**
 * Admin global audit trail.
 *
 * - listAuditEntries: paginated audit log with filters for targetType, action, adminId.
 *   JOINs users table to resolve adminId → adminName.
 *   Returns null adminName when action was taken by AI_AGENT.
 *
 * No raw SQL. Drizzle ORM only.
 */

import { eq, and, count, gte, lte, desc, sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import { adminActions, users, vendorTranslations, vendors } from '@/server/db/schema.js';
import { VENDOR_DEFAULT_LOCALE } from '@/server/db/queries/vendors.js';

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

export type AuditTargetType =
  | 'DEAL'
  | 'VENDOR'
  | 'REVIEW'
  | 'USER'
  | 'PURCHASE'
  | 'KB_ARTICLE'
  | 'SETTING'
  | 'OUTBOX_EVENT'
  | 'SHARE_LINK';
export type AuditAction =
  | 'APPROVE'
  | 'REJECT'
  | 'FREEZE'
  | 'UNFREEZE'
  | 'BAN'
  | 'REMOVE_REVIEW'
  | 'RESTORE_REVIEW'
  | 'ADD_QUANTITY'
  | 'GRANT_ADMIN'
  | 'REVOKE_ADMIN'
  | 'REQUEST_REFUND'
  | 'RESEND_RECEIPT'
  | 'KB_CREATE'
  | 'KB_UPDATE'
  | 'KB_DELETE'
  | 'SETTING_UPDATE'
  | 'OUTBOX_RETRY'
  | 'SHARE_LINK_TOGGLE'
  | 'SHARE_LINK_EXPIRE';

export interface ListAuditEntriesFilter {
  targetType?: AuditTargetType;
  action?: AuditAction;
  adminId?: string;
  /** Substring search on the admin's display name (case-insensitive). */
  adminSearch?: string;
  /** ISO date string YYYY-MM-DD — inclusive lower bound on createdAt. */
  from?: string;
  /** ISO date string YYYY-MM-DD — inclusive upper bound on createdAt. */
  to?: string;
  limit: number;
  offset: number;
}

export interface AuditEntryRow {
  id: string;
  /** Null when action was taken by AI_AGENT */
  adminId: string | null;
  /** Resolved display name; null for AI_AGENT */
  adminName: string | null;
  targetType: string;
  targetId: string;
  action: string;
  note: string | null;
  createdAt: string;
}

// ─── listAuditEntries ─────────────────────────────────────────────────────────

export async function listAuditEntries(
  db: DrizzleClient,
  filter: ListAuditEntriesFilter,
): Promise<{ data: AuditEntryRow[]; total: number }> {
  const conditions = [];

  if (filter.targetType) {
    conditions.push(eq(adminActions.targetType, filter.targetType as never));
  }
  if (filter.action) {
    conditions.push(eq(adminActions.action, filter.action as never));
  }
  if (filter.adminId) {
    conditions.push(eq(adminActions.adminId, filter.adminId));
  }
  if (filter.from) {
    conditions.push(gte(adminActions.createdAt, new Date(filter.from)));
  }
  if (filter.to) {
    // Upper bound: end of the given day
    const toDate = new Date(filter.to);
    toDate.setUTCHours(23, 59, 59, 999);
    conditions.push(lte(adminActions.createdAt, toDate));
  }

  // adminSearch filters on vendor_translations display_name for the admin user
  const adminSearchCondition = filter.adminSearch
    ? sql`EXISTS (SELECT 1 FROM ${vendorTranslations} vt2 INNER JOIN ${vendors} v2 ON v2.id = vt2.vendor_id AND v2.owner_user_id = ${adminActions.adminId} WHERE vt2.locale = ${VENDOR_DEFAULT_LOCALE} AND vt2.display_name ILIKE ${`%${filter.adminSearch}%`})`
    : undefined;

  const allConditions = [
    ...(conditions.length > 0 ? [and(...conditions)] : []),
    ...(adminSearchCondition ? [adminSearchCondition] : []),
  ];
  const whereClause = allConditions.length > 0 ? and(...allConditions) : undefined;

  // Use a left join to resolve adminId to a display name.
  // adminId is null for AI agent actions.
  const adminAlias = users;

  const [rows, [countRow]] = await Promise.all([
    db
      .select({
        id: adminActions.id,
        adminId: adminActions.adminId,
        adminName: sql<string | null>`COALESCE(
          ${users.displayName},
          (SELECT vt2.display_name FROM ${vendorTranslations} vt2 INNER JOIN ${vendors} v2 ON v2.id = vt2.vendor_id AND v2.owner_user_id = ${adminActions.adminId} WHERE vt2.locale = ${VENDOR_DEFAULT_LOCALE} LIMIT 1),
          CASE WHEN ${adminActions.adminId} IS NOT NULL THEN 'Admin #' || LEFT(${adminActions.adminId}::text, 8) ELSE NULL END
        )`,
        targetType: adminActions.targetType,
        targetId: adminActions.targetId,
        action: adminActions.action,
        note: adminActions.note,
        createdAt: adminActions.createdAt,
      })
      .from(adminActions)
      .leftJoin(adminAlias, eq(adminActions.adminId, adminAlias.id))
      .where(whereClause)
      .orderBy(desc(adminActions.createdAt))
      .limit(filter.limit)
      .offset(filter.offset),
    // Count query must also join users when adminSearch is active
    db
      .select({ count: count() })
      .from(adminActions)
      .leftJoin(adminAlias, eq(adminActions.adminId, adminAlias.id))
      .where(whereClause),
  ]);

  const data: AuditEntryRow[] = rows.map((row) => ({
    id: row.id,
    adminId: row.adminId ?? null,
    adminName: row.adminId ? (row.adminName ?? null) : null,
    targetType: row.targetType,
    targetId: row.targetId as string,
    action: row.action,
    note: row.note ?? null,
    createdAt: row.createdAt.toISOString(),
  }));

  return { data, total: countRow?.count ?? 0 };
}