<?php
$cnf = file_get_contents("/root/.my.cnf");
preg_match("/password=\"?(.*)\"?/", $cnf, $matches);

$pass = str_replace('"', "", $matches[1]);

$x = new mysqli('localhost', 'root', $pass, 'mysql');

if (!$x) {
    die("MySQL error");
}

$hostname = gethostname();
$hostname = $x->real_escape_string($hostname);

$sql = "SELECT DISTINCT host from user where host != '127.0.0.1' and host != 'localhost' and host != '$hostname'";

$r = $x->query($sql);
if (!$r) {
    die("MySQL error");
}

$csfallowPath = "/etc/csf/remote_mysql.allow";
$csfallow = file_exists($csfallowPath) ? file_get_contents($csfallowPath) : '';
$allows = explode("\n", $csfallow);

$addition = '';
$currentRules = [];
$removedSomething = false;

echo "Start...\n";
while ($row = $r->fetch_assoc()) {
    $host = $row['host'] ?? ($row['Host'] ?? '');

    if (empty($host) || strpos($host, '*') !== false) {
        continue;
    }

    echo "Check $host - ";

    if (preg_match("/^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.%$/", $host)) {
        $ip = str_replace(".%", ".0/24", $host);
        echo "Found a /24...$ip ";
    } elseif (preg_match("/^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$/", $host)) {
        $ip = $host;
    } else {
        if (!preg_match("/([a-zA-Z0-9_-]){5,60}/", $host)) {
            echo "Invalid host $host, skip\r\n";
            continue;
        }

        $ip = gethostbyname($host);
        if (empty($ip) || $ip === $host || !preg_match("/^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$/", $ip)) {
            echo "Does not resolve, skip\r\n";
            continue;
        }

        echo "Resolves to $ip. ";
    }

    $rule = "tcp:in:d=3306:s=$ip";
    $currentRules[] = $rule;

    if (array_search($rule, $allows) !== FALSE) {
        echo "Already added\n";
    } else {
        echo "Adding $rule\n";
        $addition .= $rule . "\n";
    }
}

// Cleanup: remove obsolete entries
$updatedRules = [];
foreach ($allows as $line) {
    $trimmed = trim($line);
    if ($trimmed === '') continue;

    if (!in_array($trimmed, $currentRules)) {
        echo "Removed rule $trimmed - no longer exists in MySQL\n";
        $removedSomething = true;
    } else {
        $updatedRules[] = $trimmed;
    }
}

// Append new entries if needed
if (!empty(trim($addition))) {
    $updatedRules = array_merge($updatedRules, explode("\n", trim($addition)));
    $updatedRules = array_unique(array_filter(array_map('trim', $updatedRules)));
}

// Save final file
file_put_contents($csfallowPath, implode("\n", $updatedRules) . "\n");

// Restart CSF if needed
if (!empty($addition) || $removedSomething) {
    echo "\n-> Changes applied, restarting CSF...\n";
    shell_exec("/usr/sbin/csf -r");
    echo "-> CSF restarted.\n";
} else {
    echo "\n-> No changes. CSF remains untouched.\n";
}