Data Table Drizzle ORM Helpers

export { buildWhereConditions } from "./filters";
export { computeFacets } from "./facets";
export { buildOrderBy } from "./sorting";
export { buildCursorPagination } from "./pagination";
export { evaluateIntervalMs } from "@/lib/data-table/interval";
export { createDrizzleHandler } from "./handler";
export type { DrizzleHandlerConfig, DrizzleHandlerResult } from "./handler";
export type {
  ColumnMapping,
  DrizzleDB,
  DrizzleQueryScope,
  SortDescriptor,
  CursorPaginationParams,
} from "./types";

import type { Column, SQL } from "drizzle-orm";
import type { PgDatabase, PgTable } from "drizzle-orm/pg-core";

/**
 * Maps tableSchema field keys to Drizzle table columns.
 * This is the bridge between your table UI schema and your database.
 *
 * @example
 * const columnMapping = {
 *   level: logs.level,
 *   date: logs.date,
 *   latency: logs.latency,
 *   "timing.dns": logs.timingDns,
 * } satisfies ColumnMapping;
 */
export type ColumnMapping = Record<string, Column>;

export type DrizzleDB = PgDatabase<any, any, any>;

/** Sort descriptor matching the URL state shape. */
export type SortDescriptor = { id: string; desc: boolean } | null;

/** Cursor-based pagination params. */
export type CursorPaginationParams = {
  cursor: Date | number | null;
  direction: "prev" | "next";
  size: number;
  cursorColumn: Column;
  /**
   * Unique column ordered last, so rows sharing a cursor value have one
   * defined order instead of whatever the plan happens to produce.
   */
  tiebreakColumn?: Column;
};

/**
 * Everything a caller needs to write its own aggregate SQL against the same
 * filtered set the handler queried.
 *
 * The three things such a caller actually needs — the resolved time range, the
 * bucket size, and the composed WHERE — are all computed inside the handler
 * already. Handing back only `SQL[]` meant every caller re-derived them.
 */
export type DrizzleQueryScope = {
  readonly db: DrizzleDB;
  readonly table: PgTable;
  readonly columns: ColumnMapping;
  /** The fully filtered set. */
  readonly where: SQL | undefined;
  /** The set slider bounds are computed over (date + non-slider filters). */
  readonly whereWithoutSliders: SQL | undefined;
  /** Resolved from an explicit date filter, else from MIN/MAX of the set. */
  readonly range: { from: Date; to: Date } | null;
  /** Bucket size for `range`, with the interval ladder already applied. */
  readonly bucketMs: number;
};

import { evaluateIntervalMs } from "@/lib/data-table/interval";
import type { FacetMetadataSchema } from "@/lib/data-table/types";
import type { Filters } from "@/lib/filters";
import { and, count, eq, sql, type Column, type SQL } from "drizzle-orm";
import {
  getTableConfig,
  type SelectedFields as PgSelectedFields,
  type PgTable,
} from "drizzle-orm/pg-core";
import { computeFacets } from "./facets";
import { buildWhereConditions } from "./filters";
import { buildCursorPagination } from "./pagination";
import { buildOrderBy } from "./sorting";
import type {
  ColumnMapping,
  DrizzleDB,
  DrizzleQueryScope,
  SortDescriptor,
} from "./types";

/**
 * Derive slider, facet, and date keys from declared filter semantics.
 *
 * These are pass groupings for the three-pass strategy, not semantics — the
 * semantics live in `Filters` and are the same everywhere.
 */
function deriveKeys(filters: Filters) {
  const sliderKeys: string[] = [];
  const facetKeys: string[] = [];
  const dateKeys: string[] = [];

  for (const { key, type } of filters.specs) {
    if (type === "slider") {
      sliderKeys.push(key);
      facetKeys.push(key);
    }
    if (type === "checkbox") facetKeys.push(key);
    if (type === "input") facetKeys.push(key);
    if (type === "timerange") dateKeys.push(key);
  }

  return { sliderKeys, facetKeys, dateKeys };
}

