<?php
require_once 'config/init.php';
require_once 'includes/auth.php';
requireAuth();

// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('log_errors', 1);
ini_set('error_log', __DIR__ . '/error.log');

try {
    $db = getDB();

    // Get date range for filtering
    $today = new DateTime();
    if (isset($_GET['start_date']) && isset($_GET['end_date'])) {
        // Validate dates
        $start_date = DateTime::createFromFormat('Y-m-d', $_GET['start_date']);
        $end_date = DateTime::createFromFormat('Y-m-d', $_GET['end_date']);
        
        if (!$start_date || !$end_date || $start_date > $end_date) {
            // Invalid dates, use default range
            $start_date = new DateTime('first day of this month');
            $end_date = new DateTime('last day of this month');
        }
    } else {
        // Default to current month
        $start_date = new DateTime('first day of this month');
        $end_date = new DateTime('last day of this month');
    }

    // Format dates for display and query
    $start_date_str = $start_date->format('Y-m-d');
    $end_date_str = $end_date->format('Y-m-d');

    // Initialize variables
    $transactions = [];
    $summaries = [
        'cash_fund' => [
            'deposits' => 0,
            'withdrawals' => 0,
            'balance' => 0
        ],
        'bank_account' => [
            'deposits' => 0,
            'withdrawals' => 0,
            'balance' => 0
        ],
        'debts' => [
            'total_debt' => 0,
            'total_paid' => 0,
            'remaining' => 0,
            'advances' => 0
        ],
        'invoices' => [
            'total_amount' => 0,
            'fixed_fees' => 0,
            'collective_entitlement' => 0,
            'consumption_amount' => 0,
            'penalties' => 0
        ]
    ];

    // 1. Fetch Cash Fund Transactions
    $query_cash = "
        SELECT 
            'cash_fund' as type,
            ft.id,
            ft.created_at as date,
            ft.amount,
            ft.transaction_type,
            ft.description as details,
            ft.reference_type,
            ft.reference_id
        FROM financial_transactions ft
        WHERE ft.created_at BETWEEN :start_date AND :end_date
        ORDER BY ft.created_at DESC";

    $stmt = $db->prepare($query_cash);
    $stmt->bindValue(':start_date', $start_date_str, SQLITE3_TEXT);
    $stmt->bindValue(':end_date', $end_date_str, SQLITE3_TEXT);
    $result = $stmt->execute();
    
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $transactions[] = $row;
        if ($row['transaction_type'] === 'DEPOSIT') {
            $summaries['cash_fund']['deposits'] += floatval($row['amount']);
        } else {
            $summaries['cash_fund']['withdrawals'] += floatval($row['amount']);
        }
    }

    // Get current cash fund balance
    $query_cash_balance = "SELECT current_balance FROM cash_fund LIMIT 1";
    $result = $db->querySingle($query_cash_balance);
    $summaries['cash_fund']['balance'] = $result ?: 0;

    // 2. Fetch Debt Transactions
    $query_debts = "
        SELECT 
            'debt' as type,
            sd.id,
            sd.created_at as date,
            sd.total_debt as amount,
            sd.remaining_debt as amount_paid,
            sd.deposit_amount as advance_payment,
            sd.meter_number,
            s.full_name as details
        FROM subscriber_debts sd
        JOIN subscribers s ON sd.subscriber_id = s.id
        WHERE sd.created_at BETWEEN :start_date AND :end_date
        ORDER BY sd.created_at DESC";

    $stmt = $db->prepare($query_debts);
    $stmt->bindValue(':start_date', $start_date_str, SQLITE3_TEXT);
    $stmt->bindValue(':end_date', $end_date_str, SQLITE3_TEXT);
    $result = $stmt->execute();
    
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $transactions[] = $row;
        $summaries['debts']['total_debt'] += floatval($row['amount']);
        $summaries['debts']['total_paid'] += floatval($row['amount']) - floatval($row['amount_paid']); // total_debt - remaining_debt = amount paid
        $summaries['debts']['advances'] += floatval($row['advance_payment']);
    }
    $summaries['debts']['remaining'] = $summaries['debts']['total_debt'] - $summaries['debts']['total_paid'];

    // 3. Fetch invoice payments
    $query1 = "
        SELECT 
            'invoice_payment' as type,
            i.id,
            i.payment_date as date,
            i.total_amount as amount,
            s.meter_number,
            s.full_name as details,
            i.status,
            i.fixed_fees,
            i.collective_entitlement_amount,
            i.consumption_amount,
            i.penalties,
            c.previous_reading,
            c.current_reading,
            (c.current_reading - c.previous_reading) as consumption,
            s.family_members
        FROM invoices i
        JOIN subscribers s ON i.subscriber_id = s.id
        JOIN consumption c ON i.consumption_id = c.id
        WHERE i.status = 'paid'
        AND i.payment_date BETWEEN :start_date AND :end_date
        ORDER BY i.payment_date DESC";

    $stmt = $db->prepare($query1);
    $stmt->bindValue(':start_date', $start_date_str, SQLITE3_TEXT);
    $stmt->bindValue(':end_date', $end_date_str, SQLITE3_TEXT);
    $result = $stmt->execute();
    
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $transactions[] = $row;
        $summaries['invoices']['total_amount'] += floatval($row['amount']);
        $summaries['invoices']['fixed_fees'] += floatval($row['fixed_fees']);
        $summaries['invoices']['collective_entitlement'] += floatval($row['collective_entitlement_amount']);
        $summaries['invoices']['consumption_amount'] += floatval($row['consumption_amount']);
        $summaries['invoices']['penalties'] += floatval($row['penalties']);
    }

    // 4. Fetch new subscriber payments
    $query2 = "
        SELECT 
            'new_subscriber' as type,
            nsp.id,
            nsp.payment_date as date,
            nsp.amount,
            nsp.meter_number,
            s.full_name as details,
            'paid' as status
        FROM new_subscriber_payments nsp
        JOIN subscribers s ON nsp.subscriber_id = s.id
        WHERE nsp.payment_date BETWEEN ? AND ?
        ORDER BY nsp.payment_date DESC";

    $stmt = $db->prepare($query2);
    $stmt->bindValue(1, $start_date_str, SQLITE3_TEXT);
    $stmt->bindValue(2, $end_date_str, SQLITE3_TEXT);
    $result = $stmt->execute();
    
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $transactions[] = $row;
    }

    // 5. Fetch contributions
    $query3 = "
        SELECT 
            'contribution' as type,
            id,
            contribution_date as date,
            amount,
            NULL as meter_number,
            reason as details,
            'contribution' as status
        FROM contributions
        WHERE contribution_date BETWEEN :start_date AND :end_date
        ORDER BY contribution_date DESC";

    try {
        error_log("Récupération des contributions entre $start_date_str et $end_date_str");
        
        $stmt = $db->prepare($query3);
        if (!$stmt) {
            error_log("Erreur de préparation de la requête des contributions: " . $db->lastErrorMsg());
            throw new Exception("Erreur lors de la préparation de la requête des contributions");
        }
        
        $stmt->bindValue(':start_date', $start_date_str, SQLITE3_TEXT);
        $stmt->bindValue(':end_date', $end_date_str, SQLITE3_TEXT);
        $result = $stmt->execute();
        
        if (!$result) {
            error_log("Erreur d'exécution de la requête des contributions: " . $db->lastErrorMsg());
            throw new Exception("Erreur lors de la récupération des contributions");
        }
        
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
            $transactions[] = $row;
        }
        
        error_log("Total des contributions récupérées: " . count($transactions));
    } catch (Exception $e) {
        error_log("ERREUR lors de la récupération des contributions: " . $e->getMessage());
        // On continue l'exécution même en cas d'erreur
    }

    // 6. Fetch expenses
    $query4 = "
        SELECT 
            'expense' as type,
            id,
            expense_date as date,
            amount,
            NULL as meter_number,
            reason as details,
            NULL as status
        FROM expenses
        WHERE expense_date BETWEEN ? AND ?
        ORDER BY expense_date DESC";

    $stmt = $db->prepare($query4);
    $stmt->bindValue(1, $start_date_str, SQLITE3_TEXT);
    $stmt->bindValue(2, $end_date_str, SQLITE3_TEXT);
    $result = $stmt->execute();
    
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $row['amount'] = -floatval($row['amount']); // Convert to negative for expenses
        $transactions[] = $row;
    }

    // Sort all transactions by date
    usort($transactions, function($a, $b) {
        return strcmp($b['date'], $a['date']);
    });

} catch (Exception $e) {
    error_log("ERREUR CRITIQUE - financial_report.php: " . $e->getMessage());
    error_log("Trace: " . $e->getTraceAsString());
    die("خطأ في معالجة البيانات: " . htmlspecialchars($e->getMessage()));
}

