/**
 * Recipient-zentrierte Aggregate für den Drilldown-Tab im Stats-Panel (Phase 2).
 *
 * Zweck: pro Empfänger einer Campaign Status + Touchpoint-Counts liefern und auf
 * Klick eine vollständige Lifeline (Email-Events + Onsite-Behavior + Conversions)
 * aufbauen. Bewusst eigener Repo-File statt Erweiterung von `campaign-repo.ts`,
 * weil die Aggregate hier eine andere Granularität haben (per Recipient) und das
 * SQL etwas komplexer ist (mehrere Aggregat-Subselects).
 *
 * Status-Ableitung (höchste Priorität gewinnt):
 *   unsubscribed > bounced > converted > clicked > opened > delivered > sent
 *
 * MPP-Sonderlogik: `isMppOnly` zeigt an, dass der Empfänger zwar Opens hat,
 * aber alle davon Apple-Proxy-Pings sind (kein "echter" Open). Wird in der UI als
 * dezenter Tooltip-Hinweis sichtbar.
 */

import { and, asc, desc, eq, sql, type SQL } from 'drizzle-orm';
import { db } from './client';
import {
  recipients,
  campaigns,
  campaignVariants,
  trackingEvents,
  behaviorEvents,
  workspaces,
  unsubscribes,
} from './schema';

export type RecipientStatus =
  | 'sent'
  | 'delivered'
  | 'opened'
  | 'clicked'
  | 'converted'
  | 'bounced'
  | 'unsubscribed';

export interface RecipientRow {
  id: string;
  email: string;
  firstName: string | null;
  lastName: string | null;
  variantId: string | null;
  variantLabel: string | null;
  deliveredAt: Date | null;
  firstOpenAt: Date | null;
  firstClickAt: Date | null;
  bounceAt: Date | null;
  unsubscribedAt: Date | null;
  conversionCount: number;
  conversionValueTotal: number;
  openCount: number;
  clickCount: number;
  behaviorCount: number;
  /** Alle Opens waren MPP-Proxy-Pings — kein echter User-Open verifiziert. */
  isMppOnly: boolean;
  lastEventAt: Date | null;
  status: RecipientStatus;
}

export interface ListRecipientsOptions {
  limit?: number;
  offset?: number;
  variantId?: string;
  status?: RecipientStatus;
  search?: string;
  sortBy?: 'lastEventAt' | 'email' | 'firstOpenAt' | 'conversionValueTotal';
  sortDir?: 'asc' | 'desc';
}

export interface ListRecipientsResult {
  rows: RecipientRow[];
  total: number;
  limit: number;
  offset: number;
}

interface AggRow {
  id: string;
  email: string;
  firstName: string | null;
  lastName: string | null;
  variantId: string | null;
  variantLabel: string | null;
  deliveredAt: number | null;
  firstOpenAt: number | null;
  firstClickAt: number | null;
  bounceAt: number | null;
  unsubscribedAt: number | null;
  conversionCount: number;
  conversionValueTotal: number;
  openCount: number;
  openVerifiedCount: number;
  clickCount: number;
  behaviorCount: number;
  lastEventAt: number | null;
}

function toDate(ms: number | null): Date | null {
  if (ms == null) return null;
  return new Date(Number(ms));
}

function maxNullable(...values: Array<number | null>): number | null {
  let best: number | null = null;
  for (const v of values) {
    if (v == null) continue;
    if (best == null || v > best) best = v;
  }
  return best;
}

function deriveStatus(row: AggRow): RecipientStatus {
  if (row.unsubscribedAt != null) return 'unsubscribed';
  if (row.bounceAt != null) return 'bounced';
  if (row.conversionCount > 0) return 'converted';
  if (row.firstClickAt != null) return 'clicked';
  // "opened" zählt nur wenn mindestens 1 verified Open — sonst bleibt es bei delivered
  if (row.openVerifiedCount > 0) return 'opened';
  if (row.deliveredAt != null) return 'delivered';
  return 'sent';
}

/**
 * Baut die Aggregat-Query: pro Recipient die Touchpoint-Aggregate aus
 * trackingEvents + behaviorEvents + unsubscribes.
 *
 * Wir bauen einen großen SELECT mit korrelierten Subselects statt Self-Joins,
 * weil SQLite die so deutlich besser plant (ein Index-Hit pro Subselect, kein
 * Sort-Merge-Join über mehrere Aggregat-Tabellen).
 */
