<?php
// Include the tcpdf library
require_once('tcpdf/tcpdf_main/tcpdf.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 (same as in the main file)
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();

// Create new PDF document
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

// Set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Your Name');
$pdf->SetTitle('Partnership Contribution Report');
$pdf->SetSubject('Report on Partnership Contributions');

// Set default header data
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, 'Partnership Contribution Report', 'Generated on ' . date('Y-m-d H:i:s'));

// Set header and footer fonts
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

// Set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// Set margins
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

// Set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

// Set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// Set some language-dependent strings (optional)
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
    require_once(dirname(__FILE__).'/lang/eng.php');
    $pdf->setLanguageArray($l);
}

// Add a page
$pdf->AddPage();

// Set font
$pdf->SetFont('helvetica', '', 10);

// HTML content for the PDF
$html = '<h1 style="text-align: center;">Partnership Contribution Report</h1>';

foreach ($reportData as $type => $data) {
    $html .= '
    <hr>
    <h2>' . htmlspecialchars(strtoupper($type)) . ' Partnership Report</h2>
    <h3>Summary</h3>
    <p><strong>Total Contribution:</strong> TZS ' . htmlspecialchars($data['summary']['total_contribution'] ?? 0) . '</p>
    <p><strong>Total Partners:</strong> ' . htmlspecialchars($data['summary']['total_partners'] ?? 0) . '</p>
    <br>
    <h3>Monthly Contribution</h3>
    <table border="1" cellpadding="5" cellspacing="0" style="width: 100%; text-align: center;">
        <thead style="background-color: #e9ecef;">
            <tr>
                <th>Jan</th><th>Feb</th><th>Mar</th><th>Apr</th><th>May</th><th>Jun</th>
                <th>Jul</th><th>Aug</th><th>Sep</th><th>Oct</th><th>Nov</th><th>Dec</th>
                <th style="background-color: #28a745; color: 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 style="font-weight: bold; background-color: #28a745; color: white;">' . $data['yearly_total'] . '</td>
            </tr>
        </tbody>
    </table>
    <br><br>
    ';
}

$pdf->writeHTML($html, true, false, true, false, '');

// Close and output PDF document
$pdf->Output('partnership_report.pdf', 'I');
?>