<?php

/**
 * generate_reconciliation_pdf.php
 *
 * Generates and streams a PDF of the daily transaction reconciliation report
 * for a given date. Uses mPDF (already installed via Composer).
 *
 * Feature: daily-transaction-reconciliation
 * Requirements: 11.1, 11.2, 11.3, 11.4
 */

// ─── Session setup ────────────────────────────────────────────────────────────
session_name('super_admin_session');

if (!is_dir(dirname(__DIR__) . '/session_data/super')) {
    mkdir(dirname(__DIR__) . '/session_data/super', 0700, true);
}
session_save_path(dirname(__DIR__) . '/session_data/super');
session_set_cookie_params(['path' => '/', 'httponly' => true, 'samesite' => 'Strict']);
session_start();

// ─── Auth guard ───────────────────────────────────────────────────────────────
if (!isset($_SESSION['sid']) || strlen($_SESSION['sid']) == 0) {
    header('location:logout.php');
    exit();
}
if (!isset($_SESSION['permission']) || $_SESSION['permission'] !== 'Super User') {
    header('location:logout.php');
    exit();
}

// ─── Dependencies ─────────────────────────────────────────────────────────────
require_once 'vendor/autoload.php';
require_once 'helpers/ReconciliationHelper.php';
include('includes/dbconnection.php');

use Mpdf\Mpdf;
use Dotenv\Dotenv;

// Load environment variables
$dotenv = Dotenv::createImmutable(__DIR__);
$dotenv->load();

$schoolName    = $_ENV['SCHOOL_NAME']    ?? 'School Portal';
$schoolAddress = $_ENV['SCHOOL_ADDRESS'] ?? '';
$logo          = $_ENV['WEBSITE_LOGO']   ?? '';

// ─── Date validation ──────────────────────────────────────────────────────────
$rawDate      = $_GET['date'] ?? date('Y-m-d');
$selectedDate = validateReconciliationDate($rawDate);

// ─── Summary queries (no pagination — full data) ──────────────────────────────

// Paystack Total
$stmt = $con->prepare("SELECT COUNT(*), COALESCE(SUM(amount),0) FROM payments WHERE DATE(payment_date)=? AND LOWER(payment_method) IN ('paystack','bank transfer')");
$stmt->bind_param('s', $selectedDate);
$stmt->execute();
$stmt->bind_result($paystackCount, $paystackTotal);
$stmt->fetch();
$stmt->close();

// Paystack Card sub-total
$stmt = $con->prepare("SELECT COUNT(*), COALESCE(SUM(amount),0) FROM payments WHERE DATE(payment_date)=? AND LOWER(payment_method)='paystack'");
$stmt->bind_param('s', $selectedDate);
$stmt->execute();
$stmt->bind_result($cardCount, $cardTotal);
$stmt->fetch();
$stmt->close();

// Bank Transfer sub-total
$stmt = $con->prepare("SELECT COUNT(*), COALESCE(SUM(amount),0) FROM payments WHERE DATE(payment_date)=? AND LOWER(payment_method)='bank transfer'");
$stmt->bind_param('s', $selectedDate);
$stmt->execute();
$stmt->bind_result($bankCount, $bankTotal);
$stmt->fetch();
$stmt->close();

// Approved Total
$stmt = $con->prepare("SELECT COUNT(*), COALESCE(SUM(amount),0) FROM payment WHERE DATE(payment_date)=?");
$stmt->bind_param('s', $selectedDate);
$stmt->execute();
$stmt->bind_result($approvedCount, $approvedTotal);
$stmt->fetch();
$stmt->close();

// Wallet Total
$stmt = $con->prepare("SELECT COUNT(*), COALESCE(SUM(amount),0) FROM payment WHERE DATE(payment_date)=? AND method='Wallet'");
$stmt->bind_param('s', $selectedDate);
$stmt->execute();
$stmt->bind_result($walletCount, $walletTotal);
$stmt->fetch();
$stmt->close();

// Other Total
$stmt = $con->prepare("SELECT COUNT(*), COALESCE(SUM(amount),0) FROM payment WHERE DATE(payment_date)=? AND LOWER(method) NOT IN ('wallet','paystack')");
$stmt->bind_param('s', $selectedDate);
$stmt->execute();
$stmt->bind_result($otherCount, $otherTotal);
$stmt->fetch();
$stmt->close();

