// src/lib/backlink/planning.ts — board de campagne netlinking d'un site.
import "server-only";
import { and, eq, gte, inArray, lt, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import {
  type BacklinkStatus,
  backlinkOutreach,
  backlinks,
  domainRatings,
  linkSources,
  sites,
} from "@/lib/db/domain-schema";
import { monthBounds } from "./planning-logic";
import { footprintAlert, relevanceScore } from "./relevance";
import type { SourceKind } from "./types";

export type PlanningCard = {
  backlinkId: string;
  status: BacklinkStatus;
  toxic: boolean;
  disavow: boolean;
  firstSeen: string | null;
  source: {
    id: string;
    platform: string;
    domainNorm: string;
    url: string;
    kind: SourceKind;
    dr: number | null;
    da: number | null;
    language: string;
  };
  score: number;
  footprint: number;
  alert: boolean;
  outreach: {
    contact: string | null;
    channel: string | null;
    attempts: number;
    lastContactAt: string | null;
    notes: string | null;
  } | null;
};

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

  // Backlinks du site joints à leur source (isolation stricte par site).
  const rows = await db
    .select({ b: backlinks, s: linkSources })
    .from(backlinks)
    .innerJoin(linkSources, eq(linkSources.id, backlinks.linkSourceId))
    .where(and(eq(backlinks.siteId, siteId), eq(linkSources.orgId, orgId)));
  if (rows.length === 0) return [];

  const domains = [...new Set(rows.map((r) => r.s.domainNorm))];
  const drRows = 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]));

  const sourceIds = [...new Set(rows.map((r) => r.s.id))];
  const fps = await db
    .select({
      linkSourceId: backlinks.linkSourceId,
      footprint: sql<string>`count(distinct ${backlinks.siteId})`,
    })
    .from(backlinks)
    .where(inArray(backlinks.linkSourceId, sourceIds))
    .groupBy(backlinks.linkSourceId);
  const fpBySource = new Map(
    fps.map((f) => [f.linkSourceId, Number(f.footprint)]),
  );

  const backlinkIds = rows.map((r) => r.b.id);
  const out = await db
    .select()
    .from(backlinkOutreach)
    .where(inArray(backlinkOutreach.backlinkId, backlinkIds));
  const outByBacklink = new Map(out.map((o) => [o.backlinkId, o]));

  return rows
    .map(({ b, s }) => {
      const dr = drByDomain.get(s.domainNorm) ?? null;
      const footprint = fpBySource.get(s.id) ?? 0;
      const o = outByBacklink.get(b.id) ?? null;
      return {
        backlinkId: b.id,
        status: b.status,
        toxic: b.toxic,
        disavow: b.disavow,
        firstSeen: b.firstSeen ? b.firstSeen.toISOString() : null,
        source: {
          id: s.id,
          platform: s.platform,
          domainNorm: s.domainNorm,
          url: s.url,
          kind: s.kind,
          dr,
          da: s.da,
          language: s.language,
        },
        score: relevanceScore(
          { language: s.language, dr, da: s.da },
          siteLanguage,
        ),
        footprint,
        alert: footprintAlert(footprint),
        outreach: o
          ? {
              contact: o.contact,
              channel: o.channel,
              attempts: o.attempts,
              lastContactAt: o.lastContactAt
                ? o.lastContactAt.toISOString()
                : null,
              notes: o.notes,
            }
          : null,
      };
    })
    .sort((a, b2) => b2.score - a.score);
}

export async function getCadence(
  siteId: string,
): Promise<{ thisMonth: number; lastMonth: number }> {
  const { thisMonthStart, lastMonthStart, nextMonthStart } = monthBounds(
    new Date(),
  );
  const lastStart = new Date(`${lastMonthStart}T00:00:00Z`);
  const thisStart = new Date(`${thisMonthStart}T00:00:00Z`);
  const nextStart = new Date(`${nextMonthStart}T00:00:00Z`);
  // ISO strings for the raw-sql FILTER bounds: postgres.js only serializes Dates
  // bound through Drizzle's typed operators (gte/lt below), not via sql``.
  const thisIso = thisStart.toISOString();
  const nextIso = nextStart.toISOString();
  const lastIso = lastStart.toISOString();

  // Une seule requête, deux compteurs filtrés (this/last month) — bornée au site.
  const row = (
    await db
      .select({
        thisMonth: sql<string>`count(*) filter (where ${backlinks.firstSeen} >= ${thisIso} and ${backlinks.firstSeen} < ${nextIso})`,
        lastMonth: sql<string>`count(*) filter (where ${backlinks.firstSeen} >= ${lastIso} and ${backlinks.firstSeen} < ${thisIso})`,
      })
      .from(backlinks)
      .where(
        and(
          eq(backlinks.siteId, siteId),
          gte(backlinks.firstSeen, lastStart),
          lt(backlinks.firstSeen, nextStart),
        ),
      )
  )[0];
  return {
    thisMonth: Number(row?.thisMonth ?? 0),
    lastMonth: Number(row?.lastMonth ?? 0),
  };
}