<?php
require_once '../config/database.php';

header('Content-Type: application/json');

try {
    // Check if user is authenticated
    if (!isAuthenticated()) {
        echo json_encode(['success' => false, 'message' => 'Unauthorized access']);
        exit;
    }

    $database = new Database();
    $conn = $database->getConnection();

    // Function to safely query table
    function safelyQueryTable($conn, $query, $tableName) {
        try {
            // Check if table exists
            $checkTable = "SHOW TABLES LIKE '$tableName'";
            $stmt = $conn->prepare($checkTable);
            $stmt->execute();
            $tableExists = $stmt->rowCount() > 0;
            
            if ($tableExists) {
                $stmt = $conn->query($query);
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                
                // Convert null values to 0 for numeric fields
                if ($result) {
                    foreach ($result as $key => $value) {
                        if (is_null($value) && (strpos($key, 'total') === 0 || 
                                               strpos($key, 'count') === 0 || 
                                               strpos($key, 'active') === 0 ||
                                               strpos($key, 'pending') === 0 ||
                                               strpos($key, 'upcoming') === 0 ||
                                               strpos($key, 'completed') === 0)) {
                            $result[$key] = 0;
                        }
                    }
                    return $result;
                }
            }
            
            // Return default empty values based on tableName
            return getDefaultEmptyValues($tableName);
        } catch (Exception $e) {
            error_log("Error querying $tableName: " . $e->getMessage());
            return getDefaultEmptyValues($tableName);
        }
    }
    
    // Function to provide default empty values for different tables
    function getDefaultEmptyValues($tableName) {
        switch ($tableName) {
            case 'members':
                return [
                    'total_members' => 0,
                    'active_members' => 0,
                    'pending_members' => 0,
                    'approved_members' => 0
                ];
            case 'events':
                return [
                    'total_events' => 0,
                    'upcoming_events' => 0,
                    'completed_events' => 0
                ];
            case 'transactions':
                return [
                    'total_transactions' => 0,
                    'total_income' => 0,
                    'pending_payments' => 0
                ];
            case 'expenses':
                return [
                    'total_expenses' => 0,
                    'total_expenses_amount' => 0,
                    'pending_expenses' => 0
                ];
            case 'documents':
                return [
                    'total_documents' => 0,
                    'recently_added' => 0
                ];
            case 'id_cards':
                return [
                    'total_cards_issued' => 0,
                    'pending_cards' => 0
                ];
            case 'member_photos':
                return [
                    'total_photos' => 0,
                    'pending_approval' => 0
                ];
            case 'receipts':
                return [
                    'total_receipts' => 0,
                    'recent_receipts' => 0
                ];
            default:
                return [];
        }
    }
    
    // Initialize default values for all stat sections
    $member_stats = getDefaultEmptyValues('members');
    $event_stats = getDefaultEmptyValues('events');
    $finance_stats = getDefaultEmptyValues('transactions');
    $expense_stats = getDefaultEmptyValues('expenses');
    $document_stats = getDefaultEmptyValues('documents');
    $id_card_stats = getDefaultEmptyValues('id_cards');
    $photo_stats = getDefaultEmptyValues('member_photos');
    $receipt_stats = getDefaultEmptyValues('receipts');
    
    // Get member statistics
    $memberQuery = "
        SELECT 
            COUNT(*) as total_members,
            COUNT(CASE WHEN status = 'active' THEN 1 END) as active_members,
            COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_members,
            COUNT(CASE WHEN status = 'approved' THEN 1 END) as approved_members
        FROM members
    ";
    $memberResult = safelyQueryTable($conn, $memberQuery, 'members');
    if (!empty($memberResult)) {
        $member_stats = $memberResult;
    }
    
    // Get event statistics - check if events table exists first
    $eventQuery = "
        SELECT 
            COUNT(*) as total_events,
            COUNT(CASE WHEN event_date >= CURDATE() THEN 1 END) as upcoming_events,
            COUNT(CASE WHEN event_date < CURDATE() THEN 1 END) as completed_events
        FROM events
    ";
    $eventResult = safelyQueryTable($conn, $eventQuery, 'events');
    if (!empty($eventResult)) {
        $event_stats = $eventResult;
    }
    
    // Get financial statistics - use finance table instead of transactions
    // Get finance statistics with enhanced analytics
    $financeQuery = "
        SELECT 
            COUNT(*) as total_transactions,
            COALESCE(SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END), 0) as total_income,
            COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_payments,
            COALESCE(SUM(CASE WHEN status = 'completed' AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE()) THEN amount ELSE 0 END), 0) as current_month_income,
            COALESCE(SUM(CASE WHEN status = 'completed' AND MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(created_at) = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN amount ELSE 0 END), 0) as previous_month_income
        FROM finance
    ";
    $financeResult = safelyQueryTable($conn, $financeQuery, 'finance');
    if (!empty($financeResult)) {
        $finance_stats = $financeResult;
        
        // Add trend data for the last 7 days/weeks
        $trendQuery = "
            SELECT 
                DATE(created_at) as period_date,
                COALESCE(SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END), 0) as daily_income
            FROM finance 
            WHERE created_at >= CURDATE() - INTERVAL 7 DAY
            GROUP BY DATE(created_at)
            ORDER BY period_date ASC
        ";
        
        try {
            $trendStmt = $conn->query($trendQuery);
            $trendData = $trendStmt->fetchAll(PDO::FETCH_ASSOC);
            
            $incomeTrend = [];
            $last7Days = [];
            for ($i = 6; $i >= 0; $i--) {
                $last7Days[] = date('Y-m-d', strtotime("-$i days"));
            }
            
            foreach ($last7Days as $date) {
                $found = false;
                foreach ($trendData as $trend) {
                    if ($trend['period_date'] === $date) {
                        $incomeTrend[] = intval($trend['daily_income']);
                        $found = true;
                        break;
                    }
                }
                if (!$found) {
                    $incomeTrend[] = 0;
                }
            }
            
            $finance_stats['income_trend'] = $incomeTrend;
        } catch (Exception $e) {
            error_log("Error getting income trend: " . $e->getMessage());
            $finance_stats['income_trend'] = [0,0,0,0,0,0,0];
        }
    }
    
    // Get expense statistics with enhanced analytics
    $expenseQuery = "
        SELECT 
            COUNT(*) as total_expenses,
            COALESCE(SUM(CASE WHEN status = 'approved' THEN amount ELSE 0 END), 0) as total_expenses_amount,
            COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_expenses,
            COALESCE(SUM(CASE WHEN status = 'approved' AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE()) THEN amount ELSE 0 END), 0) as current_month_expenses
        FROM expenses
    ";
    $expenseResult = safelyQueryTable($conn, $expenseQuery, 'expenses');
    if (!empty($expenseResult)) {
        $expense_stats = $expenseResult;
        
        // Add expense trend data for the last 7 days
        $expenseTrendQuery = "
            SELECT 
                DATE(created_at) as period_date,
                COALESCE(SUM(CASE WHEN status = 'approved' THEN amount ELSE 0 END), 0) as daily_expenses
            FROM expenses 
            WHERE created_at >= CURDATE() - INTERVAL 7 DAY
            GROUP BY DATE(created_at)
            ORDER BY period_date ASC
        ";
        
        try {
            $expenseTrendStmt = $conn->query($expenseTrendQuery);
            $expenseTrendData = $expenseTrendStmt->fetchAll(PDO::FETCH_ASSOC);
            
            $expensesTrend = [];
            $last7Days = [];
            for ($i = 6; $i >= 0; $i--) {
                $last7Days[] = date('Y-m-d', strtotime("-$i days"));
            }
            
            foreach ($last7Days as $date) {
                $found = false;
                foreach ($expenseTrendData as $trend) {
                    if ($trend['period_date'] === $date) {
                        $expensesTrend[] = intval($trend['daily_expenses']);
                        $found = true;
                        break;
                    }
                }
                if (!$found) {
                    $expensesTrend[] = 0;
                }
            }
            
            $expense_stats['expenses_trend'] = $expensesTrend;
        } catch (Exception $e) {
            error_log("Error getting expenses trend: " . $e->getMessage());
            $expense_stats['expenses_trend'] = [0,0,0,0,0,0,0];
        }
    }
    
    // Get document statistics
    $documentQuery = "
        SELECT 
            COUNT(*) as total_documents,
            COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as recently_added
        FROM documents
    ";
    $documentResult = safelyQueryTable($conn, $documentQuery, 'documents');
    if (!empty($documentResult)) {
        $document_stats = $documentResult;
    }
    
    // Get ID card statistics
    $idCardQuery = "
        SELECT 
            COUNT(*) as total_cards_issued,
            COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_cards
        FROM id_cards
    ";
    $idCardResult = safelyQueryTable($conn, $idCardQuery, 'id_cards');
    if (!empty($idCardResult)) {
        $id_card_stats = $idCardResult;
    }
    
    // Get photo statistics
    $photoQuery = "
        SELECT 
            COUNT(*) as total_photos,
            COUNT(CASE WHEN approval_status = 'pending' THEN 1 END) as pending_approval
        FROM member_photos
    ";
    $photoResult = safelyQueryTable($conn, $photoQuery, 'member_photos');
    if (!empty($photoResult)) {
        $photo_stats = $photoResult;
    }
    
    // Get receipt statistics
    $receiptQuery = "
        SELECT 
            COUNT(*) as total_receipts,
            COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as recent_receipts
        FROM receipts
    ";
    $receiptResult = safelyQueryTable($conn, $receiptQuery, 'receipts');
    if (!empty($receiptResult)) {
        $receipt_stats = $receiptResult;
    }

    // If chart data was requested
    $chart_data = null;
    if (isset($_GET['chart']) && $_GET['chart'] === 'members_growth') {
        // Get monthly member signup data for the past 12 months
        $chartQuery = "
            SELECT 
                DATE_FORMAT(created_at, '%Y-%m') as month,
                COUNT(*) as new_members
            FROM members
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
            GROUP BY DATE_FORMAT(created_at, '%Y-%m')
            ORDER BY month ASC
        ";
        
        try {
            $stmt = $conn->query($chartQuery);
            $chartResults = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Format for ChartJS
            $labels = [];
            $values = [];
            
            // Create a full 12-month dataset with zeroes for months with no signups
            $end = new DateTime();
            $start = (new DateTime())->modify('-11 months')->modify('first day of this month');
            $period = new DatePeriod(
                $start,
                new DateInterval('P1M'),
                $end
            );
            
            $monthData = [];
            foreach ($chartResults as $row) {
                $monthData[$row['month']] = (int)$row['new_members'];
            }
            
            foreach ($period as $date) {
                $monthKey = $date->format('Y-m');
                $labels[] = $date->format('M Y');
                $values[] = isset($monthData[$monthKey]) ? $monthData[$monthKey] : 0;
            }
            
            $chart_data = [
                'labels' => $labels,
                'values' => $values
            ];
        } catch (Exception $e) {
            error_log("Error generating chart data: " . $e->getMessage());
        }
    }

    echo json_encode([
        'success' => true, 
        'data' => [
            'member_stats' => isset($member_stats) ? $member_stats : [],
            'event_stats' => isset($event_stats) ? $event_stats : [],
            'finance_stats' => isset($finance_stats) ? $finance_stats : [],
            'expense_stats' => isset($expense_stats) ? $expense_stats : [],
            'document_stats' => isset($document_stats) ? $document_stats : [],
            'id_card_stats' => isset($id_card_stats) ? $id_card_stats : [],
            'photo_stats' => isset($photo_stats) ? $photo_stats : [],
            'receipt_stats' => isset($receipt_stats) ? $receipt_stats : [],
            'chart_data' => $chart_data
        ]
    ]);

} catch (Exception $e) {
    error_log("Dashboard API Error: " . $e->getMessage());
    echo json_encode([
        'success' => false, 
        'message' => 'Database error: ' . $e->getMessage(),
        'debug' => [
            'file' => $e->getFile(),
            'line' => $e->getLine(),
            'trace' => $e->getTraceAsString()
        ]
    ]);
}
?>
