<?php
// security_audit.php - Read-only security audit.
// Modes:
//   - Authenticated Master/Endie via session (HTML output)
//   - CLI                                  (text output)
//   - Web cron: ?cron=1&token=<config.backup_sync.cron_key> (text + Telegram if findings)
// Never writes to app data.

require 'db.php';
require_once 'helpers.php';

$isCli  = (php_sapi_name() === 'cli');
$isCron = isset($_GET['cron']);
$wantHtml = !$isCli && !$isCron;

if ($isCron) {
    $expected = (string) getConfig('backup_sync.cron_key', '');
    $provided = (string) ($_GET['token'] ?? '');
    if ($expected === '' || !hash_equals($expected, $provided)) {
        http_response_code(403);
        header('Content-Type: text/plain');
        die("Forbidden.");
    }
    header('Content-Type: text/plain; charset=utf-8');
} elseif (!$isCli) {
    if (!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] !== true) {
        http_response_code(401);
        header('Content-Type: text/plain');
        die("Authentication required.");
    }
    $username = strtolower((string)($_SESSION['user'] ?? ''));
    $role     = strtolower((string)($_SESSION['role'] ?? ''));
    if ($username !== 'endie' && $role !== 'master') {
        http_response_code(403);
        header('Content-Type: text/plain');
        die("Master/Endie access only.");
    }
}

$findings = [];

// --- Failed-login spikes ------------------------------------------------
try {
    $stmt = $pdo->query("SELECT ip_address, COUNT(*) AS c
                          FROM login_attempts
                          WHERE success = 0 AND attempt_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
                          GROUP BY ip_address
                          HAVING c >= 10
                          ORDER BY c DESC");
    $rows = $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
    foreach ($rows as $r) {
        $findings[] = sprintf('failed_login_spike: ip=%s count=%d in 24h', $r['ip_address'], (int)$r['c']);
    }
} catch (Throwable $e) {
    $findings[] = 'failed_login_query_error: ' . $e->getMessage();
}

// --- Stale users (no recent successful login AND no recent activity) -----
// A user is "stale" only if BOTH of these are true:
//   - no login_attempts.success=1 row in the last 180 days
//   - no activity_logs row in the last 180 days
// (api.php's login path historically did not write success rows; activity is
//  the more reliable signal that the account is actively in use.)
//
// NOTE: we keep NULLs as NULL and compare via IS NULL in the HAVING clause.
// MySQL strict mode rejects '' as a TIMESTAMP value (error 1525), so the
// previous COALESCE(..., '') approach failed.
try {
    $stmt = $pdo->query("SELECT u.username,
                                la.last_ok  AS last_ok,
                                al.last_act AS last_act
                          FROM users u
                          LEFT JOIN (
                                SELECT username, MAX(attempt_time) AS last_ok
                                FROM login_attempts WHERE success = 1
                                GROUP BY username
                          ) la ON la.username = u.username
                          LEFT JOIN (
                                SELECT user AS username, MAX(created_at) AS last_act
                                FROM activity_logs
                                GROUP BY user
                          ) al ON LOWER(al.username) = LOWER(u.username)
                          WHERE (la.last_ok IS NULL OR la.last_ok < DATE_SUB(NOW(), INTERVAL 180 DAY))
                            AND (al.last_act IS NULL OR al.last_act < DATE_SUB(NOW(), INTERVAL 180 DAY))");
    $rows = $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
    foreach ($rows as $r) {
        $lastOk  = !empty($r['last_ok'])  ? $r['last_ok']  : 'never';
        $lastAct = !empty($r['last_act']) ? $r['last_act'] : 'never';
        $findings[] = sprintf('stale_user: %s last_login=%s last_activity=%s', $r['username'], $lastOk, $lastAct);
    }
} catch (Throwable $e) {
    $findings[] = 'stale_user_query_error: ' . $e->getMessage();
}

// --- Activity log signals -----------------------------------------------
try {
    $stmt = $pdo->query("SELECT user, action, COUNT(*) c
                          FROM activity_logs
                          WHERE created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
                            AND action IN ('DELETE_JOB','DELETE_STOCK','DELETE_USER','REFUND')
                          GROUP BY user, action
                          HAVING c >= 5
                          ORDER BY c DESC");
    $rows = $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
    foreach ($rows as $r) {
        $findings[] = sprintf('high_volume_action: user=%s action=%s count=%d', $r['user'], $r['action'], (int)$r['c']);
    }
} catch (Throwable $e) {
    $findings[] = 'activity_query_error: ' . $e->getMessage();
}

// --- Permissive perms snapshot ------------------------------------------
foreach (['config.php', 'db.php', '.htaccess'] as $sensitive) {
    $p = __DIR__ . '/' . $sensitive;
    if (is_file($p)) {
        $mode = substr(sprintf('%o', fileperms($p)), -3);
        if (in_array($mode, ['666', '777', '776', '775'], true)) {
            $findings[] = "permissive_perms: $sensitive mode=$mode";
        }
    }
}

// --- Output -------------------------------------------------------------
if ($wantHtml) {
    header('Content-Type: text/html; charset=utf-8');
    echo '<!DOCTYPE html><html><head><meta charset="utf-8"><title>Security Audit</title>';
    echo '<style>body{font-family:Arial,sans-serif;background:#f5f6fa;margin:20px}.box{max-width:900px;margin:0 auto;background:#fff;padding:20px;border-radius:8px;box-shadow:0 2px 10px rgba(0,0,0,0.08)}.ok{background:#d4edda;padding:12px;border-radius:6px;color:#155724}.bad{background:#f8d7da;padding:12px;border-radius:6px;color:#721c24}pre{background:#f8f9fa;padding:12px;border-radius:6px;white-space:pre-wrap;font-size:13px}</style></head><body><div class="box">';
    echo '<h1>Security Audit</h1>';
    if (empty($findings)) {
        echo '<div class="ok">No findings in last audit window.</div>';
    } else {
        echo '<div class="bad">' . count($findings) . ' finding(s):</div><pre>';
        foreach ($findings as $f) { echo htmlspecialchars($f) . "\n"; }
        echo '</pre>';
    }
    echo '<p><a href="dashboard.php">&larr; Dashboard</a></p></div></body></html>';
    exit;
}

echo "Security Audit @ " . date('c') . "\n";
if (empty($findings)) {
    echo "OK - no findings.\n";
} else {
    echo "FINDINGS:\n";
    foreach ($findings as $f) { echo "  - $f\n"; }
}

// Telegram alert (only when running as cron and findings present)
if ($isCron && !empty($findings)) {
    $bot  = (string) getConfig('telegram.bot_token', '');
    $chat = (string) getConfig('telegram.chat_id', '');
    $enabled = (bool) getConfig('telegram.enabled', false);
    if ($enabled && $bot !== '' && $chat !== '') {
        $msg = "⚠️ <b>SECURITY AUDIT</b>\n" . count($findings) . " finding(s):\n\n" . implode("\n", array_slice($findings, 0, 20));
        $ch = curl_init("https://api.telegram.org/bot{$bot}/sendMessage");
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, ['chat_id' => $chat, 'text' => $msg, 'parse_mode' => 'HTML']);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, true);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
        curl_setopt($ch, CURLOPT_TIMEOUT, 10);
        curl_exec($ch);
        curl_close($ch);
    }
}
