<?php
/**
 * Script pour corriger le calcul des revenus totaux en incluant les paiements mensuels
 */

require_once 'config.php';
require_once 'includes/functions.php';
require_once 'includes/functions_finance.php';
require_once 'database/init.php';
require_once 'includes/functions_income.php';

// Inclusion des paiements mensuels dans le calcul des revenus totaux
function calculateTotalIncomeWithMonthlyPayments($startDate = null, $endDate = null) {
    // Paramètres pour la requête des revenus
    $incomeParams = [];
    $incomeWhereClause = '';
    
    if ($startDate && $endDate) {
        $incomeWhereClause = "WHERE payment_date BETWEEN ? AND ?";
        $incomeParams = [$startDate, $endDate];
    } elseif ($startDate) {
        $incomeWhereClause = "WHERE payment_date >= ?";
        $incomeParams = [$startDate];
    } elseif ($endDate) {
        $incomeWhereClause = "WHERE payment_date <= ?";
        $incomeParams = [$endDate];
    }
    
    // Calcul des revenus à partir de la table income
    $incomeQuery = "SELECT SUM(amount) as total FROM income {$incomeWhereClause}";
    $incomeResult = fetchOne($incomeQuery, $incomeParams);
    $incomeTotal = $incomeResult ? (float)$incomeResult['total'] : 0;
    
    // Paramètres pour la requête des paiements mensuels
    $mpParams = [];
    $mpWhereClause = '';
    
    if ($startDate && $endDate) {
        $mpWhereClause = "WHERE payment_status = 'paid' AND payment_date BETWEEN ? AND ?";
        $mpParams = [$startDate, $endDate];
    } elseif ($startDate) {
        $mpWhereClause = "WHERE payment_status = 'paid' AND payment_date >= ?";
        $mpParams = [$startDate];
    } elseif ($endDate) {
        $mpWhereClause = "WHERE payment_status = 'paid' AND payment_date <= ?";
        $mpParams = [$endDate];
    } else {
        $mpWhereClause = "WHERE payment_status = 'paid'";
    }
    
    // Calcul des revenus à partir des paiements mensuels
    $mpQuery = "SELECT SUM(amount_paid) as total FROM monthly_payments {$mpWhereClause}";
    $mpResult = fetchOne($mpQuery, $mpParams);
    $monthlyPaymentsTotal = $mpResult ? (float)$mpResult['total'] : 0;
    
    // Total des revenus = revenus de la table income + revenus des paiements mensuels
    return $incomeTotal + $monthlyPaymentsTotal;
}

// Remplacer la fonction calculateTotalIncome dans functions.php
$functionsContent = file_get_contents('includes/functions.php');
$newFunction = <<<'EOT'

// حساب إجمالي المداخيل لفترة معينة
function calculateTotalIncome($startDate = null, $endDate = null) {
    // Paramètres pour la requête des revenus
    $incomeParams = [];
    $incomeWhereClause = '';
    
    if ($startDate && $endDate) {
        $incomeWhereClause = "WHERE payment_date BETWEEN :start_date AND :end_date";
        $incomeParams[':start_date'] = $startDate;
        $incomeParams[':end_date'] = $endDate;
    } elseif ($startDate) {
        $incomeWhereClause = "WHERE payment_date >= :start_date";
        $incomeParams[':start_date'] = $startDate;
    } elseif ($endDate) {
        $incomeWhereClause = "WHERE payment_date <= :end_date";
        $incomeParams[':end_date'] = $endDate;
    }
    
    // Calcul des revenus à partir de la table income
    $incomeQuery = "SELECT SUM(amount) as total FROM income {$incomeWhereClause}";
    $incomeResult = fetchOne($incomeQuery, $incomeParams);
    $incomeTotal = $incomeResult ? (float)$incomeResult['total'] : 0;
    
    // Paramètres pour la requête des paiements mensuels
    $mpParams = [];
    $mpWhereClause = '';
    
    if ($startDate && $endDate) {
        $mpWhereClause = "WHERE payment_status = 'paid' AND payment_date BETWEEN :start_date AND :end_date";
        $mpParams[':start_date'] = $startDate;
        $mpParams[':end_date'] = $endDate;
    } elseif ($startDate) {
        $mpWhereClause = "WHERE payment_status = 'paid' AND payment_date >= :start_date";
        $mpParams[':start_date'] = $startDate;
    } elseif ($endDate) {
        $mpWhereClause = "WHERE payment_status = 'paid' AND payment_date <= :end_date";
        $mpParams[':end_date'] = $endDate;
    } else {
        $mpWhereClause = "WHERE payment_status = 'paid'";
    }
    
    // Calcul des revenus à partir des paiements mensuels
    $mpQuery = "SELECT SUM(amount_paid) as total FROM monthly_payments {$mpWhereClause}";
    $mpResult = fetchOne($mpQuery, $mpParams);
    $monthlyPaymentsTotal = $mpResult ? (float)$mpResult['total'] : 0;
    
    // Total des revenus = revenus de la table income + revenus des paiements mensuels
    return $incomeTotal + $monthlyPaymentsTotal;
}
EOT;

// Remplacer la fonction existante
$pattern = '/\/\/ حساب إجمالي المداخيل لفترة معينة\s*function calculateTotalIncome\([^\)]*\)\s*\{[^}]*\}/s';
$updatedContent = preg_replace($pattern, $newFunction, $functionsContent);

// Enregistrer le fichier mis à jour
file_put_contents('includes/functions.php', $updatedContent);

