<?php
// FILE: nat_logs.php (Strict User Filtering Fix)

ini_set('memory_limit', '2048M');
date_default_timezone_set('Asia/Kolkata');

require 'db.php'; 
session_start();

if (!isset($_SESSION['username']) || $_SESSION['username'] !== 'admin') { die("Access Denied"); }

// ClickHouse Helper
function query_clickhouse($query) {
    $url = 'http://127.0.0.1:8123/';
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $query . " FORMAT JSON");
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($ch);
    curl_close($ch);
    return json_decode($response, true);
}

// --- PAGINATION & INPUTS ---
$page = isset($_REQUEST['page']) ? (int)$_REQUEST['page'] : 1;
if ($page < 1) $page = 1;
// Search karte waqt hum limit badha denge taaki filter hone ke baad bhi data dikhe
$limit = 100; 
$offset = ($page - 1) * $limit;

// Default Dates
$default_from_date = date('Y-m-d');
$default_to_date = date('Y-m-d');

// Inputs
$f_date = $_REQUEST['f_date'] ?? $default_from_date;
$t_date = $_REQUEST['t_date'] ?? $default_to_date;
$f_hour = $_REQUEST['f_hour'] ?? '00';
$f_min  = $_REQUEST['f_min']  ?? '00';
$t_hour = $_REQUEST['t_hour'] ?? '23';
$t_min  = $_REQUEST['t_min']  ?? '59';

$user_name = trim($_REQUEST['user_name'] ?? '');
$src_ip    = trim($_REQUEST['src_ip'] ?? '');
$src_port  = trim($_REQUEST['src_port'] ?? '');
$dst_ip    = trim($_REQUEST['dst_ip'] ?? '');
$dst_port  = trim($_REQUEST['dst_port'] ?? '');
$proto     = $_REQUEST['proto'] ?? 'any';

// --- QUERY BUILDER ---
$where = [];

// 1. Date & Time
if ($f_date == $t_date) {
    $where[] = "log_date = '$f_date'";
    if ("$f_hour:$f_min" !== "00:00" || "$t_hour:$t_min" !== "23:59") {
        $f_time = "$f_hour:$f_min:00";
        $t_time = "$t_hour:$t_min:59";
        $where[] = "log_time >= '$f_time' AND log_time <= '$t_time'";
    }
} else {
    $where[] = "log_date >= '$f_date' AND log_date <= '$t_date'";
}

// 2. IP & Port Filters
if (!empty($src_ip))    $where[] = "src_ip = '$src_ip'";
if (!empty($src_port))  $where[] = "src_port = $src_port";
if (!empty($dst_ip))    $where[] = "dst_ip = '$dst_ip'";
if (!empty($dst_port))  $where[] = "dst_port = $dst_port";
if ($proto !== 'any')   $where[] = "proto = '" . strtoupper($proto) . "'";

// 3. User Search (Optimization: Pre-filter by IPs)
if (!empty($user_name)) {
    $sub_where = ["user ILIKE '%$user_name%'"];
    try {
        $stmt = $pdo->prepare("SELECT framedipaddress FROM radacct WHERE username LIKE ? GROUP BY framedipaddress");
        $stmt->execute(["%$user_name%"]);
        $ips = $stmt->fetchAll(PDO::FETCH_COLUMN);
        if (!empty($ips)) {
            $ip_list = "'" . implode("','", $ips) . "'";
            $sub_where[] = "src_ip IN ($ip_list)";
        }
    } catch(Exception $e) {}
    $where[] = "(" . implode(" OR ", $sub_where) . ")";
}

$where_sql = implode(' AND ', $where);
if(empty($where_sql)) $where_sql = "1=1"; 

// --- EXECUTE ---
// Note: Agar user search kar raha hai to hum thoda zyada data uthayenge (LIMIT 500)
// taaki filter karne ke baad khali page na dikhe.
$fetch_limit = (!empty($user_name)) ? 500 : $limit;
$sql = "SELECT * FROM nat_logs.records WHERE $where_sql ORDER BY log_date DESC, log_time DESC LIMIT $fetch_limit OFFSET $offset";

$data = [];
$result = query_clickhouse($sql);
$final_rows = []; // Isme hum filter karke data rakhenge

