/**
 * services/grant-check.js — which grants from the role script are missing.
 *
 * Owns: parsing db/setup/tunevault_reader.sql for the objects it grants, and
 *       reporting which of them the connection cannot see.
 * Does NOT own: applying grants (nothing does — the role script is served as
 *       text for a DBA to run), or the connection/query plumbing.
 *
 * Why this exists: the role script grew from 31 grants to 52 between proxy
 * 3.21.22 and 3.45.0. Agents auto-upgrade unattended, so an existing customer
 * can be moved onto code that queries objects their reader role was never
 * granted — and the result is ORA-00942 on individual checks with nothing
 * anywhere connecting that to the upgrade. A handful of surfaces (mcp.js, the
 * access/space advisors, the security-posture badge) explain it; most do not.
 *
 * The required list is PARSED from the role script rather than restated here.
 * A second copy would drift from it the same way three different db-ops counts
 * drifted in CLAUDE.md.
 */

'use strict';

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

const ROLE_SCRIPT = path.join(__dirname, '..', 'db', 'setup', 'tunevault_reader.sql');

// Two shapes appear in the script, and only matching the first would silently
// under-report: 32 lines are qualified, 10 are not.
//   GRANT SELECT ON APPS.FND_USER   TO tunevault_reader;   -- owner-qualified
//   GRANT SELECT ON DBA_HIST_SNAPSHOT TO tunevault_reader; -- SYS view, unqualified
// Unqualified names are SYS-owned dictionary/dynamic views (DBA_*, V_$*).
// Privilege grants with no object at all (CREATE SESSION, SELECT_CATALOG_ROLE,
// SELECT ANY DICTIONARY) and EXECUTE grants are deliberately out of scope: they
// are not owner.object rows in ALL_OBJECTS and cannot be probed this way.
const GRANT_QUALIFIED_RE   = /^\s*GRANT\s+SELECT\s+ON\s+([A-Z0-9_$#]+)\.([A-Z0-9_$#]+)\s+TO\s/gim;
const GRANT_UNQUALIFIED_RE = /^\s*GRANT\s+SELECT\s+ON\s+([A-Z0-9_$#]+)\s+TO\s/gim;

/**
 * Objects the role script grants SELECT on, as [{owner, name}].
 * Role grants (GRANT SELECT_CATALOG_ROLE TO ...) are deliberately excluded:
 * they have no owner.object to probe.
 */
function parseRequiredObjects(sqlText) {
  const src = sqlText != null ? sqlText : fs.readFileSync(ROLE_SCRIPT, 'utf8');
  const out = new Map();
  let m;
  GRANT_QUALIFIED_RE.lastIndex = 0;
  while ((m = GRANT_QUALIFIED_RE.exec(src)) !== null) {
    const owner = m[1].toUpperCase();
    const name  = m[2].toUpperCase();
    out.set(`${owner}.${name}`, { owner, name });
  }
  GRANT_UNQUALIFIED_RE.lastIndex = 0;
  while ((m = GRANT_UNQUALIFIED_RE.exec(src)) !== null) {
    const name = m[1].toUpperCase();
    out.set(`SYS.${name}`, { owner: 'SYS', name });
  }
  return [...out.values()];
}

// ── Non-object grants ────────────────────────────────────────────────────────
// The role script does not only grant SELECT on objects. It also grants system
// privileges, a role, and EXECUTE on five DBMS_* packages. Reporting "all grants
// present" while a customer is missing EXECUTE ON DBMS_WORKLOAD_REPOSITORY is
// worse than no check at all — every AWR-backed surface fails and the check says
// it is fine.
//
// GRANT CREATE SESSION            TO <role>;   -- system privilege
// GRANT SELECT ANY DICTIONARY     TO <role>;   -- system privilege
// GRANT ADVISOR                   TO <role>;   -- system privilege
// GRANT SELECT_CATALOG_ROLE       TO <role>;   -- role
// GRANT EXECUTE ON DBMS_ADDM      TO <role>;   -- package EXECUTE
//
// `GRANT <role> TO <someone-else>` is deliberately NOT a requirement: it assigns
// the role to an account, and is not something the connected session can report
// about itself. Only grants made TO the reader role are requirements, so the
// role name is derived from the script rather than hardcoded.
const GRANT_ANY_RE     = /^\s*GRANT\s+(.+?)\s+TO\s+([A-Za-z0-9_$#]+)\s*;/gim;
const GRANT_EXECUTE_RE = /^\s*GRANT\s+EXECUTE\s+ON\s+([A-Za-z0-9_$#.]+)\s+TO\s/gim;

/** The role the script grants TO most often — the reader role it is building. */
function readerRoleName(src) {
  const tally = new Map();
  let m;
  GRANT_ANY_RE.lastIndex = 0;
  while ((m = GRANT_ANY_RE.exec(src)) !== null) {
    const g = m[2].toUpperCase();
    tally.set(g, (tally.get(g) || 0) + 1);
  }
  let best = null, n = -1;
  for (const [g, c] of tally) if (c > n) { best = g; n = c; }
  return best;
}

/**
 * { sysPrivs: ['CREATE SESSION', ...], roles: ['SELECT_CATALOG_ROLE'],
 *   execs: ['DBMS_ADDM', ...] } — parsed from the SAME script as the objects.
 */
function parseRequiredPrivileges(sqlText) {
  const src  = sqlText != null ? sqlText : fs.readFileSync(ROLE_SCRIPT, 'utf8');
  const role = readerRoleName(src);
  const sysPrivs = new Set(), roles = new Set(), execs = new Set();

  GRANT_EXECUTE_RE.lastIndex = 0;
  let m;
  while ((m = GRANT_EXECUTE_RE.exec(src)) !== null) {
    // Package EXECUTE is reported by its bare name in the dictionary views.
    execs.add(m[1].toUpperCase().split('.').pop());
  }

  GRANT_ANY_RE.lastIndex = 0;
  while ((m = GRANT_ANY_RE.exec(src)) !== null) {
    const what    = m[1].trim().toUpperCase().replace(/\s+/g, ' ');
    const grantee = m[2].toUpperCase();
    if (role && grantee !== role) continue;      // not a requirement of the role
    if (/^SELECT\s+ON\s/.test(what))  continue;  // object grants handled elsewhere
    if (/^EXECUTE\s+ON\s/.test(what)) continue;  // handled above
    if (/_ROLE$/.test(what)) roles.add(what);
    else sysPrivs.add(what);
  }
  return { sysPrivs: [...sysPrivs], roles: [...roles], execs: [...execs] };
}

/**
 * EFFECTIVE privileges, not just directly-granted ones: SESSION_PRIVS and
 * SESSION_ROLES include everything reaching the session through roles, which is
 * what decides whether a query actually works. EXECUTE is taken from both
 * USER_TAB_PRIVS (direct) and ROLE_TAB_PRIVS (via an enabled role) — checking
 * only the former reports a false "missing" for the common case where the DBA
 * granted the package to a role.
 */
function buildPrivilegeSql() {
  return (
    "SELECT 'PRIV' AS kind, privilege AS nm FROM session_privs " +
    "UNION ALL SELECT 'ROLE', role FROM session_roles " +
    "UNION ALL SELECT 'EXEC', table_name FROM user_tab_privs WHERE privilege = 'EXECUTE' " +
    "UNION ALL SELECT 'EXEC', table_name FROM role_tab_privs WHERE privilege = 'EXECUTE'"
  );
}

function diffMissingPrivileges(required, rows) {
  const held = { PRIV: new Set(), ROLE: new Set(), EXEC: new Set() };
  for (const r of rows || []) {
    const kind = (r.kind || r.KIND || '').toUpperCase();
    const nm   = (r.nm || r.NM || '').toUpperCase();
    if (held[kind]) held[kind].add(nm);
  }
  // SELECT ANY DICTIONARY implies the catalog reads SELECT_CATALOG_ROLE grants,
  // but they are separate grants in the script and are reported separately —
  // inferring one from the other is how a real gap gets hidden.
  const missingSys  = required.sysPrivs.filter(p => !held.PRIV.has(p));
  const missingRole = required.roles.filter(r => !held.ROLE.has(r));
  const missingExec = required.execs.filter(e => !held.EXEC.has(e));
  const missing = [
    ...missingSys.map(p  => `privilege ${p}`),
    ...missingRole.map(r => `role ${r}`),
    ...missingExec.map(e => `EXECUTE ON ${e}`),
  ];
  const total = required.sysPrivs.length + required.roles.length + required.execs.length;
  return {
    required_count: total,
    missing_count:  missing.length,
    missing,
    ok: missing.length === 0,
  };
}

/**
 * One query, not N: an object the session has no privilege on simply does not
 * appear in ALL_OBJECTS, so the visible set answers every object at once.
 * Returns the SQL and the required list so the caller can run it on whichever
 * transport the connection uses.
 */
function buildVisibilitySql(required) {
  const pairs = required
    .map(o => `('${o.owner}','${o.name}')`)
    .join(',');
  return `SELECT owner, object_name FROM all_objects WHERE (owner, object_name) IN (${pairs})`;
}

/**
 * Diff the visible set against what the role script requires.
 * `visibleRows` is whatever the query returned — row shape is tolerated in both
 * the lowercased (Oracle driver) and uppercased forms.
 */
function diffMissing(required, visibleRows) {
  const seen = new Set(
    (visibleRows || []).map(r => {
      const owner = (r.owner || r.OWNER || '').toUpperCase();
      const name  = (r.object_name || r.OBJECT_NAME || '').toUpperCase();
      return `${owner}.${name}`;
    })
  );
  const missing = required.filter(o => !seen.has(`${o.owner}.${o.name}`));
  return {
    required_count: required.length,
    visible_count:  required.length - missing.length,
    missing_count:  missing.length,
    missing:        missing.map(o => `${o.owner}.${o.name}`),
    ok:             missing.length === 0,
  };
}

/** Operator-facing explanation. Names the objects; never just "insufficient privileges". */
function explain(result, appUrl) {
  if (result.ok) return 'All grants from the role script are in place.';
  const shown = result.missing.slice(0, 12);
  const more  = result.missing.length - shown.length;
  return (
    `${result.missing_count} of ${result.required_count} required grants are missing, so the checks ` +
    `that read them fail with ORA-00942 and no explanation.\n` +
    `Missing: ${shown.join(', ')}${more > 0 ? ` (+${more} more)` : ''}\n` +
    `Fix: have a DBA re-run the role script — ${appUrl || ''}/setup/role-script — as SYSDBA. ` +
    `It is idempotent, so re-running it is safe even if most grants already exist. ` +
    `The script grew as TuneVault gained checks, and agents upgrade unattended, so an ` +
    `install that was complete when it was set up can fall behind it.`
  );
}

module.exports = {
  parseRequiredObjects, buildVisibilitySql, diffMissing,
  parseRequiredPrivileges, buildPrivilegeSql, diffMissingPrivileges, readerRoleName,
  explain, ROLE_SCRIPT,
};
