import { PGlite } from '@electric-sql/pglite'
import { eq } from 'drizzle-orm'
import { drizzle } from 'drizzle-orm/pglite'
import { bigint, pgTable, text } from 'drizzle-orm/pg-core'
import type { TransactionalDatabase } from '@platform-modules/db'
import { withTransactionIdentity } from '@platform-modules/db'
import { ledgerEntries, ledgerSchema, walletBalances } from './schema.js'
import { ledgerEntryVesting, vestingSchema, walletVesting } from './vesting-schema.js'

export const subscriptions = pgTable('subscriptions', {
  tenantId: text('tenant_id').primaryKey(),
  creditBalance: bigint('credit_balance', { mode: 'bigint' }).notNull().default(0n),
})

export const walletBuckets = pgTable('wallet_buckets', {
  ownerId: text('owner_id').primaryKey(),
  pending: bigint('pending', { mode: 'bigint' }).notNull().default(0n),
  matured: bigint('matured', { mode: 'bigint' }).notNull().default(0n),
})

export const testSchema = {
  ...ledgerSchema,
  ...vestingSchema,
  subscriptions,
  walletBuckets,
}

export type TestSchema = typeof testSchema

export async function createTestDb(): Promise<TransactionalDatabase<TestSchema>> {
  const client = new PGlite()
  const db = withTransactionIdentity(drizzle(client, { schema: testSchema })) as unknown as TransactionalDatabase<TestSchema>

  await client.exec(`
    CREATE TABLE ledger_entries (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      delta bigint NOT NULL,
      currency text,
      reason text NOT NULL,
      ref jsonb,
      idempotency_key text NOT NULL,
      created_at timestamptz NOT NULL DEFAULT NOW()
    );
    CREATE UNIQUE INDEX ledger_entries_idempotency_key_uq ON ledger_entries (idempotency_key);

    CREATE TABLE wallet_balances (
      owner_id text PRIMARY KEY,
      balance bigint NOT NULL DEFAULT 0,
      updated_at timestamptz NOT NULL DEFAULT NOW()
    );

    CREATE TABLE subscriptions (
      tenant_id text PRIMARY KEY,
      credit_balance bigint NOT NULL DEFAULT 0
    );

    CREATE TABLE wallet_buckets (
      owner_id text PRIMARY KEY,
      pending bigint NOT NULL DEFAULT 0,
      matured bigint NOT NULL DEFAULT 0
    );

    CREATE TABLE ledger_entry_vesting (
      entry_id uuid PRIMARY KEY REFERENCES ledger_entries(id),
      mature_at timestamptz,
      swept_at timestamptz,
      withdrawable_at timestamptz
    );
    CREATE INDEX ledger_entry_vesting_mature_idx ON ledger_entry_vesting (mature_at) WHERE swept_at IS NULL;

    CREATE TABLE wallet_vesting (
      owner_id text PRIMARY KEY,
      pending_minor bigint NOT NULL DEFAULT 0,
      matured_minor bigint NOT NULL DEFAULT 0,
      carried_debt_minor bigint NOT NULL DEFAULT 0,
      withdrawable_minor bigint NOT NULL DEFAULT 0,
      lifetime_earned_minor bigint NOT NULL DEFAULT 0,
      updated_at timestamptz NOT NULL DEFAULT NOW(),
      CONSTRAINT wallet_vesting_pending_minor_nonneg CHECK (pending_minor >= 0),
      CONSTRAINT wallet_vesting_matured_minor_nonneg CHECK (matured_minor >= 0),
      CONSTRAINT wallet_vesting_carried_debt_minor_nonneg CHECK (carried_debt_minor >= 0),
      CONSTRAINT wallet_vesting_withdrawable_minor_nonneg CHECK (withdrawable_minor >= 0)
    );
  `)

  return db
}

export async function seedWalletBalance(
  db: TransactionalDatabase<TestSchema>,
  ownerId: string,
  balance: bigint,
): Promise<void> {
  await db.insert(walletBalances).values({ ownerId, balance })
}

export async function seedSubscriptionBalance(
  db: TransactionalDatabase<TestSchema>,
  tenantId: string,
  creditBalance: bigint,
): Promise<void> {
  await db.insert(subscriptions).values({ tenantId, creditBalance })
}

export async function countLedgerRows(db: TransactionalDatabase<TestSchema>): Promise<number> {
  const rows = await db.select({ id: ledgerEntries.id }).from(ledgerEntries)
  return rows.length
}

export async function ledgerRowForKey(
  db: TransactionalDatabase<TestSchema>,
  key: string,
): Promise<{ delta: bigint } | undefined> {
  const [row] = await db
    .select({ delta: ledgerEntries.delta })
    .from(ledgerEntries)
    .where(eq(ledgerEntries.idempotencyKey, key))
    .limit(1)
  return row
}

export async function seedLedgerEntry(
  db: TransactionalDatabase<TestSchema>,
  id: string, delta: bigint, key: string,
): Promise<void> {
  await db.insert(ledgerEntries).values({ id, delta, reason: 'earn', idempotencyKey: key })
}

export async function seedWalletVesting(
  db: TransactionalDatabase<TestSchema>,
  ownerId: string,
  buckets: Partial<{ pendingMinor: bigint; maturedMinor: bigint; carriedDebtMinor: bigint; withdrawableMinor: bigint; lifetimeEarnedMinor: bigint }> = {},
): Promise<void> {
  await db.insert(walletVesting).values({ ownerId, ...buckets })
}

export async function getWalletVesting(db: TransactionalDatabase<TestSchema>, ownerId: string) {
  const [row] = await db.select().from(walletVesting).where(eq(walletVesting.ownerId, ownerId)).limit(1)
  return row
}

export async function seedLedgerEntryVesting(
  db: TransactionalDatabase<TestSchema>,
  entryId: string,
  opts: { matureAt?: Date; sweptAt?: Date | null } = {},
): Promise<void> {
  await db.insert(ledgerEntryVesting).values({
    entryId,
    matureAt: opts.matureAt ?? new Date(Date.now() - 86_400_000),
    sweptAt: opts.sweptAt ?? null,
  })
}

export async function getLedgerEntryVesting(db: TransactionalDatabase<TestSchema>, entryId: string) {
  const [row] = await db
    .select()
    .from(ledgerEntryVesting)
    .where(eq(ledgerEntryVesting.entryId, entryId))
    .limit(1)
  return row
}