import { eq, and, lte, asc, sql } from 'drizzle-orm';
import { db } from './client';
import { campaigns, recipients, emailQueue, campaignVariants, trackingEvents, workspaces, type Campaign, type Recipient } from './schema';

/** Eine Variante einer Campaign (1+ Stück). Bei N>1 Varianten = A/B-Test mit Even-Split. */
export interface CampaignVariantInput {
  /** Anzeige-Label A/B/C — wird als utm_content-Suffix beim Send angehängt. */
  label: string;
  subject: string;
  preheader: string;
  emailHtml: string;
  emailText: string;
  mjmlSource: string;
  strategyJson: object;
  spamReportJson: object;
  spamScore: number;
}

/** UTM-Defaults der Campaign — Auto-Append an alle Links zur Send-Zeit. */
export interface CampaignUtmDefaults {
  source?: string;
  medium?: string;
  campaign?: string;
  content?: string;
  term?: string;
}

/**
 * Champion-Test-Konfiguration:
 * - testPercent: Anteil (0-100) der Recipients die in der Test-Phase sofort versendet werden
 * - winnerHours: Wartezeit in Stunden bevor Winner-Variante an Hold-Group geschickt wird
 *
 * Bei abgeschalteter Champion-Test (oder weniger als 2 Varianten) → klassischer Even-Split.
 */
export interface ChampionTestConfig {
  testPercent: number;
  winnerHours: number;
}

export interface CampaignSnapshotInput {
  workspaceId: string;
  workspaceSlug: string;
  campaignName: string;
  briefingJson: object;       // gesamtes Briefing als JSON
  tiptapJson: object;          // Tiptap-Editor-State
  /**
   * 1+ Varianten — bei genau 1 Element = normaler Send (kein A/B-Test).
   * Bei 2+ Elementen werden Recipients per modulo-N evenly gesplittet.
   */
  variants: CampaignVariantInput[];
  /** Optional: explizit gewählter Sender (überschreibt Workspace-default beim Versand). */
  senderEmail?: string;
  /** Optional: Display-Name des gewählten Senders. */
  senderName?: string;
  /** Aggregierter Spam-Verdict für die Campaign-Row (typischerweise vom A-Variant). */
  spamVerdict: 'ship' | 'fix' | 'reject';
  /** UTM-Defaults für Auto-Append im Worker — null/undefined = kein Tracking. */
  utmDefaults?: CampaignUtmDefaults;
  /**
   * Champion-Test-Config: nur effektiv wenn ≥ 2 Varianten + championTest gesetzt.
   * Bei aktivem Champion-Test wird `testPercent` der Recipients sofort versendet,
   * der Rest wartet `winnerHours` Stunden auf den Winner und wird dann mit der Winner-Variante geschickt.
   */
  championTest?: ChampionTestConfig;
  recipients: Array<{
    email: string;
    firstName?: string;
    lastName?: string;
    fullName?: string;
    company?: string;
    crmId?: string;
    /**
     * Pool-Discriminator für Login-Direktlinks (Kompass-Magic-Links).
     *  - 'private'  → Shop-Backend looked in `address_pools` (Default)
     *  - 'business' → Shop-Backend looked in `business_places`
     * Wenn nicht gesetzt: 'private' (matcht Schema-Default).
     */
    poolType?: 'private' | 'business';
    /**
     * Optional: Roher CRM-Record (alle Spalten der Datenquelle).
     * Wird in `recipients.customDataJson` persistiert und vom Worker für generische
     * Merge-Tag-Substitution `{{column_name}}` genutzt.
     */
    customData?: Record<string, unknown>;
  }>;
  createdById: string;
  /**
   * Optional: Pre-Schedule pro Recipient — Length muss exakt zu `recipients.length` passen.
   * Wenn gesetzt, wird `emailQueue.scheduledAt = jobSchedule[i]` statt `now/heldUntil`.
   * Champion-Test + jobSchedule schliessen sich aus (würde das held-Zeitfenster brechen).
   */
  jobSchedule?: Date[];
}

export interface CampaignWithCounts extends Campaign {
  recipientCount: number;
  queuedCount: number;
  sentCount: number;
  failedCount: number;
}

/**
 * Persistiert Campaign + Variants + Recipients + Queue atomic.
 *
 * **Even-Split-Logic** bei A/B-Tests (variants.length > 1):
 * Recipient[i].variantId = variants[i % variants.length].id
 * → bei 100 Empfängern + 2 Varianten: 50 × A, 50 × B (deterministisch).
 *
 * **Sender-Snapshot** wird auf der ersten Variante in `_generated` gespeichert (Backwards-Compat
 * mit `getCampaignForWorker`); für variant-aware Lookup nutzt der Worker `getJobContext()`.
 */
