// src/lib/content/stats.ts — per-page GSC performance over a period (+ previous period).
import "server-only";
import { and, eq, gte, isNotNull, sql } from "drizzle-orm";
import { unstable_cache } from "next/cache";
import { db } from "@/lib/db/client";
import { contentItems, gscPageSnapshots } from "@/lib/db/domain-schema";
import { timed } from "@/lib/observability/timed";
import {
  densifySeries,
  detectDrop,
  normalizeUrl,
  weightedPosition,
} from "./stats-logic";

export type StatsPeriod = 7 | 28 | 90;

export interface PageStatRow {
  page: string;
  clicks: number;
  impressions: number;
  /** Fraction 0..1, 3 décimales. */
  ctr: number | null;
  /** 1 décimale, pondérée par impressions. */
  position: number | null;
  prevClicks: number | null;
  prevImpressions: number | null;
  prevPosition: number | null;
  /** Clics/jour sur la période courante, trous = 0. */
  spark: number[];
  contentItemId: string | null;
  contentTitle: string | null;
  dropping: boolean;
}

export interface StatsKpis {
  clicks: number;
  impressions: number;
  ctr: number | null;
  position: number | null;
  prevClicks: number | null;
  prevImpressions: number | null;
  prevCtr: number | null;
  prevPosition: number | null;
}

const iso = (dd: Date) => dd.toISOString().slice(0, 10);
const ctrOf = (clicks: number, impressions: number): number | null =>
  impressions > 0 ? Math.round((clicks / impressions) * 1000) / 1000 : null;
const num = (v: unknown): number => Number(v ?? 0);
const numOrNull = (v: unknown): number | null => (v == null ? null : Number(v));
const round1 = (v: number) => Math.round(v * 10) / 10;

/** Per-page aggregates: current period vs previous equal period, daily spark, content match. */
// Le worker GSC écrit 1×/jour (cron Dokploy, fenêtre J-5→J-1) : 1 h de
// staleness est indolore et évite de re-scanner l'historique à chaque visite.
// Invalidation time-based uniquement — le writer est du SQL direct côté Rust,
// il ne peut pas revalidateTag. Le tag existe pour une invalidation app future.
export async function getPageStats(
  siteId: string,
  periodDays: StatsPeriod,
): Promise<{ rows: PageStatRow[]; kpis: StatsKpis }> {
  // Timing EXTERNE au cache : les hits loguent ~0-1 ms, les miss le coût réel
  // — le flux [query-timing] porte donc aussi la fréquence et le hit-rate,
  // nécessaires pour reconstituer la charge analytique totale (décision Phase 1).
  return timed("gsc-stats.total", { siteId, period: periodDays }, () =>
    unstable_cache(
      () => computePageStats(siteId, periodDays),
      ["gsc-page-stats", siteId, String(periodDays)],
      { revalidate: 3600, tags: [`gsc-stats-${siteId}`] },
    )(),
  );
}

