import { drizzle as drizzlePg } from "drizzle-orm/postgres-js";
import { drizzle as drizzleSqlite } from "drizzle-orm/libsql";
import { migrate as libsqlMigrate } from "drizzle-orm/libsql/migrator";
import { createClient } from "@libsql/client";
import postgres from "postgres";
import * as pgSchema from "./schema.js";
import * as sqliteSchema from "./schema.sqlite.js";

/**
 * Create a Drizzle database instance from a connection URL.
 *
 * URL prefix determines the driver:
 *   postgres:// or postgresql://  →  postgres.js (PostgreSQL)
 *   file:                         →  @libsql/client (SQLite file)
 *   file::memory:                 →  @libsql/client (SQLite in-memory, tests)
 *   libsql://                     →  @libsql/client (Turso cloud)
 */
export function createDb(databaseUrl: string): Database {
  if (databaseUrl.startsWith("postgres")) {
    return drizzlePg(postgres(databaseUrl), { schema: pgSchema }) as Database;
  }
  return drizzleSqlite(
    createClient({ url: databaseUrl }),
    { schema: sqliteSchema }
  ) as unknown as Database;
}

/**
 * Run SQLite migrations on a database created with createDb().
 * Uses the same drizzle-orm instance as createDb() to avoid cross-package
 * module boundary issues that occur when using the migrator from a different
 * drizzle-orm installation.
 *
 * Only works with SQLite databases (file: or libsql: URLs).
 * For PostgreSQL, use drizzle-kit migrate directly.
 *
 * @param db - Database created with createDb() for a SQLite URL
 * @param migrationsFolder - Absolute path to the folder containing migration SQL files
 */
export async function runSqliteMigrations(
  db: Database,
  migrationsFolder: string
): Promise<void> {
  await libsqlMigrate(db as any, { migrationsFolder });
}

// Database type uses the Postgres schema as the TypeScript source of truth.
// Both dialects produce compatible column names and TypeScript types,
// so the cast is safe at the app layer.
export type Database = ReturnType<typeof drizzlePg<typeof pgSchema>>;

export type Transaction = Parameters<Parameters<Database["transaction"]>[0]>[0];

export type DbOrTransaction = Database | Transaction;

export * from "./schema.js";