import { sql, type SQL } from 'drizzle-orm';

/**
 * Drizzle fan-out gotcha
 * ----------------------
 * Drizzle's `sql` template tag spreads JS arrays interpolated as
 * `${arr}` into N separate placeholders, so `sql\`(${['a','b']})\``
 * compiles to `($1, $2)` rather than a single array bind. Postgres
 * then rejects that record/tuple shape against an array-typed column
 * with `42804: column "x" is of type text[] but expression is of
 * type record`.
 *
 * `bindValue` normalises every dynamic value going into a SET clause
 * or VALUES list so it always becomes one bind:
 *   - JS arrays  -> a single `'{...}'::text[]` literal cast
 *   - Plain objects -> `JSON.stringify(...)::jsonb`
 *   - Everything else -> a normal scalar bind
 *
 * Anywhere we build dynamic INSERT/UPDATE SQL by hand (i.e. anything
 * doing `sql.join(sets, sql\`, \`)` or fanning a `vals.map(...)` into
 * a VALUES list) MUST route every dynamic value through this helper.
 * That's what keeps the next person who adds a `text[]` column from
 * silently re-introducing the same outage.
 */

export function toPgArrayLiteral(arr: unknown[]): string {
  const escaped = arr.map(el => {
    if (el === null || el === undefined) return 'NULL';
    return '"' + String(el).replace(/\\/g, '\\\\').replace(/"/g, '\\"') + '"';
  });
  return '{' + escaped.join(',') + '}';
}

export function bindValue(v: unknown): SQL {
  if (Array.isArray(v)) {
    return sql`${toPgArrayLiteral(v)}::text[]`;
  }
  if (
    v !== null &&
    typeof v === 'object' &&
    !(v instanceof Date) &&
    !(v instanceof Buffer)
  ) {
    return sql`${JSON.stringify(v)}::jsonb`;
  }
  return sql`${v as string | number | boolean | null}`;
}