import type { Pool } from 'pg'
import { formatDevSqlLog } from './dev-log.js'

export type DbQueryFn = (sql: string, params?: unknown[]) => Promise<{ rows: unknown[] }>

export type DbQueryExecutor = (
  sql: string,
  params?: unknown[]
) => Promise<{ rows: unknown[] } | unknown[]>

/** 结构化 SQL 日志事件（平台 logger 消费） */
export interface SqlLogEvent {
  sql: string
  /** 参数内联后的可执行 SQL（开发日志 / 平台 logger 优先展示） */
  runnableSql?: string
  params?: unknown[]
  durationMs: number
  rowCount: number
  tag?: string
}

export interface LoggedDbQueryOptions {
  /**
   * 是否打印 SQL
   * - 未设置：开发环境默认 true，生产环境默认 false
   * - DB_LOG_SQL / STAGO_LOG_SQL：true/false/1/0 显式控制
   */
  logSql?: boolean
  /** 日志前缀，如 @ops/catindex@0.3.14 */
  tag?: string
  /** 结构化 SQL 日志（生产推荐） */
  logEvent?: (event: SqlLogEvent) => void
  /** 纯文本 SQL 日志（开发兜底） */
  log?: (message: string) => void
}

/** 是否启用 SQL 日志（DB_LOG_SQL / STAGO_LOG_SQL） */
export function isSqlLogEnabled(): boolean {
  const raw = process.env.DB_LOG_SQL ?? process.env.STAGO_LOG_SQL
  if (raw === '0' || raw === 'false') return false
  if (raw === '1' || raw === 'true') return true
  return process.env.NODE_ENV !== 'production'
}

function resolveLogSql(option?: boolean): boolean {
  return option ?? isSqlLogEnabled()
}

/** 将 pg 占位符参数格式化为 SQL 字面量 */
export function formatSqlParam(value: unknown): string {
  if (value === null || value === undefined) return 'NULL'
  if (typeof value === 'number' && Number.isFinite(value)) return String(value)
  if (typeof value === 'bigint') return `${value}`
  if (typeof value === 'boolean') return value ? 'TRUE' : 'FALSE'
  if (value instanceof Date) return `'${value.toISOString().replace(/'/g, "''")}'`
  if (Array.isArray(value)) {
    if (value.length === 0) return 'ARRAY[]::text[]'
    const items = value.map((item) => formatSqlParam(item))
    return `ARRAY[${items.join(', ')}]`
  }
  if (Buffer.isBuffer(value)) {
    return `'\\x${value.toString('hex')}'`
  }
  if (typeof value === 'object') {
    return `'${JSON.stringify(value).replace(/'/g, "''")}'::jsonb`
  }
  return `'${String(value).replace(/'/g, "''")}'`
}

/** 将 $1,$2,... 替换为字面量，生成可在 psql / dbhub 直接执行的 SQL */
export function formatRunnableSql(sql: string, params?: unknown[]): string {
  const trimmed = sql.trim()
  if (!params?.length) return trimmed
  let result = trimmed
  for (let index = params.length; index >= 1; index--) {
    const literal = formatSqlParam(params[index - 1])
    result = result.replace(new RegExp(`\\$${index}(?!\\d)`, 'g'), literal)
  }
  return result
}

function formatSqlLogMessage(
  sql: string,
  params: unknown[] | undefined,
  ms: number,
  rowCount: number,
  tag?: string
): string {
  return formatDevSqlLog({
    tag,
    durationMs: ms,
    rowCount,
    runnableSql: formatRunnableSql(sql, params),
  })
}

function normalizeRows(result: { rows: unknown[] } | unknown[]): { rows: unknown[] } {
  return Array.isArray(result) ? { rows: result } : result
}

/**
 * 包装任意 db 执行函数，按环境变量打印 SQL、参数、耗时与行数。
 */
export function createLoggedDbQuery(
  execute: DbQueryExecutor,
  options: LoggedDbQueryOptions = {}
): DbQueryFn {
  const logSql = resolveLogSql(options.logSql)
  const { tag, logEvent, log } = options

  return async (sql: string, params?: unknown[]) => {
    const start = Date.now()
    const result = normalizeRows(await execute(sql, params))
    if (logSql) {
      const durationMs = Date.now() - start
      const rowCount = result.rows.length
      if (logEvent) {
        logEvent({
          sql,
          runnableSql: formatRunnableSql(sql, params),
          params,
          durationMs,
          rowCount,
          tag,
        })
      } else if (log) {
        log(formatSqlLogMessage(sql, params, durationMs, rowCount, tag))
      } else {
        console.log(formatSqlLogMessage(sql, params, durationMs, rowCount, tag))
      }
    }
    return result
  }
}

/** 包装 pg Pool.query */
export function createLoggedPoolQuery(pool: Pool, options: LoggedDbQueryOptions = {}): DbQueryFn {
  return createLoggedDbQuery(
    async (sql, params) => {
      const result = await pool.query(sql, params)
      return { rows: result.rows }
    },
    options
  )
}