'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();

// Tables with stale or missing optimizer statistics
const STALE_TABLES_SQL = `
SELECT s.owner, s.table_name, s.stale_stats,
       NVL(s.num_rows, 0) AS num_rows,
       NVL(s.blocks, 0) AS blocks,
       s.stattype_locked,
       TO_CHAR(s.last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_analyzed_str
FROM DBA_TAB_STATISTICS s
WHERE s.owner NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','XDB','ORDDATA',
                      'APEX_030200','APEX_040200','APEX_050000',
                      'APEX_180100','APEX_190100','APEX_200200','APEX_210100')
  AND s.object_type = 'TABLE'
  AND (s.stale_stats = 'YES' OR s.last_analyzed IS NULL)
ORDER BY NVL(s.num_rows, 0) DESC NULLS LAST
FETCH FIRST 20 ROWS ONLY`;

// Indexes with stale stats on the tables identified above
const STALE_IDX_SQL = `
SELECT i.owner, i.index_name, i.table_name, i.stale_stats,
       NVL(i.num_rows, 0) AS num_rows,
       TO_CHAR(i.last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_analyzed_str
FROM DBA_IND_STATISTICS i
WHERE i.owner NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','XDB','ORDDATA')
  AND i.object_type = 'INDEX'
  AND (i.stale_stats = 'YES' OR i.last_analyzed IS NULL)
ORDER BY NVL(i.num_rows, 0) DESC NULLS LAST
FETCH FIRST 20 ROWS ONLY`;

// Statistics Advisor findings if available (Oracle 12.2+)
const SA_FINDINGS_SQL = `
SELECT f.task_id, f.finding_id, f.type, SUBSTR(f.message, 1, 500) AS message
FROM DBA_ADVISOR_FINDINGS f
JOIN DBA_ADVISOR_TASKS t ON t.task_id = f.task_id
WHERE t.advisor_name = 'Statistics Advisor'
  AND t.created > SYSDATE - 7
ORDER BY f.impact DESC NULLS LAST
FETCH FIRST 20 ROWS ONLY`;

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);
}

// Build DBMS_STATS.GATHER_TABLE_STATS command for a stale table
function gatherCmd(owner, tableName) {
  return `EXEC DBMS_STATS.GATHER_TABLE_STATS('${owner}','${tableName}',CASCADE=>TRUE,DEGREE=>4);`;
}

function mapStaleTables(rows) {
  return rows.map(r => ({
    owner:          r.OWNER          || '',
    table_name:     r.TABLE_NAME     || '',
    stale_stats:    r.STALE_STATS    || null,
    num_rows:       r.NUM_ROWS       != null ? Number(r.NUM_ROWS) : null,
    blocks:         r.BLOCKS         != null ? Number(r.BLOCKS)   : null,
    stattype_locked: r.STATTYPE_LOCKED || null,
    last_analyzed:  r.LAST_ANALYZED_STR || null,
    gather_cmd:     gatherCmd(r.OWNER || '', r.TABLE_NAME || ''),
  }));
}

function mapStaleIdx(rows) {
  return rows.map(r => ({
    owner:         r.OWNER           || '',
    index_name:    r.INDEX_NAME      || '',
    table_name:    r.TABLE_NAME      || '',
    stale_stats:   r.STALE_STATS     || null,
    num_rows:      r.NUM_ROWS        != null ? Number(r.NUM_ROWS) : null,
    last_analyzed: r.LAST_ANALYZED_STR || null,
  }));
}

function mapSaFindings(rows) {
  return rows.map(r => ({
    task_id:    Number(r.TASK_ID    || 0),
    finding_id: Number(r.FINDING_ID || 0),
    type:       r.TYPE    || '',
    message:    r.MESSAGE || '',
  }));
}

/**
 * GET /api/connections/:connId/stats-advisor
 *
 * Returns:
 *   staleTables   — tables with stale/missing stats, sorted by num_rows DESC, with
 *                   ready-to-run DBMS_STATS.GATHER_TABLE_STATS command
 *   staleIndexes  — indexes with stale/missing stats
 *   saFindings    — Oracle Statistics Advisor findings from last 7 days (12.2+ only)
 *
 * Requires SELECT_CATALOG_ROLE for DBA_TAB_STATISTICS, DBA_IND_STATISTICS.
 * DBA_ADVISOR_FINDINGS query is non-fatal if advisor not available.
 */
router.get('/:connId/stats-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 staleTableRows = [];
    let staleIdxRows   = [];
    let saRows         = [];

    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,
      };
      staleTableRows = await queryViaAgent(connId, sqlCreds, STALE_TABLES_SQL);
      staleIdxRows   = await queryViaAgent(connId, sqlCreds, STALE_IDX_SQL  );
      saRows         = await queryViaAgent(connId, sqlCreds, SA_FINDINGS_SQL).catch(() => []); // 12.2+ only
    } 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 r1  = await connection.execute(STALE_TABLES_SQL, [], { outFormat: fmt });
        staleTableRows = r1.rows || [];
        const r2  = await connection.execute(STALE_IDX_SQL,    [], { outFormat: fmt });
        staleIdxRows   = r2.rows || [];
        const r3  = await connection.execute(SA_FINDINGS_SQL,  [], { outFormat: fmt }).catch(() => ({ rows: [] }));
        saRows         = r3.rows || [];
      } finally {
        if (connection) await connection.close().catch(() => {});
      }
    }

    const staleTables  = mapStaleTables(staleTableRows);
    const staleIndexes = mapStaleIdx(staleIdxRows);
    const saFindings   = mapSaFindings(saRows);

    return res.json({ staleTables, staleIndexes, saFindings });
  } catch (err) {
    console.error('[stats-advisor]', err.message);
    if (err.message && (err.message.includes('ORA-00942') || err.message.includes('insufficient privileges'))) {
      return res.json({ staleTables: [], staleIndexes: [], saFindings: [],
        error: 'SELECT_CATALOG_ROLE required for DBA_TAB_STATISTICS and DBA_IND_STATISTICS.' });
    }
    res.status(500).json({ error: err.message || 'Failed to fetch statistics advisor data' });
  }
});

module.exports = router;
