// src/lib/keywords/queries.ts — server-side reads for keywords (per-site).
import "server-only";
import { and, asc, eq, inArray, notExists, sql } from "drizzle-orm";
import { unstable_cache } from "next/cache";
import { buildContentAnalysis } from "@/lib/content/analysis";
import type { ContentAnalysis } from "@/lib/content/types";
import { db } from "@/lib/db/client";
import {
  keywordExclusions,
  keywords,
  keywordTargets,
  keywordThemes,
  sitePages,
} from "@/lib/db/domain-schema";
import type { TargetCandidate } from "@/lib/onboarding/first-target";
import type { KeywordOverview } from "./overview";
import { queryKeywordOverview } from "./overview-query";
import type { SerpSnapshot } from "./serp-types";
import type { TargetStatus, TargetsState } from "./targets";
import { EXCLUDED_TARGET_STATUSES } from "./targets";
import type { Keyword } from "./types";

/** Cache tag for a site's keyword-overview aggregate (ELEVA-78). In-process
 *  keyword mutations call `revalidateTag(keywordOverviewTag(siteId))`. */
export const keywordOverviewTag = (siteId: string): string =>
  `keyword-overview:${siteId}`;

type KeywordRow = typeof keywords.$inferSelect;
/** The subset of columns `toKeyword` reads — the full row minus `serp`/`semantic`
 *  (heavy jsonb, TOAST — jamais mappées sur le `Keyword` applicatif) and the
 *  `*_primary` STORED generated columns (0058: dénormalisation SQL-only,
 *  consommées en tri/filtre serveur), minus `targetPageId` (jamais exposé — on
 *  projette l'URL, pas l'id), PLUS `targetPageUrl` (LEFT JOIN site_pages, 0064).
 *  `targetPageSource`/`Score` restent des colonnes `keywords`. `targetPageUrl`
 *  est optionnel : les lectures qui ne joignent pas site_pages (getDispatchData)
 *  omettent la colonne → `targetPage` retombe à null. Callers may pass the full
 *  row (assignable) or the lean projection. */
export type KeywordReadRow = Omit<
  KeywordRow,
  | "serp"
  | "semantic"
  // Rail de statut d'enrichissement : servi par son action dédiée
  // (fetchEnrichStatusesAction), jamais mappé dans le `Keyword` UI ni projeté
  // par `keywordSelect` — exclu de la lecture lean au même titre que serp/semantic.
  | "enrichment"
  | "volumePrimary"
  | "kdPrimary"
  | "cpcPrimary"
  | "growthPrimary"
  | "targetPageId"
> & {
  targetPageUrl?: string | null;
};

/** Map a DB row to the UI Keyword (flat lang/country -> nested locale).
 *  Exported for list-query.ts (searchKeywords maps the same projection). */
export function toKeyword(r: KeywordReadRow): Keyword {
  return {
    id: r.id,
    siteId: r.siteId,
    term: r.term,
    locale: { lang: r.lang, country: r.country },
    theme: r.theme,
    status: r.status,
    intent: r.intent,
    stages: r.stages,
    primarySource: r.primarySource,
    sources: r.sources,
    metrics: r.metrics,
    trend: r.trend,
    trendBySource: r.trendBySource ?? {},
    serpFeatures: r.serpFeatures ?? undefined,
    briefs: r.briefs,
    gsc: r.gsc ?? null,
    gscByCountry: r.gscByCountry ?? {},
    enrichmentExports: r.enrichmentExports ?? {},
    // Page ciblée (0064) : `url` vient du LEFT JOIN site_pages, la source/score
    // des colonnes `keywords`. Un mapping n'est réel que si les deux sont là.
    targetPage:
      r.targetPageUrl && r.targetPageSource
        ? {
            url: r.targetPageUrl,
            source: r.targetPageSource,
            score: r.targetPageScore ?? null,
          }
        : null,
  };
}

/** Explicit column projection for the UI Keyword — every field `toKeyword`
 *  reads, EXCLUDING the heavy `serp` jsonb (TOAST) which is never mapped here.
 *  Keeping this in sync with `toKeyword` keeps the reservoir/recherche read lean. */