export function createCampaignWithRecipients(input: CampaignSnapshotInput): { campaignId: string; queuedCount: number; variantIds: string[] } {
  if (input.variants.length === 0) {
    throw new Error('createCampaignWithRecipients: mindestens 1 Variante erforderlich');
  }
  if (input.jobSchedule && input.jobSchedule.length !== input.recipients.length) {
    throw new Error('jobSchedule.length muss zu recipients.length passen');
  }
  if (input.jobSchedule && input.championTest && input.variants.length > 1) {
    throw new Error('jobSchedule und championTest gleichzeitig nicht unterstützt');
  }

  return db.transaction((tx) => {
    const campaignId = crypto.randomUUID();
    const now = new Date();
    const aVariant = input.variants[0]; // erste Variante = "Master" für Campaign-Row

    // Champion-Test nur effektiv mit ≥ 2 Varianten — sonst klassischer Send (alle pending)
    const isChampionTest = !!input.championTest && input.variants.length > 1;
    const testPercent = isChampionTest && input.championTest
      ? Math.max(1, Math.min(99, input.championTest.testPercent))
      : 100;
    const winnerHours = input.championTest?.winnerHours ?? 4;
    const heldUntil = isChampionTest
      ? new Date(now.getTime() + winnerHours * 60 * 60 * 1000)
      : now;

    // 1. Campaign-Row
    tx.insert(campaigns).values({
      id: campaignId,
      workspaceId: input.workspaceId,
      createdById: input.createdById,
      name: input.campaignName,
      briefingJson: input.briefingJson,
      tiptapJson: input.tiptapJson,
      strategyJson: aVariant.strategyJson,
      spamReportJson: aVariant.spamReportJson,
      spamScore: aVariant.spamScore,
      spamVerdict: input.spamVerdict,
      abEnabled: input.variants.length > 1,
      abWinnerHours: winnerHours,
      utmDefaultsJson: input.utmDefaults ?? null,
      status: 'sending',
      startedAt: now,
    }).run();

    // 2. Variants — pro Variant eine Row in campaign_variants
    const variantIds: string[] = [];
    for (const v of input.variants) {
      const variantId = crypto.randomUUID();
      variantIds.push(variantId);
      tx.insert(campaignVariants).values({
        id: variantId,
        campaignId,
        label: v.label,
        subject: v.subject,
        preheader: v.preheader,
        mjml: v.mjmlSource,
        html: v.emailHtml,
        htmlText: v.emailText,
        strategyJson: v.strategyJson,
        spamReportJson: v.spamReportJson,
        spamScore: v.spamScore,
      }).run();
    }

    // 3. Recipients — Even-Split: recipient[i].variantId = variantIds[i % N]
    const recipientIds: string[] = [];
    input.recipients.forEach((r, i) => {
      const recipientId = crypto.randomUUID();
      const variantId = variantIds[i % variantIds.length];
      recipientIds.push(recipientId);
      // customDataJson: rawRecord der Datenquelle UND die expliziten fullName/company-Felder.
      // Reihenfolge so, dass customData (vollständiger CRM-Record) gewinnt — keine Überschreibung
      // mit potentiell stale fullName/company aus dem Mapping.
      const customDataJson = {
        fullName: r.fullName,
        company: r.company,
        ...(r.customData ?? {}),
      };
      tx.insert(recipients).values({
        id: recipientId,
        campaignId,
        variantId,
        crmId: r.crmId,
        email: r.email,
        firstName: r.firstName,
        lastName: r.lastName,
        // poolType für Login-Direktlinks: bei nicht-gesetzt fällt der Drizzle-Default
        // ('private') aus dem Schema. Explizit setzen wenn vorhanden, sonst Default-Pfad.
        poolType: r.poolType ?? 'private',
        customDataJson,
      }).run();
    });

    // 4. Email-Queue: 1 Job pro Recipient.
    //   - Even-Split (Default): alle Recipients sofort pending mit Even-Split-VariantId
    //   - Champion-Test: erste testPercent% sofort pending (Even-Split unter ihnen),
    //     Rest mit status='champion_held' und scheduledAt = now + winnerHours.
    //     Der Promotion-Tick im Worker wird sie nach Ablauf der Frist auf den Winner umstellen.
    const total = input.recipients.length;
    const testCutoff = isChampionTest ? Math.ceil(total * (testPercent / 100)) : total;
    input.recipients.forEach((_, i) => {
      const variantId = variantIds[i % variantIds.length];
      const inTestPhase = i < testCutoff;
      // jobSchedule überschreibt das Default-Scheduling (now/heldUntil) — wird beim
      // Pre-Schedule-Versand vom Send-Endpoint gesetzt. Im Champion-Test-Pfad ist
      // jobSchedule per Konstruktor-Check ausgeschlossen.
      const slot = input.jobSchedule?.[i] ?? (inTestPhase ? now : heldUntil);
      tx.insert(emailQueue).values({
        id: crypto.randomUUID(),
        campaignId,
        recipientId: recipientIds[i],
        variantId,
        status: inTestPhase ? 'pending' : 'champion_held',
        scheduledAt: slot,
      }).run();
    });

    // 5. Backwards-Compat: aVariant in `strategyJson._generated` ablegen, damit der alte
    // `getCampaignForWorker()` weiter funktioniert (nutzen wir aktuell nicht, aber erhält
    // die Schnittstelle stabil).
    const enrichedStrategy = {
      ...aVariant.strategyJson,
      _generated: {
        emailHtml: aVariant.emailHtml,
        emailText: aVariant.emailText,
        mjmlSource: aVariant.mjmlSource,
        senderEmail: input.senderEmail,
        senderName: input.senderName,
      },
    };
    tx.update(campaigns)
      .set({ strategyJson: enrichedStrategy })
      .where(eq(campaigns.id, campaignId))
      .run();

    return { campaignId, queuedCount: recipientIds.length, variantIds };
  });
}

export function getCampaignWithCounts(campaignId: string): CampaignWithCounts | undefined {
  const c = db.select().from(campaigns).where(eq(campaigns.id, campaignId)).get();
  if (!c) return undefined;

  const all = db.select().from(emailQueue).where(eq(emailQueue.campaignId, campaignId)).all();
  return {
    ...c,
    recipientCount: all.length,
    queuedCount: all.filter(j => j.status === 'pending').length,
    sentCount: all.filter(j => j.status === 'sent').length,
    failedCount: all.filter(j => j.status === 'failed' || j.status === 'bounced').length,
  };
}

/**
 * Worker-API: pickt N pending Jobs auf, lockt sie auf 'sending' (atomar).
 * Returnt die gepickten Jobs mit Recipient-Daten + variantId (für variant-aware HTML-Lookup).
 */
export interface PickedJob {
  jobId: string;
  campaignId: string;
  recipientId: string;
  /** A/B-Test-Variant — null bei Single-Variant-Sends (Pre-Refactor-Campaigns). */
  variantId: string | null;
  recipientEmail: string;
  recipientFirstName?: string | null;
  recipientLastName?: string | null;
  /**
   * Externe CRM-Kundennummer (z.B. Cumas/Nova-ID), wenn beim Recipient gesetzt.
   * Wird als System-Tag `{{CRM_ID}}` in URLs substituiert — perfekt für Conversion-
   * Attribution beim Ziel-CMS, das den Kunden über diese ID schon kennt.
   */
  recipientCrmId?: string | null;
  /**
   * Vollständiger CRM-Record (alle Spalten der Datenquelle) für generische
   * Merge-Tag-Substitution `{{column_name}}` im Worker. Null wenn Recipient nichts gespeichert hat.
   */
  customData?: Record<string, unknown> | null;
  /**
   * Pool-Discriminator für Login-Direktlinks (Kompass-Magic-Links).
   * 'private' (default) → Shop-Backend looked in `address_pools`.
   * 'business' → Shop-Backend looked in `business_places` (URL-Suffix `&type=business`).
   */
  recipientPoolType: 'private' | 'business';
}

