import { getDbExec } from "@agent-native/core/db";
import { and, eq, isNull, or } from "drizzle-orm";

import { FIRST_PARTY_ANALYTICS_QUERY_TIMEOUT_MS } from "../../shared/dashboard-report-timeouts.js";
import { getDb, schema } from "../db/index.js";
import {
  EXCEPTION_EVENT_NAME,
  ingestAnalyticsExceptionEvents,
  type DerivedExceptionFields,
} from "./error-capture.js";
import {
  firstPartyCacheKey,
  withFirstPartyCache,
} from "./first-party-analytics-cache.js";

export interface AnalyticsScope {
  userEmail: string;
  orgId: string | null;
}

export interface IncomingAnalyticsEvent {
  event: string;
  properties?: Record<string, unknown>;
  context?: Record<string, unknown>;
  userId?: string | null;
  anonymousId?: string | null;
  sessionId?: string | null;
  timestamp?: string | number | Date | null;
}

export interface AnalyticsQueryResult {
  rows: Record<string, unknown>[];
  schema: { name: string; type: string }[];
}

export interface AnalyticsQueryOptions {
  /** Cache only callers with a stable dashboard-panel lifecycle. */
  cache?: boolean;
  /** Bound the database work for callers with a smaller delivery deadline. */
  timeoutMs?: number;
}

const MAX_EVENTS_PER_REQUEST = 100;
const MAX_QUERY_ROWS = 5_000;
const FIRST_PARTY_QUERY_TABLES = new Set([
  "analytics_events",
  "session_recordings",
]);
const RESERVED_ALIAS_WORDS = new Set([
  "where",
  "on",
  "group",
  "order",
  "limit",
  "join",
  "left",
  "right",
  "inner",
  "outer",
  "cross",
  "full",
  "having",
  "union",
]);

function nowIso(): string {
  return new Date().toISOString();
}

function todayIsoDate(): string {
  return nowIso().slice(0, 10);
}

function randomHex(bytes: number): string {
  const arr = new Uint8Array(bytes);
  if (!globalThis.crypto?.getRandomValues) {
    throw new Error("Secure random generation is unavailable");
  }
  globalThis.crypto.getRandomValues(arr);
  return Array.from(arr, (b) => b.toString(16).padStart(2, "0")).join("");
}

function id(prefix: string): string {
  return `${prefix}_${randomHex(12)}`;
}

export function generateAnalyticsPublicKey(): string {
  return `anpk_${randomHex(24)}`;
}

export async function createAnalyticsPublicKey(
  scope: AnalyticsScope,
  name: string,
): Promise<Record<string, unknown>> {
  const db = getDb() as any;
  const publicKey = generateAnalyticsPublicKey();
  const createdAt = nowIso();
  const row = {
    id: id("apk"),
    name: name.trim() || "Default key",
    publicKey,
    publicKeyPrefix: publicKey.slice(0, 13),
    replayAllowedOrigins: "[]",
    replayMaxBytesPerDay: 100 * 1024 * 1024,
    replayMaxRequestsPerMinute: 120,
    createdAt,
    ownerEmail: scope.userEmail,
    orgId: scope.orgId,
  };
  await db.insert(schema.analyticsPublicKeys).values(row);
  return {
    id: row.id,
    name: row.name,
    publicKey,
    publicKeyPrefix: row.publicKeyPrefix,
    replayAllowedOrigins: [],
    replayMaxBytesPerDay: row.replayMaxBytesPerDay,
    replayMaxRequestsPerMinute: row.replayMaxRequestsPerMinute,
    createdAt,
    orgId: row.orgId,
    revokedAt: null,
    lastUsedAt: null,
  };
}

