<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';
require 'dompdf/vendor/autoload.php';
require 'dompdf/vendor/autoload.php'; // For PhpSpreadsheet

use Dompdf\Dompdf;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// --- Filter date ---
$filter_date = $_POST['filter_date'] ?? date('Y-m-d');

// --- Fetch total sales per payment method ---
$sales_sql = "
SELECT payment_method, SUM(total) AS sales_total
FROM sales
WHERE DATE(sale_date) = '$filter_date'
GROUP BY payment_method
";
$sales_result = $conn->query($sales_sql);

// --- Fetch total deposits per payment method ---
$deposit_sql = "
SELECT payment_method, SUM(amount) AS deposit_total
FROM cash_deposits
WHERE deposit_date = '$filter_date'
GROUP BY payment_method
";
$deposit_result = $conn->query($deposit_sql);

// Prepare arrays
$sales = [];
while($row = $sales_result->fetch_assoc()) $sales[$row['payment_method']] = $row['sales_total'];

$deposits = [];
while($row = $deposit_result->fetch_assoc()) $deposits[$row['payment_method']] = $row['deposit_total'];

$all_methods = array_unique(array_merge(array_keys($sales), array_keys($deposits)));

// --- Check for discrepancies for email alert ---
$discrepancy_exists = false;
foreach($all_methods as $method) {
    $sales_total = $sales[$method] ?? 0;
    $deposit_total = $deposits[$method] ?? 0;
    if($sales_total != $deposit_total) $discrepancy_exists = true;
}

// --- Email alert (optional) ---
if($discrepancy_exists){
    $to = "finance@yourcompany.com";
    $subject = "Reconciliation Alert for $filter_date";
    $message = "There is a discrepancy in payment methods on $filter_date. Please review the reconciliation system.";
    // Uncomment below to actually send
    // mail($to, $subject, $message);
}

// --- Handle Export ---
if(isset($_GET['export'])){
    $type = strtolower($_GET['export']);
    if($type == 'pdf'){
        $html = '<h3 style="text-align:center;">Reconciliation Report - '.$filter_date.'</h3>';
        $html .= '<table border="1" cellpadding="5" cellspacing="0" width="100%">';
        $html .= '<tr style="background:#000;color:#fff;">
                    <th>Payment Method</th>
                    <th>Total Sales</th>
                    <th>Total Deposits</th>
                    <th>Difference</th>
                    <th>Status</th>
                  </tr>';
        foreach($all_methods as $method){
            $sales_total = $sales[$method] ?? 0;
            $deposit_total = $deposits[$method] ?? 0;
            $diff = $sales_total - $deposit_total;
            $status = ($diff==0)?'Balanced':'Discrepancy';
            $html .= "<tr>
                        <td>$method</td>
                        <td>".number_format($sales_total,2)."</td>
                        <td>".number_format($deposit_total,2)."</td>
                        <td>".number_format($diff,2)."</td>
                        <td>$status</td>
                      </tr>";
        }
        $dompdf = new Dompdf();
        $dompdf->loadHtml($html);
        $dompdf->setPaper('A4','landscape');
        $dompdf->render();
        $dompdf->stream("reconciliation_$filter_date.pdf", ["Attachment"=>true]);
        exit;
    } elseif($type == 'excel'){
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Reconciliation Report');

        $sheet->fromArray(['Payment Method','Total Sales','Total Deposits','Difference','Status'],NULL,'A1');
        $row_num = 2;
        foreach($all_methods as $method){
            $sales_total = $sales[$method] ?? 0;
            $deposit_total = $deposits[$method] ?? 0;
            $diff = $sales_total - $deposit_total;
            $status = ($diff==0)?'Balanced':'Discrepancy';
            $sheet->setCellValue("A$row_num",$method);
            $sheet->setCellValue("B$row_num",$sales_total);
            $sheet->setCellValue("C$row_num",$deposit_total);
            $sheet->setCellValue("D$row_num",$diff);
            $sheet->setCellValue("E$row_num",$status);
            $row_num++;
        }

        $writer = new Xlsx($spreadsheet);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="reconciliation_'.$filter_date.'.xlsx"');
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }
}
?>

<div class="container mt-4">
      <!-- ================= WELCOME MESSAGE ================= -->
    
    <div class="d-flex justify-content-between align-items-center mb-3">
        <h3 class="text-center">Automated Reconciliation</h3>
        <div>
             <a href="deposits.php" class="btn btn-danger">Deposits</a>

            <a href="reconciliation.php?export=pdf" class="btn btn-primary">Export PDF</a>
            <a href="reconciliation.php?export=excel" class="btn btn-success">Export Excel</a>

        </div>
    </div>
    <hr>

    <form method="POST" class="row g-3 mb-4">
        <div class="col-md-6">
            <input type="date" name="filter_date" value="<?= htmlspecialchars($filter_date) ?>" class="form-control" required>
        </div>
        <div class="col-md-6">
            <button type="submit" class="btn btn-primary w-100">Filter</button>
        </div>
    </form>

    <table class="table table-bordered table-striped">
        <thead class="bg-dark text-white">
            <tr>
                <th>Payment Method</th>
                <th>Total Sales (MK)</th>
                <th>Total Deposits (MK)</th>
                <th>Difference (MK)</th>
                <th>Status</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $grand_sales = 0;
            $grand_deposits = 0;
            foreach($all_methods as $method){
                $sales_total = $sales[$method] ?? 0;
                $deposit_total = $deposits[$method] ?? 0;
                $diff = $sales_total - $deposit_total;

                $grand_sales += $sales_total;
                $grand_deposits += $deposit_total;

             $status_icon = ($diff == 0) 
    ? '<i class="fa fa-check-circle text-success"></i> Balanced' 
    : '<i class="fa fa-exclamation-triangle text-danger"></i> Discrepancy';

echo "
<tr>
    <td>$method</td>
    <td>".number_format($sales_total,2)."</td>
    <td>".number_format($deposit_total,2)."</td>
    <td>".number_format($diff,2)."</td>
    <td>$status_icon</td>
</tr>
";

            }
            ?>
        </tbody>
        <tfoot>
            <tr class="fw-bold">
                <td>Total</td>
                <td>MK <?= number_format($grand_sales,2) ?></td>
                <td>MK <?= number_format($grand_deposits,2) ?></td>
                <td>MK <?= number_format($grand_sales - $grand_deposits,2) ?></td>
                <td></td>
            </tr>
        </tfoot>
    </table>
</div>

<?php include 'includes/footer.php'; ?>
