#!/usr/bin/env node
// Apply reference/sql/v_orders_summary.sql to the database — safely:
//   1. live backup to reference/sql/backups/ (abort if it cannot be written)
//   2. capture 20 newest + 20 oldest + 20 random shipped sales orders from the CURRENT view
//   3. BEGIN → CREATE OR REPLACE VIEW → column list must be unchanged → EXPLAIN ANALYZE of a
//      30-day page must run < 500 ms → the 60 captured rows must be identical → COMMIT
//      (any failure, or --dry-run → ROLLBACK)
//
//   node reference/sql/apply-view.cjs [--dry-run] [--max-ms=500]
const fs = require('fs')
const path = require('path')
const { SCHEMA, VIEW, connect } = require('./_db.cjs')
const { backupView, readLiveView } = require('./backup-view.cjs')

const dryRun = process.argv.includes('--dry-run')
const maxMs = Number((process.argv.find(a => a.startsWith('--max-ms=')) || '--max-ms=500').split('=')[1])

const stable = (o) => JSON.stringify(o, Object.keys(o).sort())

async function sampleIds (client) {
  const q = async (sql) => (await client.query(sql)).rows.map(r => r.c_order_id)
  const newest = await q(`SELECT c_order_id FROM c_order WHERE issotrx = 'Y' ORDER BY created DESC LIMIT 20`)
  const oldest = await q(`SELECT c_order_id FROM c_order WHERE issotrx = 'Y' ORDER BY created ASC LIMIT 20`)
  const shipped = await q(`SELECT DISTINCT c_order_id FROM m_inout WHERE c_order_id IS NOT NULL ORDER BY c_order_id DESC LIMIT 200`)
  const pick = shipped.filter((_, i) => i % 10 === 0).slice(0, 20)
  return [...new Set([...newest, ...oldest, ...pick])]
}

async function snapshot (client, ids) {
  const r = await client.query(`SELECT row_to_json(v) AS j FROM ${SCHEMA}.${VIEW} v WHERE c_order_id = ANY($1::numeric[]) ORDER BY c_order_id`, [ids])
  return r.rows.map(x => x.j)
}

;(async () => {
  const ddlFile = path.join(__dirname, `${VIEW}.sql`)
  const ddl = fs.readFileSync(ddlFile, 'utf8')
  if (!ddl.includes(`CREATE OR REPLACE VIEW ${SCHEMA}.${VIEW} AS`)) throw new Error('DDL file malformed')

  const { client, env } = await connect()
  try {
    // 1. backup
    const { file, live: before } = await backupView(client, env)
    console.log(`[1/4] backup: ${path.relative(process.cwd(), file)} (${before.columns.length} columns)`)

    // 2. reference data from the current view
    const ids = await sampleIds(client)
    const beforeRows = await snapshot(client, ids)
    console.log(`[2/4] captured ${beforeRows.length} reference rows from the current view`)

    // 3. apply inside a transaction
    await client.query('BEGIN')
    await client.query(ddl)
    const after = await readLiveView(client)
    const colsBefore = before.columns.map(c => `${c.name}:${c.type}`).join(',')
    const colsAfter = after.columns.map(c => `${c.name}:${c.type}`).join(',')
    if (colsBefore !== colsAfter) throw new Error(`column list changed!\n before: ${colsBefore}\n after:  ${colsAfter}`)

    const ex = await client.query(`EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM ${SCHEMA}.${VIEW}
      WHERE issotrx = 'Y' AND created >= now() - interval '30 days' ORDER BY created DESC LIMIT 50 OFFSET 50`)
    const execMs = ex.rows[0]['QUERY PLAN'][0]['Execution Time']
    const ex2 = await client.query(`EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM ${SCHEMA}.${VIEW} WHERE issotrx = 'Y' ORDER BY created DESC LIMIT 50`)
    const execAllMs = ex2.rows[0]['QUERY PLAN'][0]['Execution Time']
    console.log(`[3/4] new view: 30-day page ${execMs.toFixed(0)} ms, unfiltered first page ${execAllMs.toFixed(0)} ms (gate < ${maxMs} ms)`)
    if (execMs > maxMs || execAllMs > maxMs) throw new Error(`speed gate failed (${execMs.toFixed(0)} / ${execAllMs.toFixed(0)} ms)`)

    const afterRows = await snapshot(client, ids)
    if (afterRows.length !== beforeRows.length) throw new Error(`row count differs: ${beforeRows.length} vs ${afterRows.length}`)
    const diffs = []
    for (let i = 0; i < beforeRows.length; i++) {
      const a = beforeRows[i], b = afterRows[i]
      for (const k of Object.keys(a)) {
        if (stable({ v: a[k] }) !== stable({ v: b[k] })) diffs.push(`${a.c_order_id}.${k}: ${JSON.stringify(a[k])} -> ${JSON.stringify(b[k])}`)
      }
    }
    if (diffs.length) throw new Error(`output differs for ${diffs.length} field(s):\n  ` + diffs.slice(0, 20).join('\n  '))
    console.log(`[4/4] ${afterRows.length} reference rows identical`)

    if (dryRun) {
      await client.query('ROLLBACK')
      console.log('DRY RUN — rolled back, database unchanged')
    } else {
      await client.query('COMMIT')
      console.log(`COMMITTED — ${SCHEMA}.${VIEW} replaced. Rollback: node reference/sql/restore-view.cjs ${path.relative(process.cwd(), file)}`)
    }
  } catch (e) {
    await client.query('ROLLBACK').catch(() => {})
    throw e
  } finally {
    await client.end()
  }
})().catch(e => { console.error('apply failed (database unchanged):', e.message); process.exit(1) })