export async function listAnalyticsPublicKeys(
  scope: AnalyticsScope,
): Promise<Record<string, unknown>[]> {
  const db = getDb() as any;
  const where = scope.orgId
    ? or(
        eq(schema.analyticsPublicKeys.orgId, scope.orgId),
        and(
          eq(schema.analyticsPublicKeys.ownerEmail, scope.userEmail),
          isNull(schema.analyticsPublicKeys.orgId),
        ),
      )
    : and(
        eq(schema.analyticsPublicKeys.ownerEmail, scope.userEmail),
        isNull(schema.analyticsPublicKeys.orgId),
      );
  const rows = await db
    .select()
    .from(schema.analyticsPublicKeys)
    .where(where)
    .orderBy(schema.analyticsPublicKeys.createdAt);

  return rows.map((row: any) => ({
    id: row.id,
    name: row.name,
    publicKeyPrefix: row.publicKeyPrefix,
    replayAllowedOrigins: parseReplayAllowedOrigins(row.replayAllowedOrigins),
    replayMaxBytesPerDay: row.replayMaxBytesPerDay ?? 100 * 1024 * 1024,
    replayMaxRequestsPerMinute: row.replayMaxRequestsPerMinute ?? 120,
    createdAt: row.createdAt,
    lastUsedAt: row.lastUsedAt ?? null,
    revokedAt: row.revokedAt ?? null,
    orgId: row.orgId ?? null,
  }));
}

function parseReplayAllowedOrigins(value: unknown): string[] {
  if (Array.isArray(value)) {
    return value.filter((item): item is string => typeof item === "string");
  }
  if (typeof value !== "string" || !value.trim()) return [];
  try {
    const parsed = JSON.parse(value);
    if (Array.isArray(parsed)) {
      return parsed.filter((item): item is string => typeof item === "string");
    }
  } catch {
    return value
      .split(/[\n,]/)
      .map((item) => item.trim())
      .filter(Boolean);
  }
  return [];
}

export async function revokeAnalyticsPublicKey(
  scope: AnalyticsScope,
  keyId: string,
): Promise<{ id: string; revokedAt: string }> {
  const db = getDb() as any;
  const where = scope.orgId
    ? and(
        eq(schema.analyticsPublicKeys.id, keyId),
        or(
          eq(schema.analyticsPublicKeys.orgId, scope.orgId),
          and(
            eq(schema.analyticsPublicKeys.ownerEmail, scope.userEmail),
            isNull(schema.analyticsPublicKeys.orgId),
          ),
        ),
      )
    : and(
        eq(schema.analyticsPublicKeys.id, keyId),
        eq(schema.analyticsPublicKeys.ownerEmail, scope.userEmail),
        isNull(schema.analyticsPublicKeys.orgId),
      );
  const revokedAt = nowIso();
  const updated = await db
    .update(schema.analyticsPublicKeys)
    .set({ revokedAt })
    .where(where)
    .returning();
  if (!updated.length) {
    throw new Error("Analytics public key not found");
  }
  return { id: keyId, revokedAt };
}

function asRecord(value: unknown): Record<string, unknown> {
  return value && typeof value === "object" && !Array.isArray(value)
    ? (value as Record<string, unknown>)
    : {};
}

function asString(value: unknown): string | null {
  if (typeof value === "string" && value.trim()) return value.trim();
  if (typeof value === "number" || typeof value === "boolean") {
    return String(value);
  }
  return null;
}

export function normalizeAnalyticsTimestamp(
  value: unknown,
  receivedAt = nowIso(),
): string {
  const fallback = (() => {
    const date = new Date(receivedAt);
    return Number.isNaN(date.getTime()) ? nowIso() : date.toISOString();
  })();
  const fallbackTime = new Date(fallback).getTime();
  const normalize = (date: Date) => {
    if (Number.isNaN(date.getTime())) return fallback;
    return date.getTime() > fallbackTime ? fallback : date.toISOString();
  };

  if (value instanceof Date) return normalize(value);
  if (typeof value === "number") {
    const d = new Date(value);
    return normalize(d);
  }
  if (typeof value === "string" && value.trim()) {
    const d = new Date(value);
    return normalize(d);
  }
  return fallback;
}

function eventDateFromTimestamp(timestamp: string): string {
  return timestamp.slice(0, 10);
}

function urlParts(url: string | null): {
  url: string | null;
  path: string | null;
  hostname: string | null;
} {
  if (!url) return { url: null, path: null, hostname: null };
  try {
    const parsed = new URL(url, "https://placeholder.agent-native.local");
    const relative = !/^https?:\/\//i.test(url);
    return {
      url: relative ? `${parsed.pathname}${parsed.search}${parsed.hash}` : url,
      path: parsed.pathname,
      hostname: relative ? null : parsed.hostname,
    };
  } catch {
    return { url, path: null, hostname: null };
  }
}

