/**
 * Free the DB unique key `c_invoice_documentno` (documentno, c_doctype_id,
 * c_bpartner_id) held by VOIDED/REVERSED AP invoices, so the same vendor
 * invoice number can be booked again after a Storno.
 *
 * Why direct Postgres (pg pool, same pattern as the openso-* routes):
 * C_Invoice.DocumentNo is NOT updateable through the REST model layer
 * (IsUpdateable='N' in the AD dictionary), so a REST PUT cannot rename it —
 * but the unique constraint is a hard DB index that does not care about
 * DocStatus, so a reversed invoice keeps blocking its number forever.
 *
 * Renames to '<documentno>-VOID-<c_invoice_id>' (deterministic + traceable,
 * truncated to the 30-char column). Guards: only docstatus VO/RE, idempotent
 * (already-suffixed rows are skipped). search_path is set explicitly because
 * the ES-sync trigger (log_invoice_changes → es_sync_queue) resolves its
 * target table unqualified.
 *
 * Fail-soft: PG not configured/reachable (dev without tunnel) → { available:
 * false, freed: 0 }; callers degrade to a clear user message.
 */
import { Pool } from 'pg'

export const freeVoidedInvoiceNumbers = async (opts: {
  invoiceId?: number
  bpartnerId?: number
  documentNo?: string
}): Promise<{ available: boolean; freed: number }> => {
  const config: any = useRuntimeConfig()
  if (!config.pgHost || !config.pgDatabase || !config.pgUser || !config.pgPassword) {
    return { available: false, freed: 0 }
  }
  if (!opts.invoiceId && !(opts.bpartnerId && opts.documentNo)) return { available: true, freed: 0 }

  const pool = new Pool({
    host: config.pgHost,
    port: parseInt(config.pgPort || '5432'),
    database: config.pgDatabase,
    user: config.pgUser,
    password: config.pgPassword
  })
  let client: any = null
  try {
    client = await pool.connect()
    await client.query(`SET search_path TO adempiere, public`)
    const where = opts.invoiceId
      ? { sql: 'c_invoice_id = $1', params: [opts.invoiceId] }
      : { sql: `c_bpartner_id = $1 AND documentno = $2 AND issotrx = 'N'`, params: [opts.bpartnerId, opts.documentNo] }
    const res = await client.query(
      `UPDATE adempiere.c_invoice
          SET documentno = left(documentno, 30 - length('-VOID-' || c_invoice_id::text)) || '-VOID-' || c_invoice_id::text
        WHERE ${where.sql}
          AND docstatus IN ('VO','RE')
          AND documentno NOT LIKE '%-VOID-%'`,
      where.params
    )
    return { available: true, freed: res.rowCount || 0 }
  } catch (e: any) {
    console.warn('[Inbox] freeVoidedInvoiceNumbers failed:', e?.message || e)
    return { available: false, freed: 0 }
  } finally {
    try { client?.release() } catch {}
    await pool.end().catch(() => {})
  }
}