'use strict';

const express = require('express');
const { requireAuth } = require('../middleware/auth');
const { getConnectionForAddm } = require('../db/addm-runs');
const { decrypt } = require('../crypto-utils');
const channel = require('../services/agent-channel');

const router = express.Router();

// Top 10 wait events last 7 days excluding idle waits
const WAIT_EVENTS_SQL = `
SELECT e.event_name, e.wait_class,
       ROUND(SUM(NVL(e.total_waits_fg, 0)), 0) AS total_waits,
       ROUND(SUM(NVL(e.time_waited_micro_fg, 0)) / 1000000, 2) AS time_waited_s,
       CASE WHEN SUM(NVL(e.total_waits_fg,0)) > 0
            THEN ROUND(SUM(NVL(e.time_waited_micro_fg,0)) / NULLIF(SUM(NVL(e.total_waits_fg,0)),0) / 1000, 3)
            ELSE 0 END AS avg_wait_ms
FROM DBA_HIST_SYSTEM_EVENT e
JOIN DBA_HIST_SNAPSHOT sh ON sh.snap_id = e.snap_id AND sh.dbid = e.dbid
WHERE sh.begin_interval_time > SYSDATE - 7
  AND e.wait_class != 'Idle'
GROUP BY e.event_name, e.wait_class
ORDER BY SUM(NVL(e.time_waited_micro_fg, 0)) DESC NULLS LAST
FETCH FIRST 10 ROWS ONLY`;

// Wait class rollup for last 7 days
const WAIT_CLASS_SQL = `
SELECT e.wait_class,
       ROUND(SUM(NVL(e.time_waited_micro_fg, 0)) / 1000000, 2) AS time_waited_s,
       ROUND(SUM(NVL(e.total_waits_fg, 0)), 0) AS total_waits
FROM DBA_HIST_SYSTEM_EVENT e
JOIN DBA_HIST_SNAPSHOT sh ON sh.snap_id = e.snap_id AND sh.dbid = e.dbid
WHERE sh.begin_interval_time > SYSDATE - 7
  AND e.wait_class != 'Idle'
GROUP BY e.wait_class
ORDER BY SUM(NVL(e.time_waited_micro_fg, 0)) DESC NULLS LAST`;

// Key workload statistics: hard parses, total parses, sorts to disk, sorts in memory
const SYSSTAT_SQL = `
SELECT st.stat_name,
       ROUND(SUM(NVL(st.value, 0)), 0) AS total_value
FROM DBA_HIST_SYSSTAT st
JOIN DBA_HIST_SNAPSHOT sh ON sh.snap_id = st.snap_id AND sh.dbid = st.dbid
WHERE sh.begin_interval_time > SYSDATE - 7
  AND st.stat_name IN (
    'parse count (total)',
    'parse count (hard)',
    'sorts (disk)',
    'sorts (memory)',
    'sorts (rows)',
    'execute count',
    'user calls',
    'db block gets',
    'physical reads'
  )
GROUP BY st.stat_name
ORDER BY st.stat_name`;

// Snapshot availability — how many AWR snapshots exist and time range
const SNAP_SQL = `
SELECT COUNT(*) AS snap_count,
       TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') AS oldest_snap,
       TO_CHAR(MAX(end_interval_time),   'YYYY-MM-DD HH24:MI') AS newest_snap
FROM DBA_HIST_SNAPSHOT
WHERE end_interval_time > SYSDATE - 7`;

function parseRows(body) {
  const cols    = body.columns || [];
  const rawRows = body.rows    || [];
  return rawRows.map(row => {
    const obj = {};
    cols.forEach((c, i) => { obj[c] = row[i]; });
    return obj;
  });
}

async function queryViaAgent(connId, sqlCreds, sql) {
  const agentOnline = await Promise.race([
    channel.isAgentConnected(connId),
    new Promise(r => setTimeout(() => r(false), 5000)),
  ]);
  if (!agentOnline) throw new Error('Agent is not connected. Wait for agent check-in and retry.');

  const proxyResp = await channel.sendToAgent(connId, {
    method: 'POST',
    path:   '/api/run_sql',
    body:   { sql, ...sqlCreds },
  }, 60000);

  const body = (proxyResp && proxyResp.body) ? proxyResp.body : {};
  if (!body.success) throw new Error(body.error || 'Agent SQL query failed');
  return parseRows(body);
}

function mapEvents(rows) {
  return rows.map(r => ({
    event_name:   r.EVENT_NAME   || '',
    wait_class:   r.WAIT_CLASS   || '',
    total_waits:  r.TOTAL_WAITS  != null ? Number(r.TOTAL_WAITS)     : null,
    time_waited_s: r.TIME_WAITED_S != null ? parseFloat(r.TIME_WAITED_S) : null,
    avg_wait_ms:  r.AVG_WAIT_MS  != null ? parseFloat(r.AVG_WAIT_MS) : null,
  }));
}

function mapClasses(rows) {
  return rows.map(r => ({
    wait_class:   r.WAIT_CLASS   || '',
    time_waited_s: r.TIME_WAITED_S != null ? parseFloat(r.TIME_WAITED_S) : null,
    total_waits:  r.TOTAL_WAITS  != null ? Number(r.TOTAL_WAITS)     : null,
  }));
}

function mapStats(rows) {
  const out = {};
  rows.forEach(r => {
    const key = (r.STAT_NAME || '').toLowerCase().replace(/[^a-z0-9]+/g, '_');
    out[key] = r.TOTAL_VALUE != null ? Number(r.TOTAL_VALUE) : null;
  });
  return out;
}