export function resolveAnalyticsEventDimensions({
  properties,
  context,
  hostname,
}: {
  properties: Record<string, unknown>;
  context: Record<string, unknown>;
  hostname: string | null;
}): { app: string | null; template: string | null } {
  const app =
    asString(properties.app) ||
    asString((properties as any).agent_native_app) ||
    asString((properties as any).agentNativeApp) ||
    asString((context as any).app) ||
    asString((context as any).agent_native_app) ||
    asString((context as any).agentNativeApp) ||
    (hostname ? hostname.split(".")[0] : null);
  const template =
    asString(properties.template) ||
    asString((properties as any).templateId) ||
    asString((properties as any).agent_native_template) ||
    asString((properties as any).agentNativeTemplate) ||
    asString((context as any).template) ||
    asString((context as any).templateId) ||
    asString((context as any).agent_native_template) ||
    asString((context as any).agentNativeTemplate) ||
    app;
  return { app, template };
}

export function parseAnalyticsTrackPayload(raw: unknown): {
  publicKey: string;
  events: IncomingAnalyticsEvent[];
} {
  const body =
    typeof raw === "string" && raw.trim() ? JSON.parse(raw) : asRecord(raw);
  const publicKey =
    asString((body as any).publicKey) ||
    asString((body as any).writeKey) ||
    asString((body as any).apiKey);
  if (!publicKey) {
    throw new Error("Missing publicKey");
  }

  const rawEvents = Array.isArray((body as any).events)
    ? (body as any).events
    : [body];
  if (rawEvents.length === 0) {
    throw new Error("No events provided");
  }
  if (rawEvents.length > MAX_EVENTS_PER_REQUEST) {
    throw new Error(`At most ${MAX_EVENTS_PER_REQUEST} events are accepted`);
  }

  const events = rawEvents.map((rawEvent: unknown) => {
    const obj = asRecord(rawEvent);
    const eventName =
      asString((obj as any).event) || asString((obj as any).name);
    if (!eventName) throw new Error("Each event requires an event name");
    return {
      event: eventName,
      properties: asRecord((obj as any).properties),
      context: asRecord((obj as any).context),
      userId: asString((obj as any).userId),
      anonymousId: asString((obj as any).anonymousId),
      sessionId: asString((obj as any).sessionId),
      timestamp: (obj as any).timestamp,
    };
  });

  return { publicKey, events };
}

export async function recordAnalyticsEvents(
  publicKey: string,
  events: IncomingAnalyticsEvent[],
): Promise<{ accepted: number; keyId: string }> {
  const db = getDb() as any;
  // guard:allow-unscoped -- public ingestion must resolve the owning tenant from the submitted write key before it can scope inserts.
  const [key] = await db
    .select()
    .from(schema.analyticsPublicKeys)
    .where(
      and(
        eq(schema.analyticsPublicKeys.publicKey, publicKey),
        isNull(schema.analyticsPublicKeys.revokedAt),
      ),
    )
    .limit(1);
  if (!key) {
    throw new Error("Invalid analytics public key");
  }

  const receivedAt = nowIso();
  const exceptionSources: Array<{
    properties: Record<string, unknown>;
    derived: DerivedExceptionFields;
  }> = [];
  const rows = events.map((event) => {
    const properties = event.properties ?? {};
    const context = event.context ?? {};
    const url =
      asString(properties.url) ||
      asString((context as any).url) ||
      asString((properties as any).href);
    const parts = urlParts(url);
    const hostname =
      parts.hostname ||
      asString(properties.hostname) ||
      asString((context as any).hostname);
    const { app, template } = resolveAnalyticsEventDimensions({
      properties,
      context,
      hostname,
    });
    const signedIn =
      asString((properties as any).signed_in) ||
      asString((properties as any).signedIn) ||
      asString((context as any).signed_in) ||
      asString((context as any).signedIn);
    const userId = event.userId ?? asString((properties as any).userId);
    const anonymousId =
      event.anonymousId ??
      asString((properties as any).anonymousId) ??
      asString((properties as any).distinctId);
    const userKey = userId || anonymousId;
    const timestamp = normalizeAnalyticsTimestamp(event.timestamp, receivedAt);
    const sessionId =
      event.sessionId ?? asString((properties as any).sessionId);

    if (event.event === EXCEPTION_EVENT_NAME) {
      exceptionSources.push({
        properties,
        derived: {
          app,
          template,
          url: parts.url,
          userId,
          anonymousId,
          userKey,
          sessionId,
          timestamp,
        },
      });
    }

    return {
      id: id("evt"),
      publicKeyId: key.id,
      eventName: event.event,
      userId,
      anonymousId,
      userKey,
      sessionId,
      timestamp,
      eventDate: eventDateFromTimestamp(timestamp),
      receivedAt,
      url: parts.url,
      path: parts.path ?? asString(properties.path),
      hostname,
      referrer:
        asString(properties.referrer) || asString((context as any).referrer),
      app,
      template,
      signedIn,
      properties: JSON.stringify(properties),
      context: JSON.stringify(context),
      ownerEmail: key.ownerEmail,
      orgId: key.orgId ?? null,
    };
  });

  if (rows.length) {
    await db.insert(schema.analyticsEvents).values(rows);
    await db
      .update(schema.analyticsPublicKeys)
      .set({ lastUsedAt: receivedAt })
      .where(eq(schema.analyticsPublicKeys.id, key.id));
  }

  // Fork captured exceptions into the dedicated error-capture tables. This is
  // best-effort: a malformed `$exception` payload must never reject the whole
  // analytics ingest (the event is still recorded in analytics_events above,
  // which keeps alerting working).
  if (exceptionSources.length) {
    try {
      await ingestAnalyticsExceptionEvents(
        {
          ownerEmail: key.ownerEmail,
          orgId: key.orgId ?? null,
          publicKeyId: key.id,
        },
        exceptionSources,
      );
    } catch (error) {
      console.warn("[first-party-analytics] Exception ingest failed:", error);
    }
  }

  return { accepted: rows.length, keyId: key.id };
}