// curStart/prevStart sont dérivées de new Date() ICI (dans le scope caché) :
// capturées hors du cache, la clé servirait des fenêtres périmées. La clé
// n'inclut pas la date ; revalidate 3600 borne la dérive de fenêtre à 1 h,
// acceptable pour des données J-1.
async function computePageStats(
  siteId: string,
  periodDays: StatsPeriod,
): Promise<{ rows: PageStatRow[]; kpis: StatsKpis }> {
  const now = Date.now();
  const curStart = iso(new Date(now - periodDays * 86400000));
  const prevStart = iso(new Date(now - 2 * periodDays * 86400000));
  const curEnd = iso(new Date(now - 86400000)); // J-1 : le worker n'écrit jamais J

  const agg = await timed("gsc-stats.agg", { siteId, period: periodDays }, () =>
    db
      .select({
        page: gscPageSnapshots.page,
        clicks: sql<string>`coalesce(sum(${gscPageSnapshots.clicks}) filter (where ${gscPageSnapshots.date} >= ${curStart}), 0)`,
        impressions: sql<string>`coalesce(sum(${gscPageSnapshots.impressions}) filter (where ${gscPageSnapshots.date} >= ${curStart}), 0)`,
        posWeighted: sql<
          string | null
        >`sum(${gscPageSnapshots.position} * ${gscPageSnapshots.impressions}) filter (where ${gscPageSnapshots.date} >= ${curStart})`,
        prevClicks: sql<
          string | null
        >`sum(${gscPageSnapshots.clicks}) filter (where ${gscPageSnapshots.date} < ${curStart})`,
        prevImpressions: sql<
          string | null
        >`sum(${gscPageSnapshots.impressions}) filter (where ${gscPageSnapshots.date} < ${curStart})`,
        prevPosWeighted: sql<
          string | null
        >`sum(${gscPageSnapshots.position} * ${gscPageSnapshots.impressions}) filter (where ${gscPageSnapshots.date} < ${curStart})`,
      })
      .from(gscPageSnapshots)
      .where(
        and(
          eq(gscPageSnapshots.siteId, siteId),
          gte(gscPageSnapshots.date, prevStart),
        ),
      )
      .groupBy(gscPageSnapshots.page),
  );

  const daily = await timed(
    "gsc-stats.daily",
    { siteId, period: periodDays },
    () =>
      db
        .select({
          page: gscPageSnapshots.page,
          date: gscPageSnapshots.date,
          clicks: gscPageSnapshots.clicks,
        })
        .from(gscPageSnapshots)
        .where(
          and(
            eq(gscPageSnapshots.siteId, siteId),
            gte(gscPageSnapshots.date, curStart),
          ),
        ),
  );
  const seriesByPage = new Map<string, { date: string; clicks: number }[]>();
  for (const r of daily) {
    const arr = seriesByPage.get(r.page) ?? [];
    arr.push({ date: r.date, clicks: r.clicks });
    seriesByPage.set(r.page, arr);
  }

  const items = await timed(
    "gsc-stats.content",
    { siteId, period: periodDays },
    () =>
      db
        .select({
          id: contentItems.id,
          title: contentItems.title,
          url: contentItems.url,
        })
        .from(contentItems)
        .where(
          and(eq(contentItems.siteId, siteId), isNotNull(contentItems.url)),
        ),
  );
  const itemByUrl = new Map(
    items
      .filter((i) => i.url)
      .map((i) => [normalizeUrl(i.url as string), i] as const),
  );

  const rows: PageStatRow[] = agg
    .map((r) => {
      const clicks = num(r.clicks);
      const impressions = num(r.impressions);
      const position =
        impressions > 0 && r.posWeighted != null
          ? round1(Number(r.posWeighted) / impressions)
          : null;
      const prevImpressions = numOrNull(r.prevImpressions);
      const prevClicks = numOrNull(r.prevClicks);
      const prevPosition =
        prevImpressions && r.prevPosWeighted != null
          ? round1(Number(r.prevPosWeighted) / prevImpressions)
          : null;
      const item = itemByUrl.get(normalizeUrl(r.page)) ?? null;
      return {
        page: r.page,
        clicks,
        impressions,
        ctr: ctrOf(clicks, impressions),
        position,
        prevClicks,
        prevImpressions,
        prevPosition,
        spark: densifySeries(seriesByPage.get(r.page) ?? [], curStart, curEnd),
        contentItemId: item?.id ?? null,
        contentTitle: item?.title ?? null,
        dropping: detectDrop(
          { clicks, impressions, position },
          {
            clicks: prevClicks ?? 0,
            impressions: prevImpressions ?? 0,
            position: prevPosition,
          },
        ),
      };
    })
    .filter((r) => r.impressions > 0 || (r.prevImpressions ?? 0) > 0)
    .sort((a, b) => b.clicks - a.clicks);

  const kClicks = rows.reduce((s, r) => s + r.clicks, 0);
  const kImpr = rows.reduce((s, r) => s + r.impressions, 0);
  const hasPrev = rows.some((r) => r.prevImpressions != null);
  const kPrevClicks = hasPrev
    ? rows.reduce((s, r) => s + (r.prevClicks ?? 0), 0)
    : null;
  const kPrevImpr = hasPrev
    ? rows.reduce((s, r) => s + (r.prevImpressions ?? 0), 0)
    : null;
  const kpis: StatsKpis = {
    clicks: kClicks,
    impressions: kImpr,
    ctr: ctrOf(kClicks, kImpr),
    position: weightedPosition(
      rows.map((r) => ({ position: r.position, impressions: r.impressions })),
    ),
    prevClicks: kPrevClicks,
    prevImpressions: kPrevImpr,
    prevCtr:
      kPrevClicks != null && kPrevImpr != null
        ? ctrOf(kPrevClicks, kPrevImpr)
        : null,
    prevPosition: hasPrev
      ? weightedPosition(
          rows.map((r) => ({
            position: r.prevPosition,
            impressions: r.prevImpressions ?? 0,
          })),
        )
      : null,
  };
  return { rows, kpis };
}