export function pickPendingJobs(limit: number): PickedJob[] {
  return db.transaction((tx) => {
    const jobs = tx.select()
      .from(emailQueue)
      .where(and(eq(emailQueue.status, 'pending'), lte(emailQueue.scheduledAt, new Date())))
      .orderBy(asc(emailQueue.scheduledAt))
      .limit(limit)
      .all();

    if (jobs.length === 0) return [];

    // Lock auf sending
    for (const j of jobs) {
      tx.update(emailQueue).set({ status: 'sending' }).where(eq(emailQueue.id, j.id)).run();
    }

    // Hole Recipients
    const result: PickedJob[] = [];
    for (const j of jobs) {
      const r = tx.select().from(recipients).where(eq(recipients.id, j.recipientId)).get();
      if (!r) continue;
      result.push({
        jobId: j.id,
        campaignId: j.campaignId,
        recipientId: j.recipientId,
        variantId: j.variantId ?? null,
        recipientEmail: r.email,
        recipientFirstName: r.firstName,
        recipientLastName: r.lastName,
        recipientCrmId: r.crmId,
        // Drizzle macht TS-Validierung des Enums; defensive '|| private' falls
        // der Wert in der DB durch eine alte Migration null sein sollte.
        recipientPoolType: (r.poolType ?? 'private') as 'private' | 'business',
        customData: (r.customDataJson as Record<string, unknown> | null) ?? null,
      });
    }
    return result;
  });
}

/**
 * Variant-aware Worker-Lookup: gegeben eine campaignId + optional variantId, returnt
 * den korrekten HTML/Subject/Preheader-Snapshot inkl. UTM-Defaults und Sender-Info.
 *
 * - variantId gesetzt → liest aus `campaign_variants`
 * - variantId null → Backwards-Compat-Fallback auf `campaigns.strategyJson._generated`
 *   (für alte Campaigns vor dem Variants-Refactor)
 */
export interface JobContext {
  workspaceId: string;
  variantId: string | null;
  variantLabel: string | null;
  emailHtml: string;
  emailText: string;
  subject: string;
  preheader: string;
  senderEmail?: string;
  senderName?: string;
  utmDefaults: {
    source?: string;
    medium?: string;
    campaign?: string;
    content?: string;
    term?: string;
  } | null;
  /**
   * CTA-URLs aus dem Briefing — Defense-in-Depth gegen unaufgelöste {{CTA_PRIMARY_URL}}-
   * Tokens. Pipeline + Render lösen die Tokens normalerweise zur Build-Zeit auf, aber
   * bei Backups/Re-Sends/DB-Migrationen kann ein altes HTML mit Token in die Worker-
   * Stage durchschlüpfen. Worker substituiert dann sicherheitshalber nochmal.
   */
  ctaPrimaryUrl?: string;
  ctaSecondaryUrl?: string;
}

export function getJobContext(campaignId: string, variantId: string | null): JobContext | undefined {
  const c = db.select().from(campaigns).where(eq(campaigns.id, campaignId)).get();
  if (!c) return undefined;

  const utmDefaults = (c.utmDefaultsJson as JobContext['utmDefaults']) ?? null;
  const sJson = c.strategyJson as {
    _generated?: { emailHtml: string; emailText: string; senderEmail?: string; senderName?: string };
  } | null;
  const senderEmail = sJson?._generated?.senderEmail;
  const senderName = sJson?._generated?.senderName;

  // CTA-URLs aus dem Briefing-JSON ziehen — werden im Worker als SYSTEM_TAGS substituiert,
  // falls je ein altes HTML mit unaufgelösten {{CTA_PRIMARY_URL}}-Tokens durchschlüpft
  // (z.B. nach DB-Restore, Backup-Replay, oder vor diesem Defense-Layer angelegte Campaigns).
  const briefing = c.briefingJson as { ctaPrimaryUrl?: string; ctaSecondaryUrl?: string } | null;
  const ctaPrimaryUrl = briefing?.ctaPrimaryUrl?.trim() || undefined;
  const ctaSecondaryUrl = briefing?.ctaSecondaryUrl?.trim() || undefined;

  if (variantId) {
    const v = db.select().from(campaignVariants).where(eq(campaignVariants.id, variantId)).get();
    if (v && v.html && v.htmlText) {
      return {
        workspaceId: c.workspaceId,
        variantId,
        variantLabel: v.label,
        emailHtml: v.html,
        emailText: v.htmlText,
        subject: v.subject,
        preheader: v.preheader,
        senderEmail,
        senderName,
        utmDefaults,
        ctaPrimaryUrl,
        ctaSecondaryUrl,
      };
    }
  }

  // Fallback: alte Campaign ohne Variants — lese aus _generated
  if (!sJson?._generated) return undefined;
  const sFull = c.strategyJson as { subject_variants?: string[]; preheader?: string } | null;
  return {
    workspaceId: c.workspaceId,
    variantId: null,
    variantLabel: null,
    emailHtml: sJson._generated.emailHtml,
    emailText: sJson._generated.emailText,
    subject: sFull?.subject_variants?.[0] ?? c.name,
    preheader: sFull?.preheader ?? '',
    senderEmail,
    senderName,
    utmDefaults,
    ctaPrimaryUrl,
    ctaSecondaryUrl,
  };
}