export const keywordSelect = {
  id: keywords.id,
  siteId: keywords.siteId,
  term: keywords.term,
  lang: keywords.lang,
  country: keywords.country,
  theme: keywords.theme,
  status: keywords.status,
  intent: keywords.intent,
  stages: keywords.stages,
  primarySource: keywords.primarySource,
  sources: keywords.sources,
  metrics: keywords.metrics,
  trend: keywords.trend,
  trendBySource: keywords.trendBySource,
  serpFeatures: keywords.serpFeatures,
  briefs: keywords.briefs,
  gsc: keywords.gsc,
  gscByCountry: keywords.gscByCountry,
  enrichmentExports: keywords.enrichmentExports,
  // Page ciblée (0064) : `url` via LEFT JOIN site_pages (toute lecture avec ce
  // select DOIT joindre site_pages sur targetPageId, sinon la colonne manque).
  targetPageUrl: sitePages.url,
  targetPageSource: keywords.targetPageSource,
  targetPageScore: keywords.targetPageScore,
} as const;

/** Keywords belonging to a site, sorted by term. */
export async function getKeywordsForSite(siteId: string): Promise<Keyword[]> {
  const rows = await db
    .select(keywordSelect)
    .from(keywords)
    .leftJoin(sitePages, eq(keywords.targetPageId, sitePages.id))
    .where(eq(keywords.siteId, siteId))
    .orderBy(asc(keywords.term));
  return rows.map(toKeyword);
}

/**
 * Candidats au PREMIER ciblage du parcours d'entrée (étape `target-country`).
 *
 * Deux règles, et aucune n'est cosmétique.
 *
 * 1. **On exclut les mots-clés déjà ciblés sur `country` au statut `planned` ou
 *    `live`** (voir `EXCLUDED_TARGET_STATUSES` dans `./targets` pour la
 *    frontière et sa raison). `setKeywordTargetAction` écrit un
 *    `onConflictDoUpdate({ set: { status } })` aveugle, alors que `live` est
 *    au-dessus de `planned` dans `STATUS_RANK` et que le ciblage en masse refuse
 *    explicitement de rétrograder. Sans ce filtre : un site dont toutes les
 *    cibles sont passées en `live` a son drapeau `target-country` à faux,
 *    l'étape requise se rouvre, propose de planifier un mot-clé DÉJÀ PUBLIÉ, et
 *    le clic le régresse en `planned` sans un mot.
 * 2. **Tri volume décroissant, valeurs nulles en dernier, puis terme et id.**
 *    Le départage à volumes tous inconnus de `pickFirstTargetCandidate` repose
 *    sur un `sort` stable, donc sur l'ordre reçu d'ici — or « volume DESC NULLS
 *    LAST » seul ne départage RIEN quand tous les volumes sont nuls, cas normal
 *    d'un réservoir Search Console ou saisi à la main : Postgres rendrait un
 *    ordre arbitraire, et deux rendus consécutifs proposeraient deux termes
 *    différents. Les clés secondaires rendent la promesse vraie. La clé
 *    primaire reste celle de l'index `keywords_site_volume_idx`.
 *
 * `country` nul (site antérieur à la garde de saisie) : aucune exclusion à
 * faire — l'étape rendra son état `no-country` quoi qu'il arrive.
 *
 * Tenancy : filtré par `site_id`, l'appelant porte le contrôle d'accès (même
 * contrat que `getKeywordsForSite`).
 */
export async function getTargetCandidates(
  siteId: string,
  country: string | null,
): Promise<TargetCandidate[]> {
  const bySite = eq(keywords.siteId, siteId);
  const where = country
    ? and(
        bySite,
        notExists(
          db
            .select({ one: sql<number>`1` })
            .from(keywordTargets)
            .where(
              and(
                eq(keywordTargets.keywordId, keywords.id),
                eq(keywordTargets.siteId, siteId),
                eq(keywordTargets.country, country),
                inArray(keywordTargets.status, EXCLUDED_TARGET_STATUSES),
              ),
            ),
        ),
      )
    : bySite;
  return db
    .select({
      id: keywords.id,
      term: keywords.term,
      volume: keywords.volumePrimary,
    })
    .from(keywords)
    .where(where)
    .orderBy(
      sql`${keywords.volumePrimary} DESC NULLS LAST`,
      asc(keywords.term),
      // `term` n'est pas unique par site (même terme, deux locales) : l'id
      // ferme le dernier degré de liberté.
      asc(keywords.id),
    )
    .limit(20);
}

