// src/lib/keywords/list-query.ts — server-side query layer for the réservoir:
// keyset pagination, filters, facets, and a reusable bulk-selection WHERE.
// Types here are the server<->client contract (props RSC + action args) —
// keep them 100% JSON-serializable (no Date, no Column, no SQL).
import "server-only";
import {
  and,
  asc,
  eq,
  gte,
  inArray,
  lte,
  notInArray,
  type SQL,
  sql,
} from "drizzle-orm";
import { z } from "zod";
import { db } from "@/lib/db/client";
import { keywordShortlist, keywords, sitePages } from "@/lib/db/domain-schema";
import { keywordSelect, toKeyword } from "./queries";
import type {
  Keyword,
  KeywordSource,
  KeywordStage,
  KeywordStatus,
  SearchIntent,
} from "./types";

export type KeywordSortField =
  | "term"
  | "volume"
  | "kd"
  | "cpc"
  | "growth"
  | "gscPosition"
  | "gscOpportunity";

export interface KeywordSort {
  field: KeywordSortField;
  desc: boolean;
}

export interface NumRange {
  min?: number;
  max?: number;
}

export interface KeywordListFilter {
  /** term ILIKE %q% (wildcards in q are escaped before interpolation). */
  q?: string;
  status?: KeywordStatus[];
  /** Slugs — `theme` (single-valued on `keywords`) `= ANY(...)`. */
  theme?: string[];
  /** `intent && ARRAY[...]` (overlap — a row matches if it carries any of these). */
  intent?: SearchIntent[];
  lang?: string;
  /** Target countries (single-valued `country` column) `= ANY(...)`. */
  countries?: string[];
  /** `sources @> ARRAY[source]` (containment, not equality — sources is multi-valued). */
  source?: KeywordSource;
  /** `stages @> ARRAY[stage]` (GIN-indexed containment). */
  stage?: KeywordStage;
  volume?: NumRange;
  kd?: NumRange;
  cpc?: NumRange;
  growth?: NumRange;
  gscBand?: "top10" | "quickwin" | "beyond" | "none";
  /** Facette « Sans page » — ne garde que les kw sans page ciblée assignée
   *  (`target_page_id IS NULL`). Colonne `keywords`, aucun JOIN requis (marche
   *  aussi dans le count). */
  noTargetPage?: boolean;
  /** Onglet « Shortlist » — ne garde que les kw présents dans
   *  `keyword_shortlist` du site (EXISTS corrélé, aucun JOIN, marche dans le
   *  count comme dans le listing). Absent/false → comportement inchangé. */
  shortlisted?: boolean;
}

export interface KeywordPageParams {
  filter: KeywordListFilter;
  sort: KeywordSort;
  /** Clamped server-side to 1..500. */
  limit: number;
  /** Opaque, base64url(JSON {v, id}). Invalid/undecodable → treated as absent. */
  cursor?: string | null;
}

export interface KeywordPage {
  rows: Keyword[];
  total: number;
  nextCursor: string | null;
}

export interface KeywordFacets {
  languages: string[];
  /** Distinct target countries present (alpha-2, e.g. "US", "GB"). */
  countries: string[];
  sources: KeywordSource[];
  /** min/max cpc_primary of the site (resp. stage) — null when no row carries a cpc. */
  cpcRange: [number, number] | null;
  /** Keywords of the site (resp. of the stage, when `stage` is passed). */
  total: number;
  /** Keywords in the GSC « quick-win » band (position 11–20) — same
   *  site/stage scope as `total`, ANDed with the quick-win band condition
   *  (reuses `gscBandCondition("quickwin")`, the exact predicate the
   *  `filterWhere` case "quickwin" applies). Feeds the Recherche
   *  « Quick-wins GSC » tab badge. */
  quickwinCount: number;
}

export type KeywordSelection =
  | { mode: "ids"; ids: string[] }
  | { mode: "filter"; filter: KeywordListFilter; except?: string[] };