/**
 * The date range the user asked for, if any.
 *
 * Read through `filters.plan` rather than off the raw search values, so the
 * single-date and reversed-bounds cases are normalized the same way they are
 * for the WHERE clause.
 */
function resolveExplicitRange(
  filters: Filters,
  search: Record<string, unknown>,
  dateKeys: string[],
): { from: Date; to: Date } | null {
  for (const op of filters.plan(search, { only: dateKeys })) {
    if (op.op === "dateRange") return { from: op.from, to: op.to };
  }
  return null;
}

/** MIN/MAX over the filtered set, for when no explicit range was given. */
async function discoverRange(
  db: DrizzleDB,
  table: PgTable,
  column: Column,
  where: SQL | undefined,
): Promise<{ from: Date; to: Date } | null> {
  const result = await db
    .select({
      from: sql<Date | null>`MIN(${column})`,
      to: sql<Date | null>`MAX(${column})`,
    })
    .from(table)
    .where(where);

  const row = result[0];
  if (!row?.from || !row?.to) return null;
  const from = new Date(row.from);
  const to = new Date(row.to);
  if (Number.isNaN(from.getTime()) || Number.isNaN(to.getTime())) return null;
  return { from, to };
}

/**
 * One config shape.
 *
 * The old two-shape union existed because `tableSchema` lives in a
 * `"use client"` file and could not be imported on the server, so the server
 * had to be handed three untyped `string[]`s instead. `defineFilters` accepts
 * `SchemaJSON`, so the declaration now crosses that boundary as data and one
 * field carries the semantics.
 */
export type DrizzleHandlerConfig = {
  db: DrizzleDB;
  table: PgTable;
  /** Built with `defineFilters(tableSchema.definition | schemaJson | specs)`. */
  filters: Filters;
  columnMapping: ColumnMapping;
  /**
   * The schema key rows are paged by. A `Date` or numeric column, since the
   * cursor is serialized as a number.
   *
   * `scope.range` and `scope.bucketMs` are read off THIS column when no
   * explicit date filter is set, so a non-time cursor (an auto-increment id,
   * say) still paginates correctly but hands aggregate callers a range built
   * from `new Date(id)`. Page by your time column unless you have no use for
   * `scope.range`.
   */
  cursorColumn: string;
  /**
   * The schema key rows are ordered by last, to break ties on the cursor
   * column. Must be unique — the primary key, normally, which is what this
   * defaults to when the table declares a single-column one.
   *
   * Without it `ORDER BY date DESC` leaves rows sharing a timestamp in
   * whatever order the plan produced, and that order is not stable: updating a
   * row moves it in the heap, and a different plan reads it back differently.
   * The page a row lands on stays correct either way — the boundary logic
   * below refuses to split a tied group — but the rows visibly shuffle within
   * their group between refetches.
   *
   * Set explicitly for a table with a composite primary key, or none.
   */
  tiebreakColumn?: string;
  defaultSize?: number;
  /**
   * Extra projected fields merged into each row — columns that are never
   * filtered or sorted, joins, computed SQL.
   *
   * `columnMapping` already declares the schema-key ↔ Drizzle-column
   * relationship, so it doubles as the projection. Anything the wire contract
   * needs but the filters do not goes here.
   */
  select?: Record<string, Column | SQL.Aliased>;
};

export type DrizzleHandlerResult<TRow = Record<string, unknown>> = {
  /** Keyed by SCHEMA keys — `"timing.dns"`, never `timingDns`. */
  data: TRow[];
  facets: Record<string, FacetMetadataSchema>;
  totalRowCount: number;
  filterRowCount: number;
  nextCursor: number | null;
  prevCursor: number | null;
  /**
   * One typed handle for callers writing their own aggregate SQL.
   *
   * Replaces `allConditions: SQL[]`, which leaked just enough to make callers
   * re-derive the range, the bucket size, and the WHERE composition by hand —
   * with `db: any` at the boundary and no test at any level.
   */
  scope: DrizzleQueryScope;
};