/** Horodatages de dernière sync (footer du menu « Sync »), partagés RSC→client. */
export type KeywordsSyncStatus = {
  lastGscSyncAt: string | null;
  lastSerankingSyncAt: string | null;
  lastMangoolsSyncAt: string | null;
};

/** Last-sync timestamps for a site's keywords, for the « Sync » menu footer.
 *  Reads the max `updatedAt` stamped inside the `gsc` jsonb and inside
 *  `metrics->'seranking'` / `metrics->'mangools'` across all the site's rows.
 *  All are ISO 8601 UTC strings, so a lexicographic `max()` is equivalent to a
 *  chronological one. Tenancy : filtered by `site_id` here ; the caller owns
 *  the auth check (same contract as `getKeywordsForSite`). */
export async function getKeywordsSyncStatus(
  siteId: string,
): Promise<KeywordsSyncStatus> {
  const [row] = await db
    .select({
      lastGscSyncAt: sql<string | null>`max((${keywords.gsc}->>'updatedAt'))`,
      lastSerankingSyncAt: sql<
        string | null
      >`max((${keywords.metrics}->'seranking'->>'updatedAt'))`,
      lastMangoolsSyncAt: sql<
        string | null
      >`max((${keywords.metrics}->'mangools'->>'updatedAt'))`,
    })
    .from(keywords)
    .where(eq(keywords.siteId, siteId));
  return {
    lastGscSyncAt: row?.lastGscSyncAt ?? null,
    lastSerankingSyncAt: row?.lastSerankingSyncAt ?? null,
    lastMangoolsSyncAt: row?.lastMangoolsSyncAt ?? null,
  };
}

/**
 * Cached keyword overview for a site (ELEVA-78). Computed via
 * `queryKeywordOverview` (overview-query.ts, P7) — 8 SQL aggregate queries
 * over `keywords`/`backlinks`, not a JS scan of every keyword row (+ jsonb);
 * `getKeywordsForSite` is NOT called on this path. We cache it keyed by
 * siteId with a per-site tag so in-process mutations can invalidate exactly
 * (`revalidateTag(keywordOverviewTag(id))`), plus a short TTL so out-of-band
 * writers self-heal: the analytics service is a SEPARATE Bun process that
 * upserts keywords directly and cannot call `revalidateTag` here, so the 30s
 * `revalidate` bounds staleness after a heavy import. The caller is
 * responsible for the auth/tenancy check (this is a pure, cacheable read
 * keyed only by siteId — no request-scoped inputs).
 */
export async function getCachedKeywordOverview(
  siteId: string,
): Promise<KeywordOverview> {
  return unstable_cache(queryKeywordOverview, ["keyword-overview", siteId], {
    tags: [keywordOverviewTag(siteId)],
    revalidate: 30,
  })(siteId);
}

/** Tenancy filter (ELEVA-72) — keeps only the ids that actually belong to the
 *  site; foreign ids (autre site/org) are silently dropped (fail-closed bulk). */
export async function filterKeywordIdsForSite(
  siteId: string,
  ids: string[],
): Promise<string[]> {
  if (ids.length === 0) return [];
  const rows = await db
    .select({ id: keywords.id })
    .from(keywords)
    .where(and(eq(keywords.siteId, siteId), inArray(keywords.id, ids)));
  return rows.map((r) => r.id);
}

/** The set of slugs excluded for a site (a GSC re-sync skips them). */
export async function getExcludedSlugs(siteId: string): Promise<Set<string>> {
  const rows = await db
    .select({ slug: keywordExclusions.slug })
    .from(keywordExclusions)
    .where(eq(keywordExclusions.siteId, siteId));
  return new Set(rows.map((r) => r.slug));
}

export type Exclusion = { slug: string; createdAt: Date };

