/**
 * routes/ebs-12-2-checks.js — EBS 12.2 deep checks API + page.
 *
 * Owns: /ebs-12-2-checks page, /api/ebs-12-2/* endpoints.
 * Does NOT own: Oracle connection storage (server.js), SSH vault (routes/ssh-targets.js),
 *               core health checks (server.js), EBS SSH checks (routes/ebs-ssh-checks.js).
 *
 * Routes:
 *   GET  /ebs-12-2-checks              — serve the deep checks page
 *   GET  /api/ebs-12-2/catalog         — check catalog metadata (no SQL/parsers)
 *   GET  /api/ebs-12-2/counts          — CHECK_COUNTS constant (marketing single source of truth)
 *   POST /api/ebs-12-2/run             — run checks for a connection
 *     Body: { connection_id, ssh_target_id? }
 */

'use strict';

const express = require('express');
const path    = require('path');

const pool         = require('../db/index');
const { decrypt }  = require('../crypto-utils');
const sshDb        = require('../db/ssh-targets');
const { requireAuth } = require('../middleware/auth');
const {
  runEbs122Checks,
  getCheckCatalog,
  CHECK_COUNTS,
} = require('../services/ebs-12-2-checks');

const router = express.Router();

// ─── Helper: load + decrypt oracle connection ─────────────────────────────────

async function getConnParams(connectionId, userId) {
  const { rows } = await pool.query(
    `SELECT id, host, port, service_name, username, encrypted_password, connection_type,
            proxy_url, proxy_api_key_enc, server_type, ebs_instance_name, apps_pwd_enc, weblogic_pwd_enc
     FROM oracle_connections WHERE id = :p1 AND user_id = :p2`,
    { p1: connectionId, p2: userId }
  );
  if (!rows.length) return null;
  const conn = rows[0];
  return {
    id: conn.id,
    host: conn.host,
    port: conn.port || 1521,
    serviceName: conn.service_name,
    username: conn.username,
    password: decrypt(conn.encrypted_password),
    connectionType: conn.connection_type,
    proxyUrl: conn.proxy_url || null,
    proxyApiKey: conn.proxy_api_key_enc ? decrypt(conn.proxy_api_key_enc) : null,
    serverType: conn.server_type,
    ebsInstanceName: conn.ebs_instance_name,
    appsPwd: conn.apps_pwd_enc ? decrypt(conn.apps_pwd_enc) : null,
    wlsPwd:  conn.weblogic_pwd_enc ? decrypt(conn.weblogic_pwd_enc) : null,
  };
}

// ─── Helper: find paired DB connection for TNS checks ─────────────────────────
// Returns { id, serviceName, username, password } or null.

async function findPairedDbConn(conn, userId) {
  if (conn.serverType === 'db' || conn.serverType === 'both') {
    return { id: conn.id, serviceName: conn.serviceName, username: conn.username, password: conn.password };
  }
  if (!conn.ebsInstanceName) return null;
  const { rows } = await pool.query(
    `SELECT id, service_name, username, encrypted_password, apps_pwd_enc
     FROM oracle_connections
     WHERE ebs_instance_name = :p1 AND user_id = :p2
       AND server_type IN ('db','both') AND connection_type = 'proxy'
     ORDER BY server_type FETCH FIRST 1 ROWS ONLY`,
    { p1: conn.ebsInstanceName, p2: userId }
  );
  if (!rows.length) return null;
  const r = rows[0];
  const appsPassword = r.apps_pwd_enc ? decrypt(r.apps_pwd_enc) : null;
  return {
    id: r.id,
    serviceName: r.service_name,
    username: appsPassword ? 'APPS' : r.username,
    password: appsPassword || decrypt(r.encrypted_password),
  };
}

// ─── GET /ebs-12-2-checks ─────────────────────────────────────────────────────

router.get('/ebs-12-2-checks', requireAuth, (req, res) => {
  res.sendFile(path.join(__dirname, '..', 'public', 'ebs-12-2-checks.html'));
});

// ─── GET /api/ebs-12-2/catalog ───────────────────────────────────────────────

