import { Pool, type PoolClient, type QueryResult } from 'pg'
import type { GpLiveDb, GpLiveDbRunResult, GpLivePreparedStatement } from './gpLiveDb'
import { adaptSqlForPostgres, splitSqlStatements } from './sqlDialect'
import { normalizePostgresRow, normalizePostgresRows } from './pgRow'

const MIGRATION_LOCK_KEY = 77377701

/** 將 sqlite 風格 bind 參數正規化為 pg 陣列 */
export function normalizeBindParams(params: unknown[] | undefined): unknown[] {
    const list = params ?? []
    if (list.length === 1 && Array.isArray(list[0])) {
        return list[0] as unknown[]
    }
    return list
}

class PostgresPreparedStatement implements GpLivePreparedStatement {
    constructor(
        private readonly pool: Pool,
        private readonly text: string
    ) {}

    async run(...params: unknown[]): Promise<GpLiveDbRunResult> {
        const values = normalizeBindParams(params)
        const result: QueryResult = await this.pool.query(this.text, values)
        return { changes: result.rowCount ?? 0 }
    }

    async finalize(): Promise<void> {
        // pg 無需像 sqlite3 那樣 finalize
    }
}

export class PostgresGpLiveDb implements GpLiveDb {
    readonly dialect = 'postgres' as const

    constructor(private readonly pool: Pool) {}

    private buildQuery(sql: string, params: unknown[] | undefined): { text: string; values: unknown[] } {
        return { text: adaptSqlForPostgres(sql), values: normalizeBindParams(params) }
    }

    async run(sql: string, ...params: unknown[]): Promise<GpLiveDbRunResult> {
        const { text, values } = this.buildQuery(sql, params)
        const result: QueryResult = await this.pool.query(text, values)
        return { changes: result.rowCount ?? 0 }
    }

    async get<T = unknown>(sql: string, ...params: unknown[]): Promise<T | undefined> {
        const { text, values } = this.buildQuery(sql, params)
        const result = await this.pool.query(text, values)
        const row = result.rows[0] as Record<string, unknown> | undefined
        if (!row) return undefined
        return normalizePostgresRow(row) as T
    }

    async all<T = unknown>(sql: string, ...params: unknown[]): Promise<T[]> {
        const { text, values } = this.buildQuery(sql, params)
        const result = await this.pool.query(text, values)
        return normalizePostgresRows(result.rows as Record<string, unknown>[]) as T[]
    }

    async prepare(sql: string): Promise<GpLivePreparedStatement> {
        return new PostgresPreparedStatement(this.pool, adaptSqlForPostgres(sql))
    }

    async exec(sql: string): Promise<void> {
        const adapted = adaptSqlForPostgres(sql)
        for (const stmt of splitSqlStatements(adapted)) {
            if (!stmt.trim()) continue
            await this.pool.query(stmt)
        }
    }

    async close(): Promise<void> {
        await this.pool.end()
    }

    getPool(): Pool {
        return this.pool
    }
}

let poolPromise: Promise<PostgresGpLiveDb> | undefined

export function getDatabaseUrl(): string {
    const url = process.env.DATABASE_URL?.trim()
    if (!url) {
        throw new Error('[gp-live] DATABASE_URL is required when using PostgreSQL (GP_LIVE_DB_DRIVER=postgres)')
    }
    return url
}

export async function getPostgresDb(): Promise<PostgresGpLiveDb> {
    if (!poolPromise) {
        poolPromise = (async () => {
            const pool = new Pool({
                connectionString: getDatabaseUrl(),
                max: Number.parseInt(process.env.PG_POOL_MAX || '20', 10) || 20,
            })
            pool.on('error', (err) => {
                console.error('[gp-live] PostgreSQL pool error:', err)
            })
            const client = await pool.connect()
            try {
                await client.query('SELECT 1')
            } finally {
                client.release()
            }
            return new PostgresGpLiveDb(pool)
        })()
    }
    return poolPromise
}

export async function withPostgresMigrationLock<T>(pool: Pool, fn: () => Promise<T>): Promise<T> {
    const client: PoolClient = await pool.connect()
    try {
        await client.query('SELECT pg_advisory_lock($1)', [MIGRATION_LOCK_KEY])
        return await fn()
    } finally {
        await client.query('SELECT pg_advisory_unlock($1)', [MIGRATION_LOCK_KEY]).catch(() => {})
        client.release()
    }
}