// Shared helper for the reference/sql scripts: Postgres connection from the repo's .env (PG_* keys).
// Dev: needs the read tunnel  ssh -N -L 5432:localhost:5432 root@192.168.12.72  (see
// .claude/rules/idempiere/prod-readonly-queries.md). Prod host: PG_HOST=localhost works directly.
const fs = require('fs')
const path = require('path')

const REPO = path.resolve(__dirname, '..', '..')
const SCHEMA = 'adempiere'
const VIEW = 'v_orders_summary'

function loadEnv (file = '.env') {
  const env = {}
  const p = path.join(REPO, file)
  if (!fs.existsSync(p)) throw new Error(`${file} not found in ${REPO}`)
  for (const line of fs.readFileSync(p, 'utf8').split('\n')) {
    const m = line.match(/^(PG_[A-Z]+)=(.*)$/)
    if (m) env[m[1]] = m[2].replace(/^['"]|['"]$/g, '')
  }
  for (const k of ['PG_HOST', 'PG_DATABASE', 'PG_USER', 'PG_PASSWORD']) {
    if (!env[k]) throw new Error(`${k} missing in ${file}`)
  }
  return env
}

async function connect () {
  const { Client } = require('pg')
  const env = loadEnv(process.env.PG_ENV_FILE || '.env')
  const client = new Client({
    host: env.PG_HOST,
    port: parseInt(env.PG_PORT || '5432'),
    database: env.PG_DATABASE,
    user: env.PG_USER,
    password: env.PG_PASSWORD,
    statement_timeout: 120000,
  })
  await client.connect()
  await client.query(`SET search_path TO ${SCHEMA}, public`)
  return { client, env }
}

function timestamp () {
  const d = new Date()
  const p = (n) => String(n).padStart(2, '0')
  return `${d.getFullYear()}${p(d.getMonth() + 1)}${p(d.getDate())}-${p(d.getHours())}${p(d.getMinutes())}${p(d.getSeconds())}`
}

module.exports = { REPO, SCHEMA, VIEW, loadEnv, connect, timestamp }