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

// Per-tablespace usage % + autoextend status combined
const USAGE_SQL = `
SELECT m.tablespace_name,
       ROUND(m.used_percent, 1) AS used_pct,
       ROUND(m.used_space * 8192 / 1073741824, 2) AS used_gb,
       ROUND(m.tablespace_size * 8192 / 1073741824, 2) AS total_gb,
       NVL((SELECT CASE WHEN MIN(autoextensible)='YES' THEN 'YES' ELSE 'NO' END
            FROM DBA_DATA_FILES df WHERE df.tablespace_name = m.tablespace_name), 'N/A') AS autoextensible,
       NVL((SELECT ROUND(SUM(CASE WHEN autoextensible='YES' THEN maxbytes ELSE bytes END)/1073741824,2)
            FROM DBA_DATA_FILES df WHERE df.tablespace_name = m.tablespace_name), 0) AS max_gb
FROM DBA_TABLESPACE_USAGE_METRICS m
ORDER BY m.used_percent DESC NULLS LAST`;

// Free space fragmentation: count of free extents per tablespace
const FRAG_SQL = `
SELECT tablespace_name,
       COUNT(*) AS free_extent_count,
       ROUND(SUM(bytes)/1048576, 1) AS total_free_mb,
       ROUND(MIN(bytes)/1048576, 2) AS min_extent_mb,
       ROUND(MAX(bytes)/1048576, 2) AS max_extent_mb
FROM DBA_FREE_SPACE
GROUP BY tablespace_name
ORDER BY COUNT(*) DESC NULLS LAST
FETCH FIRST 30 ROWS ONLY`;

// AWR-based growth trend: space used over last 30 days (requires Diagnostics Pack)
const GROWTH_SQL = `
SELECT s.tablespace_name,
       TO_CHAR(TRUNC(sh.begin_interval_time, 'DD'), 'YYYY-MM-DD') AS day,
       ROUND(MAX(s.tablespace_usedsize) * 8192 / 1073741824, 2) AS used_gb
FROM DBA_HIST_TBSPC_SPACE_USAGE s
JOIN DBA_HIST_SNAPSHOT sh ON sh.snap_id = s.snap_id AND sh.dbid = s.dbid
WHERE sh.begin_interval_time > SYSDATE - 30
GROUP BY s.tablespace_name, TRUNC(sh.begin_interval_time, 'DD')
ORDER BY s.tablespace_name, TRUNC(sh.begin_interval_time, 'DD')`;

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 mapUsage(rows) {
  return rows.map(r => ({
    tablespace_name: r.TABLESPACE_NAME || '',
    used_pct:        r.USED_PCT        != null ? parseFloat(r.USED_PCT)  : null,
    used_gb:         r.USED_GB         != null ? parseFloat(r.USED_GB)   : null,
    total_gb:        r.TOTAL_GB        != null ? parseFloat(r.TOTAL_GB)  : null,
    autoextensible:  r.AUTOEXTENSIBLE  || 'N/A',
    max_gb:          r.MAX_GB          != null ? parseFloat(r.MAX_GB)    : null,
    // flags populated below
    is_critical: false,
    is_warning:  false,
  }));
}

function mapFrag(rows) {
  return rows.map(r => ({
    tablespace_name:   r.TABLESPACE_NAME   || '',
    free_extent_count: r.FREE_EXTENT_COUNT != null ? Number(r.FREE_EXTENT_COUNT) : null,
    total_free_mb:     r.TOTAL_FREE_MB     != null ? parseFloat(r.TOTAL_FREE_MB) : null,
    min_extent_mb:     r.MIN_EXTENT_MB     != null ? parseFloat(r.MIN_EXTENT_MB) : null,
    max_extent_mb:     r.MAX_EXTENT_MB     != null ? parseFloat(r.MAX_EXTENT_MB) : null,
    is_fragmented:     false, // flagged below
  }));
}

function mapGrowth(rows) {
  return rows.map(r => ({
    tablespace_name: r.TABLESPACE_NAME || '',
    day:             r.DAY             || '',
    used_gb:         r.USED_GB         != null ? parseFloat(r.USED_GB) : null,
  }));
}

/**
 * GET /api/connections/:connId/space-advisor
 *
 * Returns:
 *   usage       — per-tablespace used %, autoextend status, critical/warning flags
 *   fragmentation — tablespaces with many small free extents (>50 = fragmented)
 *   growth      — AWR-based daily usage trend last 30 days (Diagnostics Pack required;
 *                 empty array if unavailable — non-fatal)
 *
 * Flags:
 *   critical: used_pct > 85 AND autoextensible = NO
 *   warning:  used_pct > 85
 *   fragmented: free_extent_count > 50
 */
router.get('/:connId/space-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 usageRows = [];
    let fragRows  = [];
    let growthRows = [];

    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,
      };
      usageRows  = await queryViaAgent(connId, sqlCreds, USAGE_SQL);
      fragRows   = await queryViaAgent(connId, sqlCreds, FRAG_SQL );
      growthRows = await queryViaAgent(connId, sqlCreds, GROWTH_SQL).catch(() => []); // needs Diagnostics Pack
    } 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(USAGE_SQL,  [], { outFormat: fmt });
        usageRows = r1.rows || [];
        const r2  = await connection.execute(FRAG_SQL,   [], { outFormat: fmt });
        fragRows  = r2.rows || [];
        const r3  = await connection.execute(GROWTH_SQL, [], { outFormat: fmt }).catch(() => ({ rows: [] }));
        growthRows = r3.rows || [];
      } finally {
        if (connection) await connection.close().catch(() => {});
      }
    }

    const usage = mapUsage(usageRows);
    usage.forEach(t => {
      t.is_warning  = t.used_pct != null && t.used_pct > 85;
      t.is_critical = t.is_warning && t.autoextensible === 'NO';
    });

    const fragmentation = mapFrag(fragRows);
    fragmentation.forEach(f => {
      f.is_fragmented = (f.free_extent_count || 0) > 50;
    });

    const growth = mapGrowth(growthRows);

    const criticalCount = usage.filter(t => t.is_critical).length;
    const warningCount  = usage.filter(t => t.is_warning  && !t.is_critical).length;

    return res.json({ usage, fragmentation, growth, criticalCount, warningCount });
  } catch (err) {
    console.error('[space-advisor]', err.message);
    if (err.message && (err.message.includes('ORA-00942') || err.message.includes('insufficient privileges'))) {
      return res.json({ usage: [], fragmentation: [], growth: [], criticalCount: 0, warningCount: 0,
        error: 'SELECT_CATALOG_ROLE required for DBA_TABLESPACE_USAGE_METRICS, DBA_DATA_FILES, DBA_FREE_SPACE.' });
    }
    res.status(500).json({ error: err.message || 'Failed to fetch space advisor data' });
  }
});

module.exports = router;
