<?php
// Database connection details
$servername = "localhost";
$username = "alvycfgu_fredy";
$password = "@Lai19911011";
$dbname = "alvycfgu_OMEGA_CHURCH";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Function to fetch and process data
function getPartnershipData($conn) {
    $data = [];
    $partnershipTypes = ['COPPER', 'SILVER', 'GOLD', 'DIAMOND', 'PREMIUM', 'PLATINUM'];
    $year = date("Y"); // You can change this to a specific year

    foreach ($partnershipTypes as $type) {
        $query = "
            SELECT
                SUM(fc.amount) as total_contribution,
                COUNT(DISTINCT pa.partner_id) as total_partners
            FROM
                Partnership_Types pt
            JOIN
                Partnership_Agreements pa ON pt.partnership_type_id = pa.partnership_type_id
            JOIN
                Financial_Contributions fc ON pa.agreement_id = fc.agreement_id
            WHERE
                pt.type_name = ?
                AND fc.category_id = 7
                AND YEAR(fc.contribution_date) = ?
        ";

        $stmt = $conn->prepare($query);
        $stmt->bind_param("si", $type, $year);
        $stmt->execute();
        $result = $stmt->get_result();
        $summary = $result->fetch_assoc();

        $monthlyQuery = "
            SELECT
                MONTH(contribution_date) as month,
                SUM(amount) as monthly_total
            FROM
                Financial_Contributions fc
            JOIN
                Partnership_Agreements pa ON fc.agreement_id = pa.agreement_id
            JOIN
                Partnership_Types pt ON pa.partnership_type_id = pt.partnership_type_id
            WHERE
                pt.type_name = ?
                AND fc.category_id = 7
                AND YEAR(fc.contribution_date) = ?
            GROUP BY
                MONTH(contribution_date)
            ORDER BY
                MONTH(contribution_date)
        ";

        $stmt = $conn->prepare($monthlyQuery);
        $stmt->bind_param("si", $type, $year);
        $stmt->execute();
        $monthlyResult = $stmt->get_result();
        $monthlyData = [];
        $yearlyTotal = 0;

        while ($row = $monthlyResult->fetch_assoc()) {
            $monthlyData[$row['month']] = $row['monthly_total'];
            $yearlyTotal += $row['monthly_total'];
        }

        $monthlyContributions = [];
        for ($i = 1; $i <= 12; $i++) {
            $monthlyContributions[] = isset($monthlyData[$i]) ? number_format($monthlyData[$i], 2) : '0.00';
        }

        $data[$type] = [
            'summary' => $summary,
            'monthly' => $monthlyContributions,
            'yearly_total' => number_format($yearlyTotal, 2)
        ];
    }
    return $data;
}

$reportData = getPartnershipData($conn);
$conn->close();

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Partnership Contribution Report</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        body {
            background-color: #f8f9fa;
        }
        .container {
            margin-top: 50px;
        }
        .card {
            border: none;
            box-shadow: 0 4px 8px rgba(0,0,0,0.1);
        }
        .card-header {
            background-color: #0d6efd;
            color: white;
            font-weight: bold;
        }
        .summary-box {
            background-color: #e9ecef;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        .btn-generate {
            background-color: #28a745;
            color: white;
            border: none;
            padding: 10px 20px;
            border-radius: 5px;
            font-weight: bold;
            transition: background-color 0.3s;
        }
        .btn-generate:hover {
            background-color: #218838;
        }
    </style>
</head>
<body>

<div class="container">
    <h1 class="text-center mb-4">Partnership Contribution Report</h1>
    <div class="text-center mb-4">
        <button id="generatePdfBtn" class="btn btn-generate">Generate Report</button>
    </div>
    <hr>
    
    <?php foreach ($reportData as $type => $data): ?>
    <div class="card mb-5">
        <div class="card-header bg-primary text-white">
            <h3 class="mb-0"><?= htmlspecialchars(strtoupper($type)) ?> 💰</h3>
        </div>
        <div class="card-body">
            <div class="summary-box">
                <h4>Summary</h4>
                <p><strong>Total Contribution:</strong> TZS <?= htmlspecialchars(number_format($data['summary']['total_contribution'] ?? 0, 2)) ?></p>
                <p><strong>Total Partners:</strong> <?= htmlspecialchars($data['summary']['total_partners'] ?? 0) ?></p>
            </div>

            <div class="table-responsive">
                <table class="table table-bordered text-center">
                    <thead class="bg-light">
                        <tr>
                            <th>January</th>
                            <th>February</th>
                            <th>March</th>
                            <th>April</th>
                            <th>May</th>
                            <th>June</th>
                            <th>July</th>
                            <th>August</th>
                            <th>September</th>
                            <th>October</th>
                            <th>November</th>
                            <th>December</th>
                            <th class="bg-success text-white">YEARLY</th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr>
                            <td><?= $data['monthly'][0] ?></td>
                            <td><?= $data['monthly'][1] ?></td>
                            <td><?= $data['monthly'][2] ?></td>
                            <td><?= $data['monthly'][3] ?></td>
                            <td><?= $data['monthly'][4] ?></td>
                            <td><?= $data['monthly'][5] ?></td>
                            <td><?= $data['monthly'][6] ?></td>
                            <td><?= $data['monthly'][7] ?></td>
                            <td><?= $data['monthly'][8] ?></td>
                            <td><?= $data['monthly'][9] ?></td>
                            <td><?= $data['monthly'][10] ?></td>
                            <td><?= $data['monthly'][11] ?></td>
                            <td class="bg-success text-white"><strong><?= $data['yearly_total'] ?></strong></td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
    <?php endforeach; ?>

</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
<script>
    document.getElementById('generatePdfBtn').addEventListener('click', function() {
        window.open('partnership_contribution_summary_pdf.php', '_blank');
    });
</script>

</body>
</html>