export function getCampaignForWorker(campaignId: string): {
  workspaceId: string;
  emailHtml: string;
  emailText: string;
  subject: string;
  preheader: string;
  /** Falls bei Campaign-Erstellung explizit gewählt — sonst leer und Worker fällt auf Workspace-Default zurück. */
  senderEmail?: string;
  senderName?: string;
} | undefined {
  const c = db.select().from(campaigns).where(eq(campaigns.id, campaignId)).get();
  if (!c) return undefined;
  const sJson = c.strategyJson as {
    _generated?: { emailHtml: string; emailText: string; senderEmail?: string; senderName?: string };
    subject_variants?: string[];
    preheader?: string;
  } | null;
  if (!sJson?._generated) return undefined;
  return {
    workspaceId: c.workspaceId,
    emailHtml: sJson._generated.emailHtml,
    emailText: sJson._generated.emailText,
    subject: sJson.subject_variants?.[0] ?? c.name,
    preheader: sJson.preheader ?? '',
    senderEmail: sJson._generated.senderEmail,
    senderName: sJson._generated.senderName,
  };
}

export function markJobSent(jobId: string, messageId?: string): void {
  db.update(emailQueue)
    .set({ status: 'sent', sentAt: new Date(), messageId })
    .where(eq(emailQueue.id, jobId))
    .run();
}

export function markJobFailed(jobId: string, error: string, willRetry: boolean): void {
  if (willRetry) {
    db.update(emailQueue)
      .set({ status: 'pending', scheduledAt: new Date(Date.now() + 5 * 60 * 1000), lastError: error })
      .where(eq(emailQueue.id, jobId))
      .run();
  } else {
    db.update(emailQueue)
      .set({ status: 'failed', lastError: error })
      .where(eq(emailQueue.id, jobId))
      .run();
  }
}

export function incrementJobAttempts(jobId: string): number {
  const j = db.select().from(emailQueue).where(eq(emailQueue.id, jobId)).get();
  if (!j) return 0;
  const next = (j.attempts ?? 0) + 1;
  db.update(emailQueue).set({ attempts: next }).where(eq(emailQueue.id, jobId)).run();
  return next;
}

export function finalizeCampaignIfDone(campaignId: string): void {
  // Container-Campaigns für Trigger-Templates leben dauerhaft mit status='sending' —
  // sie sammeln über die Lebenszeit beliebig viele Trigger-Sends. Wenn ihre Queue
  // momentan leer ist, dürfen wir sie NICHT auf 'sent' setzen, sonst lehnt der
  // nächste Trigger-Call sie ab (oder Stats werden inkorrekt).
  const c = db.select({ kind: campaigns.kind }).from(campaigns).where(eq(campaigns.id, campaignId)).get();
  if (c?.kind === 'trigger_container') return;

  const all = db.select().from(emailQueue).where(eq(emailQueue.campaignId, campaignId)).all();
  const stillActive = all.some(j => j.status === 'pending' || j.status === 'sending');
  if (!stillActive) {
    db.update(campaigns)
      .set({ status: 'sent', completedAt: new Date() })
      .where(eq(campaigns.id, campaignId))
      .run();
  }
}

// ─── Tracking-Helpers (Variant-Stats: Open/Click pro A/B-Variante) ─────────

/**
 * Atomarer Open-Counter-Increment auf der Variante. Wird vom `/api/track/open`-Pixel-Endpoint
 * aufgerufen. Schreibt zusätzlich einen Event-Log-Eintrag in `tracking_events` für spätere
 * Dedupe-Auswertungen (z.B. unique opens via DISTINCT recipientId).
 *
 * `pixelCtx` (Phase 4): optionale Header-Auswertung mit UA/IP-Hash/MPP-Flag — kommt vom
 * `readOpenPixelContext()`-Helper. Stats-Aggregat liefert anhand `is_mpp=0` die echten Opens.
 * Der raw `openCount` auf der Variante bleibt mit-MPP, damit historische Reports stabil sind.
 */
export interface OpenPixelMeta {
  userAgent?: string;
  ipHash?: string;
  isMpp?: boolean;
}

export function incrementVariantOpen(
  variantId: string,
  campaignId: string,
  recipientId?: string,
  pixelCtx?: OpenPixelMeta,
): void {
  db.update(campaignVariants)
    .set({ openCount: sql`${campaignVariants.openCount} + 1` })
    .where(eq(campaignVariants.id, variantId))
    .run();
  db.insert(trackingEvents).values({
    id: crypto.randomUUID(),
    campaignId,
    variantId,
    recipientId,
    messageId: '', // Open-Pixel hat keinen messageId-Kontext
    eventType: 'open',
    userAgent: pixelCtx?.userAgent ?? null,
    ipHash: pixelCtx?.ipHash ?? null,
    isMpp: pixelCtx?.isMpp ?? null,
  }).run();
}

/**
 * Atomarer Click-Counter-Increment auf der Variante. Wird vom `/api/track/click`-Redirect-Endpoint
 * VOR dem 302 aufgerufen. Speichert die Ziel-URL für Click-Heatmaps.
 */
export function incrementVariantClick(variantId: string, campaignId: string, recipientId: string | undefined, linkUrl: string): void {
  db.update(campaignVariants)
    .set({ clickCount: sql`${campaignVariants.clickCount} + 1` })
    .where(eq(campaignVariants.id, variantId))
    .run();
  db.insert(trackingEvents).values({
    id: crypto.randomUUID(),
    campaignId,
    variantId,
    recipientId,
    messageId: '',
    eventType: 'click',
    linkUrl,
  }).run();
}

/** Aggregat-Stats pro Variante einer Campaign — für das Stats-Dashboard im AdMail. */
export interface VariantStats {
  variantId: string;
  label: string;
  sentCount: number;
  openCount: number;
  clickCount: number;
  /** Anzahl Recipients zugeordnet — sentCount kann tiefer sein wenn nicht alle gesendet wurden */
  assignedRecipients: number;
}

export function getVariantStats(campaignId: string): VariantStats[] {
  const variants = db.select().from(campaignVariants).where(eq(campaignVariants.campaignId, campaignId)).all();
  return variants.map((v) => {
    const assignedRecipients = db.select({ count: sql<number>`count(*)` })
      .from(recipients)
      .where(and(eq(recipients.campaignId, campaignId), eq(recipients.variantId, v.id)))
      .get()?.count ?? 0;
    return {
      variantId: v.id,
      label: v.label,
      sentCount: v.sentCount,
      openCount: v.openCount,
      clickCount: v.clickCount,
      assignedRecipients,
    };
  });
}

