/**
 * routes/grant-check.js — tell an operator WHICH grants are missing.
 *
 * Owns: GET /api/connections/:id/grant-check.
 * Does NOT own: the required-grant list (parsed from db/setup/tunevault_reader.sql
 *       by services/grant-check.js), applying grants (nothing does), or the
 *       agent transport.
 *
 * The role script went from 31 grants to 52 between proxy 3.21.22 and 3.45.0, and
 * agents auto-upgrade unattended. So an install that was complete when it was set
 * up can be moved onto code that reads objects it was never granted, and the only
 * symptom is ORA-00942 on individual checks — which a handful of surfaces explain
 * and most do not. This names the objects and points at the fix.
 */

'use strict';

const express = require('express');
const pool    = require('../db/index');
const channel = require('../services/agent-channel');
const { decrypt } = require('../crypto-utils');
const { requireAuth, requireConnectionOwner } = require('../middleware/auth');
const grants = require('../services/grant-check');

const router = express.Router();

const APP_URL = process.env.APP_URL || 'https://tunevault.app';

router.get('/connections/:connectionId/grant-check', requireAuth, requireConnectionOwner,
  async (req, res) => {
    const connId = parseInt(req.params.connectionId, 10);
    try {
      const { rows } = await pool.query(
        `SELECT id, connection_type, service_name, username, encrypted_password, host, port
           FROM oracle_connections WHERE id = :p1`,
        { p1: connId }
      );
      const conn = rows[0];
      if (!conn) return res.status(404).json({ error: 'Connection not found' });
      if (conn.connection_type !== 'proxy') {
        return res.status(400).json({
          error: 'Grant check runs through the agent; this connection is Direct TCP.' });
      }

      const required  = grants.parseRequiredObjects();
      const reqPrivs  = grants.parseRequiredPrivileges();
      const creds = {
        service_name: conn.service_name,
        username:     conn.username,
        password:     conn.encrypted_password ? decrypt(conn.encrypted_password) : null,
        host:         conn.host,
        port:         conn.port,
      };

      async function run(sql) {
        const resp = await channel.sendToAgent(connId, {
          method: 'POST', path: '/api/run_sql', body: { sql, ...creds },
        }, 60000);
        const body = (resp && resp.body) || {};
        if (body.success === false || body.error) throw new Error(body.error || 'no rows');
        return body.rows || [];
      }

      // Both halves must succeed. A partial answer reported as a result would
      // say "clean" about the half that never ran — the failure mode this check
      // exists to prevent.
      let objRows, privRows;
      try {
        objRows  = await run(grants.buildVisibilitySql(required));
        privRows = await run(grants.buildPrivilegeSql());
      } catch (err) {
        return res.status(502).json({
          error: 'Could not complete the grant check',
          detail: err.message,
          checked: false,
        });
      }

      const objects    = grants.diffMissing(required, objRows);
      const privileges = grants.diffMissingPrivileges(reqPrivs, privRows);
      const combined = {
        ok:             objects.ok && privileges.ok,
        required_count: objects.required_count + privileges.required_count,
        missing_count:  objects.missing_count  + privileges.missing_count,
        missing:        [...objects.missing, ...privileges.missing],
      };
      res.json({
        ...combined,
        objects,
        privileges,
        checked: true,
        role_script_url: `${APP_URL}/setup/role-script`,
        explanation: grants.explain(combined, APP_URL),
      });
    } catch (err) {
      console.error('[grant-check]', err.message);
      res.status(500).json({ error: 'Grant check failed', detail: err.message });
    }
  });

module.exports = router;