if (isset($result['data'])) {
    $raw_data = $result['data'];
    $ip_cache = []; 

    foreach ($raw_data as $row) {
        $log_ts_str = $row['log_date'] . ' ' . $row['log_time'];
        $check_ip = trim($row['src_ip']); 
        
        $detected_user = '-';
        
        // Cache Check
        if (isset($ip_cache[$check_ip])) {
            $detected_user = $ip_cache[$check_ip];
        } else {
            $found_user = null;
            try {
                // Method 1: Strict Match
                $stmt = $pdo->prepare("SELECT username FROM radacct WHERE framedipaddress = ? AND acctstarttime <= ? AND (acctstoptime >= ? OR acctstoptime IS NULL) LIMIT 1");
                $stmt->execute([$check_ip, $log_ts_str, $log_ts_str]);
                $found_user = $stmt->fetchColumn();

                // Method 2: History Match
                if (!$found_user) {
                    $stmt = $pdo->prepare("SELECT username FROM radacct WHERE framedipaddress = ? ORDER BY acctstarttime DESC LIMIT 1");
                    $stmt->execute([$check_ip]);
                    $found_user = $stmt->fetchColumn();
                }

                if ($found_user) {
                    $detected_user = $found_user;
                }
                $ip_cache[$check_ip] = $detected_user; // Store raw username in cache
            } catch (Exception $e) { }
        }

        // --- STRICT FILTER LOGIC ---
        // Agar user search kar raha hai, aur detected name match nahi hua, to SKIP karo.
        if (!empty($user_name)) {
            if (stripos($detected_user, $user_name) === false) {
                continue; // Ye row skip kar do (Jaise Neeraj wala case)
            }
        }

        // HTML Formatting for display
        $display_user = ($detected_user !== '-') ? "<span class='fw-bold text-primary'>$detected_user</span>" : "<span class='text-muted'>-</span>";
        
        // Update Row
        $row['user'] = $display_user;
        $final_rows[] = $row;
        
        // Agar humne user ko dikhane layak 100 rows bhar li hain, to break kar do
        if (count($final_rows) >= $limit) break;
    }
}

function get_page_link($p) {
    $params = $_GET;
    $params['page'] = $p;
    return '?' . http_build_query($params);
}

require 'header.php';
?>

