<?php
/**
 * Dashboard Cards Diagnostic
 * GHAMECC Choir Management Platform
 */

// Display all errors for debugging
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

echo "<h1>Dashboard Cards Diagnostic</h1>";

// Include database connection
require_once 'config/database.php';

// Check if database connection is working
echo "<h2>Database Connection</h2>";
if (isset($conn) && $conn instanceof mysqli) {
    if ($conn->connect_error) {
        echo "<p style='color:red'>✗ Database connection failed: " . htmlspecialchars($conn->connect_error) . "</p>";
    } else {
        echo "<p style='color:green'>✓ Database connection successful</p>";
        
        // Check database credentials being used
        echo "<h2>Database Credentials</h2>";
        echo "<p>Host: " . htmlspecialchars($host) . "</p>";
        echo "<p>Username: " . htmlspecialchars($username) . "</p>";
        echo "<p>Database: " . htmlspecialchars($database) . "</p>";
        
        // Check if tables exist
        echo "<h2>Table Existence Check</h2>";
        $tables = ['members', 'societies', 'circuits', 'dioceses'];
        
        foreach ($tables as $table) {
            $result = $conn->query("SHOW TABLES LIKE '$table'");
            if ($result && $result->num_rows > 0) {
                echo "<p style='color:green'>✓ Table '$table' exists</p>";
                
                // Check if table has data
                $countResult = $conn->query("SELECT COUNT(*) as count FROM $table");
                if ($countResult && $row = $countResult->fetch_assoc()) {
                    echo "<p style='margin-left:20px;'>- Records: " . $row['count'] . "</p>";
                }
            } else {
                echo "<p style='color:red'>✗ Table '$table' does not exist</p>";
            }
        }
        
        // Test the actual queries used in dashboard cards
        echo "<h2>Dashboard Card Queries Test</h2>";
        
        // Test member count query
        echo "<h3>Members Card</h3>";
        $memberQuery = "SELECT COUNT(*) as count FROM members";
        $memberResult = $conn->query($memberQuery);
        if ($memberResult) {
            $memberCount = $memberResult->fetch_assoc();
            echo "<p>Query: " . htmlspecialchars($memberQuery) . "</p>";
            echo "<p>Result: " . ($memberCount ? $memberCount['count'] : "No result") . "</p>";
        } else {
            echo "<p style='color:red'>✗ Query failed: " . htmlspecialchars($conn->error) . "</p>";
            echo "<p>Query: " . htmlspecialchars($memberQuery) . "</p>";
        }
        
        // Test society count query
        echo "<h3>Societies Card</h3>";
        $societyQuery = "SELECT COUNT(*) as count FROM societies";
        $societyResult = $conn->query($societyQuery);
        if ($societyResult) {
            $societyCount = $societyResult->fetch_assoc();
            echo "<p>Query: " . htmlspecialchars($societyQuery) . "</p>";
            echo "<p>Result: " . ($societyCount ? $societyCount['count'] : "No result") . "</p>";
        } else {
            echo "<p style='color:red'>✗ Query failed: " . htmlspecialchars($conn->error) . "</p>";
            echo "<p>Query: " . htmlspecialchars($societyQuery) . "</p>";
        }
        
        // Test circuit count query
        echo "<h3>Circuits Card</h3>";
        $circuitQuery = "SELECT COUNT(*) as count FROM circuits";
        $circuitResult = $conn->query($circuitQuery);
        if ($circuitResult) {
            $circuitCount = $circuitResult->fetch_assoc();
            echo "<p>Query: " . htmlspecialchars($circuitQuery) . "</p>";
            echo "<p>Result: " . ($circuitCount ? $circuitCount['count'] : "No result") . "</p>";
        } else {
            echo "<p style='color:red'>✗ Query failed: " . htmlspecialchars($conn->error) . "</p>";
            echo "<p>Query: " . htmlspecialchars($circuitQuery) . "</p>";
        }
        
        // Test diocese count query
        echo "<h3>Dioceses Card</h3>";
        $dioceseQuery = "SELECT COUNT(*) as count FROM dioceses";
        $dioceseResult = $conn->query($dioceseQuery);
        if ($dioceseResult) {
            $dioceseCount = $dioceseResult->fetch_assoc();
            echo "<p>Query: " . htmlspecialchars($dioceseQuery) . "</p>";
            echo "<p>Result: " . ($dioceseCount ? $dioceseCount['count'] : "No result") . "</p>";
        } else {
            echo "<p style='color:red'>✗ Query failed: " . htmlspecialchars($conn->error) . "</p>";
            echo "<p>Query: " . htmlspecialchars($dioceseQuery) . "</p>";
        }
        
        // Check fetchRow function
        echo "<h2>fetchRow Function Test</h2>";
        if (function_exists('fetchRow')) {
            echo "<p style='color:green'>✓ fetchRow function exists</p>";
            
            try {
                $testResult = fetchRow("SELECT 1 as test");
                echo "<p>Test query result: " . print_r($testResult, true) . "</p>";
            } catch (Exception $e) {
                echo "<p style='color:red'>✗ fetchRow function threw an exception: " . htmlspecialchars($e->getMessage()) . "</p>";
            }
        } else {
            echo "<p style='color:red'>✗ fetchRow function does not exist</p>";
            
            // Check if database.php is properly included
            echo "<p>Checking if database.php is properly included...</p>";
            $databasePhpContent = file_get_contents('config/database.php');
            if (strpos($databasePhpContent, 'function fetchRow') !== false) {
                echo "<p>fetchRow function is defined in database.php but not available. This suggests an issue with how database.php is included.</p>";
            } else {
                echo "<p>fetchRow function is not defined in database.php.</p>";
            }
        }
    }
} else {
    echo "<p style='color:red'>✗ Database connection variable not available</p>";
}

// Server information
echo "<h2>Server Information</h2>";
echo "<p>PHP Version: " . phpversion() . "</p>";
echo "<p>Server Software: " . htmlspecialchars($_SERVER['SERVER_SOFTWARE'] ?? 'Unknown') . "</p>";
echo "<p>Document Root: " . htmlspecialchars($_SERVER['DOCUMENT_ROOT'] ?? 'Unknown') . "</p>";
echo "<p>Script Path: " . htmlspecialchars($_SERVER['SCRIPT_FILENAME'] ?? 'Unknown') . "</p>";
?>