/**
 * The table's primary key, when it is a single column — the default tiebreak.
 *
 * A composite key would need every one of its columns to be a valid tiebreak,
 * and one of them alone is not unique, so those tables get `undefined` and
 * have to name a `tiebreakColumn` themselves.
 */
function singleColumnPrimaryKey(table: PgTable): Column | undefined {
  const config = getTableConfig(table);
  const inline = config.columns.filter((column) => column.primary);
  if (inline.length === 1) return inline[0];
  if (config.primaryKeys.length === 1) {
    const columns = config.primaryKeys[0]?.columns;
    if (columns?.length === 1) return columns[0];
  }
  return undefined;
}

/**
 * Create a high-level query handler that encapsulates the three-pass filtering
 * strategy, faceted search, counts, and cursor pagination.
 *
 * Chart data and percentiles are intentionally excluded — handle them in user-land.
 *
 * @example
 * ```ts
 * // `tableSchema` is importable (not a "use client" file)
 * const handler = createDrizzleHandler({
 *   db,
 *   table: logs,
 *   filters: defineFilters(tableSchema.definition),
 *   columnMapping,
 *   cursorColumn: "date",
 * });
 *
 * // `tableSchema` is "use client" — the declaration crosses as data
 * const handler = createDrizzleHandler({
 *   db,
 *   table: logs,
 *   filters: defineFilters(schemaJson),
 *   columnMapping,
 *   cursorColumn: "date",
 * });
 *
 * const result = await handler.execute(search);
 * ```
 */
