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

// Tables > 100 MB with compression disabled, joined to actual segment sizes
const UNCOMP_TABLES_SQL = `
SELECT s.owner, s.segment_name AS table_name,
       ROUND(s.bytes / 1048576, 1) AS size_mb,
       NVL(t.compression, 'DISABLED') AS compression,
       NVL(t.compress_for, 'NONE') AS compress_for,
       NVL(t.num_rows, 0) AS num_rows,
       NVL(t.avg_row_len, 0) AS avg_row_len,
       TO_CHAR(t.last_analyzed, 'YYYY-MM-DD') AS last_analyzed
FROM DBA_SEGMENTS s
JOIN DBA_TABLES t
  ON t.owner = s.owner AND t.table_name = s.segment_name
WHERE s.segment_type = 'TABLE'
  AND s.bytes > 104857600
  AND 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 (t.compression = 'DISABLED' OR t.compression IS NULL OR t.compression = 'NONE')
ORDER BY s.bytes DESC NULLS LAST
FETCH FIRST 30 ROWS ONLY`;

// Already-compressed tables (for context / comparison)
const COMP_TABLES_SQL = `
SELECT owner, table_name, compression, compress_for,
       ROUND(NVL(blocks, 0) * 8192 / 1048576, 1) AS est_size_mb,
       num_rows
FROM DBA_TABLES
WHERE compression != 'DISABLED' AND compression IS NOT NULL
  AND owner NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','XDB','ORDDATA')
ORDER BY NVL(blocks, 0) 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 mapUncomp(rows) {
  return rows.map(r => ({
    owner:        r.OWNER        || '',
    table_name:   r.TABLE_NAME   || '',
    size_mb:      r.SIZE_MB      != null ? parseFloat(r.SIZE_MB)   : null,
    compression:  r.COMPRESSION  || 'DISABLED',
    compress_for: r.COMPRESS_FOR || 'NONE',
    num_rows:     r.NUM_ROWS     != null ? Number(r.NUM_ROWS)      : null,
    avg_row_len:  r.AVG_ROW_LEN  != null ? Number(r.AVG_ROW_LEN)  : null,
    last_analyzed: r.LAST_ANALYZED || null,
  }));
}

function mapComp(rows) {
  return rows.map(r => ({
    owner:        r.OWNER        || '',
    table_name:   r.TABLE_NAME   || '',
    compression:  r.COMPRESSION  || '',
    compress_for: r.COMPRESS_FOR || '',
    est_size_mb:  r.EST_SIZE_MB  != null ? parseFloat(r.EST_SIZE_MB) : null,
    num_rows:     r.NUM_ROWS     != null ? Number(r.NUM_ROWS)         : null,
  }));
}

/**
 * GET /api/connections/:connId/compression-advisor
 *
 * Returns tables > 100 MB with compression disabled — prime candidates
 * for OLTP (3-5× savings) or HCC (10×+) compression.
 * Also returns currently-compressed tables for comparison.
 */
router.get('/:connId/compression-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('compression-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 uncompressed = [];
    let compressed   = [];

    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,
      };
      uncompressed = mapUncomp(await errs.guard('Uncomp tables', () => queryViaAgent(connId, sqlCreds, UNCOMP_TABLES_SQL)));
      compressed   = mapComp(  await errs.guard('Compressed tables (DBA_TABLES compression)', () => queryViaAgent(connId, sqlCreds, COMP_TABLES_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 r1  = await errs.guard('Uncomp tables', () => connection.execute(UNCOMP_TABLES_SQL, [], { outFormat: fmt }), { rows: [] });
        uncompressed = mapUncomp(r1.rows || []);
        const r2  = await errs.guard('Comp tables', () => connection.execute(COMP_TABLES_SQL,   [], { outFormat: fmt }), { rows: [] });
        compressed   = mapComp(r2.rows || []);
      } finally {
        if (connection) await connection.close().catch(() => {});
      }
    }

    // Compute estimated savings range for each uncompressed table
    uncompressed.forEach(t => {
      if (t.size_mb != null) {
        t.est_oltp_min_mb = Math.round(t.size_mb / 5);  // 5× lower bound
        t.est_oltp_max_mb = Math.round(t.size_mb / 3);  // 3× lower bound
        t.est_hcc_mb      = Math.round(t.size_mb / 10); // 10× conservative
      }
    });

    const totalUncompMb = uncompressed.reduce((s, t) => s + (t.size_mb || 0), 0);

    return res.json({ uncompressed, compressed, totalUncompMb: Math.round(totalUncompMb),
      check_errors: errs.errors });
  } catch (err) {
    console.error('[compression-advisor]', err.message);
    if (err.message && (err.message.includes('ORA-00942') || err.message.includes('insufficient privileges'))) {
      return res.json({ uncompressed: [], compressed: [], totalUncompMb: 0,
        error: 'SELECT privilege on DBA_SEGMENTS and DBA_TABLES required (SELECT_CATALOG_ROLE).' });
    }
    res.status(500).json({ error: err.message || 'Failed to fetch compression advisor data' });
  }
});

module.exports = router;