function buildAggSelect(campaignId: string) {
  const te = trackingEvents;
  const be = behaviorEvents;
  const u = unsubscribes;

  // Subselects für die Aggregat-Werte. SQLite optimiert das via
  // events_recipient_event_ts-Index, der für (recipientId, eventType, createdAt) existiert.
  const deliveredAt = sql<number | null>`(
    SELECT MIN(${te.createdAt}) FROM ${te}
    WHERE ${te.recipientId} = ${recipients.id} AND ${te.eventType} = 'delivered'
  )`;
  const firstOpenAt = sql<number | null>`(
    SELECT MIN(${te.createdAt}) FROM ${te}
    WHERE ${te.recipientId} = ${recipients.id} AND ${te.eventType} = 'open'
  )`;
  const firstClickAt = sql<number | null>`(
    SELECT MIN(${te.createdAt}) FROM ${te}
    WHERE ${te.recipientId} = ${recipients.id} AND ${te.eventType} = 'click'
  )`;
  const bounceAt = sql<number | null>`(
    SELECT MIN(${te.createdAt}) FROM ${te}
    WHERE ${te.recipientId} = ${recipients.id} AND ${te.eventType} = 'bounce'
  )`;
  const unsubscribedAt = sql<number | null>`(
    SELECT MIN(${u.createdAt}) FROM ${u}
    WHERE lower(${u.email}) = lower(${recipients.email})
  )`;
  const conversionCount = sql<number>`(
    SELECT COUNT(*) FROM ${te}
    WHERE ${te.recipientId} = ${recipients.id} AND ${te.eventType} = 'conversion'
  )`;
  const conversionValueTotal = sql<number>`(
    SELECT COALESCE(SUM(${te.value}), 0) FROM ${te}
    WHERE ${te.recipientId} = ${recipients.id} AND ${te.eventType} = 'conversion'
  )`;
  const openCount = sql<number>`(
    SELECT COUNT(*) FROM ${te}
    WHERE ${te.recipientId} = ${recipients.id} AND ${te.eventType} = 'open'
  )`;
  const openVerifiedCount = sql<number>`(
    SELECT COUNT(*) FROM ${te}
    WHERE ${te.recipientId} = ${recipients.id} AND ${te.eventType} = 'open'
      AND (${te.isMpp} IS NULL OR ${te.isMpp} = 0)
  )`;
  const clickCount = sql<number>`(
    SELECT COUNT(*) FROM ${te}
    WHERE ${te.recipientId} = ${recipients.id} AND ${te.eventType} = 'click'
  )`;
  const behaviorCount = sql<number>`(
    SELECT COUNT(*) FROM ${be}
    WHERE ${be.recipientId} = ${recipients.id}
  )`;
  // lastEventAt = MAX über alle relevanten timestamp-Quellen — wird unten in JS final
  // berechnet, weil SQLite kein elegantes GREATEST(...) hat. Wir liefern hier
  // den max trackingEvent-Timestamp und mergen in JS mit behaviorMax/unsubscribe.
  const lastEventAt = sql<number | null>`(
    SELECT MAX(${te.createdAt}) FROM ${te} WHERE ${te.recipientId} = ${recipients.id}
  )`;
  const lastBehaviorAt = sql<number | null>`(
    SELECT MAX(${be.createdAt}) FROM ${be} WHERE ${be.recipientId} = ${recipients.id}
  )`;

  return {
    deliveredAt,
    firstOpenAt,
    firstClickAt,
    bounceAt,
    unsubscribedAt,
    conversionCount,
    conversionValueTotal,
    openCount,
    openVerifiedCount,
    clickCount,
    behaviorCount,
    lastEventAt,
    lastBehaviorAt,
  };
}

/**
 * Paginierte Empfänger-Liste mit Status pro Person. Filter + Sortierung passieren im SQL,
 * Status-Filter wird in JS final angewandt (weil der derived status ein CASE über
 * mehrere Aggregate ist — billiger Post-Filter als nochmal HAVING zu bauen).
 *
 * Performance-Notiz: bei großen Campaigns (>5000 Empfänger) und aktivem Status-Filter
 * der nur wenige Treffer hat, kann die Pagination ungenau werden (offset zählt vor
 * dem Filter). v1 akzeptiert das — die Praxis-Campaigns liegen <2000 Empfänger.
 */
