/**
 * Workspace-Dashboard-Aggregat-API.
 *
 * Liefert pro Workspace die wichtigsten KPIs über alle Campaigns innerhalb eines
 * Zeitraums (default 30 Tage): Sends, Opens (verified), Clicks, Conversions
 * mit Umsatz-Summe. Quelle: `tracking_events` aggregiert über `campaigns.workspace_id`.
 *
 * Query-Params:
 *   - days (1..365, default 30)
 *
 * Performance: pro Workspace 1-2 grouped queries — Skala bis ~10k Events pro Workspace
 * problemlos. Bei mehr → Materialisierung in eine Stats-Snapshot-Tabelle erwägen.
 */
import { NextRequest, NextResponse } from 'next/server';
import { db } from '@/lib/db/client';
import { workspaces, campaigns, trackingEvents, emailQueue } from '@/lib/db/schema';
import { eq, and, sql, gte } from 'drizzle-orm';
import { withAuth } from '@/core/auth';

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

export interface BrandDashboardRow {
  workspaceId: string;
  workspaceSlug: string;
  brandName: string;
  primaryColor: string;
  sends: number;
  opens: number;
  opensVerified: number;
  clicks: number;
  conversions: number;
  conversionValue: number;
  attributionModel: string;
  attributionLookbackDays: 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 since = new Date(Date.now() - days * 86400_000);

  try {
    const allBrands = db.select({
      id: workspaces.id,
      slug: workspaces.slug,
      name: workspaces.name,
      primaryColor: workspaces.primaryColor,
      attributionModel: workspaces.attributionModel,
      attributionLookbackDays: workspaces.attributionLookbackDays,
    }).from(workspaces).all();

    // Sends pro Workspace (count emailQueue.status='sent', joined via campaign.workspaceId)
    const sendsByBrand = db
      .select({
        workspaceId: campaigns.workspaceId,
        sends: sql<number>`count(*)`,
      })
      .from(emailQueue)
      .innerJoin(campaigns, eq(emailQueue.campaignId, campaigns.id))
      .where(and(
        eq(emailQueue.status, 'sent'),
        gte(emailQueue.sentAt, since),
      ))
      .groupBy(campaigns.workspaceId)
      .all();
    const sendsMap = new Map(sendsByBrand.map((r) => [r.workspaceId, Number(r.sends)]));

    // Events pro Workspace: opens (raw + verified), clicks, conversions (count + value)
    const eventsByBrand = db
      .select({
        workspaceId: campaigns.workspaceId,
        eventType: trackingEvents.eventType,
        isMpp: trackingEvents.isMpp,
        c: sql<number>`count(*)`,
        v: sql<number>`coalesce(sum(${trackingEvents.value}), 0)`,
      })
      .from(trackingEvents)
      .innerJoin(campaigns, eq(trackingEvents.campaignId, campaigns.id))
      .where(gte(trackingEvents.createdAt, since))
      .groupBy(campaigns.workspaceId, trackingEvents.eventType, trackingEvents.isMpp)
      .all();

    interface BrandAgg { opens: number; opensVerified: number; clicks: number; conversions: number; conversionValue: number }
    const aggMap = new Map<string, BrandAgg>();
    for (const r of eventsByBrand) {
      const cur = aggMap.get(r.workspaceId) ?? { opens: 0, opensVerified: 0, clicks: 0, conversions: 0, conversionValue: 0 };
      const cnt = Number(r.c);
      const val = Number(r.v);
      if (r.eventType === 'open') {
        cur.opens += cnt;
        if (!r.isMpp) cur.opensVerified += cnt;
      } else if (r.eventType === 'click') {
        cur.clicks += cnt;
      } else if (r.eventType === 'conversion') {
        cur.conversions += cnt;
        cur.conversionValue += val;
      }
      aggMap.set(r.workspaceId, cur);
    }

    const rows: BrandDashboardRow[] = allBrands.map((b) => {
      const agg = aggMap.get(b.id) ?? { opens: 0, opensVerified: 0, clicks: 0, conversions: 0, conversionValue: 0 };
      return {
        workspaceId: b.id,
        workspaceSlug: b.slug,
        brandName: b.name,
        primaryColor: b.primaryColor,
        sends: sendsMap.get(b.id) ?? 0,
        opens: agg.opens,
        opensVerified: agg.opensVerified,
        clicks: agg.clicks,
        conversions: agg.conversions,
        conversionValue: agg.conversionValue,
        attributionModel: b.attributionModel ?? 'last-click',
        attributionLookbackDays: b.attributionLookbackDays ?? 30,
      };
    });

    return NextResponse.json({ ok: true, days, since: since.toISOString(), workspaces: rows });
  } catch (e) {
    return NextResponse.json(
      { error: e instanceof Error ? e.message : 'Workspace-stats lookup failed' },
      { status: 500 },
    );
  }
});
