<?php
/**
 * Oracle Connection Diagnostic
 * Run: D:\laragon\bin\php\php-8.3.30-Win32-vs16-x64\php.exe D:\laragon\app\laraveladmin\check_oracle.php
 */

echo "=== Oracle Connection Diagnostic ===\n\n";

// 1. PHP extensions
echo "[1] PHP Extensions:\n";
echo "    oci8    : " . (extension_loaded('oci8')    ? "LOADED ✓" : "NOT LOADED ✗") . "\n";
echo "    pdo_oci : " . (extension_loaded('pdo_oci') ? "LOADED ✓" : "NOT LOADED ✗") . "\n\n";

if (!extension_loaded('oci8') && !extension_loaded('pdo_oci')) {
    echo "[FIX] Neither oci8 nor pdo_oci is loaded.\n";
    echo "      Check D:\\laragon\\bin\\php\\php-8.3.30-Win32-vs16-x64\\php.ini has:\n";
    echo "        extension=oci8_19\n";
    echo "        extension=pdo_oci\n\n";
    echo "      Also ensure Oracle Instant Client DLLs (oci.dll etc.) are in:\n";
    echo "        D:\\laragon\\bin\\php\\php-8.3.30-Win32-vs16-x64\\\n";
    echo "      Run setup_oracle.php to install them automatically.\n";
    exit(1);
}

// 2. Check Oracle IC DLLs in PHP dir
echo "[2] Oracle DLLs in PHP directory:\n";
$phpDir = 'D:\\laragon\\bin\\php\\php-8.3.30-Win32-vs16-x64';
$keyDlls = ['oci.dll', 'oraociei19.dll', 'oraociicus19.dll', 'orannzsbb19.dll'];
$found = 0;
foreach ($keyDlls as $dll) {
    $exists = file_exists($phpDir . '\\' . $dll);
    echo "    $dll : " . ($exists ? "FOUND ✓" : "missing") . "\n";
    if ($exists) $found++;
}
if ($found === 0) {
    echo "\n    [FIX] Oracle Instant Client DLLs are missing.\n";
    echo "    Run setup_oracle.php to download and install them:\n";
    echo "      php D:\\laragon\\app\\laraveladmin\\setup_oracle.php\n";
    exit(1);
}

// 3. PDO Oracle connection
echo "\n[3] Testing Oracle DB connection:\n";
echo "    Host    : 161.97.117.207:1521\n";
echo "    Database: ORCL\n";
echo "    Username: C##FILE\n\n";

$dsn  = 'oci:dbname=161.97.117.207:1521/ORCL;charset=AL32UTF8';
$user = 'C##FILE';
$pass = 'ozone';

try {
    $pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_TIMEOUT => 10]);
    echo "    CONNECTION SUCCESS ✓\n\n";

    // 4. Test LAW_FIRM_MASTER table
    echo "[4] Querying LAW_FIRM_MASTER:\n";
    try {
        $stmt = $pdo->query("SELECT * FROM LAW_FIRM_MASTER WHERE ROWNUM <= 3");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if ($rows) {
            echo "    Columns: " . implode(', ', array_keys($rows[0])) . "\n\n";
            foreach ($rows as $i => $row) {
                echo "    Row " . ($i+1) . ":\n";
                foreach ($row as $col => $val) {
                    echo "      $col = $val\n";
                }
                echo "\n";
            }
        } else {
            echo "    Table is empty or no rows returned.\n";
        }
    } catch (Exception $e) {
        echo "    TABLE ERROR: " . $e->getMessage() . "\n";
    }

} catch (PDOException $e) {
    $msg = $e->getMessage();
    echo "    CONNECTION FAILED ✗\n";
    echo "    Error: $msg\n\n";

    // Diagnose common errors
    if (str_contains($msg, 'ORA-12541') || str_contains($msg, 'TNS:no listener')) {
        echo "    [ISSUE] Server not reachable or wrong port.\n";
        echo "    [FIX]   Check host 161.97.117.207 and port 1521 are accessible.\n";
    } elseif (str_contains($msg, 'ORA-12154') || str_contains($msg, 'TNS:could not resolve')) {
        echo "    [ISSUE] TNS resolution failed — check DSN format.\n";
    } elseif (str_contains($msg, 'ORA-01017') || str_contains($msg, 'invalid username/password')) {
        echo "    [ISSUE] Wrong username or password.\n";
        echo "    [FIX]   Verify C##FILE / ozone credentials.\n";
    } elseif (str_contains($msg, 'ORA-28000') || str_contains($msg, 'account is locked')) {
        echo "    [ISSUE] Oracle account C##FILE is locked.\n";
        echo "    [FIX]   Ask DB admin to run: ALTER USER \"C##FILE\" ACCOUNT UNLOCK;\n";
    } elseif (str_contains($msg, 'could not find driver') || str_contains($msg, 'driver not found')) {
        echo "    [ISSUE] pdo_oci extension not loaded properly.\n";
        echo "    [FIX]   Run setup_oracle.php then restart Laragon.\n";
    }
}

echo "\n=== Done ===\n";