export function createDrizzleHandler(config: DrizzleHandlerConfig) {
  const {
    db,
    table,
    filters,
    columnMapping,
    cursorColumn,
    tiebreakColumn,
    defaultSize = 40,
    select: extraSelect,
  } = config;
  const { sliderKeys, facetKeys, dateKeys } = deriveKeys(filters);

  const cursorCol = columnMapping[cursorColumn];
  if (!cursorCol) {
    throw new Error(
      `cursorColumn "${cursorColumn}" not found in columnMapping`,
    );
  }

  let tiebreakCol: Column | undefined;
  if (tiebreakColumn !== undefined) {
    tiebreakCol = columnMapping[tiebreakColumn];
    if (!tiebreakCol) {
      throw new Error(
        `tiebreakColumn "${tiebreakColumn}" not found in columnMapping`,
      );
    }
  } else {
    tiebreakCol = singleColumnPrimaryKey(table);
  }

  // Fail loudly at construction, not silently at query time.
  //
  // `buildWhereConditions` and `buildOrderBy` both skip a key that is missing
  // from the mapping. `ColumnMapping` is `Record<string, Column>`, so a typo
  // was not a type error either — the filter just stopped filtering, with no
  // error, no warning, and no failing test.
  const unmapped = filters.specs
    .map((spec) => spec.key)
    .filter((key) => !columnMapping[key]);
  if (unmapped.length > 0) {
    throw new Error(
      `[createDrizzleHandler] These filterable columns are missing from columnMapping:\n` +
        unmapped.map((key) => `  - ${key}`).join("\n") +
        `\n\n  Fix: add them, e.g.\n` +
        `  columnMapping: {\n` +
        unmapped
          .map(
            (key) =>
              `    ${JSON.stringify(key)}: table.${key.replace(/[.\-_](\w)/g, (_, c) => c.toUpperCase())},`,
          )
          .join("\n") +
        `\n  }`,
    );
  }

  // Rows come back keyed by SCHEMA keys because the projection IS the mapping.
  // Callers used to hand-write the inverse of this — twice, and the two copies
  // had already drifted.
  // `ColumnMapping` is driver-agnostic (`Column`) by design, while `db.select`
  // wants pg's narrower `SelectedFields`. Every value here is a real pg column
  // or aliased SQL; narrowing `ColumnMapping` itself would make the public type
  // pg-specific for every consumer.
  const projection = {
    ...columnMapping,
    ...(extraSelect ?? {}),
  } as PgSelectedFields;

  return {
    /** Derived keys (exposed for advanced use cases) */
    sliderKeys,
    facetKeys,
    dateKeys,

    async execute(
      search: Record<string, unknown>,
    ): Promise<DrizzleHandlerResult> {
      const size = typeof search.size === "number" ? search.size : defaultSize;
      const sort = (search.sort as SortDescriptor) ?? null;
      const cursor = (search.cursor as Date | number | null) ?? null;
      const direction = (search.direction as "prev" | "next") ?? "next";

      // --- Three-pass filtering strategy ---

      // Pass 1: Date range conditions only
      const dateConditions = buildWhereConditions(
        filters,
        search,
        columnMapping,
        {
          only: dateKeys,
        },
      );

      // Pass 2: Date + non-slider filters (for slider facet bounds)
      const nonSliderConditions = buildWhereConditions(
        filters,
        search,
        columnMapping,
        { exclude: [...sliderKeys, ...dateKeys] },
      );
      const pass2Conditions = [...dateConditions, ...nonSliderConditions];

      // Pass 3: All conditions including sliders
      const sliderConditions = buildWhereConditions(
        filters,
        search,
        columnMapping,
        { only: sliderKeys },
      );
      const allConditions = [...pass2Conditions, ...sliderConditions];

      const allWhereForScope =
        allConditions.length > 0 ? and(...allConditions) : undefined;
      const pass2Where =
        pass2Conditions.length > 0 ? and(...pass2Conditions) : undefined;

      // Resolve the time range once, here, rather than leaving every caller
      // writing custom aggregate SQL to rediscover it. An explicit date filter
      // wins; otherwise it comes from MIN/MAX over the filtered set.
      const explicitRange = resolveExplicitRange(filters, search, dateKeys);

      // --- Facets + range (parallel) ---
      const [sliderFacets, otherFacets, discoveredRange] = await Promise.all([
        computeFacets(db, table, columnMapping, pass2Conditions, sliderKeys, {
          sliderKeys,
        }),
        computeFacets(
          db,
          table,
          columnMapping,
          allConditions,
          facetKeys.filter((k) => !sliderKeys.includes(k)),
        ),
        // Skipped entirely when the range is already known, so the common
        // "user picked a date range" path costs nothing extra.
        explicitRange
          ? Promise.resolve(null)
          : discoverRange(db, table, cursorCol, allWhereForScope),
      ]);

      const range = explicitRange ?? discoveredRange;

      const facets = { ...sliderFacets, ...otherFacets };

      // --- Counts (parallel) ---
      const allWhere = allWhereForScope;

      const [totalResult, filterResult] = await Promise.all([
        db.select({ total: count() }).from(table),
        db.select({ total: count() }).from(table).where(allWhere),
      ]);

      const totalRowCount = totalResult[0]?.total ?? 0;
      const filterRowCount = filterResult[0]?.total ?? 0;

      // --- Sort + Cursor Pagination ---
      const orderBy = buildOrderBy(columnMapping, sort);

      const {
        cursorCondition,
        orderBy: cursorOrderBy,
        tiebreakOrderBy,
        needsReverse,
      } = buildCursorPagination({
        cursor,
        direction,
        size,
        cursorColumn: cursorCol,
        tiebreakColumn: tiebreakCol,
      });

      const dataConditions = cursorCondition
        ? [...allConditions, cursorCondition]
        : allConditions;

      const dataWhere =
        dataConditions.length > 0 ? and(...dataConditions) : undefined;

      // Cursor first, then the caller's sort, then the tiebreak — the last
      // one is what makes the order total, so nothing may follow it.
      const orderClauses = sql.join(
        [cursorOrderBy, orderBy, tiebreakOrderBy].filter(
          (clause): clause is SQL => clause !== undefined,
        ),
        sql`, `,
      );

      // One extra row reveals whether the page boundary splits a group of
      // rows sharing the same cursor value.
      const rows = await db
        .select(projection)
        .from(table)
        .where(dataWhere)
        .orderBy(orderClauses)
        .limit(size + 1);

      // Read by SCHEMA key. This used to read `row[cursorCol.name]` — the SQL
      // column name. They coincide for `date`; for any cursor column whose DB
      // name differs from its key (`timing_dns` vs `"timing.dns"`) the lookup
      // was `undefined`, so `boundaryValue` was null, the whole tie-snapping
      // block was skipped, and `nextCursor` came back null — silently ending
      // pagination after one page.
      const getCursorValue = (row: Record<string, unknown>): number | null => {
        if (!row) return null;
        const val = row[cursorColumn];
        if (val instanceof Date) return val.getTime();
        if (typeof val === "number") return val;
        return null;
      };

      // --- Page boundary must fall between cursor values ---
      //
      // The next page is fetched with a strict `<` (or `>`) predicate on the
      // cursor column, so any row sharing the boundary value that did not fit
      // on this page would never be returned by any page. Rather than split a
      // group of tied rows, end the page before it and let the group start the
      // next one.
      let page = rows;

      const boundaryValue =
        rows.length > size ? getCursorValue(rows[size]) : null;

      // A cursor column that yields neither a Date nor a number cannot be
      // serialized into a cursor at all, so leave those tables untouched.
      if (boundaryValue !== null) {
        const overflowRow = rows[size];

        page = rows.slice(0, size);
        while (
          page.length > 0 &&
          getCursorValue(page[page.length - 1]) === boundaryValue
        ) {
          page.pop();
        }

        // Degenerate case: a single cursor value spans the whole page, so
        // there is no boundary to retreat to. Return the entire tied group —
        // overflowing `size` is the only way to make progress without
        // dropping rows.
        if (page.length === 0) {
          page = await db
            .select(projection)
            .from(table)
            .where(
              and(...dataConditions, eq(cursorCol, overflowRow[cursorColumn])),
            )
            .orderBy(orderClauses);
        }
      }

      if (needsReverse) {
        page.reverse();
      }

      // --- Cursors ---
      const lastRow = page[page.length - 1];
      const firstRow = page[0];

      const nextCursor = lastRow ? getCursorValue(lastRow) : null;
      const prevCursor = firstRow
        ? getCursorValue(firstRow)
        : new Date().getTime();

      const scope: DrizzleQueryScope = {
        db,
        table,
        columns: columnMapping,
        where: allWhereForScope,
        whereWithoutSliders: pass2Where,
        range,
        bucketMs: evaluateIntervalMs(
          range ? range.to.getTime() - range.from.getTime() : 0,
        ),
      };

      return {
        data: page,
        facets,
        totalRowCount,
        filterRowCount,
        nextCursor,
        prevCursor,
        scope,
      };
    },
  };
}