// Handle record deletion
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'delete_record') {
    $response = ['success' => false, 'message' => ''];
    $type = $_POST['type'];
    $id = intval($_POST['id']);
    
    try {
        switch ($type) {
            case 'invoice_payment':
                $stmt = $db->prepare("UPDATE invoices SET status = 'unpaid', payment_date = NULL WHERE id = ?");
                break;
            case 'new_subscriber':
                $stmt = $db->prepare("DELETE FROM new_subscriber_payments WHERE id = ?");
                break;
            case 'contribution':
                try {
                    error_log("Tentative de suppression de la contribution ID: $id");
                    $stmt = $db->prepare("DELETE FROM contributions WHERE id = :id");
                    if (!$stmt) {
                        throw new Exception("Erreur de préparation de la requête de suppression");
                    }
                    $stmt->bindValue(':id', $id, SQLITE3_INTEGER);
                    if ($stmt->execute()) {
                        error_log("Contribution supprimée avec succès");
                    } else {
                        throw new Exception("Erreur lors de la suppression");
                    }
                } catch (Exception $e) {
                    error_log("ERREUR lors de la suppression de la contribution: " . $e->getMessage());
                    throw $e;
                }
                break;
            case 'expense':
                $stmt = $db->prepare("DELETE FROM expenses WHERE id = ?");
                break;
            default:
                throw new Exception("نوع السجل غير صالح");
        }
        
        $stmt->bindValue(1, $id, SQLITE3_INTEGER);
        if ($stmt->execute()) {
            $response['success'] = true;
            $response['message'] = "تم حذف السجل بنجاح";
        } else {
            throw new Exception($db->lastErrorMsg());
        }
    } catch (Exception $e) {
        $response['message'] = $e->getMessage();
    }
    
    header('Content-Type: application/json');
    echo json_encode($response);
    exit;
}
?>

