<?php
/**
 * Mesh Security Dashboard — Data Layer
 * Queries sec_tool_runs, sec_events, sec_lockdown_state,
 * sec_bash_integrity, sec_key_rotations, and nodes tables.
 */
if (!defined('DASHBOARD_ROOT')) { http_response_code(403); exit; }

/* ------------------------------------------------------------------ */
/*  Known mesh nodes (fallback labels when DB rows are missing)       */
/* ------------------------------------------------------------------ */
$meshNodes = [
    414342 => 'Shield',
    558201 => 'Origin',
    244939 => 'Proxy',
    333941 => 'Filter',
    199335 => 'DNS',
    669427 => 'Engine',
    714820 => 'Payment',
    869377 => 'Vault',
];

/* ------------------------------------------------------------------ */
/*  1. Node health — last heartbeat + tool-run counts                 */
/* ------------------------------------------------------------------ */
try {
    $nodeHealthRows = Database::fetchAll("
        SELECT  n.node_id,
                n.hostname,
                n.node_role,
                n.status          AS node_status,
                n.last_heartbeat,
                COALESCE(tr.run_count, 0)   AS tool_run_count,
                COALESCE(tr.fail_count, 0)  AS tool_fail_count,
                COALESCE(tr.last_run, n.last_heartbeat) AS last_activity
        FROM    nodes n
        LEFT JOIN (
            SELECT  node_id,
                    COUNT(*)                                    AS run_count,
                    SUM(CASE WHEN exit_code <> 0 THEN 1 ELSE 0 END) AS fail_count,
                    MAX(finished_at)                            AS last_run
            FROM    sec_tool_runs
            WHERE   status_code = 1
            GROUP BY node_id
        ) tr ON tr.node_id = n.node_id
        WHERE   n.node_id IN (" . implode(',', array_keys($meshNodes)) . ")
        ORDER BY n.node_id
    ");
} catch (Throwable $e) {
    $nodeHealthRows = [];
}

// Index by node_id and back-fill any missing nodes
$nodeHealth = [];
foreach ($nodeHealthRows as $r) {
    $nodeHealth[(int)$r['node_id']] = $r;
}
foreach ($meshNodes as $nid => $label) {
    if (!isset($nodeHealth[$nid])) {
        $nodeHealth[$nid] = [
            'node_id'        => $nid,
            'hostname'       => $label,
            'node_role'      => strtolower($label),
            'node_status'    => 'unknown',
            'last_heartbeat' => null,
            'tool_run_count' => 0,
            'tool_fail_count'=> 0,
            'last_activity'  => null,
        ];
    }
}
ksort($nodeHealth);

/* ------------------------------------------------------------------ */
/*  2. Recent security events (last 20)                               */
/* ------------------------------------------------------------------ */
try {
    $secEvents = Database::fetchAll("
        SELECT  se.node_id,
                COALESCE(n.hostname, se.node_id) AS hostname,
                se.source_tag,
                se.event_type,
                se.severity,
                se.details_enc,
                se.ip_address_enc,
                se.created_at
        FROM    sec_events se
        LEFT JOIN nodes n ON n.node_id = se.node_id
        WHERE   se.status_code = 1
        ORDER BY se.created_at DESC
        LIMIT 20
    ");
} catch (Throwable $e) {
    $secEvents = [];
}

/* ------------------------------------------------------------------ */
/*  3. Lockdown state per node                                        */
/* ------------------------------------------------------------------ */
try {
    $lockdownRows = Database::fetchAll("
        SELECT  ls.node_id,
                COALESCE(n.hostname, ls.node_id) AS hostname,
                ls.level,
                ls.activated_at,
                ls.deactivated_at,
                ls.activated_by
        FROM    sec_lockdown_state ls
        LEFT JOIN nodes n ON n.node_id = ls.node_id
        WHERE   ls.status_code = 1
          AND   ls.deactivated_at IS NULL
        ORDER BY ls.activated_at DESC
    ");
} catch (Throwable $e) {
    $lockdownRows = [];
}

// Index lockdown by node_id (latest active)
$lockdownByNode = [];
foreach ($lockdownRows as $r) {
    $nid = (int)$r['node_id'];
    if (!isset($lockdownByNode[$nid])) {
        $lockdownByNode[$nid] = $r;
    }
}

/* ------------------------------------------------------------------ */
/*  4. Integrity check results (sec_bash_integrity)                   */
/* ------------------------------------------------------------------ */
try {
    $integrityRows = Database::fetchAll("
        SELECT  bi.node_id,
                COALESCE(n.hostname, bi.node_id) AS hostname,
                bi.file_path,
                bi.expected_hash,
                bi.actual_hash,
                bi.matched,
                bi.checked_at
        FROM    sec_bash_integrity bi
        LEFT JOIN nodes n ON n.node_id = bi.node_id
        WHERE   bi.status_code = 1
        ORDER BY bi.checked_at DESC
        LIMIT 50
    ");
} catch (Throwable $e) {
    $integrityRows = [];
}

// Aggregate pass / fail counts
$integrityPassCount = 0;
$integrityFailCount = 0;
foreach ($integrityRows as $r) {
    if ((int)$r['matched'] === 1) {
        $integrityPassCount++;
    } else {
        $integrityFailCount++;
    }
}

/* ------------------------------------------------------------------ */
/*  5. Key rotation log                                               */
/* ------------------------------------------------------------------ */
try {
    $keyRotations = Database::fetchAll("
        SELECT  kr.node_id,
                COALESCE(n.hostname, kr.node_id) AS hostname,
                kr.key_type,
                kr.rotated_at,
                kr.verified
        FROM    sec_key_rotations kr
        LEFT JOIN nodes n ON n.node_id = kr.node_id
        WHERE   kr.status_code = 1
        ORDER BY kr.rotated_at DESC
        LIMIT 20
    ");
} catch (Throwable $e) {
    $keyRotations = [];
}

/* ------------------------------------------------------------------ */
/*  6. Totals for header badges                                       */
/* ------------------------------------------------------------------ */
try {
    $totalEvents = (int)Database::scalar("
        SELECT COUNT(*) FROM sec_events WHERE status_code = 1
    ");
} catch (Throwable $e) {
    $totalEvents = 0;
}

try {
    $criticalEvents = (int)Database::scalar("
        SELECT COUNT(*) FROM sec_events
        WHERE status_code = 1 AND severity = 'critical'
          AND created_at >= NOW() - INTERVAL 24 HOUR
    ");
} catch (Throwable $e) {
    $criticalEvents = 0;
}

try {
    $canaryTriggerCount = (int)Database::scalar("
        SELECT COUNT(*) FROM sec_canary_triggers
        WHERE status_code = 1
          AND detected_at >= NOW() - INTERVAL 24 HOUR
    ");
} catch (Throwable $e) {
    $canaryTriggerCount = 0;
}
