import { NextRequest, NextResponse } from 'next/server';
import { and, eq, gte, sql, desc } from 'drizzle-orm';
import { db } from '@/lib/db/client';
import { campaigns, workspaces, trackingEvents, recipients, behaviorEvents } from '@/lib/db/schema';
import { withAuth } from '@/core/auth';

export const runtime = 'nodejs';
export const dynamic = 'force-dynamic';

/**
 * Cross-Campaign-Dashboard-Aggregat. Liefert KPIs der letzten N Tage über alle
 * Workspaces, plus Workspace-Vergleich, Top-Performer und "Hot Leads jetzt".
 *
 * Query-Params:
 *   - days (default 30, min 1, max 365)
 *
 * Response-Struktur:
 *   {
 *     range: { fromIso, toIso, days },
 *     totals: { campaigns, sent, delivered, verifiedOpens, rawOpens, clicks, conversions, conversionValue },
 *     byBrand: [{ slug, name, sent, delivered, verifiedOpens, rawOpens, clicks, conversions, conversionValue }],
 *     topCampaigns: [{ id, name, workspaceSlug, sent, verifiedOpens, rawOpens, clicks, conversions, conversionValue }],
 *     hotLeads: [{ recipientId, email, campaignName, clickedAt, behaviorCount }]
 *   }
 *
 * Performance: das sind 5-6 separate Aggregat-Queries gegen indexed columns,
 * läuft im SQLite WAL-Mode in <100ms bei realistischen Datenmengen.
 */

interface BrandStats {
  slug: string;
  name: string;
  sent: number;
  delivered: number;
  verifiedOpens: number;
  /** Alle Open-Pixel (inkl. Apple-MPP-Vorabladen) — distinct Empfänger. */
  rawOpens: number;
  clicks: number;
  conversions: number;
  conversionValue: number;
}

interface TopCampaign {
  id: string;
  name: string;
  workspaceSlug: string;
  sent: number;
  verifiedOpens: number;
  rawOpens: number;
  clicks: number;
  conversions: number;
  conversionValue: number;
}

interface HotLead {
  recipientId: string;
  email: string;
  firstName: string | null;
  lastName: string | null;
  campaignName: string;
  workspaceSlug: string;
  clickedAt: string;
  behaviorCount: number;
}