// Pending
$stmt = $con->prepare("SELECT COUNT(*), COALESCE(SUM(amount),0) FROM message WHERE DATE(timestamp)=? AND status=0");
$stmt->bind_param('s', $selectedDate);
$stmt->execute();
$stmt->bind_result($pendingCount, $pendingTotal);
$stmt->fetch();
$stmt->close();

// Approved Paystack-method (for variance)
// In the payment table, card payments use method='Paystack' only
$stmt = $con->prepare("SELECT COALESCE(SUM(amount),0) FROM payment WHERE DATE(payment_date)=? AND LOWER(method)='paystack'");
$stmt->bind_param('s', $selectedDate);
$stmt->execute();
$stmt->bind_result($approvedPaystackAmount);
$stmt->fetch();
$stmt->close();

// Derived values
$grandTotal = computeGrandTotal([(float)$approvedTotal]);
$variance   = computeVariance((float)$paystackTotal, (float)$approvedPaystackAmount);
$varianceLabel = ($variance == 0)
    ? 'Balanced'
    : 'Discrepancy Detected (' . formatNaira(abs($variance)) . ($variance > 0 ? ' surplus' : ' deficit') . ')';

// ─── Full transaction log (no LIMIT) ─────────────────────────────────────────
$dataSql = "SELECT p.studentName, p.studentno, p.pay_for AS purpose, p.amount,
                   p.method AS channel, p.Term AS term, p.session, p.payment_date,
                   'payment' AS source_table
            FROM payment p WHERE DATE(p.payment_date) = ?
            UNION ALL
            SELECT py.studentName, py.studentno, py.fee_type AS purpose, py.amount,
                   py.payment_method AS channel, '' AS term, '' AS session, py.payment_date,
                   'payments' AS source_table
            FROM payments py WHERE DATE(py.payment_date) = ?
            ORDER BY payment_date DESC";

$dataStmt = $con->prepare($dataSql);
$dataStmt->bind_param('ss', $selectedDate, $selectedDate);
$dataStmt->execute();
$txResult     = $dataStmt->get_result();
$transactions = [];
while ($row = $txResult->fetch_assoc()) {
    $transactions[] = $row;
}
$dataStmt->close();

// ─── Build HTML for PDF ───────────────────────────────────────────────────────
$formattedDate = date('d M Y', strtotime($selectedDate));