<style>
    .dma-box { background-color: #eef1f6; border: 1px solid #dcdcdc; padding: 15px; font-family: Arial, sans-serif; font-size: 13px; }
    .dma-header { color: blue; font-weight: bold; font-size: 16px; margin-bottom: 5px; border-bottom: 1px solid #a0a0a0; padding-bottom: 2px; display: inline-block; }
    .dma-label { font-weight: bold; text-align: right; display: inline-block; width: 100px; margin-right: 5px; color: #333; }
    .dma-input { border: 1px solid #7f9db9; padding: 2px; font-size: 12px; width: 160px; }
    .dma-input-small { border: 1px solid #7f9db9; padding: 2px; font-size: 12px; width: 60px; }
    .dma-row { margin-bottom: 6px; }
    .dma-hint { color: #666; font-style: italic; margin-left: 5px; font-size: 11px; }
    .dma-btn { background: #f0f0f0; border: 1px solid #888; padding: 3px 15px; font-weight: bold; cursor: pointer; color: #000; font-size: 13px; border-radius: 3px; }
    .dma-btn:hover { background: #ddd; }
</style>

<div class="container-fluid mt-3">
    
    <div class="dma-header">Find connection data</div>
    <div class="dma-box">
        <form method="GET">
            <div class="dma-row">
                <span class="dma-label">User name:</span>
                <input type="text" name="user_name" class="dma-input" value="<?php echo htmlspecialchars($user_name); ?>">
                <span class="dma-hint">(user name)</span>
            </div>
            
            <div class="dma-row">
                <span class="dma-label">Source IP:</span>
                <input type="text" name="src_ip" class="dma-input" style="width:120px;" value="<?php echo htmlspecialchars($src_ip); ?>">
                <span style="font-weight:bold; margin:0 5px;">port:</span>
                <input type="text" name="src_port" class="dma-input" style="width:50px;" value="<?php echo htmlspecialchars($src_port); ?>">
            </div>

            <div class="dma-row">
                <span class="dma-label">Destination IP:</span>
                <input type="text" name="dst_ip" class="dma-input" style="width:120px;" value="<?php echo htmlspecialchars($dst_ip); ?>">
                <span style="font-weight:bold; margin:0 5px;">port:</span>
                <input type="text" name="dst_port" class="dma-input" style="width:50px;" value="<?php echo htmlspecialchars($dst_port); ?>">
            </div>

            <div class="dma-row">
                <span class="dma-label" style="vertical-align: top;">Protocol:</span>
                <div style="display:inline-block;">
                    <label><input type="radio" name="proto" value="any" <?php if($proto=='any') echo 'checked'; ?>> <span style="font-weight:bold; color:blue;">Any</span></label><br>
                    <label><input type="radio" name="proto" value="tcp" <?php if($proto=='tcp') echo 'checked'; ?>> TCP</label><br>
                    <label><input type="radio" name="proto" value="udp" <?php if($proto=='udp') echo 'checked'; ?>> UDP</label>
                </div>
            </div>

            <div class="dma-row">
                <span class="dma-label">From date:</span>
                <input type="date" name="f_date" class="dma-input" style="width:110px;" value="<?php echo $f_date; ?>">
                <select name="f_hour" class="dma-input-small"><?php for($i=0;$i<=23;$i++) { $v=sprintf("%02d",$i); $sel=($f_hour==$v)?'selected':''; echo "<option value='$v' $sel>$v</option>"; } ?></select>
                <select name="f_min" class="dma-input-small"><?php for($i=0;$i<=59;$i++) { $v=sprintf("%02d",$i); $sel=($f_min==$v)?'selected':''; echo "<option value='$v' $sel>$v</option>"; } ?></select>
            </div>

            <div class="dma-row">
                <span class="dma-label">To date:</span>
                <input type="date" name="t_date" class="dma-input" style="width:110px;" value="<?php echo $t_date; ?>">
                <select name="t_hour" class="dma-input-small"><?php for($i=0;$i<=23;$i++) { $v=sprintf("%02d",$i); $sel=($t_hour==$v)?'selected':''; echo "<option value='$v' $sel>$v</option>"; } ?></select>
                <select name="t_min" class="dma-input-small"><?php for($i=0;$i<=59;$i++) { $v=sprintf("%02d",$i); $sel=($t_min==$v)?'selected':''; echo "<option value='$v' $sel>$v</option>"; } ?></select>
            </div>

            <div class="dma-row" style="margin-top:15px; text-align:center; width: 450px;">
                <button type="submit" class="dma-btn">Generate report</button>
            </div>
        </form>
    </div>

    <?php if(!empty($final_rows)): ?>
    <div class="card mt-4 shadow-sm">
        <div class="card-header bg-white border-bottom fw-bold d-flex justify-content-between align-items-center">
            <span>Results</span>
            <div>
                <?php if($page > 1): ?>
                <a href="<?php echo get_page_link($page-1); ?>" class="btn btn-outline-secondary btn-sm me-1">&laquo; Prev</a>
                <?php endif; ?>
                <span class="badge bg-light text-dark border mx-2">Page <?php echo $page; ?></span>
                <a href="<?php echo get_page_link($page+1); ?>" class="btn btn-primary btn-sm">Next &raquo;</a>
            </div>
        </div>
        <div class="table-responsive">
            <table class="table table-sm table-striped table-bordered mb-0" style="font-size: 12px;">
                <thead class="table-dark">
                    <tr><th>Date</th><th>Time</th><th>Router</th><th>User</th><th>Proto</th><th>Src IP</th><th>Port</th><th>Dst IP</th><th>Port</th></tr>
                </thead>
                <tbody>
                    <?php foreach($final_rows as $row): ?>
                    <tr>
                        <td><?php echo $row['log_date']; ?></td>
                        <td class="fw-bold"><?php echo $row['log_time']; ?></td>
                        <td><?php echo $row['router']; ?></td>
                        <td><?php echo $row['user']; ?></td>
                        <td><?php echo $row['proto']; ?></td>
                        <td class="text-primary font-monospace"><?php echo $row['src_ip']; ?></td>
                        <td><?php echo $row['src_port']; ?></td>
                        <td class="text-danger font-monospace"><?php echo $row['dst_ip']; ?></td>
                        <td><?php echo $row['dst_port']; ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    <?php elseif(isset($_REQUEST['user_name'])): ?>
        <div class="alert alert-warning mt-3 text-center">No records found matching "<b><?php echo htmlspecialchars($user_name); ?></b>".</div>
    <?php endif; ?>

</div>
<?php require 'footer.php'; ?>