// Tester la nouvelle fonction
$currentMonth = date('Y-m-01');
$nextMonth = date('Y-m-01', strtotime('+1 month'));
$endOfMonth = date('Y-m-d', strtotime($nextMonth) - 1);

// Calcul des revenus avec la nouvelle fonction
$totalIncome = calculateTotalIncome($currentMonth, $endOfMonth);

// Calcul des revenus à partir de la table income uniquement
$incomeQuery = "SELECT SUM(amount) as total FROM income WHERE payment_date BETWEEN ? AND ?";
$incomeResult = fetchOne($incomeQuery, [$currentMonth, $endOfMonth]);
$incomeTotal = $incomeResult ? (float)$incomeResult['total'] : 0;

// Calcul des revenus à partir des paiements mensuels uniquement
$mpQuery = "SELECT SUM(amount_paid) as total FROM monthly_payments WHERE payment_status = 'paid' AND payment_date BETWEEN ? AND ?";
$mpResult = fetchOne($mpQuery, [$currentMonth, $endOfMonth]);
$monthlyPaymentsTotal = $mpResult ? (float)$mpResult['total'] : 0;

// Afficher les résultats
echo "<h1>Correction du calcul des revenus</h1>";
echo "<p>La fonction <code>calculateTotalIncome</code> a été mise à jour pour inclure les paiements mensuels dans le calcul des revenus totaux.</p>";

echo "<h2>Résultats pour le mois en cours (" . date('m/Y') . ")</h2>";
echo "<ul>";
echo "<li>Revenus de la table income: " . formatCurrency($incomeTotal) . "</li>";
echo "<li>Revenus des paiements mensuels: " . formatCurrency($monthlyPaymentsTotal) . "</li>";
echo "<li>Total des revenus: " . formatCurrency($totalIncome) . "</li>";
echo "</ul>";

echo "<p>Le problème a été résolu. Les paiements mensuels sont maintenant inclus dans le calcul des revenus totaux.</p>";
echo "<p>Vous pouvez maintenant vérifier le tableau de bord et les rapports pour vous assurer que les paiements mensuels sont correctement comptabilisés dans les revenus.</p>";

// Créer une fonction pour afficher les sources de revenus dans les rapports
$reportsIncomeSection = <<<'EOT'
<?php
/**
 * قسم المداخيل في التقارير
 */

/**
 * عرض تفاصيل المداخيل في التقارير
 *
 * @param string $startDate تاريخ البداية
 * @param string $endDate تاريخ النهاية
 */
function displayIncomeSection($startDate, $endDate) {
    // الحصول على المداخيل من جدول income
    $incomeQuery = "SELECT source, SUM(amount) as total FROM income 
                    WHERE payment_date BETWEEN ? AND ? 
                    GROUP BY source";
    $incomeResult = fetchAll($incomeQuery, [$startDate, $endDate]);
    
    // الحصول على المداخيل من الدفعات الشهرية
    $mpQuery = "SELECT SUM(amount_paid) as total FROM monthly_payments 
                WHERE payment_status = 'paid' AND payment_date BETWEEN ? AND ?";
    $mpResult = fetchOne($mpQuery, [$startDate, $endDate]);
    $monthlyPaymentsTotal = $mpResult ? (float)$mpResult['total'] : 0;
    
    // تجميع المداخيل حسب المصدر
    $incomeBySource = [];
    foreach ($incomeResult as $row) {
        $incomeBySource[$row['source']] = (float)$row['total'];
    }
    
    // إضافة المداخيل من الدفعات الشهرية
    if ($monthlyPaymentsTotal > 0) {
        if (isset($incomeBySource['monthly_subscription'])) {
            $incomeBySource['monthly_subscription'] += $monthlyPaymentsTotal;
        } else {
            $incomeBySource['monthly_subscription'] = $monthlyPaymentsTotal;
        }
    }
    
    // حساب إجمالي المداخيل
    $totalIncome = array_sum($incomeBySource);
    
    // عرض جدول المداخيل
    ?>
    <div class="card mb-4">
        <div class="card-header bg-success text-white">
            <h5 class="mb-0"><i class="fas fa-money-bill-wave me-2"></i> المداخيل حسب المصدر</h5>
        </div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-striped table-hover">
                    <thead class="table-light">
                        <tr>
                            <th>المصدر</th>
                            <th>المبلغ</th>
                            <th>النسبة</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($incomeBySource as $source => $amount): ?>
                        <tr>
                            <td>
                                <?php 
                                switch ($source) {
                                    case 'subscription':
                                        echo 'اشتراكات سنوية';
                                        break;
                                    case 'monthly_subscription':
                                        echo 'اشتراكات شهرية';
                                        break;
                                    case 'donation':
                                        echo 'تبرعات';
                                        break;
                                    case 'event':
                                        echo 'فعاليات';
                                        break;
                                    case 'other':
                                        echo 'أخرى';
                                        break;
                                    default:
                                        echo $source;
                                }
                                ?>
                            </td>
                            <td><?php echo formatCurrency($amount); ?></td>
                            <td><?php echo number_format(($amount / $totalIncome) * 100, 1) . '%'; ?></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                    <tfoot class="table-light">
                        <tr>
                            <th>المجموع</th>
                            <th><?php echo formatCurrency($totalIncome); ?></th>
                            <th>100%</th>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>
    <?php
}
?>
EOT;

// Enregistrer le fichier de section des revenus pour les rapports
file_put_contents('reports/income_section.php', $reportsIncomeSection);

echo "<p>Un nouveau fichier <code>reports/income_section.php</code> a été créé pour afficher les détails des revenus dans les rapports, y compris les paiements mensuels.</p>";
?>
