<?php
declare(strict_types=1);

/**
 * Database connection diagnosis.
 *
 *   php tools/dbcheck.php
 *
 * Works through the connection one layer at a time and says which layer failed,
 * instead of the single "not reachable" message the web app shows.
 */

if (PHP_SAPI !== 'cli') {
    exit("This script runs from the command line only.\n");
}

require_once dirname(__DIR__) . '/config/config.php';

$pass = 0;
$fail = 0;

function ok(string $msg): void    { global $pass; $pass++; echo "  OK    {$msg}\n"; }
function bad(string $msg, string $fix = ''): void
{
    global $fail;
    $fail++;
    echo "  FAIL  {$msg}\n";
    if ($fix !== '') {
        foreach (explode("\n", $fix) as $line) {
            echo "        -> {$line}\n";
        }
    }
}
function info(string $msg): void { echo "        {$msg}\n"; }

echo "\nCompetitor Portal - database check\n";
echo str_repeat('-', 60) . "\n";
echo "PHP " . PHP_VERSION . " (" . PHP_SAPI . ")\n\n";

// ---------------------------------------------------------------- 1. driver
echo "1. PDO driver\n";
if (!extension_loaded('pdo')) {
    bad('PDO is not loaded.', 'apt install php-mysql && systemctl reload apache2');
} elseif (!in_array('mysql', PDO::getAvailableDrivers(), true)) {
    bad('pdo_mysql is not loaded.',
        "apt install php" . PHP_MAJOR_VERSION . "." . PHP_MINOR_VERSION . "-mysql\n"
      . "systemctl reload apache2   (or: systemctl restart php" . PHP_MAJOR_VERSION
      . "." . PHP_MINOR_VERSION . "-fpm)");
} else {
    ok('pdo_mysql available (drivers: ' . implode(', ', PDO::getAvailableDrivers()) . ')');
}

// ---------------------------------------------------------------- 2. config
echo "\n2. Configuration\n";
$localConfig = dirname(__DIR__) . '/config/config.local.php';
if (is_file($localConfig)) {
    ok('config.local.php is present and loaded');
} else {
    info('No config.local.php - using the defaults in config.php');
}

info('DB_HOST = ' . DB_HOST . '   DB_PORT = ' . DB_PORT);
info('DB_NAME = ' . DB_NAME . '   DB_USER = ' . DB_USER);

if (DB_PASS === 'CHANGE_ME' || DB_PASS === '') {
    bad('DB_PASS is still the placeholder.',
        'Set a real password in config/config.local.php');
} else {
    ok('DB_PASS is set (' . strlen(DB_PASS) . ' characters)');
}

if (DB_HOST === '127.0.0.1') {
    info("DB_HOST is an IP, so PDO will use TCP. If MySQL only listens on its");
    info("unix socket, change this to 'localhost'.");
}

