<?php
/**
 * Database Tables Check Script
 * Shows which tables exist and which are missing
 */

echo "🔍 DATABASE TABLES CHECK\n";
echo "========================\n\n";

require_once __DIR__ . '/src/db.php';

$pdo = getPDO();

// List of tables the application expects
$expectedTables = [
    'companies',
    'users',
    'user_sessions',
    'clients',
    'proposals',
    'contracts',
    'invoices',
    'invoice_items',
    'subscription_plans',
    'subscriptions',
    'usage_tracking',
    'payment_methods',
    'billing_invoices',
    // Tables that might be missing
    'templates',
    'modules',
    'invoice_payments',
    'invoice_settings',
    'contract_templates',
    'tax_brackets',
    'labels'
];

echo "📊 CHECKING TABLES\n";
echo "==================\n";

$existingTables = [];
$missingTables = [];

foreach ($expectedTables as $table) {
    try {
        $stmt = $pdo->query("SELECT 1 FROM `$table` LIMIT 1");
        $count = $pdo->query("SELECT COUNT(*) as count FROM `$table`")->fetch()['count'];
        $existingTables[] = $table;
        echo "✅ $table: $count records\n";
    } catch (PDOException $e) {
        $missingTables[] = $table;
        echo "❌ $table: MISSING\n";
    }
}

echo "\n📈 SUMMARY\n";
echo "==========\n";
echo "✅ Existing tables: " . count($existingTables) . "\n";
echo "❌ Missing tables: " . count($missingTables) . "\n";

if (!empty($missingTables)) {
    echo "\n❌ MISSING TABLES:\n";
    echo "==================\n";
    foreach ($missingTables as $table) {
        echo "- $table\n";
    }
    
    echo "\n🔧 SOLUTIONS:\n";
    echo "=============\n";
    echo "1. Run the MySQL setup script: php final_mysql_setup.php\n";
    echo "2. Or create missing tables manually\n";
    echo "3. Or update code to handle missing tables gracefully\n";
}

if (!empty($existingTables)) {
    echo "\n✅ EXISTING TABLES:\n";
    echo "==================\n";
    foreach ($existingTables as $table) {
        $count = $pdo->query("SELECT COUNT(*) as count FROM `$table`")->fetch()['count'];
        echo "- $table: $count records\n";
    }
}

echo "\n🎯 RECOMMENDATIONS:\n";
echo "==================\n";

if (count($missingTables) > 5) {
    echo "⚠️  Too many missing tables. Run: php final_mysql_setup.php\n";
} elseif (count($missingTables) > 0) {
    echo "⚠️  Some tables missing. Check if application can work without them\n";
} else {
    echo "✅ All expected tables exist!\n";
}

// Check if we have the minimum required tables
$requiredTables = ['companies', 'users', 'clients', 'proposals', 'contracts', 'invoices'];
$hasRequired = true;
foreach ($requiredTables as $table) {
    if (!in_array($table, $existingTables)) {
        $hasRequired = false;
        break;
    }
}

if ($hasRequired) {
    echo "✅ All required tables exist - application should work!\n";
} else {
    echo "❌ Missing required tables - application will have errors\n";
}

echo "\n🚀 NEXT STEPS:\n";
echo "==============\n";
if (count($missingTables) > 0) {
    echo "1. Run: php final_mysql_setup.php to create all tables\n";
    echo "2. Or continue with missing tables (limited functionality)\n";
} else {
    echo "✅ Database is ready! Test the application at http://localhost:8000/index.php\n";
}

echo "\n" . str_repeat("=", 50) . "\n";
echo "TABLE CHECK COMPLETE\n";
echo str_repeat("=", 50) . "\n";