/** A site's exclusions (slug + when), for the manage-exclusions UI. */
export async function getExclusionsForSite(
  siteId: string,
): Promise<Exclusion[]> {
  return db
    .select({
      slug: keywordExclusions.slug,
      createdAt: keywordExclusions.createdAt,
    })
    .from(keywordExclusions)
    .where(eq(keywordExclusions.siteId, siteId))
    .orderBy(asc(keywordExclusions.slug));
}

/** Themes (categories) for a site, sorted by label. */
export async function getThemesForSite(
  siteId: string,
): Promise<{ slug: string; label: string }[]> {
  return db
    .select({ slug: keywordThemes.slug, label: keywordThemes.label })
    .from(keywordThemes)
    .where(eq(keywordThemes.siteId, siteId))
    .orderBy(asc(keywordThemes.label));
}

export interface PlanningRow {
  keyword: Keyword;
  analysis: ContentAnalysis;
  targets: Partial<Record<string, TargetStatus>>;
  /** SERP snapshot for the keyword's primary locale (country), or null if not yet imported. */
  snapshot: SerpSnapshot | null;
}

/** Keywords at stage `planning`, with readiness + per-country targets. */
export async function getPlanningKeywords(
  siteId: string,
): Promise<PlanningRow[]> {
  const rows = await db
    // Explicit projection: `toKeyword` reads every `keywordSelect` column and
    // `buildContentAnalysis`/the snapshot lookup below read `serp` + `briefs`
    // (`briefs` already in `keywordSelect`). That's the whole row minus nothing
    // extra, but stated explicitly so an added heavy column can't silently
    // bloat this site-wide, unbounded read.
    .select({ ...keywordSelect, serp: keywords.serp })
    .from(keywords)
    .leftJoin(sitePages, eq(keywords.targetPageId, sitePages.id))
    .where(
      and(
        eq(keywords.siteId, siteId),
        // Containment (`@>`) instead of `'planning' = any(...)` so the
        // `keywords_stages_gin` GIN index is usable; same logical filter
        // (rows whose `stages` array contains 'planning').
        sql`${keywords.stages} @> ARRAY['planning']`,
      ),
    )
    .orderBy(asc(keywords.term));
  const targetsBySite = await getTargetsForSite(siteId);
  return rows.map((r) => {
    const kw = toKeyword(r);
    return {
      keyword: kw,
      analysis: buildContentAnalysis(r.serp, r.briefs, kw.locale.country),
      targets: targetsBySite[kw.id] ?? {},
      snapshot: r.serp?.[kw.locale.country] ?? null,
    };
  });
}

export interface DispatchCountry {
  country: string;
  status: TargetStatus;
  snapshot: SerpSnapshot | null;
  analysis: ContentAnalysis;
}
export interface DispatchData {
  keyword: Keyword;
  countries: DispatchCountry[];
}

/** Single keyword + per planned-country snapshot + readiness, for the Dispatch view. */
export async function getDispatchData(
  siteId: string,
  keywordId: string,
): Promise<DispatchData | null> {
  const [r] = await db
    .select()
    .from(keywords)
    .where(and(eq(keywords.siteId, siteId), eq(keywords.id, keywordId)))
    .limit(1);
  if (!r) return null;
  const targets = (await getTargetsForSite(siteId))[keywordId] ?? {};
  const countries: DispatchCountry[] = Object.entries(targets)
    .filter(([, s]) => s === "planned")
    .map(([country, status]) => ({
      country,
      status: status as TargetStatus,
      snapshot: r.serp?.[country] ?? null,
      analysis: buildContentAnalysis(r.serp, r.briefs, country),
    }));
  return { keyword: toKeyword(r), countries };
}

/** Persisted per-keyword × country targeting for a site (keywordId -> country -> status). */
export async function getTargetsForSite(siteId: string): Promise<TargetsState> {
  const rows = await db
    .select({
      keywordId: keywordTargets.keywordId,
      country: keywordTargets.country,
      status: keywordTargets.status,
    })
    .from(keywordTargets)
    .where(eq(keywordTargets.siteId, siteId));
  const state: TargetsState = {};
  for (const r of rows) {
    const entry = state[r.keywordId] ?? {};
    entry[r.country] = r.status;
    state[r.keywordId] = entry;
  }
  return state;
}