import type { FacetMetadataSchema } from "@/lib/data-table/types";
import { and, count, max, min, sql, type SQL } from "drizzle-orm";
import type { PgTable } from "drizzle-orm/pg-core";
import type { ColumnMapping, DrizzleDB } from "./types";

/**
 * Compute faceted counts for filter fields via SQL.
 *
 * For each key:
 * - Slider fields: SELECT MIN(col), MAX(col), COUNT(*)
 * - Array columns: unnest + GROUP BY for unique values
 * - Standard columns: GROUP BY for unique values with counts
 *
 * All facet queries run in parallel via Promise.all.
 */
export async function computeFacets(
  db: DrizzleDB,
  table: PgTable,
  mapping: ColumnMapping,
  baseConditions: SQL[],
  facetKeys: string[],
  options?: {
    /** Keys that should return min/max instead of grouped values */
    sliderKeys?: string[];
  },
): Promise<Record<string, FacetMetadataSchema>> {
  const whereCondition =
    baseConditions.length > 0 ? and(...baseConditions) : undefined;
  const sliderKeys = options?.sliderKeys ?? [];

  const queries = facetKeys.map(async (key) => {
    const column = mapping[key];
    if (!column) return [key, null] as const;

    if (sliderKeys.includes(key)) {
      const result = await db
        .select({
          min: min(column),
          max: max(column),
          total: count(),
        })
        .from(table)
        .where(whereCondition);

      const row = result[0];

      return [
        key,
        {
          rows: [],
          total: Number(row?.total ?? 0),
          min: row?.min != null ? Number(row.min) : undefined,
          max: row?.max != null ? Number(row.max) : undefined,
        } satisfies FacetMetadataSchema,
      ] as const;
    }

    // Array columns: unnest + GROUP BY
    if (column.dataType === "array") {
      const raw = await db.execute(
        sql`SELECT val as value, COUNT(*)::int as total
            FROM (
              SELECT unnest(${column}) as val
              FROM ${table}
              ${whereCondition ? sql`WHERE ${whereCondition}` : sql``}
            ) sub
            GROUP BY val
            ORDER BY total DESC`,
      );
      const result: { value: string; total: number }[] = Array.isArray(raw)
        ? raw
        : (raw as { rows: { value: string; total: number }[] }).rows;

      const total = result.reduce((sum, r) => sum + Number(r.total), 0);

      return [
        key,
        {
          rows: result.map((r) => ({
            value: r.value,
            total: Number(r.total),
          })),
          total,
        } satisfies FacetMetadataSchema,
      ] as const;
    }

    // Standard column: GROUP BY
    const raw = await db.execute(
      sql`SELECT ${column} as value, COUNT(*)::int as total
          FROM ${table}
          ${whereCondition ? sql`WHERE ${whereCondition}` : sql``}
          GROUP BY ${column}
          ORDER BY total DESC`,
    );
    const result: { value: string | number | boolean; total: number }[] =
      Array.isArray(raw)
        ? raw
        : (
            raw as {
              rows: { value: string | number | boolean; total: number }[];
            }
          ).rows;

    const total = result.reduce((sum, r) => sum + Number(r.total), 0);

    let minVal: number | undefined;
    let maxVal: number | undefined;
    if (result.length > 0 && typeof result[0].value === "number") {
      minVal = Math.min(...result.map((r) => Number(r.value)));
      maxVal = Math.max(...result.map((r) => Number(r.value)));
    }

    return [
      key,
      {
        rows: result.map((r) => ({
          value: r.value,
          total: Number(r.total),
        })),
        total,
        min: minVal,
        max: maxVal,
      } satisfies FacetMetadataSchema,
    ] as const;
  });

  const results = await Promise.all(queries);
  return Object.fromEntries(results.filter(([, v]) => v !== null));
}

