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

if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorized access']);
    exit;
}

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

try {
    $database = new Database();
    $pdo = $database->getConnection();
    
    // Overall membership statistics
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(*) as total_fees,
            COUNT(CASE WHEN status = 'paid' THEN 1 END) as paid_fees,
            COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_fees,
            COUNT(CASE WHEN status = 'overdue' THEN 1 END) as overdue_fees,
            SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) as total_collected,
            SUM(CASE WHEN status != 'paid' THEN amount ELSE 0 END) as total_outstanding,
            AVG(amount) as average_fee
        FROM membership_fees
    ");
    $stmt->execute();
    $fee_stats = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Fee statistics by type
    $stmt = $pdo->prepare("
        SELECT 
            fee_type,
            COUNT(*) as count,
            SUM(amount) as total_amount,
            AVG(amount) as average_amount,
            COUNT(CASE WHEN status = 'paid' THEN 1 END) as paid_count
        FROM membership_fees
        GROUP BY fee_type
        ORDER BY total_amount DESC
    ");
    $stmt->execute();
    $fee_by_type = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Monthly fee collection trend (last 12 months)
    $stmt = $pdo->prepare("
        SELECT 
            DATE_FORMAT(paid_date, '%Y-%m') as month,
            COUNT(*) as fees_paid,
            SUM(amount) as amount_collected
        FROM membership_fees
        WHERE status = 'paid' 
        AND paid_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
        GROUP BY DATE_FORMAT(paid_date, '%Y-%m')
        ORDER BY month ASC
    ");
    $stmt->execute();
    $monthly_collections = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Payment method statistics
    $stmt = $pdo->prepare("
        SELECT 
            COALESCE(payment_method, 'Unknown') as method,
            COUNT(*) as count,
            SUM(amount) as total_amount
        FROM membership_fees
        WHERE status = 'paid'
        GROUP BY payment_method
        ORDER BY total_amount DESC
    ");
    $stmt->execute();
    $payment_methods = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Overdue fees summary
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(*) as overdue_count,
            SUM(amount) as overdue_amount,
            AVG(DATEDIFF(NOW(), due_date)) as avg_days_overdue
        FROM membership_fees
        WHERE due_date < CURDATE() AND status != 'paid'
    ");
    $stmt->execute();
    $overdue_summary = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Format monthly collections
    foreach ($monthly_collections as &$month) {
        $month['month_name'] = date('M Y', strtotime($month['month'] . '-01'));
        $month['amount_collected'] = number_format($month['amount_collected'], 2);
    }
    
    // Format fee by type
    foreach ($fee_by_type as &$type) {
        $type['total_amount'] = number_format($type['total_amount'], 2);
        $type['average_amount'] = number_format($type['average_amount'], 2);
        $type['collection_rate'] = $type['count'] > 0 ? round(($type['paid_count'] / $type['count']) * 100, 2) : 0;
    }
    
    // Format payment methods
    foreach ($payment_methods as &$method) {
        $method['total_amount'] = number_format($method['total_amount'], 2);
    }
    
    echo json_encode([
        'success' => true,
        'data' => [
            'total_fees' => (int)$fee_stats['total_fees'],
            'paid_fees' => (int)$fee_stats['paid_fees'],
            'pending_fees' => (int)$fee_stats['pending_fees'],
            'overdue_fees' => (int)$fee_stats['overdue_fees'],
            'total_revenue' => $fee_stats['total_collected'],
            'total_collected' => number_format($fee_stats['total_collected'], 2),
            'total_outstanding' => number_format($fee_stats['total_outstanding'], 2),
            'average_fee' => number_format($fee_stats['average_fee'], 2),
            'collection_rate' => $fee_stats['total_fees'] > 0 ? round(($fee_stats['paid_fees'] / $fee_stats['total_fees']) * 100, 2) : 0
        ],
        'overview' => [
            'total_fees' => (int)$fee_stats['total_fees'],
            'paid_fees' => (int)$fee_stats['paid_fees'],
            'pending_fees' => (int)$fee_stats['pending_fees'],
            'overdue_fees' => (int)$fee_stats['overdue_fees'],
            'total_collected' => number_format($fee_stats['total_collected'], 2),
            'total_outstanding' => number_format($fee_stats['total_outstanding'], 2),
            'average_fee' => number_format($fee_stats['average_fee'], 2),
            'collection_rate' => $fee_stats['total_fees'] > 0 ? round(($fee_stats['paid_fees'] / $fee_stats['total_fees']) * 100, 2) : 0
        ],
        'fee_by_type' => $fee_by_type,
        'monthly_collections' => $monthly_collections,
        'payment_methods' => $payment_methods,
        'overdue_summary' => [
            'count' => (int)$overdue_summary['overdue_count'],
            'amount' => number_format($overdue_summary['overdue_amount'], 2),
            'avg_days_overdue' => round($overdue_summary['avg_days_overdue'], 1)
        ]
    ]);
    
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>
