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

const ACCESS_TASKS_SQL = `
SELECT task_id, task_name, status,
       TO_CHAR(created, 'YYYY-MM-DD HH24:MI:SS') AS created_str
FROM DBA_ADVISOR_TASKS
WHERE advisor_name = 'SQL Access Advisor'
  AND created > SYSDATE - 30
ORDER BY created DESC NULLS LAST
FETCH FIRST 10 ROWS ONLY`;

function accessRecsSql(taskIds) {
  return `
SELECT r.task_id, r.rec_id, r.type,
       ROUND(r.benefit, 2) AS benefit,
       SUBSTR(a.message, 1, 500) AS action_message,
       a.attr1, a.attr2, a.attr3
FROM DBA_ADVISOR_RECOMMENDATIONS r
JOIN DBA_ADVISOR_ACTIONS a ON a.task_id = r.task_id AND a.rec_id = r.rec_id
WHERE r.task_id IN (${taskIds})
ORDER BY r.benefit 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 mapTasks(rows) {
  return rows.map(r => ({
    task_id:   Number(r.TASK_ID || 0),
    task_name: r.TASK_NAME || '',
    status:    r.STATUS    || '',
    created:   r.CREATED_STR || null,
  }));
}

function mapRecs(rows) {
  return rows.map(r => ({
    task_id:        Number(r.TASK_ID  || 0),
    rec_id:         Number(r.REC_ID   || 0),
    type:           r.TYPE            || '',
    benefit:        r.BENEFIT  != null ? parseFloat(r.BENEFIT) : null,
    action_message: r.ACTION_MESSAGE  || '',
    attr1:          r.ATTR1    || '',
    attr2:          r.ATTR2    || '',
    attr3:          r.ATTR3    || '',
  }));
}

/**
 * GET /api/connections/:connId/access-advisor
 *
 * Returns SQL Access Advisor tasks + top recommendations (indexes, MVs, partitioning).
 * Requires Oracle Tuning Pack license (EE + Tuning Pack).
 * Supports both agent-channel (proxy) and direct TCP connections.
 */
router.get('/:connId/access-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 tasks           = [];
    let recommendations = [];

    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 taskRows = await queryViaAgent(connId, sqlCreds, ACCESS_TASKS_SQL);
      tasks = mapTasks(taskRows);

      const taskIds = tasks.map(t => t.task_id).filter(Boolean);
      if (taskIds.length > 0) {
        const safeIds = taskIds.map(Number).filter(n => !isNaN(n)).join(',');
        const recRows = await queryViaAgent(connId, sqlCreds, accessRecsSql(safeIds));
        recommendations = mapRecs(recRows);
      }
    } 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(ACCESS_TASKS_SQL, [], { outFormat: fmt });
        tasks     = mapTasks(r1.rows || []);
        const taskIds = tasks.map(t => t.task_id).filter(Boolean);
        if (taskIds.length > 0) {
          const safeIds = taskIds.map(Number).filter(n => !isNaN(n)).join(',');
          const r2 = await connection.execute(accessRecsSql(safeIds), [], { outFormat: fmt });
          recommendations = mapRecs(r2.rows || []);
        }
      } finally {
        if (connection) await connection.close().catch(() => {});
      }
    }

    return res.json({ tasks, recommendations, licensed: true });
  } catch (err) {
    console.error('[access-advisor]', err.message);
    if (err.message && (err.message.includes('ORA-00942') || err.message.includes('insufficient privileges'))) {
      return res.json({ tasks: [], recommendations: [], licensed: false, error: 'Oracle Tuning Pack license required for SQL Access Advisor (DBA_ADVISOR_TASKS / DBA_ADVISOR_RECOMMENDATIONS).' });
    }
    res.status(500).json({ error: err.message || 'Failed to fetch SQL Access Advisor data' });
  }
});

module.exports = router;