/** Inkrementiert sentCount auf der Variante — wird vom Worker nach erfolgreichem Send aufgerufen. */
export function incrementVariantSent(variantId: string): void {
  db.update(campaignVariants)
    .set({ sentCount: sql`${campaignVariants.sentCount} + 1` })
    .where(eq(campaignVariants.id, variantId))
    .run();
}

/**
 * Schreibt einen `delivered`-Event in tracking_events (Phase 4).
 *
 * Best-effort "von Microsoft akzeptiert" — nicht echt zugestellt (echte Delivery-
 * Webhooks gibt es bei Graph `Mail.Send` nicht), aber besser als nur `email_queue.status`
 * für KPIs wie Delivery-Rate. Wird vom Worker direkt nach erfolgreichem `sendMail()`
 * aufgerufen, NICHT im Failure-Path.
 *
 * `messageId` ist hier echt befüllbar (anders als bei open/click) — der Caller hat die
 * gerade vergebene Mail-ID im Kontext.
 */
export function recordDelivered(
  campaignId: string,
  recipientId: string,
  variantId: string | null,
  messageId: string,
): void {
  try {
    db.insert(trackingEvents).values({
      id: crypto.randomUUID(),
      campaignId,
      recipientId,
      variantId,
      messageId,
      eventType: 'delivered',
      source: 'system',
    }).run();
  } catch (e) {
    // Best-effort: bei DB-Fehlern loggen, nicht werfen — Send selbst hat bereits geklappt
    console.error('[recordDelivered] failed:', e instanceof Error ? e.message : e);
  }
}

// ─── Conversion-Tracking (Phase 3) ─────────────────────────────────────────

export interface ConversionEventInput {
  /** Recipient-ID — wir leiten daraus campaignId + variantId ab. */
  recipientId: string;
  /**
   * Optionaler Geld-/Punkt-Wert (Antrags-Wert in EUR, Punkte, etc.). Bleibt NULL wenn nicht
   * gesetzt — Stats-Aggregation summiert nur die nicht-NULL-Werte.
   */
  value?: number;
  /**
   * Frei-formatiges JSON-Bag mit beliebigen Conversion-Details. Üblich:
   *  - Pixel-Conversion: `{ kind: 'form_submit', part: 'hp' }`
   *  - API-Polling: `{ orderId, kind, payloadHash, … }`
   */
  meta?: Record<string, unknown>;
  /** Pflicht — woher kommt die Conversion? Pixel-URL, API-Polling, Shop-Webhook, manuelles Backoffice, System. */
  source: 'pixel' | 'api_poll' | 'webhook' | 'manual' | 'system';
}

export type ConversionEventResult =
  | { ok: true; eventId: string }
  | { ok: false; reason: 'recipient_not_found' | 'recipient_no_campaign' | 'kind_disabled' };

/**
 * Schreibt ein `tracking_events`-Event mit `eventType='conversion'`.
 *
 * Recipient-Lookup-Logik:
 *  - recipientId muss existieren — sonst silent-fail (`recipient_not_found`).
 *  - Wir leiten campaignId + variantId aus dem Recipient ab — der Caller muss diese nicht kennen.
 *  - messageId leer (Pflichtfeld im Schema) — Conversion-Events haben keinen direkten
 *    Email-Send-Bezug, sondern landen über die Recipient-Beziehung.
 *
 * Best-effort: wirft NIE — alle Fehler werden als ConversionEventResult zurückgegeben damit
 * der Pixel-Endpoint silent fortfahren kann (kein 4xx an den Mailclient/Browser).
 */
export function recordConversion(input: ConversionEventInput): ConversionEventResult {
  try {
    // Recipient + Campaign + Workspace in einem JOIN für den Kind-Filter.
    // activeConversionKinds ist ein Workspace-Setting (Phase 3).
    const r = db.select({
      campaignId: recipients.campaignId,
      variantId: recipients.variantId,
      activeKinds: workspaces.activeConversionKinds,
    })
      .from(recipients)
      .innerJoin(campaigns, eq(campaigns.id, recipients.campaignId))
      .innerJoin(workspaces, eq(workspaces.id, campaigns.workspaceId))
      .where(eq(recipients.id, input.recipientId))
      .get();

    if (!r) return { ok: false, reason: 'recipient_not_found' };
    if (!r.campaignId) return { ok: false, reason: 'recipient_no_campaign' };

    // Phase 3: Kind-Filter. meta.kind muss in activeConversionKinds enthalten sein.
    // Wenn meta.kind nicht gesetzt ist → akzeptieren (legacy/manuelle Events).
    const kind = (input.meta as { kind?: string } | undefined)?.kind;
    if (kind && Array.isArray(r.activeKinds) && r.activeKinds.length > 0 && !r.activeKinds.includes(kind as 'order' | 'lead' | 'magic_login' | 'form_submit')) {
      return { ok: false, reason: 'kind_disabled' };
    }

    const eventId = crypto.randomUUID();
    db.insert(trackingEvents).values({
      id: eventId,
      campaignId: r.campaignId,
      recipientId: input.recipientId,
      variantId: r.variantId ?? null,
      messageId: '',
      eventType: 'conversion',
      value: input.value ?? null,
      metaJson: input.meta ?? null,
      source: input.source,
    }).run();

    return { ok: true, eventId };
  } catch (e) {
    // Best-effort: bei DB-Fehlern loggen, nicht werfen
    console.error('[recordConversion] failed:', e instanceof Error ? e.message : e);
    return { ok: false, reason: 'recipient_not_found' }; // Generic fail
  }
}

/**
 * Findet alle Recipient-IDs einer Campaign — nützlich für den API-Poller, der nach
 * Cumas-Orders mit matching `recipientId` sucht. Nur sent-Recipients (verlustarm).
 */