router.get('/api/ebs-12-2/catalog', requireAuth, (req, res) => {
  res.json({ catalog: getCheckCatalog() });
});

// ─── GET /api/ebs-12-2/counts ────────────────────────────────────────────────
// Single source of truth for check counts used in marketing copy.

router.get('/api/ebs-12-2/counts', (req, res) => {
  res.json({
    db_core: CHECK_COUNTS.db_core,
    ebs_native: CHECK_COUNTS.ebs_native,
    ebs_ssh_legacy: CHECK_COUNTS.ebs_ssh_legacy,
    ebs_12_2: CHECK_COUNTS.ebs_12_2,
    ebs_security: CHECK_COUNTS.ebs_security,
    ebs_performance: CHECK_COUNTS.ebs_performance,
    db_ops: CHECK_COUNTS.db_ops,
    db_total: CHECK_COUNTS.db_total,
    ebs_total: CHECK_COUNTS.ebs_total,
    health_checks_total: CHECK_COUNTS.health_checks_total,
    grand_total_with_ops: CHECK_COUNTS.grand_total_with_ops,
  });
});

// ─── POST /api/ebs-12-2/run ──────────────────────────────────────────────────

router.post('/api/ebs-12-2/run', requireAuth, async (req, res) => {
  const { connection_id, ssh_target_id } = req.body || {};
  if (!connection_id) {
    return res.status(400).json({ error: 'connection_id required' });
  }

  const connParams = await getConnParams(connection_id, req.user.id);
  if (!connParams) {
    return res.status(404).json({ error: 'Connection not found' });
  }

  // Resolve SSH target if provided
  let sshTarget = null;
  if (ssh_target_id) {
    const targets = await sshDb.listTargetsByUser(req.user.id);
    sshTarget = targets.find(t => t.id === parseInt(ssh_target_id, 10)) || null;
    if (!sshTarget) {
      return res.status(404).json({ error: 'SSH target not found' });
    }
  }

  // For proxy connections use agent-channel; for direct TCP use oracledb
  let oracleConn = null;
  let dbConnId = null;
  let dbSqlCreds = null;

  if (connParams.connectionType === 'proxy' || connParams.proxyUrl) {
    oracleConn = null;
    const dbConn = await findPairedDbConn(connParams, req.user.id).catch(() => null);
    if (dbConn) {
      dbConnId = dbConn.id;
      // Prefer APPS creds from app-tier connection if set, otherwise use paired DB creds
      const appsPwd = connParams.appsPwd || dbConn.password;
      const appsUser = connParams.appsPwd ? 'APPS' : dbConn.username;
      dbSqlCreds = { service_name: dbConn.serviceName, username: appsUser, password: appsPwd };
    }
  } else {
    try {
      const oracledb = require('oracledb');
      oracleConn = await oracledb.getConnection({
        user: connParams.username,
        password: connParams.password,
        connectString: `${connParams.host}:${connParams.port}/${connParams.serviceName}`,
      });
    } catch (err) {
      console.warn('ebs-12-2-checks: oracle connect failed:', err.message);
      oracleConn = null;
    }
  }

  try {
    console.log('[ebs-12-2] run — user:', dbSqlCreds?.username, 'has_appsPwd:', !!connParams.appsPwd, 'has_wlsPwd:', !!connParams.wlsPwd, 'dbConnId:', dbConnId);
    const results = await runEbs122Checks({
      oracleConn,
      dbConnId,
      dbSqlCreds,
      appTierConnId: connParams.id,
      appsPwd: connParams.appsPwd || null,
      wlsPwd:  connParams.wlsPwd  || null,
      targetId: sshTarget ? sshTarget.id : null,
      role: sshTarget ? sshTarget.role : null,
      initiatedBy: req.user.email,
      timeoutMs: 20_000,
    });

    res.json({
      connection_id,
      ssh_target_id: sshTarget ? sshTarget.id : null,
      ...results,
    });
  } finally {
    if (oracleConn) {
      try { await oracleConn.close(); } catch {}
    }
  }
});

module.exports = router;