import type {
  FilterOp,
  Filters,
  FilterSelection,
} from "@/lib/filters";
import {
  and,
  between,
  eq,
  gte,
  ilike,
  inArray,
  lte,
  sql,
  type Column,
  type SQL,
} from "drizzle-orm";
import type { ColumnMapping } from "./types";

/**
 * Compile one canonical op to SQL.
 *
 * Exhaustive over the six `FilterOp` members with no default branch. The op
 * union is closed precisely so that adding a seventh fails to compile here
 * instead of silently matching nothing.
 *
 * Nothing in this function inspects the shape of a filter *value* — it only
 * ever sees an op that the declared semantics already chose. That is why a
 * numeric checkbox can no longer compile to `BETWEEN`.
 */
function toSQL(op: FilterOp, column: Column): SQL {
  switch (op.op) {
    case "substring":
      // Case-insensitive, matching the in-memory engine.
      // NOTE: LIKE metacharacters in the value are deliberately not escaped —
      // see `sql-injection.test.ts`, which pins this as safe-but-broadening.
      return ilike(column, `%${op.value}%`);

    case "equals":
      return eq(column, op.value);

    case "oneOf":
      return inArray(column, op.values);

    case "overlaps": {
      // Postgres array overlap. The column is a set on both sides.
      //
      // The literal is cast to the column's OWN array type, read off the
      // schema. `&&` resolves no implicit casts whatsoever: `integer[] &&
      // text[]` is a type error, and so is `integer[] && numeric[]`. This used
      // to emit `::text[]` unconditionally, so `col.array(col.number())` — a
      // combination the builder permits and its docstring endorses — failed at
      // query time with `operator does not exist`.
      //
      // Deriving the cast from the declared `itemKind` cannot work either: a
      // `number` item says nothing about whether the column is `integer[]`,
      // `bigint[]` or `double precision[]`, and Postgres rejects all three
      // mismatches. The column knows, so the column is asked.
      //
      // `sql.raw` is safe here for the same reason `${column}` is: the type
      // comes from the Drizzle schema, never from a request.
      const arrayType = column.getSQLType();
      return sql`${column} && ARRAY[${sql.join(
        op.values.map((value) => sql`${value}`),
        sql`, `,
      )}]::${sql.raw(arrayType)}`;
    }

    case "numberRange":
      return between(column, op.min, op.max);

    case "dateRange":
      return and(gte(column, op.from), lte(column, op.to))!;
  }
}

