<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>IP查询管理系统 - 白名单配置</title>
<style>
*{margin:0;padding:0;box-sizing:border-box;font-family:"Microsoft Yahei",Arial,sans-serif}
body{background:#f5f7fa;padding:20px}
.container{max-width:1200px;margin:0 auto;background:#fff;padding:30px;border-radius:8px;box-shadow:0 2px 10px rgba(0,0,0,0.1)}
h1{color:#333;margin-bottom:20px;border-bottom:2px solid #409eff;padding-bottom:10px}
h2{color:#409eff;margin:30px 0 15px;font-size:18px}
table{width:100%;border-collapse:collapse;margin:10px 0}
th,td{padding:12px 15px;text-align:left;border:1px solid #e6e6e6}
th{background:#f8f9fa;color:#333;font-weight:600}
tr:nth-child(even){background:#fafafa}
.empty{color:#999;padding:20px;text-align:center}
.btn-refresh,.btn-export{background:#409eff;color:#fff;border:none;padding:10px 20px;border-radius:4px;cursor:pointer;font-size:14px;margin-bottom:20px;margin-right:10px}
.btn-export{background:#67c23a}
.btn-refresh:hover{background:#66b1ff}
.btn-export:hover{background:#85ce61}
.success{color:#67c23a;padding:10px;background:#f0f9eb;border-radius:4px;margin:10px 0}
.error{color:#f56c6c;padding:10px;background:#fef0f0;border-radius:4px;margin:10px 0}
.cmd-box{background:#f8f9fa;padding:15px;border-radius:4px;margin:10px 0;overflow-x:auto;font-family:monospace;color:#333}
.ip-list{margin:10px 0;padding:10px;background:#f8f9fa;border-radius:4px;line-height:1.8}
</style>
</head>
<body>
<div class="container">
<h1>IP查询管理系统</h1>
<button class="btn-refresh" onclick="location.reload()">刷新数据</button>

<?php
// 数据库配置
$db_host = '127.0.0.1';
$db_port = 3306;
$db_name = 'root';
$db_user = 'root';
$db_password = '123..00';
$db_charset = 'utf8mb4';

// 自动更新防火墙函数
function addFirewallRules($ips) {
    $ipv4_ips = [];
    $ipv6_ips = [];
    
    foreach ($ips as $ip) {
        if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
            $ipv4_ips[] = $ip;
        } elseif (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
            $ipv6_ips[] = $ip;
        }
    }

    $cmds = [];

    // IPv4规则
    if (!empty($ipv4_ips)) {
        $cmds[] = "iptables -F";
        $cmds[] = "iptables -A INPUT -p tcp --dport 9999 -j DROP";
        foreach ($ipv4_ips as $ip) {
            $cmds[] = "iptables -A INPUT -p tcp --dport 9999 -s {$ip} -j ACCEPT";
        }
        $cmds[] = "iptables-save > /etc/sysconfig/iptables";
        $cmds[] = "systemctl restart iptables";
    }

    // IPv6规则
    if (!empty($ipv6_ips)) {
        $cmds[] = "ip6tables -F";
        $cmds[] = "ip6tables -A INPUT -p tcp --dport 9999 -j DROP";
        foreach ($ipv6_ips as $ip) {
            $cmds[] = "ip6tables -A INPUT -p tcp --dport 9999 -s {$ip} -j ACCEPT";
        }
        $cmds[] = "ip6tables-save > /etc/sysconfig/ip6tables";
        $cmds[] = "systemctl restart ip6tables";
    }

    foreach ($cmds as $c) {
        exec($c, $output, $return_var);
        if ($return_var !== 0) {
            echo "<div class='error'>执行失败: $c</div>";
        }
    }

    echo "<div class='success'>✅ 防火墙规则已更新</div>";
}


// 展示用户登录IP归类
try {
    $dsn = "mysql:host=$db_host;port=$db_port;dbname=$db_name;charset=$db_charset";
    $pdo = new PDO($dsn, $db_user, $db_password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT u.username, l.ip, MAX(l.login_at) AS last_login_time
            FROM user_login_log l
            LEFT JOIN users u ON l.user_id = u.id
            GROUP BY u.username, l.ip
            ORDER BY u.username, last_login_time DESC";
    $stmt = $pdo->query($sql);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $user_ips = [];
    foreach ($rows as $r) {
        $user = $r['username'] ?: '未知用户';
        $ip = $r['ip'];
        $last_time = $r['last_login_time'];
        $user_ips[$user][] = ['ip' => $ip, 'last' => $last_time];
    }
?>

<h2>1. 用户登录IP 归类展示（按用户+IP去重）</h2>
<table>
<tr>
    <th>用户名</th>
    <th>使用过的IP（去重）</th>
    <th>最近登录时间</th>
</tr>
<?php foreach ($user_ips as $username => $ips): ?>
    <?php foreach ($ips as $i => $item): ?>
    <tr>
        <?php if ($i === 0): ?>
        <td rowspan="<?= count($ips) ?>"><?= htmlspecialchars($username) ?></td>
        <?php endif; ?>
        <td><?= htmlspecialchars($item['ip']) ?></td>
        <td><?= htmlspecialchars($item['last']) ?></td>
    </tr>
    <?php endforeach; ?>
<?php endforeach; ?>
</table>

<?php
} catch(PDOException $e) {
    echo '<div class="error">数据库查询失败：'.$e->getMessage().'</div>';
}
?>

<!-- 提取IP表单 -->
<form id="exportForm" method="post" style="display:none;">
    <input type="hidden" name="export_ips" value="1">
</form>

</div>
</body>
</html>