// ---------------------------------------------------------------- 3. server
echo "\n3. Reaching the server\n";
$serverUp = false;
try {
    // Connect without naming a database, to separate "server down" from "no such db".
    $dsn = sprintf('mysql:host=%s;port=%d', DB_HOST, DB_PORT);
    $probe = new PDO($dsn, DB_USER, DB_PASS, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    $version = $probe->query('SELECT VERSION()')->fetchColumn();
    ok("Connected as '" . DB_USER . "' - server is " . $version);
    $serverUp = true;
} catch (PDOException $e) {
    $code = $e->getCode();
    $msg  = $e->getMessage();

    if (str_contains($msg, 'Access denied')) {
        bad('The server answered but rejected these credentials.',
            "Check DB_USER / DB_PASS, and that the user exists for this host:\n"
          . "  SELECT user, host FROM mysql.user WHERE user = '" . DB_USER . "';\n"
          . "A user created as '" . DB_USER . "'@'localhost' will not match a TCP\n"
          . "connection from 127.0.0.1 on some setups - create both, or use localhost.");
    } elseif (str_contains($msg, "Can't connect") || str_contains($msg, 'Connection refused')) {
        bad('No MySQL server answered.',
            "systemctl status mysql   (or mariadb)\n"
          . "systemctl start mysql\n"
          . "If it is running, check DB_HOST/DB_PORT and whether it binds to a socket only.");
    } elseif (str_contains($msg, 'No such file or directory')) {
        bad('The unix socket was not found.',
            "MySQL may not be running, or the socket path differs.\n"
          . "Find it with:  mysqladmin variables | grep socket\n"
          . "Then set DB_HOST to '127.0.0.1' to use TCP instead.");
    } else {
        bad('Connection failed: ' . $msg, 'SQLSTATE ' . (string) $code);
    }
}

// ---------------------------------------------------------------- 4. schema
if ($serverUp) {
    echo "\n4. The database itself\n";
    try {
        $dsn = sprintf('mysql:host=%s;port=%d;dbname=%s;charset=%s',
            DB_HOST, DB_PORT, DB_NAME, DB_CHARSET);
        $pdo = new PDO($dsn, DB_USER, DB_PASS, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
        ok("Database '" . DB_NAME . "' opened");

        $expected = ['users', 'companies', 'tags', 'company_tags', 'company_relationships',
                     'products', 'links', 'watches', 'visits', 'notes', 'own_products'];
        $have = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
        $missing = array_diff($expected, $have);

        if ($missing) {
            bad('Missing tables: ' . implode(', ', $missing),
                'mysql -u root -p < database/schema.sql');
        } else {
            ok(count($have) . ' tables present');
        }

        foreach (['v_company_overview', 'v_watch_queue'] as $view) {
            try {
                $pdo->query("SELECT 1 FROM `{$view}` LIMIT 1");
                ok("View {$view} works");
            } catch (PDOException $e) {
                bad("View {$view} is missing or broken.", $e->getMessage());
            }
        }

        if (!$missing) {
            $companies = (int) $pdo->query('SELECT COUNT(*) FROM companies')->fetchColumn();
            $users     = (int) $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();
            $withPw    = (int) $pdo->query(
                'SELECT COUNT(*) FROM users WHERE password_hash IS NOT NULL')->fetchColumn();

            $companies > 0
                ? ok("{$companies} companies loaded")
                : bad('No companies.', 'mysql -u root -p < database/seed_data.sql');

            if ($users === 0) {
                bad('No user accounts.', 'mysql -u root -p < database/seed_data.sql');
            } elseif ($withPw === 0) {
                bad("{$users} accounts, none with a password - nobody can sign in.",
                    'php tools/set_password.php <username> "<passphrase>"');
            } else {
                ok("{$users} accounts, {$withPw} with a password set");
            }
        }

        // Confirm the grants cover what the app actually does.
        try {
            $pdo->query('SELECT 1 FROM users LIMIT 1');
            ok('SELECT permitted');
        } catch (PDOException $e) {
            bad('SELECT refused.', $e->getMessage());
        }

    } catch (PDOException $e) {
        if (str_contains($e->getMessage(), 'Unknown database')) {
            bad("Database '" . DB_NAME . "' does not exist.",
                'mysql -u root -p < database/schema.sql');
        } else {
            bad('Could not open the database: ' . $e->getMessage());
        }
    }
}

// ---------------------------------------------------------------- 5. writable
echo "\n5. Writable paths\n";
foreach (['logs' => LOG_DIR, 'uploads' => UPLOAD_DIR] as $label => $dir) {
    if (!is_dir($dir)) {
        bad("{$label}/ does not exist.", "mkdir -p {$dir}");
    } elseif (!is_writable($dir)) {
        bad("{$label}/ is not writable.",
            "chown -R www-data:www-data {$dir}\nchmod 775 {$dir}");
    } else {
        ok("{$label}/ is writable");
    }
}

// ---------------------------------------------------------------- summary
echo "\n" . str_repeat('-', 60) . "\n";
echo $fail === 0
    ? "All {$pass} checks passed. The portal should load.\n\n"
    : "{$fail} problem(s) found, {$pass} checks passed. Fix the FAIL lines above.\n\n";

exit($fail === 0 ? 0 : 1);