export const GET = withAuth(async (req: NextRequest, _ctx) => {
  const url = req.nextUrl;
  const days = Math.max(1, Math.min(365, Number(url.searchParams.get('days')) || 30));
  const sinceMs = Date.now() - days * 86400_000;
  const since = new Date(sinceMs);

  try {
    // ─── Totals: über alle Workspaces ────────────────────────────────────────
    // sentCount = Summe über alle Campaigns deren startedAt im Range liegt
    const sentRow = db
      .select({ total: sql<number>`COALESCE(SUM(${campaigns.spamScore} * 0), 0)` })
      // Trick: wir brauchen die Spalte nicht, COALESCE-Hack erlaubt clean SQL
      .from(campaigns)
      .where(gte(campaigns.startedAt, since))
      .get();
    void sentRow;

    // Sent: aus tracking_events 'delivered'-Events (= "Microsoft hat angenommen")
    const deliveredRow = db
      .select({ c: sql<number>`COUNT(*)` })
      .from(trackingEvents)
      .where(and(eq(trackingEvents.eventType, 'delivered'), gte(trackingEvents.createdAt, since)))
      .get();

    const verifiedOpensRow = db
      .select({ c: sql<number>`COUNT(DISTINCT ${trackingEvents.recipientId})` })
      .from(trackingEvents)
      .where(and(
        eq(trackingEvents.eventType, 'open'),
        gte(trackingEvents.createdAt, since),
        sql`(${trackingEvents.isMpp} IS NULL OR ${trackingEvents.isMpp} = 0)`,
      ))
      .get();

    const rawOpensRow = db
      .select({ c: sql<number>`COUNT(DISTINCT ${trackingEvents.recipientId})` })
      .from(trackingEvents)
      .where(and(
        eq(trackingEvents.eventType, 'open'),
        gte(trackingEvents.createdAt, since),
      ))
      .get();

    const clicksRow = db
      .select({ c: sql<number>`COUNT(DISTINCT ${trackingEvents.recipientId})` })
      .from(trackingEvents)
      .where(and(eq(trackingEvents.eventType, 'click'), gte(trackingEvents.createdAt, since)))
      .get();

    const conversionsRow = db
      .select({
        c: sql<number>`COUNT(*)`,
        v: sql<number>`COALESCE(SUM(${trackingEvents.value}), 0)`,
      })
      .from(trackingEvents)
      .where(and(eq(trackingEvents.eventType, 'conversion'), gte(trackingEvents.createdAt, since)))
      .get();

    const campaignsRow = db
      .select({ c: sql<number>`COUNT(*)` })
      .from(campaigns)
      .where(gte(campaigns.startedAt, since))
      .get();

    const totals = {
      campaigns: Number(campaignsRow?.c ?? 0),
      sent: Number(deliveredRow?.c ?? 0), // delivered ≈ sent für Dashboard-Zweck
      delivered: Number(deliveredRow?.c ?? 0),
      verifiedOpens: Number(verifiedOpensRow?.c ?? 0),
      rawOpens: Number(rawOpensRow?.c ?? 0),
      clicks: Number(clicksRow?.c ?? 0),
      conversions: Number(conversionsRow?.c ?? 0),
      conversionValue: Number(conversionsRow?.v ?? 0),
    };

    // ─── Per Workspace ───────────────────────────────────────────────────────
    const brandList = db.select({ id: workspaces.id, slug: workspaces.slug, name: workspaces.name }).from(workspaces).all();
    const byBrand: BrandStats[] = [];
    for (const b of brandList) {
      const dRow = db
        .select({ c: sql<number>`COUNT(*)` })
        .from(trackingEvents)
        .innerJoin(campaigns, eq(campaigns.id, trackingEvents.campaignId))
        .where(and(
          eq(campaigns.workspaceId, b.id),
          eq(trackingEvents.eventType, 'delivered'),
          gte(trackingEvents.createdAt, since),
        ))
        .get();
      const vOpensRow = db
        .select({ c: sql<number>`COUNT(DISTINCT ${trackingEvents.recipientId})` })
        .from(trackingEvents)
        .innerJoin(campaigns, eq(campaigns.id, trackingEvents.campaignId))
        .where(and(
          eq(campaigns.workspaceId, b.id),
          eq(trackingEvents.eventType, 'open'),
          gte(trackingEvents.createdAt, since),
          sql`(${trackingEvents.isMpp} IS NULL OR ${trackingEvents.isMpp} = 0)`,
        ))
        .get();
      const rOpensRow = db
        .select({ c: sql<number>`COUNT(DISTINCT ${trackingEvents.recipientId})` })
        .from(trackingEvents)
        .innerJoin(campaigns, eq(campaigns.id, trackingEvents.campaignId))
        .where(and(
          eq(campaigns.workspaceId, b.id),
          eq(trackingEvents.eventType, 'open'),
          gte(trackingEvents.createdAt, since),
        ))
        .get();
      const cRow = db
        .select({ c: sql<number>`COUNT(DISTINCT ${trackingEvents.recipientId})` })
        .from(trackingEvents)
        .innerJoin(campaigns, eq(campaigns.id, trackingEvents.campaignId))
        .where(and(
          eq(campaigns.workspaceId, b.id),
          eq(trackingEvents.eventType, 'click'),
          gte(trackingEvents.createdAt, since),
        ))
        .get();
      const convRow = db
        .select({ c: sql<number>`COUNT(*)`, v: sql<number>`COALESCE(SUM(${trackingEvents.value}), 0)` })
        .from(trackingEvents)
        .innerJoin(campaigns, eq(campaigns.id, trackingEvents.campaignId))
        .where(and(
          eq(campaigns.workspaceId, b.id),
          eq(trackingEvents.eventType, 'conversion'),
          gte(trackingEvents.createdAt, since),
        ))
        .get();
      const delivered = Number(dRow?.c ?? 0);
      byBrand.push({
        slug: b.slug,
        name: b.name,
        sent: delivered,
        delivered,
        verifiedOpens: Number(vOpensRow?.c ?? 0),
        rawOpens: Number(rOpensRow?.c ?? 0),
        clicks: Number(cRow?.c ?? 0),
        conversions: Number(convRow?.c ?? 0),
        conversionValue: Number(convRow?.v ?? 0),
      });
    }

    // ─── Top Campaigns (nach conversion-value desc, fallback click-count) ──
    const topRows = db
      .select({
        id: campaigns.id,
        name: campaigns.name,
        workspaceSlug: workspaces.slug,
        delivered: sql<number>`(SELECT COUNT(*) FROM tracking_events WHERE campaign_id = ${campaigns.id} AND event_type = 'delivered')`,
        verifiedOpens: sql<number>`(SELECT COUNT(DISTINCT recipient_id) FROM tracking_events WHERE campaign_id = ${campaigns.id} AND event_type = 'open' AND (is_mpp IS NULL OR is_mpp = 0))`,
        rawOpens: sql<number>`(SELECT COUNT(DISTINCT recipient_id) FROM tracking_events WHERE campaign_id = ${campaigns.id} AND event_type = 'open')`,
        clicks: sql<number>`(SELECT COUNT(DISTINCT recipient_id) FROM tracking_events WHERE campaign_id = ${campaigns.id} AND event_type = 'click')`,
        conversions: sql<number>`(SELECT COUNT(*) FROM tracking_events WHERE campaign_id = ${campaigns.id} AND event_type = 'conversion')`,
        conversionValue: sql<number>`(SELECT COALESCE(SUM(value), 0) FROM tracking_events WHERE campaign_id = ${campaigns.id} AND event_type = 'conversion')`,
      })
      .from(campaigns)
      .innerJoin(workspaces, eq(workspaces.id, campaigns.workspaceId))
      .where(and(gte(campaigns.startedAt, since), eq(campaigns.kind, 'marketing')))
      .orderBy(desc(sql<number>`(SELECT COALESCE(SUM(value), 0) FROM tracking_events WHERE campaign_id = ${campaigns.id} AND event_type = 'conversion')`))
      .limit(10)
      .all();
    const topCampaigns: TopCampaign[] = topRows.map((r) => ({
      id: r.id,
      name: r.name,
      workspaceSlug: r.workspaceSlug,
      sent: Number(r.delivered),
      verifiedOpens: Number(r.verifiedOpens),
      rawOpens: Number(r.rawOpens),
      clicks: Number(r.clicks),
      conversions: Number(r.conversions),
      conversionValue: Number(r.conversionValue),
    }));

    // ─── Hot Leads (letzte 24h) ──────────────────────────────────────────
    const hotSince = new Date(Date.now() - 24 * 3600_000);
    // Recipients die in den letzten 24h geklickt haben + Behavior-Count > 0
    const hotRows = db
      .select({
        recipientId: recipients.id,
        email: recipients.email,
        firstName: recipients.firstName,
        lastName: recipients.lastName,
        campaignName: campaigns.name,
        workspaceSlug: workspaces.slug,
        clickedAt: sql<number>`(SELECT MAX(created_at) FROM tracking_events WHERE recipient_id = ${recipients.id} AND event_type = 'click')`,
        behaviorCount: sql<number>`(SELECT COUNT(*) FROM behavior_events WHERE recipient_id = ${recipients.id})`,
      })
      .from(recipients)
      .innerJoin(campaigns, eq(campaigns.id, recipients.campaignId))
      .innerJoin(workspaces, eq(workspaces.id, campaigns.workspaceId))
      .innerJoin(trackingEvents, eq(trackingEvents.recipientId, recipients.id))
      .where(and(
        eq(trackingEvents.eventType, 'click'),
        gte(trackingEvents.createdAt, hotSince),
      ))
      .groupBy(recipients.id)
      .orderBy(desc(sql`MAX(${trackingEvents.createdAt})`))
      .limit(20)
      .all();
    const hotLeads: HotLead[] = hotRows
      .filter((r) => Number(r.behaviorCount) > 0)
      .map((r) => ({
        recipientId: r.recipientId,
        email: r.email,
        firstName: r.firstName ?? null,
        lastName: r.lastName ?? null,
        campaignName: r.campaignName,
        workspaceSlug: r.workspaceSlug,
        clickedAt: new Date(Number(r.clickedAt)).toISOString(),
        behaviorCount: Number(r.behaviorCount),
      }));

    return NextResponse.json({
      ok: true,
      range: { fromIso: since.toISOString(), toIso: new Date().toISOString(), days },
      totals,
      byBrand,
      topCampaigns,
      hotLeads,
    });
  } catch (e) {
    return NextResponse.json(
      { error: e instanceof Error ? e.message : 'Dashboard lookup failed' },
      { status: 500 },
    );
  } finally {
    // behaviorEvents-Import wird in der Future-Expansion verwendet (Hot-Lead-Behavior-Aggregat)
    void behaviorEvents;
  }
});