$html = "
<style>
    body        { font-family: Arial, sans-serif; font-size: 11px; color: #333; }
    h1          { font-size: 18px; margin: 0 0 2px; }
    h2          { font-size: 14px; margin: 10px 0 4px; }
    .center     { text-align: center; }
    .school-hdr { text-align: center; margin-bottom: 10px; }
    .school-hdr img { width: 60px; height: 60px; }
    hr          { border: 1px solid #ccc; margin: 6px 0; }
    table       { width: 100%; border-collapse: collapse; font-size: 10px; }
    th          { background-color: #2c3e50; color: #fff; padding: 6px 4px; text-align: left; }
    td          { padding: 5px 4px; border-bottom: 1px solid #eee; }
    tr:nth-child(even) td { background-color: #f9f9f9; }
    .summary-table th { background-color: #34495e; }
    .balanced   { color: #27ae60; font-weight: bold; }
    .discrepancy{ color: #e74c3c; font-weight: bold; }
    .no-records { text-align: center; color: #888; padding: 20px; font-style: italic; }
    .footer-note{ font-size: 9px; color: #888; margin-top: 8px; }
</style>

<div class='school-hdr'>
    " . (!empty($logo) ? "<img src='" . htmlspecialchars($logo) . "' alt='Logo'><br>" : '') . "
    <h1>" . htmlspecialchars($schoolName) . "</h1>
    <p>" . htmlspecialchars($schoolAddress) . "</p>
    <hr>
    <h2>Daily Transaction Reconciliation &mdash; {$formattedDate}</h2>
    <p style='font-size:9px;color:#888;'>Generated on " . date('d M Y H:i:s') . "</p>
</div>

<h2>Reconciliation Summary</h2>
<table class='summary-table' border='1'>
    <thead>
        <tr>
            <th>Category</th>
            <th>Count</th>
            <th>Amount</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td><strong>Paystack Inflows (Total)</strong></td>
            <td>" . (int)$paystackCount . "</td>
            <td>" . formatNaira((float)$paystackTotal) . "</td>
        </tr>
        <tr>
            <td>&nbsp;&nbsp;&nbsp;&#8627; Card / Inline</td>
            <td>" . (int)$cardCount . "</td>
            <td>" . formatNaira((float)$cardTotal) . "</td>
        </tr>
        <tr>
            <td>&nbsp;&nbsp;&nbsp;&#8627; Bank Transfer (DVA)</td>
            <td>" . (int)$bankCount . "</td>
            <td>" . formatNaira((float)$bankTotal) . "</td>
        </tr>
        <tr>
            <td><strong>Approved Payments (All Methods)</strong></td>
            <td>" . (int)$approvedCount . "</td>
            <td>" . formatNaira((float)$approvedTotal) . "</td>
        </tr>
        <tr>
            <td><strong>Wallet Payments</strong></td>
            <td>" . (int)$walletCount . "</td>
            <td>" . formatNaira((float)$walletTotal) . "</td>
        </tr>
        <tr>
            <td><strong>Other Payments</strong></td>
            <td>" . (int)$otherCount . "</td>
            <td>" . formatNaira((float)$otherTotal) . "</td>
        </tr>
        <tr>
            <td><strong>Pending (Unprocessed)</strong></td>
            <td>" . (int)$pendingCount . "</td>
            <td>" . formatNaira((float)$pendingTotal) . "</td>
        </tr>
        <tr>
            <td><strong>Grand Total (Approved)</strong></td>
            <td>&mdash;</td>
            <td><strong>" . formatNaira((float)$grandTotal) . "</strong></td>
        </tr>
        <tr>
            <td><strong>Reconciliation Variance</strong>
                <br><span style='font-size:8px;color:#888;'>Paystack inflows vs Paystack-method approved allocations</span>
            </td>
            <td>&mdash;</td>
            <td class='" . ($variance == 0 ? 'balanced' : 'discrepancy') . "'>" . $varianceLabel . "</td>
        </tr>
    </tbody>
</table>

<br>
<h2>Transaction Log</h2>
";

if (empty($transactions)) {
    $html .= "<p class='no-records'>No transactions found for this date.</p>";
} else {
    $html .= "
    <table border='1'>
        <thead>
            <tr>
                <th>#</th>
                <th>Student Name</th>
                <th>ADM No.</th>
                <th>Fee Type / Purpose</th>
                <th>Amount (&#8358;)</th>
                <th>Method</th>
                <th>Term</th>
                <th>Session</th>
                <th>Date &amp; Time</th>
                <th>Source</th>
            </tr>
        </thead>
        <tbody>";

    $cnt = 1;
    foreach ($transactions as $tx) {
        $html .= "<tr>
            <td>" . $cnt++ . "</td>
            <td>" . htmlspecialchars($tx['studentName']) . "</td>
            <td>" . htmlspecialchars($tx['studentno']) . "</td>
            <td>" . htmlspecialchars($tx['purpose']) . "</td>
            <td>" . formatNaira((float)$tx['amount']) . "</td>
            <td>" . htmlspecialchars($tx['channel']) . "</td>
            <td>" . htmlspecialchars($tx['term']) . "</td>
            <td>" . htmlspecialchars($tx['session']) . "</td>
            <td>" . date('d M Y H:i', strtotime($tx['payment_date'])) . "</td>
            <td>" . htmlspecialchars($tx['source_table']) . "</td>
        </tr>";
    }

    $html .= "
        </tbody>
    </table>
    <p class='footer-note'>Total records: " . count($transactions) . "</p>";
}

// ─── Generate and stream PDF ──────────────────────────────────────────────────
try {
    $mpdf = new Mpdf([
        'margin_top'    => 15,
        'margin_bottom' => 15,
        'margin_left'   => 12,
        'margin_right'  => 12,
        'orientation'   => 'L', // Landscape for wide table
    ]);

    $mpdf->SetHeader($schoolName . ' || Daily Transaction Reconciliation — ' . $formattedDate);
    $mpdf->SetFooter('Generated on ' . date('d M Y H:i') . ' || Page {PAGENO} of {nbpg}');
    $mpdf->WriteHTML($html);
    $mpdf->Output('reconciliation_' . $selectedDate . '.pdf', 'D');
} catch (\Exception $e) {
    error_log('ReconciliationPDF error: ' . $e->getMessage());
    header('location:daily_reconciliation.php?date=' . urlencode($selectedDate) . '&pdf_error=1');
    exit();
}
