<?php
/**
 * OTIS Database Validation Script — Local Dev
 * Run once, then delete
 */
define('ROOT_PATH', realpath(__DIR__));

// Force local credentials for validation
$db = new PDO(
    "mysql:host=localhost;dbname=otis;charset=utf8mb4",
    "root", "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
);
echo "✅ DB Connection: OK\n\n";

// Get all tables
$stmt = $db->query("SHOW TABLES");
$actualTables = array_column($stmt->fetchAll(PDO::FETCH_NUM), 0);

// Expected core tables
$expectedTables = [
    'users', 'activity_logs',
    'health_records', 'health_medications', 'health_vitals', 'health_fasting_sessions',
    'finance_incomes', 'finance_expenses', 'finance_saving_goals', 'finance_installments',
    'finance_debts', 'finance_investments', 'finance_categories', 'finance_accounts',
    'calendar_events', 'calendar_categories', 'calendar_time_blocks',
    'todo_tasks', 'todo_categories',
    'journal_entries',
    'behavior_habits', 'behavior_logs',
    'spiritual_prayers', 'spiritual_quran', 'spiritual_azkar',
    'education_skills', 'education_courses',
    'vault_entries',
    'monthly_plans',
];

echo "📋 ALL TABLES IN DB:\n";
foreach ($actualTables as $t) echo "  - $t\n";

echo "\n📌 EXPECTED TABLES CHECK:\n";
$missing = [];
foreach ($expectedTables as $t) {
    if (in_array($t, $actualTables)) {
        echo "  ✅ $t\n";
    } else {
        echo "  ❌ MISSING: $t\n";
        $missing[] = $t;
    }
}

echo "\nTotal tables in DB: " . count($actualTables) . "\n";
echo "Missing count: " . count($missing) . "\n";

// Check for orphaned data
echo "\n🔍 QUICK DATA COUNT:\n";
$tables_to_count = ['calendar_events', 'todo_tasks', 'finance_expenses', 'journal_entries', 'behavior_habits'];
foreach ($tables_to_count as $t) {
    try {
        $cnt = $db->query("SELECT COUNT(*) FROM $t")->fetchColumn();
        echo "  $t: $cnt rows\n";
    } catch(Exception $e) {
        echo "  $t: ERROR - table not found\n";
    }
}

echo "\n✅ Validation complete.\n";