export function getRecipientsByEmail(emails: string[]): Array<{ id: string; campaignId: string; email: string }> {
  if (emails.length === 0) return [];
  // SQLite IN-Clause — Drizzle unterstützt `inArray`
  const rows = db.select({
    id: recipients.id,
    campaignId: recipients.campaignId,
    email: recipients.email,
  })
    .from(recipients)
    .where(sql`lower(${recipients.email}) IN (${sql.join(emails.map(e => sql`${e.toLowerCase()}`), sql`, `)})`)
    .all();
  return rows;
}

/**
 * Bulk-Lookup von Recipients per ID — für Cumas-Poller wenn der Match per
 * konfiguriertem Custom-Feld (z.B. `extra_data.mf_rcp`) statt Email läuft.
 * IDs, die nicht existieren, werden stillschweigend ignoriert (kein Throw).
 */
export function getRecipientsByIds(ids: string[]): Array<{ id: string; campaignId: string; email: string }> {
  if (ids.length === 0) return [];
  const rows = db.select({
    id: recipients.id,
    campaignId: recipients.campaignId,
    email: recipients.email,
  })
    .from(recipients)
    .where(sql`${recipients.id} IN (${sql.join(ids.map(i => sql`${i}`), sql`, `)})`)
    .all();
  return rows;
}

/**
 * Aggregiert die Conversion-Counter pro Variante einer Campaign.
 * Wird optional vom Stats-Endpoint genutzt — separat von `getVariantStats()` weil Conversions
 * (Phase 3) später nachgerüstet wurden.
 */
export interface VariantConversionStats {
  variantId: string;
  conversionCount: number;
  /** Summe der `value`-Spalte (null-werte ausgeschlossen) — z.B. Gesamt-EUR. */
  conversionValueTotal: number;
  /** Phase 3: Aufteilung nach Herkunft (pixel/webhook/api_poll/manual/system). */
  bySource: Record<string, number>;
  valueBySource: Record<string, number>;
}

export function getVariantConversionStats(campaignId: string): VariantConversionStats[] {
  // Group by (variantId, source) — wir aggregieren danach in JS, weil
  // SQLite-PIVOT umständlich ist und die Source-Kardinalität klein (≤5).
  const rows = db.select({
    variantId: trackingEvents.variantId,
    source: trackingEvents.source,
    count: sql<number>`count(*)`,
    valueTotal: sql<number>`coalesce(sum(${trackingEvents.value}), 0)`,
  })
    .from(trackingEvents)
    .where(and(
      eq(trackingEvents.campaignId, campaignId),
      eq(trackingEvents.eventType, 'conversion'),
    ))
    .groupBy(trackingEvents.variantId, trackingEvents.source)
    .all();

  const byVariant = new Map<string, VariantConversionStats>();
  for (const r of rows) {
    if (r.variantId == null) continue;
    const src = r.source ?? 'unknown';
    const count = Number(r.count);
    const value = Number(r.valueTotal);
    let agg = byVariant.get(r.variantId);
    if (!agg) {
      agg = { variantId: r.variantId, conversionCount: 0, conversionValueTotal: 0, bySource: {}, valueBySource: {} };
      byVariant.set(r.variantId, agg);
    }
    agg.conversionCount += count;
    agg.conversionValueTotal += value;
    agg.bySource[src] = (agg.bySource[src] ?? 0) + count;
    agg.valueBySource[src] = (agg.valueBySource[src] ?? 0) + value;
  }
  return [...byVariant.values()];
}

/**
 * Liefert die Touches eines Recipients innerhalb eines Lookback-Zeitraums
 * vor `conversionAt`. Wird von der Multi-Touch-Engine benutzt um die
 * Conversion-Credits zu verteilen.
 *
 * Nur 'click' und 'open' Events. Sortierung asc nach createdAt.
 */
export interface RecipientTouchRow {
  campaignId: string;
  variantId: string | null;
  eventType: 'click' | 'open';
  createdAt: Date;
}

export function getRecipientTouches(
  recipientId: string,
  fromDate: Date,
  toDate: Date,
): RecipientTouchRow[] {
  const rows = db.select({
    campaignId: trackingEvents.campaignId,
    variantId: trackingEvents.variantId,
    eventType: trackingEvents.eventType,
    createdAt: trackingEvents.createdAt,
  })
    .from(trackingEvents)
    .where(and(
      eq(trackingEvents.recipientId, recipientId),
      sql`${trackingEvents.eventType} IN ('click', 'open')`,
      sql`${trackingEvents.createdAt} >= ${fromDate}`,
      sql`${trackingEvents.createdAt} <= ${toDate}`,
    ))
    .orderBy(asc(trackingEvents.createdAt))
    .all();
  return rows.map((r) => ({
    campaignId: r.campaignId,
    variantId: r.variantId ?? null,
    eventType: r.eventType as 'click' | 'open',
    createdAt: r.createdAt instanceof Date ? r.createdAt : new Date(Number(r.createdAt)),
  }));
}

/** Eine einzelne Conversion-Row aus tracking_events — für die Attribution-Engine. */
export interface ConversionRow {
  eventId: string;
  recipientId: string;
  campaignId: string;
  variantId: string | null;
  value: number | null;
  createdAt: Date;
}

/**
 * Liefert alle Conversion-Events einer Campaign — als Input für die
 * Multi-Touch-Engine. Wir filtern nur recipient-getragene Conversions
 * (ohne recipientId kann nicht attribuiert werden, das wären manuelle System-Events).
 */
export function getCampaignConversions(campaignId: string): ConversionRow[] {
  const rows = db.select({
    id: trackingEvents.id,
    recipientId: trackingEvents.recipientId,
    campaignId: trackingEvents.campaignId,
    variantId: trackingEvents.variantId,
    value: trackingEvents.value,
    createdAt: trackingEvents.createdAt,
  })
    .from(trackingEvents)
    .where(and(
      eq(trackingEvents.campaignId, campaignId),
      eq(trackingEvents.eventType, 'conversion'),
      sql`${trackingEvents.recipientId} IS NOT NULL`,
    ))
    .all();
  const result: ConversionRow[] = [];
  for (const r of rows) {
    if (r.recipientId == null) continue;
    result.push({
      eventId: r.id,
      recipientId: r.recipientId,
      campaignId: r.campaignId,
      variantId: r.variantId,
      value: r.value,
      createdAt: r.createdAt instanceof Date ? r.createdAt : new Date(Number(r.createdAt)),
    });
  }
  return result;
}