// ---------------------------------------------------------------------------
// WHERE builder — shared by searchKeywords, getKeywordFacets (via a lighter
// site/stage-only variant) and selectionWhere's "filter" mode.
// ---------------------------------------------------------------------------

/** Escapes ILIKE wildcards (`%`, `_`) and the escape char itself (`\`) so a
 *  free-text query can't inject pattern behaviour — the query still matches
 *  as a literal substring, wildcards stay inert. */
function escapeLikePattern(raw: string): string {
  return raw.replace(/[\\%_]/g, (c) => `\\${c}`);
}

/** Site + filter → a single AND'd WHERE clause. Always includes the site
 *  scope (tenancy) as its first condition, so the returned SQL is never
 *  empty/undefined. */
export function filterWhere(siteId: string, filter: KeywordListFilter): SQL {
  const conds: SQL[] = [eq(keywords.siteId, siteId)];
  const q = filter.q?.trim();
  if (q) {
    conds.push(sql`${keywords.term} ILIKE ${`%${escapeLikePattern(q)}%`}`);
  }
  if (filter.status?.length) {
    conds.push(inArray(keywords.status, filter.status));
  }
  if (filter.theme?.length) {
    // Raw `= ANY(...)` rather than `inArray` — `keywords.theme` is typed
    // `KeywordTheme | null` at compile time, but the column also carries
    // free-form per-site slugs (`keyword_themes.slug`, see actions.ts
    // createThemeAction), so the filter is intentionally generic string[]
    // rather than coupled to the fixed 5-value enum.
    conds.push(
      sql`${keywords.theme} = ANY(ARRAY[${sql.join(
        filter.theme.map((t) => sql`${t}`),
        sql`, `,
      )}]::text[])`,
    );
  }
  if (filter.intent?.length) {
    conds.push(
      sql`${keywords.intent} && ARRAY[${sql.join(
        filter.intent.map((i) => sql`${i}`),
        sql`, `,
      )}]::text[]`,
    );
  }
  if (filter.lang) conds.push(eq(keywords.lang, filter.lang));
  if (filter.countries?.length) {
    conds.push(
      sql`${keywords.country} = ANY(ARRAY[${sql.join(
        filter.countries.map((c) => sql`${c}`),
        sql`, `,
      )}]::text[])`,
    );
  }
  if (filter.source) {
    conds.push(sql`${keywords.sources} @> ARRAY[${filter.source}]::text[]`);
  }
  if (filter.stage) {
    conds.push(sql`${keywords.stages} @> ARRAY[${filter.stage}]::text[]`);
  }
  if (filter.volume?.min !== undefined)
    conds.push(gte(keywords.volumePrimary, filter.volume.min));
  if (filter.volume?.max !== undefined)
    conds.push(lte(keywords.volumePrimary, filter.volume.max));
  if (filter.kd?.min !== undefined)
    conds.push(gte(keywords.kdPrimary, filter.kd.min));
  if (filter.kd?.max !== undefined)
    conds.push(lte(keywords.kdPrimary, filter.kd.max));
  if (filter.cpc?.min !== undefined)
    conds.push(gte(keywords.cpcPrimary, filter.cpc.min));
  if (filter.cpc?.max !== undefined)
    conds.push(lte(keywords.cpcPrimary, filter.cpc.max));
  if (filter.growth?.min !== undefined)
    conds.push(gte(keywords.growthPrimary, filter.growth.min));
  if (filter.growth?.max !== undefined)
    conds.push(lte(keywords.growthPrimary, filter.growth.max));
  if (filter.gscBand) conds.push(gscBandCondition(filter.gscBand));
  if (filter.noTargetPage) conds.push(sql`${keywords.targetPageId} IS NULL`);
  if (filter.shortlisted) {
    conds.push(
      sql`EXISTS (SELECT 1 FROM ${keywordShortlist} ks WHERE ks.site_id = ${keywords.siteId} AND ks.keyword_id = ${keywords.id})`,
    );
  }
  // `conds` always has >= 1 element (the site scope), so `and(...)` never
  // returns undefined here.
  return and(...conds) as SQL;
}

