src/lib/db.ts

import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const db =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['error'] : [],
  })

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = db

// ─── Auto-Initialize Database ────────────────────────────────────────
// Creates all tables via raw SQL if they don't exist.
// This makes the app self-bootstrapping — no prisma CLI needed at runtime.

let _initPromise: Promise<void> | null = null

const INIT_SQL = `
CREATE TABLE IF NOT EXISTS "User" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "email" TEXT NOT NULL UNIQUE,
  "name" TEXT NOT NULL,
  "password" TEXT NOT NULL,
  "plan" TEXT NOT NULL DEFAULT 'free',
  "stripeCid" TEXT,
  "apiKey" TEXT,
  "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS "Session" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "token" TEXT NOT NULL UNIQUE,
  "userId" TEXT NOT NULL REFERENCES "User"("id") ON DELETE CASCADE,
  "expiresAt" DATETIME NOT NULL,
  "createdAt" DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS "Invoice" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "userId" TEXT NOT NULL REFERENCES "User"("id") ON DELETE CASCADE,
  "filename" TEXT,
  "vendor" TEXT,
  "invNumber" TEXT,
  "invDate" TEXT,
  "dueDate" TEXT,
  "amount" REAL,
  "vatAmount" REAL,
  "total" REAL,
  "currency" TEXT NOT NULL DEFAULT 'USD',
  "status" TEXT NOT NULL DEFAULT 'done',
  "isDuplicate" BOOLEAN NOT NULL DEFAULT 0,
  "confidence" REAL,
  "rawJson" TEXT,
  "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS "RateLimit" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "key" TEXT NOT NULL UNIQUE,
  "count" INTEGER NOT NULL DEFAULT 0,
  "resetAt" DATETIME NOT NULL,
  "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS "ActivityLog" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "userId" TEXT NOT NULL REFERENCES "User"("id") ON DELETE CASCADE,
  "type" TEXT NOT NULL,
  "title" TEXT NOT NULL,
  "description" TEXT NOT NULL,
  "metadata" TEXT,
  "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS "ResetToken" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "email" TEXT NOT NULL REFERENCES "User"("email") ON DELETE CASCADE,
  "token" TEXT NOT NULL UNIQUE,
  "expiresAt" DATETIME NOT NULL,
  "usedAt" DATETIME,
  "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS "ActivityLog_userId_idx" ON "ActivityLog"("userId");
`.trim()

export async function ensureDb(): Promise<void> {
  if (_initPromise) return _initPromise

  _initPromise = (async () => {
    try {
      // Check if User table already exists
      const rows = await db.$queryRaw<Array<{ name: string }>>`
        SELECT name FROM sqlite_master WHERE type='table' AND name='User'
      `
      if (rows.length > 0) return // Already initialized
    } catch {
      // Table doesn't exist or DB file doesn't exist — proceed to create
    }

    try {
      // Create all tables
      for (const stmt of INIT_SQL.split('\n\n')) {
        const trimmed = stmt.trim()
        if (trimmed) await db.$executeRawUnsafe(trimmed)
      }
      console.log('[DB] Tables created successfully')
    } catch (error) {
      console.error('[DB] Failed to create tables:', error)
      _initPromise = null // Allow retry
      throw error
    }
  })()

  return _initPromise
}