function stripSqlLiterals(sql: string): string {
  let out = "";
  let i = 0;
  let inSingle = false;
  let inDouble = false;
  while (i < sql.length) {
    const ch = sql[i];
    const next = sql[i + 1];
    if (!inSingle && !inDouble && ch === "-" && next === "-") {
      while (i < sql.length && sql[i] !== "\n") i++;
      out += " ";
      continue;
    }
    if (!inSingle && !inDouble && ch === "/" && next === "*") {
      i += 2;
      while (i < sql.length && !(sql[i] === "*" && sql[i + 1] === "/")) i++;
      i += 2;
      out += " ";
      continue;
    }
    if (!inDouble && ch === "'") {
      out += " ";
      if (inSingle && next === "'") {
        i += 2;
        continue;
      }
      inSingle = !inSingle;
      i++;
      continue;
    }
    if (!inSingle && ch === '"') {
      out += " ";
      inDouble = !inDouble;
      i++;
      continue;
    }
    out += inSingle || inDouble ? " " : ch;
    i++;
  }
  return out;
}

export function validateFirstPartyAnalyticsSql(sql: string): void {
  const stripped = stripSqlLiterals(sql).trim();
  const lowered = stripped.toLowerCase();
  if (!/^(select|with)\b/.test(lowered)) {
    throw new Error(
      "First-party analytics queries must start with SELECT or WITH",
    );
  }
  if (stripped.includes(";")) {
    throw new Error("Only a single SELECT statement is allowed");
  }
  if (
    /\b(insert|update|delete|drop|alter|truncate|create|replace|pragma|attach|detach|vacuum|grant|revoke)\b/i.test(
      stripped,
    )
  ) {
    throw new Error("Only read-only SELECT queries are allowed");
  }
  if (stripped.includes("?")) {
    throw new Error("Bind placeholders are not supported in dashboard SQL");
  }
  if (/\$\d+\b/.test(stripped)) {
    throw new Error("Bind placeholders are not supported in dashboard SQL");
  }
  if (/\bsession_replay_chunks\b/i.test(stripped)) {
    throw new Error(
      "First-party analytics queries cannot read session replay chunks",
    );
  }

  const cteNames = new Set<string>();
  const cteRe = /\b([a-zA-Z_][a-zA-Z0-9_]*)\s+as\s*\(/gi;
  for (const match of stripped.matchAll(cteRe)) {
    cteNames.add(match[1].toLowerCase());
  }

  let usesAllowedTable = false;
  const tableRe =
    /\b(?:from|join)\s+([a-zA-Z_][a-zA-Z0-9_]*(?:\.[a-zA-Z_][a-zA-Z0-9_]*)?)/gi;
  for (const match of stripped.matchAll(tableRe)) {
    const ref = match[1].toLowerCase();
    if (FIRST_PARTY_QUERY_TABLES.has(ref)) {
      usesAllowedTable = true;
      continue;
    }
    if (cteNames.has(ref)) continue;
    throw new Error(
      `First-party analytics queries can only read analytics_events or session_recordings (found ${match[1]})`,
    );
  }
  if (!usesAllowedTable) {
    throw new Error(
      "Query must read from analytics_events or session_recordings",
    );
  }
}

function scopeClause(scope: AnalyticsScope): {
  sql: string;
  args: Array<string | null>;
} {
  if (scope.orgId) {
    return {
      sql: "(org_id = ? OR (org_id IS NULL AND owner_email = ?))",
      args: [scope.orgId, scope.userEmail],
    };
  }
  return {
    sql: "(org_id IS NULL AND owner_email = ?)",
    args: [scope.userEmail],
  };
}

function freshnessClause(tableName: string): string {
  if (tableName === "analytics_events") {
    return "(COALESCE(NULLIF(event_date, ''), substr(timestamp, 1, 10)) <= ?)";
  }
  return "(substr(started_at, 1, 10) <= ?)";
}

export function scopedAnalyticsSql(
  sql: string,
  scope: AnalyticsScope,
  today = todayIsoDate(),
): { sql: string; args: Array<string | null> } {
  const args: Array<string | null> = [];
  const aliasRe =
    /\b(from|join)\s+(analytics_events|session_recordings)\b(\s+(?:as\s+)?(?!where\b|on\b|group\b|order\b|limit\b|join\b|left\b|right\b|inner\b|outer\b|cross\b|full\b|having\b|union\b)([a-zA-Z_][a-zA-Z0-9_]*))?/gi;
  const rewritten = sql.replace(
    aliasRe,
    (full, keyword, tableName, aliasPart, alias) => {
      const normalizedTable = String(tableName).toLowerCase();
      const normalizedAlias =
        typeof alias === "string" ? alias.toLowerCase() : "";
      const usableAlias =
        aliasPart &&
        normalizedAlias &&
        !RESERVED_ALIAS_WORDS.has(normalizedAlias)
          ? aliasPart
          : ` AS ${normalizedTable}`;
      const scopeDef = scopeClause(scope);
      args.push(...scopeDef.args, today);
      return `${keyword} (SELECT * FROM ${normalizedTable} WHERE ${scopeDef.sql} AND ${freshnessClause(normalizedTable)})${usableAlias}`;
    },
  );
  return { sql: rewritten, args };
}

function valueType(value: unknown): string {
  if (typeof value === "number") return "number";
  if (typeof value === "boolean") return "boolean";
  return "string";
}

function inferSchema(rows: Record<string, unknown>[]): {
  name: string;
  type: string;
}[] {
  const first = rows.find((row) => row && typeof row === "object");
  if (!first) return [];
  return Object.entries(first).map(([name, value]) => ({
    name,
    type: valueType(value),
  }));
}

export async function queryFirstPartyAnalytics(
  sql: string,
  scope: AnalyticsScope,
  options: AnalyticsQueryOptions = {},
): Promise<AnalyticsQueryResult> {
  validateFirstPartyAnalyticsSql(sql);
  const scoped = scopedAnalyticsSql(sql, scope);
  const wrappedSql = `SELECT * FROM (${scoped.sql}) AS first_party_analytics_query LIMIT ${MAX_QUERY_ROWS}`;
  const timeoutMs = Math.max(
    1,
    options.timeoutMs ?? FIRST_PARTY_ANALYTICS_QUERY_TIMEOUT_MS,
  );
  // The cache key is the fully scoped SQL + args, which already embeds
  // org_id/owner_email (see scopeClause) — a cache hit can only ever return
  // rows the same tenant was already entitled to query.
  const cacheKey = firstPartyCacheKey(wrappedSql, scoped.args);
  const compute = async (): Promise<AnalyticsQueryResult> => {
    const exec = getDbExec();
    const result = await exec.execute({
      sql: wrappedSql,
      args: scoped.args,
      timeoutMs,
      maxAttempts: 1,
    });
    const rows = result.rows as Record<string, unknown>[];
    return { rows, schema: inferSchema(rows) };
  };
  if (!options.cache) return compute();
  return withFirstPartyCache(cacheKey, wrappedSql, compute, { timeoutMs });
}