/**
 * Coercion helpers for raw SQL rows. postgres.js (as configured by drizzle) returns bigint counts as
 * strings, timestamps as strings ("2026-09-11 02:41:00.123456+00") and jsonb already parsed.
 */
export type Row = Record<string, unknown>;

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

export function reqStr(v: unknown, fallback = ""): string {
  return str(v) ?? fallback;
}

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

export function int(v: unknown, fallback = 0): number {
  const n = num(v);
  return n == null ? fallback : Math.trunc(n);
}

export function float(v: unknown, fallback = 0): number {
  return num(v) ?? fallback;
}

export function bool(v: unknown): boolean {
  return v === true || v === "t" || v === "true" || v === 1 || v === "1";
}

/** Timestamp (string from the driver, Date from other paths) → ISO 8601. */
export function iso(v: unknown): string | null {
  if (v == null) return null;
  if (v instanceof Date) return Number.isNaN(v.getTime()) ? null : v.toISOString();
  const s = String(v).trim();
  if (!s) return null;
  if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s; // date column: keep the day
  const d = new Date(s.replace(" ", "T").replace(/([+-]\d{2})$/, "$1:00"));
  if (Number.isNaN(d.getTime())) {
    const d2 = new Date(s);
    return Number.isNaN(d2.getTime()) ? s : d2.toISOString();
  }
  return d.toISOString();
}

export function reqIso(v: unknown): string {
  return iso(v) ?? new Date(0).toISOString();
}

export function strArray(v: unknown): string[] {
  if (Array.isArray(v)) return v.filter((x) => x != null).map(String);
  if (typeof v === "string") {
    // text[] literal "{a,b}" when not parsed by the driver
    if (v.startsWith("{") && v.endsWith("}")) return v.slice(1, -1).split(",").map((s) => s.replace(/^"|"$/g, "")).filter(Boolean);
    return [v];
  }
  return [];
}

export function json<T = unknown>(v: unknown, fallback: T): T {
  if (v == null) return fallback;
  if (typeof v === "string") {
    try { return JSON.parse(v) as T; } catch { return fallback; }
  }
  return v as T;
}

export function record(v: unknown): Record<string, string | number> {
  const j = json<Record<string, unknown>>(v, {});
  const out: Record<string, string | number> = {};
  if (j && typeof j === "object" && !Array.isArray(j)) for (const [k, val] of Object.entries(j)) if (typeof val === "string" || typeof val === "number") out[k] = val;
  return out;
}

/** {id, slug, name} reference from prefixed columns (e.g. "op_id", "op_slug", "op_name"). */
export function ref(r: Row, prefix: string): { id: string; slug: string; name: string } | null {
  const id = str(r[`${prefix}_id`]);
  if (!id) return null;
  return { id, slug: reqStr(r[`${prefix}_slug`]), name: reqStr(r[`${prefix}_name`]) };
}

export function round(v: number | null, digits = 2): number | null {
  if (v == null) return null;
  const f = 10 ** digits;
  return Math.round(v * f) / f;
}