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

// Indexes under monitoring that have never been used
const UNUSED_SQL = `
SELECT u.index_name, u.table_name, u.monitoring, u.used, u.start_monitoring,
       i.owner, i.index_type, i.uniqueness, i.num_rows, i.leaf_blocks,
       ROUND(NVL(s.bytes,0)/1048576, 1) AS size_mb
FROM V$OBJECT_USAGE u
JOIN DBA_INDEXES i ON i.index_name = u.index_name AND i.table_name = u.table_name
LEFT JOIN DBA_SEGMENTS s
  ON s.segment_name = u.index_name AND s.owner = i.owner AND s.segment_type = 'INDEX'
WHERE u.used = 'NO'
  AND u.monitoring = 'YES'
ORDER BY NVL(s.bytes,0) DESC NULLS LAST`;

// All user index columns for server-side redundancy analysis
const IND_COLS_SQL = `
SELECT ic.table_owner, ic.table_name, ic.index_name,
       ic.column_name, ic.column_position,
       i.uniqueness, i.index_type, i.status,
       i.num_rows, i.leaf_blocks,
       ROUND(NVL(s.bytes,0)/1048576, 1) AS size_mb
FROM DBA_IND_COLUMNS ic
JOIN DBA_INDEXES i
  ON i.owner = ic.table_owner AND i.index_name = ic.index_name
LEFT JOIN DBA_SEGMENTS s
  ON s.segment_name = ic.index_name AND s.owner = ic.table_owner AND s.segment_type = 'INDEX'
WHERE ic.table_owner NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','XDB','ORDDATA','APEX_030200','APEX_040200','APEX_050000','APEX_180100','APEX_190100','APEX_200200','APEX_210100')
ORDER BY ic.table_owner, ic.table_name, ic.index_name, ic.column_position
FETCH FIRST 2000 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 index column map: { 'OWNER.TABLE.INDEX': ['COL1','COL2',...] }
function buildIndexMap(rows) {
  const map = {};
  rows.forEach(r => {
    const key = `${r.TABLE_OWNER}.${r.TABLE_NAME}.${r.INDEX_NAME}`;
    if (!map[key]) {
      map[key] = {
        owner:      r.TABLE_OWNER || '',
        table_name: r.TABLE_NAME  || '',
        index_name: r.INDEX_NAME  || '',
        uniqueness: r.UNIQUENESS  || '',
        index_type: r.INDEX_TYPE  || '',
        status:     r.STATUS      || '',
        num_rows:   r.NUM_ROWS    != null ? Number(r.NUM_ROWS)   : null,
        leaf_blocks: r.LEAF_BLOCKS != null ? Number(r.LEAF_BLOCKS) : null,
        size_mb:    r.SIZE_MB     != null ? parseFloat(r.SIZE_MB) : null,
        columns:    [],
      };
    }
    if (r.COLUMN_NAME) map[key].columns.push(r.COLUMN_NAME);
  });
  return map;
}

// Detect prefix-redundant indexes:
// Index A is redundant if Index B (on same table) covers all of A's columns as a prefix.
function findRedundant(indexMap) {
  const redundant = [];
  const byTable = {};

  Object.values(indexMap).forEach(idx => {
    const tk = `${idx.owner}.${idx.table_name}`;
    if (!byTable[tk]) byTable[tk] = [];
    byTable[tk].push(idx);
  });

  Object.values(byTable).forEach(idxList => {
    idxList.forEach(candidate => {
      if (candidate.uniqueness === 'UNIQUE') return; // keep unique indexes
      idxList.forEach(covering => {
        if (covering.index_name === candidate.index_name) return;
        if (covering.columns.length <= candidate.columns.length) return;
        // covering is longer — check if candidate is a prefix of covering
        const isPrefix = candidate.columns.every((col, i) => covering.columns[i] === col);
        if (isPrefix) {
          redundant.push({
            owner:          candidate.owner,
            table_name:     candidate.table_name,
            redundant_index: candidate.index_name,
            covered_by:     covering.index_name,
            redundant_cols: candidate.columns.join(', '),
            covering_cols:  covering.columns.join(', '),
            size_mb:        candidate.size_mb,
          });
        }
      });
    });
  });

  // Deduplicate (each redundant_index appears once)
  const seen = new Set();
  return redundant.filter(r => {
    const k = `${r.owner}.${r.table_name}.${r.redundant_index}`;
    if (seen.has(k)) return false;
    seen.add(k);
    return true;
  });
}

/**
 * GET /api/connections/:connId/index-advisor
 *
 * Returns:
 *   unusedIndexes  — indexes with monitoring=YES and used=NO since monitoring started
 *   redundantIndexes — indexes whose column list is a strict prefix of a longer sibling index
 *
 * Requires: SELECT on V$OBJECT_USAGE and SELECT_CATALOG_ROLE for DBA_INDEXES/DBA_IND_COLUMNS.
 */
router.get('/:connId/index-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 unusedRows  = [];
    let indColRows  = [];

    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,
      };
      // V$OBJECT_USAGE may fail if no monitoring was ever enabled — treat as empty
      unusedRows = await queryViaAgent(connId, sqlCreds, UNUSED_SQL).catch(() => []);
      indColRows = await queryViaAgent(connId, sqlCreds, IND_COLS_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 connection.execute(UNUSED_SQL,    [], { outFormat: fmt }).catch(() => ({ rows: [] }));
        unusedRows = r1.rows || [];
        const r2  = await connection.execute(IND_COLS_SQL,  [], { outFormat: fmt });
        indColRows = r2.rows || [];
      } finally {
        if (connection) await connection.close().catch(() => {});
      }
    }

    const unusedIndexes = unusedRows.map(r => ({
      owner:            r.OWNER           || '',
      index_name:       r.INDEX_NAME      || '',
      table_name:       r.TABLE_NAME      || '',
      monitoring:       r.MONITORING      || '',
      used:             r.USED            || 'NO',
      start_monitoring: r.START_MONITORING|| null,
      index_type:       r.INDEX_TYPE      || '',
      uniqueness:       r.UNIQUENESS      || '',
      num_rows:         r.NUM_ROWS        != null ? Number(r.NUM_ROWS)   : null,
      size_mb:          r.SIZE_MB         != null ? parseFloat(r.SIZE_MB): null,
    }));

    const indexMap       = buildIndexMap(indColRows);
    const redundantIndexes = findRedundant(indexMap);

    return res.json({ unusedIndexes, redundantIndexes });
  } catch (err) {
    console.error('[index-advisor]', err.message);
    if (err.message && (err.message.includes('ORA-00942') || err.message.includes('insufficient privileges'))) {
      return res.json({ unusedIndexes: [], redundantIndexes: [],
        error: 'SELECT_CATALOG_ROLE required for DBA_INDEXES and DBA_IND_COLUMNS. V$OBJECT_USAGE requires SELECT on the view.' });
    }
    res.status(500).json({ error: err.message || 'Failed to fetch index advisor data' });
  }
});

module.exports = router;
