#!/usr/bin/env node
// Dump the LIVE definition of adempiere.v_orders_summary (definition, owner, grants, column list)
// into reference/sql/backups/v_orders_summary.<timestamp>.sql as a ready-to-run restore file.
//
//   node reference/sql/backup-view.cjs            # writes the backup, prints the path
//
// Restore with:  node reference/sql/restore-view.cjs reference/sql/backups/<file>
const fs = require('fs')
const path = require('path')
const { SCHEMA, VIEW, connect, timestamp } = require('./_db.cjs')

async function readLiveView (client) {
  const meta = await client.query(
    `SELECT c.relkind, pg_get_userbyid(c.relowner) AS owner
       FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE n.nspname = $1 AND c.relname = $2`, [SCHEMA, VIEW])
  if (meta.rowCount !== 1 || meta.rows[0].relkind !== 'v') throw new Error(`${SCHEMA}.${VIEW} is not a view`)
  const def = await client.query(`SELECT pg_get_viewdef($1::regclass, true) AS d`, [`${SCHEMA}.${VIEW}`])
  const definition = String(def.rows[0].d || '').trim()
  if (definition.length < 200) throw new Error('view definition came back empty/too short')
  const cols = await client.query(
    `SELECT column_name, data_type, character_maximum_length, numeric_precision, numeric_scale
       FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2 ORDER BY ordinal_position`,
    [SCHEMA, VIEW])
  const grants = await client.query(
    `SELECT grantee, privilege_type FROM information_schema.role_table_grants
      WHERE table_schema = $1 AND table_name = $2 ORDER BY grantee, privilege_type`, [SCHEMA, VIEW])
  return {
    owner: meta.rows[0].owner,
    definition,
    columns: cols.rows.map(r => ({ name: r.column_name, type: r.data_type })),
    grants: grants.rows,
  }
}

function renderBackup (live, env) {
  const grantLines = []
  const byGrantee = {}
  for (const g of live.grants) (byGrantee[g.grantee] ||= []).push(g.privilege_type)
  for (const [grantee, privs] of Object.entries(byGrantee)) {
    grantLines.push(`GRANT ${privs.join(', ')} ON ${SCHEMA}.${VIEW} TO "${grantee}";`)
  }
  const def = live.definition.replace(/;\s*$/, '')
  return [
    `-- Backup of ${SCHEMA}.${VIEW}`,
    `-- taken ${new Date().toISOString()} from ${env.PG_HOST}:${env.PG_PORT || 5432}/${env.PG_DATABASE} (owner: ${live.owner})`,
    `-- columns (${live.columns.length}): ${live.columns.map(c => c.name).join(', ')}`,
    `-- Restore:  node reference/sql/restore-view.cjs <this file>`,
    ``,
    `CREATE OR REPLACE VIEW ${SCHEMA}.${VIEW} AS`,
    def + ';',
    ``,
    `ALTER VIEW ${SCHEMA}.${VIEW} OWNER TO "${live.owner}";`,
    ...grantLines,
    ``,
  ].join('\n')
}

async function backupView (client, env) {
  const live = await readLiveView(client)
  const dir = path.join(__dirname, 'backups')
  fs.mkdirSync(dir, { recursive: true })
  const file = path.join(dir, `${VIEW}.${timestamp()}.sql`)
  fs.writeFileSync(file, renderBackup(live, env))
  const written = fs.statSync(file).size
  if (written < 500) throw new Error(`backup file suspiciously small (${written} bytes): ${file}`)
  return { file, live }
}

module.exports = { readLiveView, backupView, renderBackup }

if (require.main === module) {
  (async () => {
    const { client, env } = await connect()
    try {
      const { file, live } = await backupView(client, env)
      console.log(`backup written: ${path.relative(process.cwd(), file)} (${fs.statSync(file).size} bytes, ${live.columns.length} columns, owner ${live.owner})`)
    } finally {
      await client.end()
    }
  })().catch(e => { console.error('backup failed:', e.message); process.exit(1) })
}