export function listRecipientsForCampaign(
  campaignId: string,
  opts?: ListRecipientsOptions,
): ListRecipientsResult {
  const limit = Math.max(1, Math.min(200, opts?.limit ?? 50));
  const offset = Math.max(0, opts?.offset ?? 0);
  const search = opts?.search?.trim().toLowerCase();
  const variantFilter = opts?.variantId;
  const statusFilter = opts?.status;

  const agg = buildAggSelect(campaignId);

  // Where-Bedingungen aufbauen — wir kombinieren via `and()`
  const whereParts: SQL[] = [eq(recipients.campaignId, campaignId)];
  if (variantFilter) {
    whereParts.push(eq(recipients.variantId, variantFilter));
  }
  if (search) {
    whereParts.push(sql`(
      lower(${recipients.email}) LIKE ${'%' + search + '%'}
      OR lower(COALESCE(${recipients.firstName}, '')) LIKE ${'%' + search + '%'}
      OR lower(COALESCE(${recipients.lastName}, '')) LIKE ${'%' + search + '%'}
    )`);
  }

  // Sortierung: für 'lastEventAt' verwenden wir das tracking-events-max
  // (kein Behavior berücksichtigt, sonst müsste das zweite Subselect-Max in den ORDER).
  // Für die häufige Sortierung reicht das — die Behavior-Events sind bisher leer (Phase 3).
  const sortBy = opts?.sortBy ?? 'lastEventAt';
  const sortDir = opts?.sortDir ?? 'desc';
  const dir = sortDir === 'asc' ? asc : desc;
  let orderExpr: SQL;
  switch (sortBy) {
    case 'email':
      orderExpr = dir(recipients.email);
      break;
    case 'firstOpenAt':
      orderExpr = sql`${agg.firstOpenAt} ${sql.raw(sortDir === 'asc' ? 'ASC' : 'DESC')} NULLS LAST`;
      break;
    case 'conversionValueTotal':
      orderExpr = sql`${agg.conversionValueTotal} ${sql.raw(sortDir === 'asc' ? 'ASC' : 'DESC')}`;
      break;
    case 'lastEventAt':
    default:
      orderExpr = sql`${agg.lastEventAt} ${sql.raw(sortDir === 'asc' ? 'ASC' : 'DESC')} NULLS LAST`;
      break;
  }

  // Wenn ein Status-Filter aktiv ist, ziehen wir mehr Zeilen und filtern in JS.
  // Wir holen up to 1000 Zeilen damit das Status-Filter-Counting halbwegs stimmt;
  // für Production-Skala ist das eine Untergrenze — v1 fokussiert UX, nicht Skalierung.
  const fetchLimit = statusFilter ? 1000 : limit;
  const fetchOffset = statusFilter ? 0 : offset;

  const baseQuery = db
    .select({
      id: recipients.id,
      email: recipients.email,
      firstName: recipients.firstName,
      lastName: recipients.lastName,
      variantId: recipients.variantId,
      variantLabel: campaignVariants.label,
      deliveredAt: agg.deliveredAt,
      firstOpenAt: agg.firstOpenAt,
      firstClickAt: agg.firstClickAt,
      bounceAt: agg.bounceAt,
      unsubscribedAt: agg.unsubscribedAt,
      conversionCount: agg.conversionCount,
      conversionValueTotal: agg.conversionValueTotal,
      openCount: agg.openCount,
      openVerifiedCount: agg.openVerifiedCount,
      clickCount: agg.clickCount,
      behaviorCount: agg.behaviorCount,
      lastTrackingAt: agg.lastEventAt,
      lastBehaviorAt: agg.lastBehaviorAt,
    })
    .from(recipients)
    .leftJoin(campaignVariants, eq(campaignVariants.id, recipients.variantId))
    .where(and(...whereParts))
    .orderBy(orderExpr)
    .limit(fetchLimit)
    .offset(fetchOffset);

  const fetched = baseQuery.all();

  // Map → AggRow → Status + lastEventAt-Merge
  const allMapped: RecipientRow[] = fetched.map((r) => {
    const aggRow: AggRow = {
      id: r.id,
      email: r.email,
      firstName: r.firstName ?? null,
      lastName: r.lastName ?? null,
      variantId: r.variantId ?? null,
      variantLabel: r.variantLabel ?? null,
      deliveredAt: r.deliveredAt == null ? null : Number(r.deliveredAt),
      firstOpenAt: r.firstOpenAt == null ? null : Number(r.firstOpenAt),
      firstClickAt: r.firstClickAt == null ? null : Number(r.firstClickAt),
      bounceAt: r.bounceAt == null ? null : Number(r.bounceAt),
      unsubscribedAt: r.unsubscribedAt == null ? null : Number(r.unsubscribedAt),
      conversionCount: Number(r.conversionCount),
      conversionValueTotal: Number(r.conversionValueTotal),
      openCount: Number(r.openCount),
      openVerifiedCount: Number(r.openVerifiedCount),
      clickCount: Number(r.clickCount),
      behaviorCount: Number(r.behaviorCount),
      lastEventAt: maxNullable(
        r.lastTrackingAt == null ? null : Number(r.lastTrackingAt),
        r.lastBehaviorAt == null ? null : Number(r.lastBehaviorAt),
      ),
    };
    return {
      id: aggRow.id,
      email: aggRow.email,
      firstName: aggRow.firstName,
      lastName: aggRow.lastName,
      variantId: aggRow.variantId,
      variantLabel: aggRow.variantLabel,
      deliveredAt: toDate(aggRow.deliveredAt),
      firstOpenAt: toDate(aggRow.firstOpenAt),
      firstClickAt: toDate(aggRow.firstClickAt),
      bounceAt: toDate(aggRow.bounceAt),
      unsubscribedAt: toDate(aggRow.unsubscribedAt),
      conversionCount: aggRow.conversionCount,
      conversionValueTotal: aggRow.conversionValueTotal,
      openCount: aggRow.openCount,
      clickCount: aggRow.clickCount,
      behaviorCount: aggRow.behaviorCount,
      isMppOnly: aggRow.openCount > 0 && aggRow.openVerifiedCount === 0,
      lastEventAt: toDate(aggRow.lastEventAt),
      status: deriveStatus(aggRow),
    };
  });

  let resultRows = allMapped;
  let total: number;

  if (statusFilter) {
    const filtered = allMapped.filter((r) => r.status === statusFilter);
    total = filtered.length;
    resultRows = filtered.slice(offset, offset + limit);
  } else {
    // Ohne Status-Filter: Total per separater COUNT-Query (genau)
    const countResult = db
      .select({ c: sql<number>`COUNT(*)` })
      .from(recipients)
      .where(and(...whereParts))
      .get();
    total = Number(countResult?.c ?? 0);
  }

  return { rows: resultRows, total, limit, offset };
}