// ─── Stats-Dashboard-Queries (Phase 2) ─────────────────────────────────────

export interface TopClickedLink {
  /** Ziel-URL inkl. UTM-Parameter — so wie der User sie wirklich angeklickt hat. */
  url: string;
  /** Anzahl Klicks insgesamt (inkl. Multi-Klicks pro Recipient). */
  clicks: number;
  /** Anzahl unique Recipients die den Link geklickt haben. */
  uniqueRecipients: number;
}

/**
 * Top-N geklickte Links einer Campaign, sortiert nach Click-Count desc.
 * Aggregiert NUR Click-Events (eventType='click'), die immer eine linkUrl tragen.
 */
export function getTopClickedLinks(campaignId: string, limit = 10): TopClickedLink[] {
  // GROUP BY linkUrl mit COUNT(*) und COUNT(DISTINCT recipientId)
  const rows = db.select({
    url: trackingEvents.linkUrl,
    clicks: sql<number>`count(*)`,
    uniqueRecipients: sql<number>`count(distinct ${trackingEvents.recipientId})`,
  })
    .from(trackingEvents)
    .where(and(
      eq(trackingEvents.campaignId, campaignId),
      eq(trackingEvents.eventType, 'click'),
    ))
    .groupBy(trackingEvents.linkUrl)
    .orderBy(sql`count(*) desc`)
    .limit(limit)
    .all();
  return rows
    .filter((r): r is { url: string; clicks: number; uniqueRecipients: number } => r.url != null)
    .map(r => ({ url: r.url, clicks: Number(r.clicks), uniqueRecipients: Number(r.uniqueRecipients) }));
}

export interface EventTimeBucket {
  /** Bucket-Start als Unix-Millis (für Frontend-Charts direkt verwendbar). */
  bucketStart: number;
  opens: number;
  clicks: number;
  conversions: number;
}

/**
 * Time-Series-Aggregat von Open/Click/Conversion-Events einer Campaign, gebucketet
 * in `bucketMinutes`-Fenster ab Send-Start (typisch: 60 = stündlich, 15 = viertelstündlich).
 *
 * SQLite-Trick: `(created_at / bucketMs) * bucketMs` floort jedes Event in seinen Bucket-Start
 * (unsere `created_at` ist `unixepoch() * 1000` → millisekunden-basiert).
 *
 * Limit: 168 Buckets (1 Woche bei 60min) — bei größeren Zeiträumen das `bucketMinutes`
 * vergrößern, nicht den Limit hochsetzen.
 */
export function getEventTimeSeries(
  campaignId: string,
  bucketMinutes = 60,
  limit = 168,
): EventTimeBucket[] {
  const bucketMs = bucketMinutes * 60 * 1000;
  const rows = db.select({
    bucketStart: sql<number>`(${trackingEvents.createdAt} / ${bucketMs}) * ${bucketMs}`,
    eventType: trackingEvents.eventType,
    count: sql<number>`count(*)`,
  })
    .from(trackingEvents)
    .where(eq(trackingEvents.campaignId, campaignId))
    .groupBy(
      sql`(${trackingEvents.createdAt} / ${bucketMs})`,
      trackingEvents.eventType,
    )
    .orderBy(sql`(${trackingEvents.createdAt} / ${bucketMs}) asc`)
    .limit(limit * 4) // 4 event-types max → effektiv `limit` Bucket-Ticks
    .all();

  // Pivot: rows[bucketStart][eventType] = count → flatte Buckets
  const byBucket = new Map<number, EventTimeBucket>();
  for (const r of rows) {
    const start = Number(r.bucketStart);
    if (!byBucket.has(start)) {
      byBucket.set(start, { bucketStart: start, opens: 0, clicks: 0, conversions: 0 });
    }
    const b = byBucket.get(start)!;
    if (r.eventType === 'open') b.opens = Number(r.count);
    else if (r.eventType === 'click') b.clicks = Number(r.count);
    else if (r.eventType === 'conversion') b.conversions = Number(r.count);
  }
  return Array.from(byBucket.values()).sort((a, b) => a.bucketStart - b.bucketStart).slice(0, limit);
}

/** Unique Open/Click-Counts pro Variante — DISTINCT recipientId statt rohe Counter. */
export interface VariantUniqueStats {
  variantId: string;
  uniqueOpens: number;
  /** Wie `uniqueOpens`, aber nur Events mit `is_mpp = 0 OR NULL` (Apple-Proxy ausgefiltert). */
  uniqueOpensVerified: number;
  uniqueClicks: number;
}

export function getVariantUniqueStats(campaignId: string): VariantUniqueStats[] {
  const variants = db.select({ id: campaignVariants.id })
    .from(campaignVariants)
    .where(eq(campaignVariants.campaignId, campaignId))
    .all();

  return variants.map(v => {
    const uniqueOpens = db.select({ c: sql<number>`count(distinct ${trackingEvents.recipientId})` })
      .from(trackingEvents)
      .where(and(
        eq(trackingEvents.variantId, v.id),
        eq(trackingEvents.eventType, 'open'),
      ))
      .get()?.c ?? 0;
    // MPP-bereinigt: is_mpp NULL (Pre-Phase4-Events) oder 0 (echter Open)
    const uniqueOpensVerified = db.select({ c: sql<number>`count(distinct ${trackingEvents.recipientId})` })
      .from(trackingEvents)
      .where(and(
        eq(trackingEvents.variantId, v.id),
        eq(trackingEvents.eventType, 'open'),
        sql`(${trackingEvents.isMpp} IS NULL OR ${trackingEvents.isMpp} = 0)`,
      ))
      .get()?.c ?? 0;
    const uniqueClicks = db.select({ c: sql<number>`count(distinct ${trackingEvents.recipientId})` })
      .from(trackingEvents)
      .where(and(
        eq(trackingEvents.variantId, v.id),
        eq(trackingEvents.eventType, 'click'),
      ))
      .get()?.c ?? 0;
    return {
      variantId: v.id,
      uniqueOpens: Number(uniqueOpens),
      uniqueOpensVerified: Number(uniqueOpensVerified),
      uniqueClicks: Number(uniqueClicks),
    };
  });
}