/**
 * Build WHERE conditions from declared filter semantics and a column mapping.
 *
 * @param filters - The declared semantics, from `defineFilters(...)`
 * @param values  - Current filter values from parsed search params
 * @param mapping - Maps filter keys to Drizzle columns
 * @param selection - `{ only, exclude }` to limit keys (for the three-pass strategy)
 */
export function buildWhereConditions(
  filters: Filters,
  values: Record<string, unknown>,
  mapping: ColumnMapping,
  selection?: FilterSelection,
): SQL[] {
  const conditions: SQL[] = [];
  for (const op of filters.plan(values, selection)) {
    const column = mapping[op.key];
    if (!column) continue;
    conditions.push(toSQL(op, column));
  }
  return conditions;
}

import { asc, desc, gt, lt, type SQL } from "drizzle-orm";
import type { CursorPaginationParams } from "./types";

/**
 * Build cursor-based pagination conditions and ordering.
 *
 * - direction "next": fetch rows BEFORE cursor (older) → ORDER BY cursorCol DESC
 * - direction "prev": fetch rows AFTER cursor (newer) → ORDER BY cursorCol ASC
 *
 * `tiebreakOrderBy` follows the cursor's direction rather than being fixed, so
 * that a "prev" page — fetched ascending and reversed in the handler — ends up
 * in the same total order as the "next" pages around it.
 */
export function buildCursorPagination(params: CursorPaginationParams): {
  cursorCondition: SQL | undefined;
  orderBy: SQL;
  tiebreakOrderBy: SQL | undefined;
  needsReverse: boolean;
} {
  const { cursor, direction, cursorColumn, tiebreakColumn } = params;

  const cursorValue =
    cursor instanceof Date
      ? cursor
      : cursor != null
        ? new Date(cursor)
        : new Date();

  if (direction === "prev") {
    return {
      cursorCondition: gt(cursorColumn, cursorValue),
      orderBy: asc(cursorColumn),
      tiebreakOrderBy: tiebreakColumn ? asc(tiebreakColumn) : undefined,
      needsReverse: true,
    };
  }

  return {
    cursorCondition: lt(cursorColumn, cursorValue),
    orderBy: desc(cursorColumn),
    tiebreakOrderBy: tiebreakColumn ? desc(tiebreakColumn) : undefined,
    needsReverse: false,
  };
}

import { asc, desc, type SQL } from "drizzle-orm";
import type { ColumnMapping, SortDescriptor } from "./types";

/**
 * Build an ORDER BY clause from a sort descriptor.
 * Returns undefined if no sort is specified or the column isn't mapped.
 */
export function buildOrderBy(
  mapping: ColumnMapping,
  sort: SortDescriptor,
): SQL | undefined {
  if (!sort) return undefined;

  const column = mapping[sort.id];
  if (!column) return undefined;

  return sort.desc ? desc(column) : asc(column);
}