import 'server-only'

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'

/** 遞增後會強制重跑 schema 遷移（避免 dev HMR 沿用舊 __gpLiveInitPromise） */
const SCHEMA_GENERATION = 19

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(),
    }
}

async function openSqliteDb(): Promise<GpLiveDb> {
    const dbPath = getDbPath()
    fs.mkdirSync(path.dirname(dbPath), { recursive: true })
    const db = await open({
        filename: dbPath,
        driver: sqlite3.Database,
    })
    return wrapSqliteDatabase(db)
}

async function bootstrapDb(db: GpLiveDb): Promise<void> {
    await initCoreSchema(db)
    if (db.dialect === 'sqlite') {
        await db.exec('PRAGMA busy_timeout = 10000;')
    }
    await runUserAndGroupsSchemaMigrations(db)
    try {
        const { seedConfigAvatarFramesFromCatalog } = await import(
            '@/server/avatarFrames/seedConfigAvatarFrames'
        )
        const sync = await seedConfigAvatarFramesFromCatalog(db)
        if (sync.inserted > 0) {
            console.log(
                `[gp-live] config_avatar_frames: synced ${sync.inserted} items from item.json; user_avatar_frames backup ${sync.backedUp}, restored ${sync.restored}, skipped ${sync.skipped}`
            )
        }
    } catch (e) {
        console.error('Error while seeding config_avatar_frames from item.json:', e)
    }

    const groupCols = await db.all<{ name: string }>(`PRAGMA table_info(groups);`)
    if (groupCols?.length && !groupCols.some((c) => c.name === 'display_id')) {
        const { ensureGroupsDisplayIdColumn } = await import('./userSchemaMigration')
        await ensureGroupsDisplayIdColumn(db)
    }
}

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

/**
 * 取得資料庫連線（依環境變數自動選擇 SQLite 或 PostgreSQL）。
 *
 * PostgreSQL：設定 `DATABASE_URL` 或 `GP_LIVE_DB_DRIVER=postgres`
 * SQLite（預設）：`data/gp-live.sqlite`，可用 `GP_LIVE_DB_PATH` 覆寫
 */
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() : openSqliteDb()
    }
    const db = await g.__gpLiveDbPromise

    if (!g.__gpLiveInitPromise) {
        g.__gpLiveInitPromise = (async () => {
            if (db.dialect === 'postgres') {
                const pg = await getPostgresDb()
                await withPostgresMigrationLock(pg.getPool(), () => bootstrapDb(db))
            } else {
                const dbPath = getDbPath()
                await withSchemaMigrationLock(dbPath, () => bootstrapDb(db))
            }
        })()
    }

    await g.__gpLiveInitPromise
    return db
}

export { usePostgres, getDbPath }