/**
 * Delivery- und Bounce-Statistiken pro Variante (Phase 4).
 *
 * `delivered`-Events werden vom Worker geschrieben (Microsoft-Accept). `bounce`
 * kommt später aus dem Inbox-Scanner — solange der nicht läuft, ist bounceCount=0.
 */
export interface VariantDeliveryStats {
  variantId: string;
  deliveredCount: number;
  bounceCount: number;
}

export function getVariantDeliveryStats(campaignId: string): VariantDeliveryStats[] {
  const variants = db.select({ id: campaignVariants.id })
    .from(campaignVariants)
    .where(eq(campaignVariants.campaignId, campaignId))
    .all();

  return variants.map(v => {
    const delivered = db.select({ c: sql<number>`count(*)` })
      .from(trackingEvents)
      .where(and(
        eq(trackingEvents.variantId, v.id),
        eq(trackingEvents.eventType, 'delivered'),
      ))
      .get()?.c ?? 0;
    const bounced = db.select({ c: sql<number>`count(*)` })
      .from(trackingEvents)
      .where(and(
        eq(trackingEvents.variantId, v.id),
        eq(trackingEvents.eventType, 'bounce'),
      ))
      .get()?.c ?? 0;
    return {
      variantId: v.id,
      deliveredCount: Number(delivered),
      bounceCount: Number(bounced),
    };
  });
}

// ─── Champion-Test-Promotion ──────────────────────────────────────────────

/**
 * Findet alle Champion-Test-Campaigns die promotion-bereit sind:
 *  - ab_enabled = true
 *  - ab_winner_variant_id = NULL (noch kein Winner gewählt)
 *  - es existieren champion_held-Jobs deren scheduledAt in der Vergangenheit liegt
 *
 * Wird vom Worker-Tick alle X Minuten gepollt.
 */
export function findCampaignsReadyForPromotion(): string[] {
  const now = new Date();
  const heldJobs = db.select({ campaignId: emailQueue.campaignId })
    .from(emailQueue)
    .where(and(eq(emailQueue.status, 'champion_held'), lte(emailQueue.scheduledAt, now)))
    .all();
  const uniqueCampaigns = Array.from(new Set(heldJobs.map((j) => j.campaignId)));
  // Filter auf abEnabled && Winner noch nicht gesetzt
  return uniqueCampaigns.filter((cid) => {
    const c = db.select().from(campaigns).where(eq(campaigns.id, cid)).get();
    return c?.abEnabled === true && !c.abWinnerVariantId;
  });
}

/**
 * Promotion-Algorithmus: pickt die Winner-Variante einer Champion-Test-Campaign,
 * markiert sie in `campaigns.ab_winner_variant_id` und promotet alle `champion_held`-Jobs
 * auf `pending` mit der Winner-VariantId — der Worker sendet sie dann beim nächsten Tick.
 *
 * Auswahl-Metrik (beste-zuerst):
 *   1. Click-Rate (clickCount / sentCount)  — primär weil Klicks = echte Conversion-Indikatoren
 *   2. Open-Rate als Fallback wenn alle Varianten 0 Clicks haben
 *   3. Bei Gleichstand → erste Variante (label-alphabetisch, also A vor B vor C)
 */
export function promoteChampion(campaignId: string): { winnerVariantId: string | null; promotedJobCount: number } {
  return db.transaction((tx) => {
    const campaign = tx.select().from(campaigns).where(eq(campaigns.id, campaignId)).get();
    if (!campaign || !campaign.abEnabled || campaign.abWinnerVariantId) {
      return { winnerVariantId: null, promotedJobCount: 0 };
    }

    const variants = tx.select().from(campaignVariants).where(eq(campaignVariants.campaignId, campaignId)).all();
    if (variants.length === 0) return { winnerVariantId: null, promotedJobCount: 0 };

    // Pick winner: höchste Click-Rate, Fallback Open-Rate, Tie-Break nach Label
    const ranked = [...variants].sort((a, b) => {
      const aCR = a.sentCount > 0 ? a.clickCount / a.sentCount : 0;
      const bCR = b.sentCount > 0 ? b.clickCount / b.sentCount : 0;
      if (aCR !== bCR) return bCR - aCR;
      const aOR = a.sentCount > 0 ? a.openCount / a.sentCount : 0;
      const bOR = b.sentCount > 0 ? b.openCount / b.sentCount : 0;
      if (aOR !== bOR) return bOR - aOR;
      return a.label.localeCompare(b.label);
    });
    const winner = ranked[0];

    // Mark winner
    tx.update(campaignVariants).set({ isWinner: true }).where(eq(campaignVariants.id, winner.id)).run();
    tx.update(campaigns).set({ abWinnerVariantId: winner.id }).where(eq(campaigns.id, campaignId)).run();

    // Promote held jobs: status → 'pending', variantId → winner, scheduledAt → now (sofort senden)
    const now = new Date();
    const heldJobs = tx.select().from(emailQueue)
      .where(and(eq(emailQueue.campaignId, campaignId), eq(emailQueue.status, 'champion_held')))
      .all();
    for (const job of heldJobs) {
      tx.update(emailQueue)
        .set({ status: 'pending', variantId: winner.id, scheduledAt: now })
        .where(eq(emailQueue.id, job.id))
        .run();
    }
    // Auch die zugeordneten Recipients auf den Winner umbiegen — damit Stats korrekt aggregieren
    const heldRecipientIds = heldJobs.map((j) => j.recipientId);
    for (const rid of heldRecipientIds) {
      tx.update(recipients).set({ variantId: winner.id }).where(eq(recipients.id, rid)).run();
    }

    return { winnerVariantId: winner.id, promotedJobCount: heldJobs.length };
  });
}
