'use strict';

/**
 * db/activity-log.js — activity_log table CRUD + query helpers.
 *
 * Owns: reading and writing activity_log records (all DBA actions, logins,
 *       health checks, executions, approvals, settings changes).
 * Does NOT own: business logic, auth, tier enforcement, PDF/CSV rendering.
 *
 * ISOLATION GUARANTEE: every read is scoped to a single company_domain.
 * No query returns rows whose company_domain differs from the viewer's.
 * Admin cross-company visibility is restricted to /admin/... endpoints only
 * (not enforced here — the route layer is responsible for that separation).
 */

const pool = require('./index');

// Valid action types — kept in sync with the route and the UI filter list
const ACTION_TYPES = [
  'login',
  'health_check',
  'sql_execution',
  'ssh_execution',
  'db_op',
  'ebs_op',
  'tuneops',
  'approval',
  'settings_change',
  'export',
];

const RESULT_VALUES = ['success', 'failed', 'pending'];

/**
 * Write a single activity log entry.
 * Silently no-ops on error so logging never breaks the calling operation.
 *
 * @param {Object} opts
 * @param {number|null}  opts.userId
 * @param {string|null}  opts.userEmail
 * @param {string|null}  opts.userName
 * @param {string|null}  opts.userRole
 * @param {string|null}  opts.companyDomain  — required for isolation; callers pass req.user.company_domain
 * @param {string}       opts.actionType   — one of ACTION_TYPES
 * @param {Object}       opts.detail       — freeform JSONB payload
 * @param {number|null}  opts.connectionId
 * @param {string|null}  opts.connectionName
 * @param {string}       opts.result       — 'success'|'failed'|'pending'
 * @param {number|null}  opts.durationMs
 * @param {string|null}  opts.ipAddress
 * @param {string|null}  opts.userAgent
 * @returns {Promise<void>}
 */
async function logActivity({
  userId = null,
  userEmail = null,
  userName = null,
  userRole = null,
  companyDomain = null,
  actionType,
  detail = {},
  connectionId = null,
  connectionName = null,
  result = 'success',
  durationMs = null,
  ipAddress = null,
  userAgent = null,
}) {
  try {
    await pool.query(
      `INSERT INTO activity_log
         (user_id, user_email, user_name, user_role, company_domain,
          action_type, detail, connection_id, connection_name,
          result, duration_ms, ip_address, user_agent)
       VALUES (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13)`,
      {
        p1: userId, p2: userEmail, p3: userName, p4: userRole, p5: companyDomain || null,
        // detail is a JSON column: a plain JS object cannot be bound (NJS-044),
        // so it must be serialised. Reads come back already parsed.
        p6: actionType, p7: JSON.stringify(detail),
        p8: connectionId, p9: connectionName,
        p10: result, p11: durationMs,
        p12: ipAddress, p13: userAgent,
      }
    );
  } catch (err) {
    // Never surface logging errors to callers
    console.error('[activity-log] write error:', err.message);
  }
}

/**
 * Query activity log with filters, pagination, and text search.
 *
 * ISOLATION: viewerCompanyDomain is ALWAYS applied as a WHERE condition.
 * When isAdmin=true the user still only sees their own company; cross-company
 * visibility belongs exclusively on /admin routes, not here.
 *
 * @param {Object} opts
 * @param {number}        opts.viewerUserId      — user performing the query
 * @param {string|null}   opts.viewerCompanyDomain — mandatory for isolation
 * @param {boolean}       opts.isAdmin           — platform admin (still company-scoped here)
 * @param {boolean}       opts.isTeamAdmin       — team admin sees all team members
 * @param {number[]}      opts.teamMemberIds     — user IDs in the viewer's team
 * @param {string|null}   opts.dateFrom          — ISO string
 * @param {string|null}   opts.dateTo            — ISO string
 * @param {number|null}   opts.filterUserId
 * @param {string[]}      opts.actionTypes       — empty = all
 * @param {number|null}   opts.connectionId
 * @param {string|null}   opts.result            — 'success'|'failed'|'pending'|null
 * @param {string|null}   opts.search            — text search on detail::text
 * @param {number}        opts.limit
 * @param {number}        opts.offset
 * @returns {Promise<{rows: Array, total: number}>}
 */
