<?php
require_once 'db_config.php';

echo "Starting data validation...\n\n";

// 1. Fetch all contacts
$contacts = [];
$stmt = $db->query("SELECT id, data FROM contacts");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $c = json_decode($row['data'], true);
    if ($c) {
        $contacts[$c['id']] = $c['name'] ?? 'Unknown';
    }
}
echo "Total Contacts Loaded: " . count($contacts) . "\n";

// 2. Fetch all accounts for validation
$accounts = [];
$stmt = $db->query("SELECT id, data FROM accounts");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $a = json_decode($row['data'], true);
    if ($a) {
        $accounts[$a['id']] = $a['name'] ?? 'Unknown';
    }
}
echo "Total Accounts Loaded: " . count($accounts) . "\n\n";

// 3. Analyze Journal Entries
$stmt = $db->query("SELECT id, data FROM journal_entries");
$total_entries = 0;
$errors = [];

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $je_id = $row['id'];
    $je = json_decode($row['data'], true);
    $total_entries++;
    
    if (!$je) {
        $errors[] = "JE #$je_id: Invalid JSON data.";
        continue;
    }
    
    $total_debit = 0;
    $total_credit = 0;
    
    if (!isset($je['rows']) || !is_array($je['rows'])) {
        $errors[] = "JE #$je_id: Missing or invalid 'rows' array.";
        continue;
    }
    
    foreach ($je['rows'] as $idx => $r) {
        $debit = (float)($r['debit'] ?? 0);
        $credit = (float)($r['credit'] ?? 0);
        $total_debit += $debit;
        $total_credit += $credit;
        
        // Check account existence
        if (!isset($r['account_id']) || empty($r['account_id'])) {
            $errors[] = "JE #$je_id (Row $idx): Missing account_id. Debit: $debit, Credit: $credit";
        } else if (!isset($accounts[$r['account_id']])) {
            $errors[] = "JE #$je_id (Row $idx): Account ID '{$r['account_id']}' does not exist in accounts table.";
        }
        
        // Check contact existence if provided
        if (isset($r['contact_id']) && !empty($r['contact_id'])) {
            if (!isset($contacts[$r['contact_id']])) {
                $errors[] = "JE #$je_id (Row $idx): Contact ID '{$r['contact_id']}' does not exist in contacts table.";
            }
        }
    }
    
    // Check balance
    if (abs($total_debit - $total_credit) > 0.01) {
        $errors[] = "JE #$je_id: Unbalanced entry! Total Debit: $total_debit, Total Credit: $total_credit";
    }
    
    // Check automatic entries specifically (often have a source type)
    if (isset($je['type']) && $je['type'] !== 'manual') {
        // Just flag suspicious stuff in automatic entries if any
    }
}

echo "Total Journal Entries Scanned: $total_entries\n\n";

if (empty($errors)) {
    echo "No errors found in journal entries!\n";
} else {
    echo "Found " . count($errors) . " errors:\n";
    foreach (array_slice($errors, 0, 50) as $err) {
        echo "- $err\n";
    }
    if (count($errors) > 50) {
        echo "... and " . (count($errors) - 50) . " more errors.\n";
    }
}
?>
