// src/lib/keywords/overview-query.ts — SQL-aggregated keyword overview (P7).
// `buildKeywordOverview` (overview.ts) stays as the pure-JS parity reference
// (+ its unit tests); this module computes the SAME `KeywordOverview` shape
// via 8 aggregate queries (Promise.all) instead of loading every keyword row
// (+ heavy jsonb) into JS. See overview-query.test.ts for the DB-gated parity
// assertion against `buildKeywordOverview`.
import "server-only";
import { asc, eq, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { backlinks as backlinksTable, keywords } from "@/lib/db/domain-schema";
import type {
  CountBy,
  KeywordOverview,
  OpportunityRow,
  TopVolumeRow,
} from "./overview";
import type { SearchIntent } from "./types";

const POSITION_BUCKETS = [
  "top3",
  "top10",
  "quickwin",
  "deep",
  "far",
  "none",
] as const;
const KD_BUCKETS = ["0-20", "21-40", "41-60", "61-80", "81-100"] as const;
const INTENTS: SearchIntent[] = [
  "informational",
  "commercial",
  "transactional",
  "navigational",
  "local",
];

/** Same rounding as `overview.ts`'s `round1` — kept local, not exported from
 *  there, to avoid coupling this SQL path to the JS-aggregation module. */
const round1 = (n: number) => Math.round(n * 10) / 10;

// Gsc position/impressions are extracted as `double precision` (never
// `real`/float4) so the arithmetic below (avg, opportunity score, rounding)
// matches JS's float64 math bit-for-bit — `real`'s ~7-digit precision could
// silently round a position like 4.328571429 differently than JS would,
// flipping a `round1` boundary. `volume_primary`/`kd_primary` stay `real`
// (T2 STORED columns) — realistic SEO volumes/kd are exact integers well
// under float4's 2^24 exact-integer range, so no precision loss there.
const gscPosition = sql<number>`((${keywords.gsc} ->> 'position')::double precision)`;
const gscImpressions = sql<number>`((${keywords.gsc} ->> 'impressions')::double precision)`;

/** Mirrors `positionBucket` (gsc-positioning.ts) exactly: same thresholds,
 *  gsc NULL -> 'none'. */
const positionBucketCase = sql<string>`(CASE
  WHEN ${keywords.gsc} IS NULL THEN 'none'
  WHEN ${gscPosition} <= 3 THEN 'top3'
  WHEN ${gscPosition} <= 10 THEN 'top10'
  WHEN ${gscPosition} <= 20 THEN 'quickwin'
  WHEN ${gscPosition} <= 50 THEN 'deep'
  ELSE 'far'
END)`;

/** Mirrors `kdBucket` (overview.ts) exactly. Only applied to rows where
 *  `kd_primary IS NOT NULL` (see query 3's WHERE) — matches the JS `kds`
 *  array, which is built by filtering out non-numeric kd first. */
const kdBucketCase = sql<string>`(CASE
  WHEN ${keywords.kdPrimary} <= 20 THEN '0-20'
  WHEN ${keywords.kdPrimary} <= 40 THEN '21-40'
  WHEN ${keywords.kdPrimary} <= 60 THEN '41-60'
  WHEN ${keywords.kdPrimary} <= 80 THEN '61-80'
  ELSE '81-100'
END)`;

/** Mirrors `opportunityScore` (gsc-positioning.ts): quickwin band only
 *  (10 < position <= 20), 0 otherwise — `score > 0` below then further
 *  implies `impressions > 0` (21 - position >= 1 in that band). */
const opportunityScoreExpr = sql<number>`(CASE
  WHEN ${keywords.gsc} IS NOT NULL AND ${gscPosition} > 10 AND ${gscPosition} <= 20
  THEN ${gscImpressions} * (21 - ${gscPosition})
  ELSE 0
END)`;

/** `coalesce(theme, '—')` — matches `k.theme ?? "—"` in `buildKeywordOverview`. */
const themeKeyExpr = sql<string>`coalesce(${keywords.theme}, '—')`;

/** SQL-aggregated keyword overview for a site. Same output shape as
 *  `buildKeywordOverview` — see that function's JSDoc/tests for the semantics
 *  each field mirrors. `getKeywordsForSite` is NOT called on this path. */
export async function queryKeywordOverview(
  siteId: string,
): Promise<KeywordOverview> {
  const siteWhere = eq(keywords.siteId, siteId);

  const [
    [scalars],
    positionRows,
    kdRows,
    themeRows,
    topByVolumeRows,
    opportunityRows,
    backlinkRows,
  ] = await Promise.all([
    // 1. Scalars — totals, sums, averages, stage counts, byIntent (as 5
    // FILTER counts rather than a literal `unnest` + GROUP BY: INTENTS is a
    // small fixed set, so a conditional-aggregate count per intent is the
    // same GROUP BY semantics in one row, without a LATERAL join).
    db
      .select({
        total: sql<number>`count(*)::int`,
        positioned: sql<number>`count(*) FILTER (WHERE ${keywords.gsc} IS NOT NULL)::int`,
        totalClicks: sql<number>`coalesce(sum((${keywords.gsc} ->> 'clicks')::int) FILTER (WHERE ${keywords.gsc} IS NOT NULL), 0)::int`,
        totalImpressions: sql<number>`coalesce(sum((${keywords.gsc} ->> 'impressions')::int) FILTER (WHERE ${keywords.gsc} IS NOT NULL), 0)::int`,
        avgPosition: sql<
          number | null
        >`avg(${gscPosition}) FILTER (WHERE ${keywords.gsc} IS NOT NULL)`,
        totalVolume: sql<number>`coalesce(sum(${keywords.volumePrimary}), 0)`,
        avgKd: sql<number | null>`avg(${keywords.kdPrimary})`,
        research: sql<number>`count(*) FILTER (WHERE ${keywords.stages} @> ARRAY['research']::text[])::int`,
        planning: sql<number>`count(*) FILTER (WHERE ${keywords.stages} @> ARRAY['planning']::text[])::int`,
        intentInformational: sql<number>`count(*) FILTER (WHERE ${keywords.intent} @> ARRAY['informational']::text[])::int`,
        intentCommercial: sql<number>`count(*) FILTER (WHERE ${keywords.intent} @> ARRAY['commercial']::text[])::int`,
        intentTransactional: sql<number>`count(*) FILTER (WHERE ${keywords.intent} @> ARRAY['transactional']::text[])::int`,
        intentNavigational: sql<number>`count(*) FILTER (WHERE ${keywords.intent} @> ARRAY['navigational']::text[])::int`,
        intentLocal: sql<number>`count(*) FILTER (WHERE ${keywords.intent} @> ARRAY['local']::text[])::int`,
      })
      .from(keywords)
      .where(siteWhere),
    // 2. positionDistribution.
    db
      .select({
        bucket: positionBucketCase,
        count: sql<number>`count(*)::int`,
      })
      .from(keywords)
      .where(siteWhere)
      .groupBy(positionBucketCase),
    // 3. kdDistribution (NULL kd_primary excluded, matches the JS `kds` filter).
    db
      .select({
        bucket: kdBucketCase,
        count: sql<number>`count(*)::int`,
      })
      .from(keywords)
      .where(sql`${siteWhere} AND ${keywords.kdPrimary} IS NOT NULL`)
      .groupBy(kdBucketCase),
    // 5. byTheme — top 6 by count; ties broken by `min(term)` ASC, which
    // reproduces the JS Map's insertion order (first occurrence scanning
    // keywords in term-ascending order, the old `getKeywordsForSite` order)
    // under a stable sort. See overview-query.test.ts for the parity proof.
    db
      .select({ key: themeKeyExpr, count: sql<number>`count(*)::int` })
      .from(keywords)
      .where(siteWhere)
      .groupBy(themeKeyExpr)
      .orderBy(sql`count(*) DESC`, sql`min(${keywords.term}) ASC`)
      .limit(6),
    // 6. topByVolume — `coalesce(volume_primary, 0)`, NOT `NULLS LAST`: the JS
    // side does `?? 0` BEFORE sorting, so a genuine NULL (no primary-source
    // metrics) and an explicit `volume: 0` are indistinguishable and must
    // interleave by term, not have NULLs pushed strictly after every zero.
    // Ties broken by `term` ASC, matching a stable JS sort over the
    // term-ordered keyword array.
    db
      .select({
        id: keywords.id,
        term: keywords.term,
        volume: sql<number>`coalesce(${keywords.volumePrimary}, 0)`,
        position: gscPosition,
      })
      .from(keywords)
      .where(siteWhere)
      .orderBy(
        sql`coalesce(${keywords.volumePrimary}, 0) DESC`,
        asc(keywords.term),
      )
      .limit(10),
    // 7. opportunities — quickwin band + score > 0; ties broken by `term` ASC
    // (same stable-sort reasoning as topByVolume).
    db
      .select({
        id: keywords.id,
        term: keywords.term,
        volume: sql<number>`coalesce(${keywords.volumePrimary}, 0)`,
        position: gscPosition,
        kd: sql<number>`coalesce(${keywords.kdPrimary}, 0)`,
        impressions: sql<number>`coalesce(${gscImpressions}, 0)`,
        score: opportunityScoreExpr,
      })
      .from(keywords)
      .where(sql`${siteWhere} AND ${opportunityScoreExpr} > 0`)
      .orderBy(sql`${opportunityScoreExpr} DESC`, asc(keywords.term))
      .limit(8),
    // 8. backlinks byStatus — total is derived by summing the group counts
    // (the old `queries.ts` read had no ORDER BY either, so row order — and
    // thus the JS Map's tie-break order — was never a guaranteed contract;
    // this orders deterministically by count DESC, status ASC).
    db
      .select({
        status: backlinksTable.status,
        count: sql<number>`count(*)::int`,
      })
      .from(backlinksTable)
      .where(eq(backlinksTable.siteId, siteId))
      .groupBy(backlinksTable.status)
      .orderBy(sql`count(*) DESC`, asc(backlinksTable.status)),
  ]);

  const total = scalars?.total ?? 0;
  const positioned = scalars?.positioned ?? 0;
  const totalClicks = scalars?.totalClicks ?? 0;
  const totalImpressions = scalars?.totalImpressions ?? 0;
  const avgPosition = scalars?.avgPosition ?? null;
  const totalVolume = scalars?.totalVolume ?? 0;
  const avgKd = scalars?.avgKd ?? null;

  const posCounts = new Map(positionRows.map((r) => [r.bucket, r.count]));
  const kdCounts = new Map(kdRows.map((r) => [r.bucket, r.count]));
  const intentCounts = new Map<SearchIntent, number>([
    ["informational", scalars?.intentInformational ?? 0],
    ["commercial", scalars?.intentCommercial ?? 0],
    ["transactional", scalars?.intentTransactional ?? 0],
    ["navigational", scalars?.intentNavigational ?? 0],
    ["local", scalars?.intentLocal ?? 0],
  ]);

  const backlinksTotal = backlinkRows.reduce((s, r) => s + r.count, 0);

  return {
    totalKeywords: total,
    positioned,
    positionedPct: total ? Math.round((positioned / total) * 100) : 0,
    avgPosition: avgPosition != null ? round1(avgPosition) : null,
    totalClicks,
    totalImpressions,
    avgCtr: totalImpressions ? totalClicks / totalImpressions : null,
    quickWins: posCounts.get("quickwin") ?? 0,
    totalVolume,
    avgKd: avgKd != null ? Math.round(avgKd) : null,
    positionDistribution: POSITION_BUCKETS.map((key) => ({
      key,
      count: posCounts.get(key) ?? 0,
    })),
    kdDistribution: KD_BUCKETS.map((key) => ({
      key,
      count: kdCounts.get(key) ?? 0,
    })),
    byIntent: INTENTS.map((key) => ({ key, count: intentCounts.get(key) ?? 0 }))
      .filter((x) => x.count > 0)
      .sort((a, b) => b.count - a.count),
    byTheme: themeRows.map(
      (r): CountBy<string> => ({
        key: r.key,
        count: r.count,
      }),
    ),
    topByVolume: topByVolumeRows.map(
      (r): TopVolumeRow => ({
        id: r.id,
        term: r.term,
        volume: r.volume,
        position: r.position,
      }),
    ),
    opportunities: opportunityRows.map(
      (r): OpportunityRow => ({
        id: r.id,
        term: r.term,
        volume: r.volume,
        position: r.position ?? 0,
        kd: r.kd,
        impressions: r.impressions,
        score: r.score,
      }),
    ),
    stages: {
      reservoir: total,
      research: scalars?.research ?? 0,
      planning: scalars?.planning ?? 0,
    },
    backlinks: backlinksTotal
      ? {
          total: backlinksTotal,
          byStatus: backlinkRows.map((r) => ({
            key: r.status,
            count: r.count,
          })),
        }
      : null,
  };
}