// src/lib/backlink/overview.ts — agrégats lecture seule du dashboard netlinking.
import "server-only";
import { eq, inArray, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import {
  type BacklinkStatus,
  backlinks,
  domainRatings,
  linkSources,
  sites,
} from "@/lib/db/domain-schema";
import { buildDrBuckets, type DrBucket } from "./overview-logic";
import { getCadence } from "./planning";
import { FOOTPRINT_THRESHOLD, relevanceScore } from "./relevance";
import type { SourceKind } from "./types";

export type BacklinkOverview = {
  pool: {
    sources: number;
    byKind: { kind: SourceKind; count: number }[]; // top 5, desc
    byLanguage: { language: string; count: number }[]; // desc
    drBuckets: DrBucket[];
  };
  risks: {
    footprintDomains: { domainNorm: string; siteCount: number }[];
    toxicSite: number;
    toxicOrg: number;
    lostSite: number;
    lostOrg: number;
    disavowSite: number;
  };
  campaign: {
    counters: Record<BacklinkStatus, number>;
    cadence: { thisMonth: number; lastMonth: number };
  };
  opportunities: {
    id: string;
    platform: string;
    domainNorm: string;
    kind: SourceKind;
    dr: number | null;
    da: number | null;
    language: string;
    score: number;
  }[];
  activity: {
    type: "prospect" | "live";
    platform: string;
    domainNorm: string;
    at: string; // ISO
  }[];
};

export async function getBacklinkOverview(
  orgId: string,
  siteId: string,
): Promise<BacklinkOverview> {
  const site = (await db.select().from(sites).where(eq(sites.id, siteId)))[0];
  const siteLanguage = site?.primaryLanguage ?? "fr";

  const sources = await db
    .select()
    .from(linkSources)
    .where(eq(linkSources.orgId, orgId));

  const domains = [...new Set(sources.map((s) => s.domainNorm))];
  const drRows = domains.length
    ? await db
        .select({ domainNorm: domainRatings.domainNorm, dr: domainRatings.dr })
        .from(domainRatings)
        .where(inArray(domainRatings.domainNorm, domains))
    : [];
  const drByDomain = new Map(drRows.map((d) => [d.domainNorm, d.dr]));

  // Backlinks du SITE actif joints à leur source (campagne + activité + risques site).
  // Borné par la taille du site, pas de l'org : ride `backlinks_site_idx`.
  const siteLinks = sources.length
    ? await db
        .select({
          linkSourceId: backlinks.linkSourceId,
          status: backlinks.status,
          toxic: backlinks.toxic,
          disavow: backlinks.disavow,
          firstSeen: backlinks.firstSeen,
          createdAt: backlinks.createdAt,
          platform: linkSources.platform,
          domainNorm: linkSources.domainNorm,
        })
        .from(backlinks)
        .innerJoin(linkSources, eq(linkSources.id, backlinks.linkSourceId))
        .where(eq(backlinks.siteId, siteId))
    : [];

  // Agrégats ORG-WIDE poussés en SQL (pas de matérialisation de lignes) :
  //  - footprint : par domaine, nb de sites distincts (filtré ≥ seuil côté SQL).
  //  - toxicOrg / lostOrg : compteurs filtrés sur tout l'org.
  const footprintRows = sources.length
    ? await db
        .select({
          domainNorm: linkSources.domainNorm,
          siteCount: sql<string>`count(distinct ${backlinks.siteId})`,
        })
        .from(backlinks)
        .innerJoin(linkSources, eq(linkSources.id, backlinks.linkSourceId))
        .where(eq(linkSources.orgId, orgId))
        .groupBy(linkSources.domainNorm)
        .having(
          sql`count(distinct ${backlinks.siteId}) >= ${FOOTPRINT_THRESHOLD}`,
        )
    : [];

  const orgRisks = sources.length
    ? (
        await db
          .select({
            toxicOrg: sql<string>`count(*) filter (where ${backlinks.toxic})`,
            lostOrg: sql<string>`count(*) filter (where ${backlinks.status} = 'lost')`,
          })
          .from(backlinks)
          .innerJoin(linkSources, eq(linkSources.id, backlinks.linkSourceId))
          .where(eq(linkSources.orgId, orgId))
      )[0]
    : undefined;

  // F1 — pool
  const byKindMap = new Map<SourceKind, number>();
  const byLangMap = new Map<string, number>();
  for (const s of sources) {
    byKindMap.set(s.kind, (byKindMap.get(s.kind) ?? 0) + 1);
    byLangMap.set(s.language, (byLangMap.get(s.language) ?? 0) + 1);
  }
  const byKind = [...byKindMap]
    .map(([kind, count]) => ({ kind, count }))
    .sort((a, b) => b.count - a.count)
    .slice(0, 5);
  const byLanguage = [...byLangMap]
    .map(([language, count]) => ({ language, count }))
    .sort((a, b) => b.count - a.count);
  const drBuckets = buildDrBuckets(
    sources.map((s) => drByDomain.get(s.domainNorm) ?? s.da),
  );

  // F2 — risques : footprint (domaines référençant ≥ FOOTPRINT_THRESHOLD sites).
  const footprintDomains = footprintRows
    .map((d) => ({ domainNorm: d.domainNorm, siteCount: Number(d.siteCount) }))
    .sort((a, b) => b.siteCount - a.siteCount);

  // F3 — campagne du site actif
  const counters: Record<BacklinkStatus, number> = {
    prospect: 0,
    planned: 0,
    in_progress: 0,
    live: 0,
    lost: 0,
  };
  for (const l of siteLinks) counters[l.status] += 1;

  // F4 — top 5 sources non prospectées par le site, par score desc
  const targeted = new Set(siteLinks.map((l) => l.linkSourceId));
  const opportunities = sources
    .filter((s) => !targeted.has(s.id))
    .map((s) => {
      const dr = drByDomain.get(s.domainNorm) ?? null;
      return {
        id: s.id,
        platform: s.platform,
        domainNorm: s.domainNorm,
        kind: s.kind,
        dr,
        da: s.da,
        language: s.language,
        score: relevanceScore(
          { language: s.language, dr, da: s.da },
          siteLanguage,
        ),
      };
    })
    .sort((a, b) => b.score - a.score)
    .slice(0, 5);

  // F5 — activité : prospect créé (createdAt) + passage live (firstSeen)
  const activity = [
    ...siteLinks.map((l) => ({
      type: "prospect" as const,
      platform: l.platform,
      domainNorm: l.domainNorm,
      at: l.createdAt,
    })),
    ...siteLinks
      .filter((l) => l.firstSeen != null)
      .map((l) => ({
        type: "live" as const,
        platform: l.platform,
        domainNorm: l.domainNorm,
        at: l.firstSeen as Date,
      })),
  ]
    .sort((a, b) => b.at.getTime() - a.at.getTime())
    .slice(0, 5)
    .map((e) => ({ ...e, at: e.at.toISOString() }));

  return {
    pool: { sources: sources.length, byKind, byLanguage, drBuckets },
    risks: {
      footprintDomains,
      toxicSite: siteLinks.filter((l) => l.toxic).length,
      toxicOrg: Number(orgRisks?.toxicOrg ?? 0),
      lostSite: siteLinks.filter((l) => l.status === "lost").length,
      lostOrg: Number(orgRisks?.lostOrg ?? 0),
      disavowSite: siteLinks.filter((l) => l.disavow).length,
    },
    campaign: { counters, cadence: await getCadence(siteId) },
    opportunities,
    activity,
  };
}