import fs from 'node:fs'
import path from 'node:path'
import { open, type Database } from 'sqlite'
import sqlite3 from 'sqlite3'
import type { GpLiveDb } from './gpLiveDb'
import { usePostgres } from './gpLiveDb'
import { initCoreSchema } from './initSchema'
import { getPostgresDb, withPostgresMigrationLock } from './postgresClient'
import { withSchemaMigrationLock } from './schemaMigrationLock'
import { runUserAndGroupsSchemaMigrations } from './userSchemaMigration'

const SCHEMA_GENERATION = 9

const DEFAULT_DB_PATH = path.join(process.cwd(), 'data', 'gp-live.sqlite')

function getDbPath() {
    return process.env.GP_LIVE_DB_PATH?.trim() || DEFAULT_DB_PATH
}

function wrapSqliteDatabase(db: Database<sqlite3.Database, sqlite3.Statement>): GpLiveDb {
    return {
        dialect: 'sqlite',
        run: (sql, ...params) => db.run(sql, ...params),
        get: (sql, ...params) => db.get(sql, ...params),
        all: (sql, ...params) => db.all(sql, ...params),
        prepare: (sql) => db.prepare(sql),
        exec: (sql) => db.exec(sql),
        close: () => db.close(),
    }
}

const g = globalThis as unknown as {
    __gpLiveDbPromise?: Promise<GpLiveDb>
    __gpLiveInitPromise?: Promise<void>
    __gpLiveSchemaGeneration?: number
}

/** WS 伺服器用：與 Next.js 共用同一套 DB 設定（SQLite / PostgreSQL） */
export async function getDb(): Promise<GpLiveDb> {
    if (g.__gpLiveSchemaGeneration !== SCHEMA_GENERATION) {
        g.__gpLiveSchemaGeneration = SCHEMA_GENERATION
        g.__gpLiveDbPromise = undefined
        g.__gpLiveInitPromise = undefined
    }

    if (!g.__gpLiveDbPromise) {
        g.__gpLiveDbPromise = usePostgres()
            ? getPostgresDb()
            : (async () => {
                  const dbPath = getDbPath()
                  fs.mkdirSync(path.dirname(dbPath), { recursive: true })
                  const db = await open({
                      filename: dbPath,
                      driver: sqlite3.Database,
                  })
                  return wrapSqliteDatabase(db)
              })()
    }
    const db = await g.__gpLiveDbPromise

    if (!g.__gpLiveInitPromise) {
        g.__gpLiveInitPromise = (async () => {
            const runMigrations = async () => {
                await initCoreSchema(db)
                if (db.dialect === 'sqlite') {
                    await db.exec('PRAGMA busy_timeout = 10000;')
                }
                await runUserAndGroupsSchemaMigrations(db)
            }

            if (db.dialect === 'postgres') {
                const pg = await getPostgresDb()
                await withPostgresMigrationLock(pg.getPool(), runMigrations)
            } else {
                const dbPath = getDbPath()
                await withSchemaMigrationLock(dbPath, runMigrations)
            }
        })()
    }

    await g.__gpLiveInitPromise
    return db
}