'use strict';

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

const router = express.Router();

// Overall memory (MEMORY_TARGET) sizing curve
const MEMORY_TARGET_SQL = `
SELECT memory_size, ROUND(memory_size_factor, 4) AS memory_size_factor,
       ROUND(estd_db_time, 2) AS estd_db_time,
       ROUND(estd_db_time_factor, 4) AS estd_db_time_factor,
       version
FROM V$MEMORY_TARGET_ADVICE
ORDER BY memory_size`;

// SGA sizing curve (when MEMORY_TARGET=0 and SGA_TARGET is set)
const SGA_TARGET_SQL = `
SELECT sga_size, ROUND(sga_size_factor, 4) AS sga_size_factor,
       ROUND(estd_db_time, 2) AS estd_db_time,
       ROUND(estd_db_time_factor, 4) AS estd_db_time_factor,
       estd_physical_reads
FROM V$SGA_TARGET_ADVICE
ORDER BY sga_size`;

// PGA sizing curve
const PGA_TARGET_SQL = `
SELECT pga_target_for_estimate,
       ROUND(pga_target_factor, 4) AS pga_target_factor,
       estd_pga_cache_hit_percentage,
       estd_overalloc_count,
       advice_status
FROM V$PGA_TARGET_ADVICE
ORDER BY pga_target_for_estimate`;

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 mapMemory(rows) {
  return rows.map(r => ({
    memory_size:         r.MEMORY_SIZE         != null ? Number(r.MEMORY_SIZE)           : null,
    memory_size_factor:  r.MEMORY_SIZE_FACTOR  != null ? parseFloat(r.MEMORY_SIZE_FACTOR) : null,
    estd_db_time:        r.ESTD_DB_TIME        != null ? parseFloat(r.ESTD_DB_TIME)       : null,
    estd_db_time_factor: r.ESTD_DB_TIME_FACTOR != null ? parseFloat(r.ESTD_DB_TIME_FACTOR): null,
    version:             r.VERSION || null,
  }));
}

function mapSga(rows) {
  return rows.map(r => ({
    sga_size:            r.SGA_SIZE            != null ? Number(r.SGA_SIZE)              : null,
    sga_size_factor:     r.SGA_SIZE_FACTOR     != null ? parseFloat(r.SGA_SIZE_FACTOR)   : null,
    estd_db_time:        r.ESTD_DB_TIME        != null ? parseFloat(r.ESTD_DB_TIME)      : null,
    estd_db_time_factor: r.ESTD_DB_TIME_FACTOR != null ? parseFloat(r.ESTD_DB_TIME_FACTOR): null,
    estd_physical_reads: r.ESTD_PHYSICAL_READS != null ? Number(r.ESTD_PHYSICAL_READS)   : null,
  }));
}

function mapPga(rows) {
  return rows.map(r => ({
    pga_target_for_estimate:       r.PGA_TARGET_FOR_ESTIMATE          != null ? Number(r.PGA_TARGET_FOR_ESTIMATE)            : null,
    pga_target_factor:             r.PGA_TARGET_FACTOR                != null ? parseFloat(r.PGA_TARGET_FACTOR)              : null,
    estd_pga_cache_hit_percentage: r.ESTD_PGA_CACHE_HIT_PERCENTAGE    != null ? parseFloat(r.ESTD_PGA_CACHE_HIT_PERCENTAGE)  : null,
    estd_overalloc_count:          r.ESTD_OVERALLOC_COUNT             != null ? Number(r.ESTD_OVERALLOC_COUNT)               : null,
    advice_status:                 r.ADVICE_STATUS || null,
  }));
}

/**
 * GET /api/connections/:connId/memory-advisor
 *
 * Returns three memory sizing curves from Oracle's built-in advisors:
 *   memoryAdvice  — V$MEMORY_TARGET_ADVICE (overall AMM sizing)
 *   sgaAdvice     — V$SGA_TARGET_ADVICE    (SGA sizing when ASMM is used)
 *   pgaAdvice     — V$PGA_TARGET_ADVICE    (PGA sizing)
 *
 * Each row represents a candidate target size and Oracle's estimated DB time
 * impact at that size, letting the DBA see where diminishing returns kick in.
 */
router.get('/:connId/memory-advisor', requireAuth, async (req, res) => {
  // Per-query failures are non-fatal, but they must not vanish: a swallowed
  // ORA-00942 used to render as "no findings", i.e. a clean bill of health.
  const errs = makeCollector('memory-advisor');
  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 memoryAdvice = [];
    let sgaAdvice    = [];
    let pgaAdvice    = [];

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

      // Run three queries; individual query failures are non-fatal (view may not apply)
      memoryAdvice = mapMemory(await errs.guard('Memory target advice (V$MEMORY_TARGET_ADVICE)', () => queryViaAgent(connId, sqlCreds, MEMORY_TARGET_SQL)));
      sgaAdvice    = mapSga(  await errs.guard('SGA target advice (V$SGA_TARGET_ADVICE)', () => queryViaAgent(connId, sqlCreds, SGA_TARGET_SQL   )));
      pgaAdvice    = mapPga(  await errs.guard('PGA target advice (V$PGA_TARGET_ADVICE)', () => queryViaAgent(connId, sqlCreds, PGA_TARGET_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 run = async (sql) => { const r = await connection.execute(sql, [], { outFormat: fmt }); return r.rows || []; };
        memoryAdvice = mapMemory(await errs.guard('Memory target advice (V$MEMORY_TARGET_ADVICE)', () => run(MEMORY_TARGET_SQL)));
        sgaAdvice    = mapSga(   await errs.guard('SGA target advice (V$SGA_TARGET_ADVICE)', () => run(SGA_TARGET_SQL   )));
        pgaAdvice    = mapPga(   await errs.guard('PGA target advice (V$PGA_TARGET_ADVICE)', () => run(PGA_TARGET_SQL   )));
      } finally {
        if (connection) await connection.close().catch(() => {});
      }
    }

    return res.json({ memoryAdvice, sgaAdvice, pgaAdvice, licensed: true, check_errors: errs.errors });
  } catch (err) {
    console.error('[memory-advisor]', err.message);
    res.status(500).json({ error: err.message || 'Failed to fetch Memory Advisor data' });
  }
});

module.exports = router;