export interface RecipientDetail extends RecipientRow {
  campaignId: string;
  campaignName: string;
  workspaceSlug: string;
  brandName: string;
  customDataJson: Record<string, unknown> | null;
  crmId: string | null;
}

/**
 * Vollständiger Detail-Lookup für den Timeline-Drawer.
 * Returns null wenn Recipient nicht existiert — API-Layer mappt das auf 404.
 */
export function getRecipientDetail(recipientId: string): RecipientDetail | null {
  // Erst Recipient + Campaign + Workspace laden — kompakt mit JOINs
  const base = db
    .select({
      id: recipients.id,
      email: recipients.email,
      firstName: recipients.firstName,
      lastName: recipients.lastName,
      variantId: recipients.variantId,
      crmId: recipients.crmId,
      customDataJson: recipients.customDataJson,
      campaignId: campaigns.id,
      campaignName: campaigns.name,
      workspaceSlug: workspaces.slug,
      brandName: workspaces.name,
    })
    .from(recipients)
    .innerJoin(campaigns, eq(campaigns.id, recipients.campaignId))
    .innerJoin(workspaces, eq(workspaces.id, campaigns.workspaceId))
    .where(eq(recipients.id, recipientId))
    .get();
  if (!base) return null;

  // Variant-Label separat (kann null sein wenn variantId null)
  const variantLabel = base.variantId
    ? db
        .select({ label: campaignVariants.label })
        .from(campaignVariants)
        .where(eq(campaignVariants.id, base.variantId))
        .get()?.label ?? null
    : null;

  // Reuse listRecipientsForCampaign-Aggregat-Logik: 1 Row Query mit dem Recipient-Filter
  const aggResult = listRecipientsForCampaign(base.campaignId, { limit: 1, search: base.email });
  // search-Filter ist toleranter — wir fallen zurück auf direktes Aggregat falls leer
  const aggRow = aggResult.rows.find((r) => r.id === recipientId);

  if (!aggRow) {
    // Fallback: leeres Aggregat zurückgeben damit Drawer trotzdem rendert
    return {
      id: base.id,
      email: base.email,
      firstName: base.firstName ?? null,
      lastName: base.lastName ?? null,
      variantId: base.variantId ?? null,
      variantLabel,
      deliveredAt: null,
      firstOpenAt: null,
      firstClickAt: null,
      bounceAt: null,
      unsubscribedAt: null,
      conversionCount: 0,
      conversionValueTotal: 0,
      openCount: 0,
      clickCount: 0,
      behaviorCount: 0,
      isMppOnly: false,
      lastEventAt: null,
      status: 'sent',
      campaignId: base.campaignId,
      campaignName: base.campaignName,
      workspaceSlug: base.workspaceSlug,
      brandName: base.brandName,
      customDataJson: (base.customDataJson as Record<string, unknown> | null) ?? null,
      crmId: base.crmId ?? null,
    };
  }

  return {
    ...aggRow,
    campaignId: base.campaignId,
    campaignName: base.campaignName,
    workspaceSlug: base.workspaceSlug,
    brandName: base.brandName,
    customDataJson: (base.customDataJson as Record<string, unknown> | null) ?? null,
    crmId: base.crmId ?? null,
  };
}

