import 'server-only';
import { sql, type SQL } from 'drizzle-orm';
import { getDb } from '@rareindex/database';

/** Run a raw SQL statement and return typed rows (drizzle `execute` returns row arrays for postgres-js). */
export async function rows<T extends Record<string, unknown>>(query: SQL): Promise<T[]> {
  // pnpm resolves two drizzle-orm instances (pg peer via pg-boss vs postgres); the SQL object is identical at runtime.
  const res = await getDb().execute(query as unknown as string);
  return res as unknown as T[];
}

export async function one<T extends Record<string, unknown>>(query: SQL): Promise<T | null> {
  const r = await rows<T>(query);
  return r[0] ?? null;
}

export function num(v: unknown): number | null {
  if (v === null || v === undefined || v === '') return null;
  const n = Number(v);
  return Number.isFinite(n) ? n : null;
}

export function int(v: unknown): number {
  const n = Number(v);
  return Number.isFinite(n) ? Math.trunc(n) : 0;
}

export function str(v: unknown): string | null {
  return v === null || v === undefined ? null : String(v);
}

export function date(v: unknown): Date | null {
  if (!v) return null;
  const d = v instanceof Date ? v : new Date(String(v));
  return Number.isNaN(d.getTime()) ? null : d;
}

export function joinAnd(parts: SQL[]): SQL {
  return parts.length ? sql.join(parts, sql` AND `) : sql`true`;
}

/** Postgres text[] literal from a JS string array (drizzle expands bare arrays into tuples, not arrays). */
export function textArray(values: string[]): SQL {
  if (!values.length) return sql`ARRAY[]::text[]`;
  return sql`ARRAY[${sql.join(values.map((v) => sql`${v}`), sql`, `)}]::text[]`;
}

/** ISO string for timestamp params (postgres-js `execute` does not serialise Date instances). */
export function iso(d: Date): string {
  return d.toISOString();
}

export function clampPage(page: unknown, size = 50): { limit: number; offset: number; page: number } {
  const p = Math.max(1, Math.trunc(Number(page) || 1));
  return { limit: size, offset: (p - 1) * size, page: p };
}

export { sql };