function mapSnap(rows) {
  const r = rows[0] || {};
  return {
    snap_count:  r.SNAP_COUNT  != null ? Number(r.SNAP_COUNT) : 0,
    oldest_snap: r.OLDEST_SNAP || null,
    newest_snap: r.NEWEST_SNAP || null,
  };
}

// Derive flags from stats
function deriveFlags(stats) {
  const flags = [];
  const totalParses = stats['parse_count__total_'] || stats['parse_count_total'] || 0;
  const hardParses  = stats['parse_count__hard_' ] || stats['parse_count_hard' ] || 0;
  const sortsDisk   = stats['sorts__disk_'        ] || stats['sorts_disk'       ] || 0;
  const sortsMem    = stats['sorts__memory_'       ] || stats['sorts_memory'     ] || 0;

  const hardRatio = totalParses > 0 ? (hardParses / totalParses * 100) : 0;
  const sortRatio = (sortsDisk + sortsMem) > 0 ? (sortsDisk / (sortsDisk + sortsMem) * 100) : 0;

  if (hardRatio > 10) {
    flags.push({ type: 'warning', metric: 'Hard Parse Ratio', value: hardRatio.toFixed(1) + '%',
      threshold: '>10%', detail: 'High hard-parse ratio indicates shared-pool pressure or cursor sharing issues. Check CURSOR_SHARING and application use of bind variables.' });
  }
  if (sortRatio > 1) {
    flags.push({ type: 'warning', metric: 'Disk Sort Ratio', value: sortRatio.toFixed(1) + '%',
      threshold: '>1%', detail: 'Sorts spilling to disk — increase PGA_AGGREGATE_TARGET or review queries doing large in-memory sorts.' });
  }
  return { flags, hardRatio: Math.round(hardRatio * 10) / 10, sortRatio: Math.round(sortRatio * 10) / 10 };
}

/**
 * GET /api/connections/:connId/awr-advisor
 *
 * Returns:
 *   waitEvents  — top 10 non-idle wait events last 7 days by total wait time
 *   waitClasses — wait class rollup for pie/donut visualization
 *   stats       — key workload statistics (parses, sorts, etc.)
 *   flags       — hard-parse and disk-sort threshold alerts
 *   snapInfo    — AWR snapshot count and time range
 *
 * Requires: Diagnostics Pack license (DBA_HIST_* views).
 */
router.get('/:connId/awr-advisor', requireAuth, async (req, res) => {
  try {
    const connId = parseInt(req.params.connId, 10);
    const conn   = await getConnectionForAddm(connId, req.user.id);
    if (!conn) return res.status(404).json({ error: 'Connection not found' });

    let eventRows = [];
    let classRows = [];
    let statRows  = [];
    let snapRows  = [];

    if (conn.connection_type === 'proxy') {
      const sqlCreds = {
        service_name: conn.service_name,
        username:     conn.username,
        password:     decrypt(conn.encrypted_password),
        host:         conn.host,
        port:         conn.port || 1521,
      };
      snapRows  = await queryViaAgent(connId, sqlCreds, SNAP_SQL       );
      eventRows = await queryViaAgent(connId, sqlCreds, WAIT_EVENTS_SQL);
      classRows = await queryViaAgent(connId, sqlCreds, WAIT_CLASS_SQL );
      statRows  = await queryViaAgent(connId, sqlCreds, SYSSTAT_SQL    );
    } else {
      let connection;
      try {
        const oracledb = require('oracledb');
        const cs       = `${conn.host}:${conn.port || 1521}/${conn.service_name}`;
        connection     = await oracledb.getConnection({
          user: conn.username, password: decrypt(conn.encrypted_password),
          connectString: cs, connectTimeout: 30,
        });
        const fmt = oracledb.OUT_FORMAT_OBJECT;
        const r0  = await connection.execute(SNAP_SQL,        [], { outFormat: fmt });
        snapRows  = r0.rows || [];
        const r1  = await connection.execute(WAIT_EVENTS_SQL, [], { outFormat: fmt });
        eventRows = r1.rows || [];
        const r2  = await connection.execute(WAIT_CLASS_SQL,  [], { outFormat: fmt });
        classRows = r2.rows || [];
        const r3  = await connection.execute(SYSSTAT_SQL,     [], { outFormat: fmt });
        statRows  = r3.rows || [];
      } finally {
        if (connection) await connection.close().catch(() => {});
      }
    }

    const waitEvents  = mapEvents(eventRows);
    const waitClasses = mapClasses(classRows);
    const stats       = mapStats(statRows);
    const snapInfo    = mapSnap(snapRows);
    const { flags, hardRatio, sortRatio } = deriveFlags(stats);

    return res.json({ waitEvents, waitClasses, stats, flags, hardRatio, sortRatio, snapInfo, licensed: true });
  } catch (err) {
    console.error('[awr-advisor]', err.message);
    if (err.message && (err.message.includes('ORA-00942') || err.message.includes('insufficient privileges'))) {
      return res.json({ waitEvents: [], waitClasses: [], stats: {}, flags: [], snapInfo: {}, licensed: false,
        error: 'Oracle Diagnostics Pack license required for DBA_HIST_* views (AWR). Ensure STATISTICS_LEVEL=TYPICAL and the pack is licensed.' });
    }
    res.status(500).json({ error: err.message || 'Failed to fetch AWR advisor data' });
  }
});

module.exports = router;