export interface TimelineEvent {
  id: string;
  source: 'email' | 'onsite' | 'crm';
  type: string;
  ts: Date;
  meta: {
    linkUrl?: string;
    isMpp?: boolean;
    productId?: string;
    value?: number;
    scrollPct?: number;
    durationMs?: number;
    url?: string;
    source?: string;
  };
}

/**
 * Chronologische Lifeline aller Events eines Empfängers. Mischt:
 *  - tracking_events (delivered/open/click/bounce/unsubscribe/complaint/conversion)
 *  - behavior_events (pageview/product_view/add_to_cart/quote_form/scroll_depth/time_on_page/custom)
 *
 * Sortierung asc nach ts — älteste zuerst, damit die UI von oben nach unten den
 * Customer-Journey-Verlauf zeigt.
 *
 * Conversion-Source landet als `meta.source` (pixel/api_poll/webhook/manual/system) —
 * relevant für Sales-Hand-off: "Order kam via Pixel, also direkt nach Klick".
 */
export function getRecipientTimeline(recipientId: string): TimelineEvent[] {
  const tracking = db
    .select({
      id: trackingEvents.id,
      type: trackingEvents.eventType,
      ts: trackingEvents.createdAt,
      linkUrl: trackingEvents.linkUrl,
      isMpp: trackingEvents.isMpp,
      value: trackingEvents.value,
      source: trackingEvents.source,
      metaJson: trackingEvents.metaJson,
    })
    .from(trackingEvents)
    .where(eq(trackingEvents.recipientId, recipientId))
    .all();

  const behavior = db
    .select({
      id: behaviorEvents.id,
      type: behaviorEvents.type,
      ts: behaviorEvents.createdAt,
      url: behaviorEvents.url,
      productId: behaviorEvents.productId,
      value: behaviorEvents.value,
      durationMs: behaviorEvents.durationMs,
      scrollPct: behaviorEvents.scrollPct,
      source: behaviorEvents.source,
    })
    .from(behaviorEvents)
    .where(eq(behaviorEvents.recipientId, recipientId))
    .all();

  const events: TimelineEvent[] = [];

  for (const e of tracking) {
    events.push({
      id: e.id,
      source: 'email',
      type: e.type,
      ts: e.ts instanceof Date ? e.ts : new Date(Number(e.ts)),
      meta: {
        linkUrl: e.linkUrl ?? undefined,
        isMpp: e.isMpp ?? undefined,
        value: e.value ?? undefined,
        source: e.source ?? undefined,
      },
    });
  }

  for (const e of behavior) {
    events.push({
      id: e.id,
      source: 'onsite',
      type: e.type,
      ts: e.ts instanceof Date ? e.ts : new Date(Number(e.ts)),
      meta: {
        url: e.url ?? undefined,
        productId: e.productId ?? undefined,
        value: e.value ?? undefined,
        durationMs: e.durationMs ?? undefined,
        scrollPct: e.scrollPct ?? undefined,
        source: e.source ?? undefined,
      },
    });
  }

  events.sort((a, b) => a.ts.getTime() - b.ts.getTime());
  return events;
}

/**
 * Variant-Optionen für die Filter-Bar in der RecipientsTable.
 * Eigene Mini-Query damit die Tabelle nicht das volle Stats-Aggregat ziehen muss.
 */
export interface VariantOption {
  id: string;
  label: string;
}

export function listVariantsForCampaign(campaignId: string): VariantOption[] {
  return db
    .select({ id: campaignVariants.id, label: campaignVariants.label })
    .from(campaignVariants)
    .where(eq(campaignVariants.campaignId, campaignId))
    .orderBy(asc(campaignVariants.label))
    .all();
}
