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

// Segment Advisor findings from auto-run tasks (last 7 days)
const SEG_FINDINGS_SQL = `
SELECT f.task_id, f.finding_id, f.type,
       f.attr1, f.attr2, f.attr3, f.attr4,
       SUBSTR(f.message, 1, 500) AS message,
       ROUND(f.impact, 2) AS impact
FROM DBA_ADVISOR_FINDINGS f
JOIN DBA_ADVISOR_TASKS t ON t.task_id = f.task_id
WHERE t.advisor_name = 'Segment Advisor'
  AND t.created > SYSDATE - 7
ORDER BY f.impact DESC NULLS LAST
FETCH FIRST 30 ROWS ONLY`;

// Segments > 1 GB — candidates for shrink/reorg regardless of advisor state
const LARGE_SEGS_SQL = `
SELECT owner, segment_name, segment_type,
       ROUND(bytes / 1073741824, 2) AS size_gb,
       TO_CHAR(last_analyzed, 'YYYY-MM-DD') AS last_analyzed
FROM DBA_SEGMENTS
WHERE bytes > 1073741824
ORDER BY bytes 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);
}

function mapFindings(rows) {
  return rows.map(r => ({
    task_id:    Number(r.TASK_ID    || 0),
    finding_id: Number(r.FINDING_ID || 0),
    type:       r.TYPE    || '',
    attr1:      r.ATTR1   || '',
    attr2:      r.ATTR2   || '',
    attr3:      r.ATTR3   || '',
    attr4:      r.ATTR4   || '',
    message:    r.MESSAGE || '',
    impact:     r.IMPACT  != null ? parseFloat(r.IMPACT) : null,
  }));
}

function mapLargeSegs(rows) {
  return rows.map(r => ({
    owner:         r.OWNER         || '',
    segment_name:  r.SEGMENT_NAME  || '',
    segment_type:  r.SEGMENT_TYPE  || '',
    size_gb:       r.SIZE_GB       != null ? parseFloat(r.SIZE_GB) : null,
    last_analyzed: r.LAST_ANALYZED || null,
  }));
}

/**
 * GET /api/connections/:connId/segment-advisor
 *
 * Returns Segment Advisor findings (shrink/reorg recommendations) plus a list
 * of segments > 1 GB that are candidates for space reclamation.
 * Requires Oracle Diagnostics Pack license.
 */
router.get('/:connId/segment-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 findings      = [];
    let largeSegments = [];

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

      const findingRows = await queryViaAgent(connId, sqlCreds, SEG_FINDINGS_SQL);
      findings = mapFindings(findingRows);

      const segRows = await queryViaAgent(connId, sqlCreds, LARGE_SEGS_SQL);
      largeSegments = mapLargeSegs(segRows);
    } 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(SEG_FINDINGS_SQL, [], { outFormat: fmt });
        findings  = mapFindings(r1.rows || []);
        const r2  = await connection.execute(LARGE_SEGS_SQL, [], { outFormat: fmt });
        largeSegments = mapLargeSegs(r2.rows || []);
      } finally {
        if (connection) await connection.close().catch(() => {});
      }
    }

    return res.json({ findings, largeSegments, licensed: true });
  } catch (err) {
    console.error('[segment-advisor]', err.message);
    if (err.message && (err.message.includes('ORA-00942') || err.message.includes('insufficient privileges'))) {
      return res.json({ findings: [], largeSegments: [], licensed: false, error: 'Oracle Diagnostics Pack license required for Segment Advisor (DBA_ADVISOR_FINDINGS).' });
    }
    res.status(500).json({ error: err.message || 'Failed to fetch Segment Advisor data' });
  }
});

module.exports = router;
