import { createNeonHttpClient } from '@platform-modules/db/postgres/neon-http';
import { createNeonServerlessClient } from '@platform-modules/db/postgres/neon-serverless';
import { createD1Client } from '@platform-modules/db/sqlite/d1';
import type { TransactionalDatabase, Dialect } from '@platform-modules/db';
import { customAuthSchema } from '@platform-modules/auth/engine-custom';
import { catalogSchema } from '@platform-modules/commerce-catalog';
import { cartSchema } from '@platform-modules/commerce-cart/store-db';
import { checkoutSchema } from '@platform-modules/commerce-checkout';
import { fulfillmentSchema } from '@platform-modules/commerce-fulfillment';
import { inventorySchema } from '@platform-modules/commerce-inventory';
import { ordersSchema } from '@platform-modules/commerce-orders';
import { promotionsSchema } from '@platform-modules/commerce-promotions';
import { reviewsSchema } from '@platform-modules/commerce-reviews';
import { jobsSchema } from '@platform-modules/jobs/db-poll';
import { outboxSchema } from '@platform-modules/jobs/outbox';
import { ledgerSchema } from '@platform-modules/ledger';

export class MissingDatabaseError extends Error {
  readonly name = 'MissingDatabaseError';

  constructor() {
    super('No database configured: set DB binding (D1) or DATABASE_URL secret (Neon)');
  }
}

const fullSchema = {
  ...customAuthSchema,
  ...ledgerSchema,
  ...catalogSchema,
  ...cartSchema,
  ...ordersSchema,
  ...checkoutSchema,
  ...inventorySchema,
  ...fulfillmentSchema,
  ...promotionsSchema,
  ...reviewsSchema,
  ...jobsSchema,
  ...outboxSchema,
};

export type DbEnv = {
  DB?: D1Database;
  DATABASE_URL?: string;
};

export type DbResult = {
  db: TransactionalDatabase<typeof fullSchema>;
  dialect: Dialect;
};

/**
 * Get database client. Prefers D1 (SQLite) if available, falls back to Neon (Postgres).
 */
export function getDb(env: DbEnv): DbResult {
  if (env.DB) {
    return {
      db: createD1Client(env.DB, { schema: fullSchema }) as unknown as TransactionalDatabase<
        typeof fullSchema
      >,
      dialect: 'sqlite',
    };
  }

  if (env.DATABASE_URL) {
    return {
      db: createNeonHttpClient({
        connectionString: env.DATABASE_URL,
        schema: fullSchema,
      }) as unknown as TransactionalDatabase<typeof fullSchema>,
      dialect: 'postgres',
    };
  }

  throw new MissingDatabaseError();
}

/** Transaction-capable client (D1 supports transactions; neon-http doesn't). */
export function getTransactionalDb(env: DbEnv): DbResult {
  if (env.DB) {
    return {
      db: createD1Client(env.DB, { schema: fullSchema }) as unknown as TransactionalDatabase<
        typeof fullSchema
      >,
      dialect: 'sqlite',
    };
  }

  if (env.DATABASE_URL) {
    return {
      db: createNeonServerlessClient({
        connectionString: env.DATABASE_URL,
        schema: fullSchema,
      }) as unknown as TransactionalDatabase<typeof fullSchema>,
      dialect: 'postgres',
    };
  }

  throw new MissingDatabaseError();
}