<!DOCTYPE html>
<html lang="ar" dir="rtl">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>التقرير المالي</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="css/dashboard.css" rel="stylesheet">
    <style>
        @media print {
            .no-print {
                display: none !important;
            }
            .container-fluid {
                width: 100% !important;
                padding: 0 !important;
            }
            .table td, .table th {
                padding: 0.3rem !important;
                font-size: 0.9rem !important;
            }
        }
        
        .financial-summary {
            background-color: #f8f9fa;
            padding: 1.5rem;
        }
        
        .summary-item {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 0.5rem;
            font-size: 1.1rem;
        }
        
        .summary-subitem {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 0.3rem;
            padding-left: 1rem;
            font-size: 0.95rem;
            color: #666;
        }
        
        .total-income {
            margin-top: 1rem;
            padding-top: 1rem;
            border-top: 2px solid #dee2e6;
            font-size: 1.2rem;
            font-weight: bold;
        }
        
        .total-balance {
            font-size: 1.3rem;
            font-weight: bold;
        }
        
        .transaction-positive {
            color: #198754;
        }
        
        .transaction-negative {
            color: #dc3545;
        }
        
        .table {
            font-size: 0.95rem;
        }
        
        .table th {
            background-color: #f8f9fa;
            font-weight: bold;
        }
        
        .table td {
            vertical-align: middle;
        }
        
        .date-range-form {
            background-color: #f8f9fa;
            padding: 1rem;
            border-radius: 0.25rem;
            margin-bottom: 1.5rem;
        }
        
        @media (max-width: 768px) {
            .summary-item, .summary-subitem {
                font-size: 0.9rem;
            }
            
            .table {
                font-size: 0.85rem;
            }
        }
    </style>
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <!-- Sidebar -->
            <?php include 'includes/sidebar.php'; ?>

            <!-- Main content -->
            <main class="col-md-9 ms-sm-auto col-lg-10 px-md-4">
                <!-- Regular view header -->
                <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom no-print">
                    <h1 class="h2">التقرير المالي</h1>
                    <div class="btn-toolbar mb-2 mb-md-0">
                        <div class="btn-group me-2">
                            <button id="printReport" class="btn btn-primary">طباعة التقرير</button>
                            <a href="financial_entry.php" class="btn btn-secondary">تسجيل معاملة جديدة</a>
                        </div>
                    </div>
                </div>

                <!-- Print Header -->
                <div class="print-only print-header">
                    <h1>التقرير المالي</h1>
                    <p>الفترة: <?= $start_date->format('Y-m-d') ?> إلى <?= $end_date->format('Y-m-d') ?></p>
                </div>

                <!-- Date Filter -->
                <div class="row mb-3 no-print">
                    <div class="col-md-6">
                        <form class="row g-3" method="get">
                            <div class="col-auto">
                                <label for="start_date" class="form-label">من تاريخ</label>
                                <input type="date" class="form-control" id="start_date" name="start_date" value="<?= $start_date_str ?>">
                            </div>
                            <div class="col-auto">
                                <label for="end_date" class="form-label">إلى تاريخ</label>
                                <input type="date" class="form-control" id="end_date" name="end_date" value="<?= $end_date_str ?>">
                            </div>
                            <div class="col-auto">
                                <label class="form-label">&nbsp;</label>
                                <button type="submit" class="form-control btn btn-primary">تصفية</button>
                            </div>
                        </form>
                    </div>
                </div>

                <!-- Financial Summary -->
                <div class="card mb-4">
                    <div class="card-header financial-summary">
                        <div class="row">
                            <div class="col-md-3">
                                <h5 class="mb-3">الصندوق المالي</h5>
                                <p>الرصيد الحالي: <?php echo number_format($summaries['cash_fund']['balance'], 2); ?> درهم</p>
                                <p>المداخيل: <?php echo number_format($summaries['cash_fund']['deposits'], 2); ?> درهم</p>
                                <p>المصاريف: <?php echo number_format($summaries['cash_fund']['withdrawals'], 2); ?> درهم</p>
                            </div>
                            <div class="col-md-3">
                                <h5 class="mb-3">الديون والتسبيقات</h5>
                                <p>مجموع الديون: <?php echo number_format($summaries['debts']['total_debt'], 2); ?> درهم</p>
                                <p>المبالغ المدفوعة: <?php echo number_format($summaries['debts']['total_paid'], 2); ?> درهم</p>
                                <p>الديون المتبقية: <?php echo number_format($summaries['debts']['remaining'], 2); ?> درهم</p>
                                <p>التسبيقات: <?php echo number_format($summaries['debts']['advances'], 2); ?> درهم</p>
                            </div>
                            <div class="col-md-3">
                                <h5 class="mb-3">الفواتير</h5>
                                <p>المبلغ الإجمالي: <?php echo number_format($summaries['invoices']['total_amount'], 2); ?> درهم</p>
                                <p>الرسوم الثابتة: <?php echo number_format($summaries['invoices']['fixed_fees'], 2); ?> درهم</p>
                                <p> الضريبة على القيمة المضافة: <?php echo number_format($summaries['invoices']['collective_entitlement'], 2); ?> درهم</p>
                                <p>مبلغ الاستهلاك: <?php echo number_format($summaries['invoices']['consumption_amount'], 2); ?> درهم</p>
                                <p>الغرامات: <?php echo number_format($summaries['invoices']['penalties'], 2); ?> درهم</p>
                            </div>
                            <div class="col-md-3">
                                <h5 class="mb-3">إحصائيات عامة</h5>
                                <p>عدد العمليات: <?php echo count($transactions); ?></p>
                                <p>المداخيل الإجمالية: <?php echo number_format($summaries['cash_fund']['deposits'], 2); ?> درهم</p>
                                <p>المصاريف الإجمالية: <?php echo number_format($summaries['cash_fund']['withdrawals'], 2); ?> درهم</p>
                                <p>الرصيد الصافي: <?php echo number_format($summaries['cash_fund']['deposits'] - $summaries['cash_fund']['withdrawals'], 2); ?> درهم</p>
                            </div>
                        </div>
                    </div>
                    <div class="card-body">
                        <div class="table-responsive">
                            <table class="table table-striped table-hover">
                                <thead>
                                    <tr>
                                        <th>التاريخ</th>
                                        <th>النوع</th>
                                        <th>رقم العداد</th>
                                        <th>التفاصيل</th>
                                        <th>المبلغ</th>
                                        <th class="no-print">الإجراءات</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($transactions as $transaction): ?>
                                    <tr class="<?php 
                                        if ($transaction['type'] == 'cash_fund' && $transaction['transaction_type'] == 'WITHDRAWAL') echo 'table-danger';
                                        elseif ($transaction['type'] == 'cash_fund' && $transaction['transaction_type'] == 'DEPOSIT') echo 'table-success';
                                        elseif ($transaction['type'] == 'debt') echo 'table-warning';
                                        else echo '';
                                    ?>">
                                        <td><?php echo date('Y-m-d', strtotime($transaction['date'])); ?></td>
                                        <td>
                                            <?php
                                            switch ($transaction['type']) {
                                                case 'cash_fund':
                                                    echo $transaction['transaction_type'] == 'DEPOSIT' ? 'إيداع في الصندوق' : 'سحب من الصندوق';
                                                    break;
                                                case 'debt':
                                                    echo 'دين';
                                                    break;
                                                case 'invoice_payment':
                                                    echo 'دفع فاتورة';
                                                    break;
                                                default:
                                                    echo $transaction['type'];
                                            }
                                            ?>
                                        </td>
                                        <td><?php echo isset($transaction['meter_number']) ? $transaction['meter_number'] : ''; ?></td>
                                        <td><?php echo $transaction['details']; ?></td>
                                        <td><?php echo number_format($transaction['amount'], 2); ?> درهم</td>
                                        <td class="no-print">
                                            <button type="button" 
                                                    class="btn btn-sm btn-danger delete-record" 
                                                    data-id="<?php echo $transaction['id']; ?>"
                                                    data-type="<?php echo $transaction['type']; ?>">
                                                حذف
                                            </button>
                                        </td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </main>
        </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        $(document).ready(function() {
            // Print report
            $('#printReport').click(function() {
                window.print();
            });

            // Delete record
            $('.delete-record').click(function() {
                if (!confirm('هل أنت متأكد من حذف هذا السجل؟')) {
                    return;
                }

                const button = $(this);
                const type = button.data('type');
                const id = button.data('id');

                $.ajax({
                    url: 'financial_report.php',
                    type: 'POST',
                    data: {
                        action: 'delete_record',
                        type: type,
                        id: id
                    },
                    dataType: 'json',
                    success: function(response) {
                        if (response.success) {
                            location.reload();
                        } else {
                            alert(response.message || 'حدث خطأ أثناء حذف السجل');
                        }
                    },
                    error: function() {
                        alert('حدث خطأ في الاتصال بالخادم');
                    }
                });
            });

            // Date validation
            $('#end_date').change(function() {
                const startDate = $('#start_date').val();
                const endDate = $(this).val();
                if (startDate && endDate && startDate > endDate) {
                    alert('تاريخ النهاية يجب أن يكون بعد تاريخ البداية');
                    $(this).val(startDate);
                }
            });

            $('#start_date').change(function() {
                const startDate = $(this).val();
                const endDate = $('#end_date').val();
                if (startDate && endDate && startDate > endDate) {
                    alert('تاريخ البداية يجب أن يكون قبل تاريخ النهاية');
                    $(this).val(endDate);
                }
            });
        });
    </script>
    <!-- Print Footer -->
   
</body>
</html>
