import type { D1Database, D1PreparedStatement } from "@cloudflare/workers-types";
import type pg from "pg";
import { v7 as uuidv7 } from "uuid";

export function newId(): string {
  return uuidv7();
}

export function newLongId(): string {
  return uuidv7();
}

export type D1Prepared = D1PreparedStatement;

export interface D1Queryable {
  prepare(sql: string): D1PreparedStatement;
  query?<T extends pg.QueryResultRow = pg.QueryResultRow>(text: string, params?: unknown[]): Promise<pg.QueryResult<T>>;
  // `any` matches D1Database's own signature. Narrowing it to `unknown` makes
  // every `const [a, b] = await db.batch([...])` destructure lose its row type.
  // biome-ignore lint/suspicious/noExplicitAny: mirrors the D1Database contract
  batch<_T = any>(statements: D1PreparedStatement[]): Promise<any[]>;
}

export type D1 = D1Queryable | D1Database;

/**
 * Run PostgreSQL-flavoured SQL (with `$1` placeholders) against the database
 * handle. In production `db` is a `PgD1Database` from pgD1.ts, whose `query()`
 * normalises timestamp functions and forwards to `pg`.
 *
 * This used to also carry a PostgreSQL -> SQLite translator plus a
 * `no such column` rescue path that patched missing columns out of failing
 * queries at runtime. Both existed because the PostgreSQL schema lagged behind
 * the SQLite one. The schema in postgres_migrations/0001_initial.sql is now
 * generated from the full migration chain, so a missing column is a real bug
 * and should surface as one.
 */
export async function queryDb<T extends pg.QueryResultRow = pg.QueryResultRow>(
  db: D1,
  text: string,
  params: unknown[] = [],
): Promise<pg.QueryResult<T>> {
  const queryable = db as D1Queryable;
  const looksLikeD1 = typeof (db as D1Database).prepare === "function" && typeof queryable.batch === "function";
  if (!looksLikeD1 && typeof queryable.query === "function") {
    return queryable.query<T>(text, params);
  }

  if (typeof (db as D1Database).prepare === "function") {
    const sqliteParams: unknown[] = [];
    let converted = text
      .replace(/\$(\d+)/g, (_match, indexText) => {
        const index = Number.parseInt(indexText, 10) - 1;
        sqliteParams.push(params[index]);
        return "?";
      })
      .replace(/\bNOW\(\)/gi, "strftime('%Y-%m-%dT%H:%M:%fZ','now')")
      .replace(/\bnow_iso\(\)/gi, "strftime('%Y-%m-%dT%H:%M:%fZ','now')");

    // Strip PostgreSQL casts that SQLite does not understand.
    converted = converted.replace(/::(?:jsonb|json|text|int|integer|bigint|boolean|uuid)\b/gi, "");

    // Rewrite the PostgreSQL JSON accessors used in task queries.
    converted = converted.replace(
      /(\b[a-zA-Z_][a-zA-Z0-9_.]*|\([^()]+\))\s*->\s*'annotations'\s*->>\s*'([^']+)'/g,
      (_match, expr, key) => `json_extract(${expr}, '$.annotations."${key}"')`,
    );

    // PostgreSQL JSONB array containment for labels -> SQLite json_each().
    converted = converted.replace(
      /COALESCE\(([^,]+),\s*'\[\]'\)\s*@>\s*to_jsonb\(\?\)/gi,
      (_match, expr) => `EXISTS (SELECT 1 FROM json_each(COALESCE(${expr}, '[]')) WHERE value = ?)`,
    );

    const stmt = (db as D1Database).prepare(converted).bind(...sqliteParams);
    const trimmed = text.trim().toUpperCase();
    if (trimmed.startsWith("SELECT") || trimmed.includes("RETURNING")) {
      const res = await stmt.all<T>();
      return { rows: res.results, rowCount: res.results.length } as unknown as pg.QueryResult<T>;
    }
    const res = await stmt.run();
    return { rows: [], rowCount: res.meta?.changes ?? 0 } as unknown as pg.QueryResult<T>;
  }

  throw new Error("Database handle does not support query() or prepare(). Check how env.DB was bound.");
}

// Hard ceiling on rows returned from a single task partition (actions or
// messages). Protects DB read budget against tasks with runaway row counts.
// Any fetch that returns exactly this many rows is at the cap — callers
// must assume older/newer rows beyond this point were silently truncated.
export const MAX_TASK_PARTITION_ROWS = 500;

/**
 * Parse JSON columns that are still stored as TEXT.
 *
 * Columns migrated to JSONB come back from `pg` already parsed, so this is a
 * no-op for them — it only acts on string values. Keeping it means call sites
 * work unchanged whether a given column is JSONB or TEXT.
 */
export function parseJsonFields<T>(row: T, fields: (keyof T)[]): T {
  for (const f of fields) {
    if (typeof row[f] === "string" && row[f]) {
      try {
        row[f] = JSON.parse(row[f] as string);
      } catch {
        // Already an object, or genuinely not JSON — leave as-is.
      }
    }
  }
  return row;
}

/**
 * Read a column that may arrive as a parsed object (JSONB) or a JSON string
 * (TEXT), and return it as an object. Use this instead of a bare `JSON.parse`,
 * which throws once a column is migrated to JSONB and `pg` hands back an
 * object.
 */
export function asJson<T>(value: unknown, fallback: T): T {
  if (value === null || value === undefined || value === "") return fallback;
  if (typeof value === "string") {
    try {
      return JSON.parse(value) as T;
    } catch {
      return fallback;
    }
  }
  return value as T;
}

/**
 * D1 can still contain legacy `datetime('now')` values in
 * `YYYY-MM-DD HH:MM:SS(.sss)` form. Parse that shape as UTC so stale-heartbeat
 * checks behave the same as PostgreSQL timestamptz reads.
 */
export function parseDbTimestamp(value: string | null | undefined): number {
  if (!value) return Number.NaN;
  if (/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(?:\.\d+)?$/.test(value)) {
    return Date.parse(`${value.replace(" ", "T")}Z`);
  }
  return Date.parse(value);
}