function gscBandCondition(
  band: NonNullable<KeywordListFilter["gscBand"]>,
): SQL {
  const pos = sql`((${keywords.gsc} ->> 'position')::real)`;
  switch (band) {
    case "none":
      return sql`${keywords.gsc} IS NULL`;
    case "top10":
      return sql`(${keywords.gsc} IS NOT NULL AND ${pos} <= 10)`;
    case "quickwin":
      return sql`(${keywords.gsc} IS NOT NULL AND ${pos} > 10 AND ${pos} <= 20)`;
    case "beyond":
      return sql`(${keywords.gsc} IS NOT NULL AND ${pos} > 20)`;
    default:
      band satisfies never;
      throw new Error(`unreachable gscBand: ${band}`);
  }
}

/** The sortable SQL expression for a given field — same expr used for
 *  ORDER BY and the keyset cursor predicate/value. */
function sortExpr(field: KeywordSortField): SQL {
  switch (field) {
    case "term":
      return sql`${keywords.term}`;
    case "volume":
      return sql`${keywords.volumePrimary}`;
    case "kd":
      return sql`${keywords.kdPrimary}`;
    case "cpc":
      return sql`${keywords.cpcPrimary}`;
    case "growth":
      return sql`${keywords.growthPrimary}`;
    case "gscPosition":
      return sql`((${keywords.gsc} ->> 'position')::real)`;
    case "gscOpportunity":
      return sql`(CASE WHEN ((${keywords.gsc} ->> 'position')::real) > 10 AND ((${keywords.gsc} ->> 'position')::real) <= 20 THEN ((${keywords.gsc} ->> 'impressions')::real) * (21 - ((${keywords.gsc} ->> 'position')::real)) ELSE 0 END)`;
    default:
      field satisfies never;
      throw new Error(`unreachable sort field: ${field}`);
  }
}

// ---------------------------------------------------------------------------
// Cursor — opaque base64url(JSON {v, id}), zod-validated on decode.
// ---------------------------------------------------------------------------

const cursorPayloadSchema = z.object({
  v: z.union([z.string(), z.number(), z.null()]),
  id: z.string(),
});
type CursorPayload = z.infer<typeof cursorPayloadSchema>;

function encodeCursor(payload: CursorPayload): string {
  return Buffer.from(JSON.stringify(payload), "utf8").toString("base64url");
}

/** Decodes + validates a cursor. Any failure (bad base64, bad JSON, wrong
 *  shape) is treated as "no cursor" (page 1) — never throws, per contract. */
function decodeCursor(cursor: string | null | undefined): CursorPayload | null {
  if (!cursor) return null;
  try {
    const json = Buffer.from(cursor, "base64url").toString("utf8");
    const parsed = cursorPayloadSchema.safeParse(JSON.parse(json));
    return parsed.success ? parsed.data : null;
  } catch {
    return null;
  }
}

/** Keyset predicate for "rows strictly after `cursor` in the ORDER BY
 *  `expr <asc|desc> NULLS LAST, id ASC` order":
 *  - `v` non-null: `(expr <op> v) OR (expr = v AND id > id0) OR expr IS NULL`
 *    (NULLS LAST → every null-expr row sorts after any non-null cursor row).
 *  - `v` null (cursor already in the NULLS-LAST tail): `expr IS NULL AND id > id0`.
 *  `<op>` is `>` for ASC, `<` for DESC — the id tiebreak is always ASC. */
function cursorPredicate(
  expr: SQL,
  sort: KeywordSort,
  cursor: CursorPayload,
): SQL {
  const idAfter = sql`${keywords.id} > ${cursor.id}`;
  if (cursor.v === null) {
    return sql`(${expr} IS NULL AND ${idAfter})`;
  }
  return sort.desc
    ? sql`((${expr} < ${cursor.v}) OR (${expr} = ${cursor.v} AND ${idAfter}) OR ${expr} IS NULL)`
    : sql`((${expr} > ${cursor.v}) OR (${expr} = ${cursor.v} AND ${idAfter}) OR ${expr} IS NULL)`;
}