async function queryActivity({
  viewerUserId,
  viewerCompanyDomain = null,
  isAdmin = false,
  isTeamAdmin = false,
  teamMemberIds = [],
  dateFrom = null,
  dateTo = null,
  filterUserId = null,
  actionTypes = [],
  connectionId = null,
  result = null,
  search = null,
  limit = 50,
  offset = 0,
}) {
  const conditions = [];
  // Oracle named binds: an object keyed p1..pN. Positional (:1, :2) is NOT safe —
  // the driver binds an array by order of appearance and ignores the digit, so a
  // predicate list built out of order would silently receive the wrong values.
  const binds = {};
  let p = 1;
  const bind = (value) => { binds[`p${p}`] = value; return `:p${p++}`; };

  // ── Company-domain isolation (defense layer 1) ─────────────────────────────
  // Always scope to the viewer's company. NULL company_domain rows are only
  // visible to the user who created them (legacy rows before migration).
  if (viewerCompanyDomain) {
    conditions.push(`(company_domain = ${bind(viewerCompanyDomain)} OR (company_domain IS NULL AND user_id = ${bind(viewerUserId)}))`);
  } else {
    // No company domain on record — restrict to own rows only (safest fallback)
    conditions.push(`user_id = ${bind(viewerUserId)}`);
  }

  // ── User-ID ownership scoping (defense layer 2) ────────────────────────────
  // Non-admins and non-team-admins may only see their own rows, even within
  // their company. Team admins see all team members within the company.
  // Oracle cannot bind an array, so `= ANY($n)` becomes an explicit IN list of
  // individual binds. An empty list would produce `IN ()`, which is a syntax
  // error, so it degrades to a never-true predicate instead.
  if (!isAdmin) {
    const visibleIds = [viewerUserId, ...teamMemberIds].filter(Boolean);
    conditions.push(visibleIds.length
      ? `user_id IN (${visibleIds.map((id) => bind(id)).join(', ')})`
      : '1 = 0');
  }

  if (dateFrom) {
    conditions.push(`created_at >= ${bind(new Date(dateFrom))}`);
  }
  if (dateTo) {
    const to = new Date(dateTo);
    to.setHours(23, 59, 59, 999);
    conditions.push(`created_at <= ${bind(to)}`);
  }
  if (filterUserId) {
    conditions.push(`user_id = ${bind(filterUserId)}`);
  }
  if (actionTypes && actionTypes.length > 0) {
    conditions.push(`action_type IN (${actionTypes.map((a) => bind(a)).join(', ')})`);
  }
  if (connectionId) {
    conditions.push(`connection_id = ${bind(connectionId)}`);
  }
  if (result) {
    conditions.push(`result = ${bind(result)}`);
  }
  if (search && search.trim()) {
    // ILIKE -> UPPER() LIKE UPPER(); detail::text -> JSON_SERIALIZE (detail is a
    // JSON column). One bind reused across all four columns by name.
    const s = bind(`%${search.trim()}%`);
    conditions.push(
      `(UPPER(JSON_SERIALIZE(detail)) LIKE UPPER(${s}) OR UPPER(user_email) LIKE UPPER(${s})` +
      ` OR UPPER(user_name) LIKE UPPER(${s}) OR UPPER(connection_name) LIKE UPPER(${s}))`
    );
  }

  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  // Count total (for pagination)
  const countRes = await pool.query(
    `SELECT COUNT(*) AS total FROM activity_log ${where}`,
    binds
  );
  const total = parseInt(countRes.rows[0].total, 10);

  // Paginated rows — always include company_domain in SELECT so the
  // route-layer serializer can run a final per-row guard.
  // LIMIT/OFFSET -> OFFSET ... ROWS FETCH NEXT ... ROWS ONLY (offset comes first).
  const { rows } = await pool.query(
    `SELECT id, user_id, user_email, user_name, user_role, action_type,
            detail, connection_id, connection_name, result, duration_ms,
            ip_address, created_at, company_domain
     FROM activity_log
     ${where}
     ORDER BY created_at DESC
     OFFSET :p_offset ROWS FETCH NEXT :p_limit ROWS ONLY`,
    { ...binds, p_offset: offset, p_limit: limit }
  );

  return { rows, total };
}

/**
 * Summary stats: counts grouped by action type and result within a date range,
 * scoped to visible user IDs AND company domain.
 */
async function getActivityStats({
  viewerUserId,
  viewerCompanyDomain = null,
  isAdmin = false,
  teamMemberIds = [],
  dateFrom,
  dateTo,
}) {
  const binds = {};
  let p = 1;
  const bind = (value) => { binds[`p${p}`] = value; return `:p${p++}`; };
  const conditions = [];

  // Company-domain isolation always applied
  if (viewerCompanyDomain) {
    conditions.push(`(company_domain = ${bind(viewerCompanyDomain)} OR (company_domain IS NULL AND user_id = ${bind(viewerUserId)}))`);
  } else {
    conditions.push(`user_id = ${bind(viewerUserId)}`);
  }

  if (!isAdmin) {
    const visibleIds = [viewerUserId, ...teamMemberIds].filter(Boolean);
    conditions.push(visibleIds.length
      ? `user_id IN (${visibleIds.map((id) => bind(id)).join(', ')})`
      : '1 = 0');
  }
  if (dateFrom) {
    conditions.push(`created_at >= ${bind(new Date(dateFrom))}`);
  }
  if (dateTo) {
    const to = new Date(dateTo);
    to.setHours(23, 59, 59, 999);
    conditions.push(`created_at <= ${bind(to)}`);
  }

  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  // COUNT(*) FILTER (WHERE x) -> COUNT(CASE WHEN x THEN 1 END): counting a CASE
  // that yields NULL when the predicate misses is the Oracle equivalent.
  const [totalsRes, typeRes, activeUsersRes] = await Promise.all([
    pool.query(
      `SELECT
         COUNT(*) AS total_actions,
         COUNT(CASE WHEN result = 'failed' THEN 1 END) AS failed_count,
         COUNT(CASE WHEN action_type IN ('sql_execution','ssh_execution','db_op','ebs_op') THEN 1 END) AS execution_count,
         COUNT(CASE WHEN action_type = 'approval' THEN 1 END) AS approval_count
       FROM activity_log ${where}`,
      binds
    ),
    pool.query(
      `SELECT action_type, COUNT(*) AS cnt
       FROM activity_log ${where}
       GROUP BY action_type ORDER BY cnt DESC`,
      binds
    ),
    pool.query(
      `SELECT COUNT(DISTINCT user_id) AS active_users
       FROM activity_log ${where}`,
      binds
    ),
  ]);

  return {
    totals: totalsRes.rows[0],
    byType: typeRes.rows,
    activeUsers: activeUsersRes.rows[0].active_users,
  };
}

/**
 * Fetch all rows (no pagination) for CSV/PDF export respecting same filters.
 * Hard-capped at 10,000 rows for safety.
 */
async function exportActivity(queryOpts) {
  const extended = { ...queryOpts, limit: 10000, offset: 0 };
  const { rows } = await queryActivity(extended);
  return rows;
}

module.exports = {
  logActivity,
  queryActivity,
  getActivityStats,
  exportActivity,
  ACTION_TYPES,
};