// ---------------------------------------------------------------------------
// searchKeywords
// ---------------------------------------------------------------------------

export async function searchKeywords(
  siteId: string,
  params: KeywordPageParams,
): Promise<KeywordPage> {
  const limit = Math.min(500, Math.max(1, Math.trunc(params.limit) || 1));
  const expr = sortExpr(params.sort.field);
  const baseWhere = filterWhere(siteId, params.filter);
  const cursor = decodeCursor(params.cursor);
  const where = cursor
    ? (and(baseWhere, cursorPredicate(expr, params.sort, cursor)) as SQL)
    : baseWhere;
  const dir = sql.raw(params.sort.desc ? "desc" : "asc");

  const [rows, [{ count }]] = await Promise.all([
    db
      .select({ ...keywordSelect, sortValue: expr })
      .from(keywords)
      .leftJoin(sitePages, eq(keywords.targetPageId, sitePages.id))
      .where(where)
      .orderBy(sql`${expr} ${dir} NULLS LAST`, asc(keywords.id))
      .limit(limit),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(keywords)
      .where(baseWhere),
  ]);

  const last = rows.at(-1);
  const nextCursor =
    rows.length < limit || !last
      ? null
      : encodeCursor({
          v: last.sortValue as string | number | null,
          id: last.id,
        });

  return { rows: rows.map(toKeyword), total: count, nextCursor };
}

// ---------------------------------------------------------------------------
// getKeywordFacets
// ---------------------------------------------------------------------------

function facetsWhere(siteId: string, stage?: KeywordStage): SQL {
  return stage
    ? (and(
        eq(keywords.siteId, siteId),
        sql`${keywords.stages} @> ARRAY[${stage}]::text[]`,
      ) as SQL)
    : eq(keywords.siteId, siteId);
}

export async function getKeywordFacets(
  siteId: string,
  stage?: KeywordStage,
): Promise<KeywordFacets> {
  const where = facetsWhere(siteId, stage);
  const [langRows, countryRows, sourceRows, cpcRow, totalRow, quickwinRow] =
    await Promise.all([
      db.selectDistinct({ lang: keywords.lang }).from(keywords).where(where),
      db
        .selectDistinct({ country: keywords.country })
        .from(keywords)
        .where(where),
      db
        .selectDistinct({
          source: sql<KeywordSource>`unnest(${keywords.sources})`,
        })
        .from(keywords)
        .where(where),
      db
        .select({
          min: sql<number | null>`min(${keywords.cpcPrimary})`,
          max: sql<number | null>`max(${keywords.cpcPrimary})`,
        })
        .from(keywords)
        .where(where),
      db
        .select({ count: sql<number>`count(*)::int` })
        .from(keywords)
        .where(where),
      db
        .select({ count: sql<number>`count(*)::int` })
        .from(keywords)
        .where(and(where, gscBandCondition("quickwin"))),
    ]);
  const cpc = cpcRow[0];
  return {
    languages: langRows.map((r) => r.lang).sort(),
    countries: countryRows
      .map((r) => r.country)
      .filter((c): c is string => Boolean(c))
      .sort(),
    sources: sourceRows.map((r) => r.source).sort(),
    cpcRange:
      cpc && cpc.min != null && cpc.max != null ? [cpc.min, cpc.max] : null,
    total: totalRow[0]?.count ?? 0,
    quickwinCount: quickwinRow[0]?.count ?? 0,
  };
}

// ---------------------------------------------------------------------------
// selectionWhere — reusable WHERE for bulk mutations (never materializes ids
// client-side in "filter" mode).
// ---------------------------------------------------------------------------

export function selectionWhere(siteId: string, sel: KeywordSelection): SQL {
  if (sel.mode === "ids") {
    return and(
      eq(keywords.siteId, siteId),
      inArray(keywords.id, sel.ids),
    ) as SQL;
  }
  return and(
    filterWhere(siteId, sel.filter),
    notInArray(keywords.id, sel.except ?? []),
  ) as SQL;
}