<?php
session_start();
include "conn.php";

$report_type = $_GET['report_type'] ?? 'day';
$month = $_GET['month'] ?? '';

$where = "WHERE payment_status='Paid' ";
if ($month) {
    if($report_type=='month') {
        $where .= " AND DATE_FORMAT(created_at, '%Y-%m') = '".mysqli_real_escape_string($con, $month)."'";
    } elseif($report_type=='day') {
        $where .= " AND DATE(created_at) = '".mysqli_real_escape_string($con, $month)."'";
    } elseif($report_type=='year') {
        $where .= " AND DATE_FORMAT(created_at, '%Y') = '".mysqli_real_escape_string($con, $month)."'";
    }
}

$sql = "SELECT i.*, a.username 
        FROM invoice i 
        LEFT JOIN ahli a ON i.customer_id=a.id
        $where 
        ORDER BY created_at ASC";

$result = mysqli_query($con, $sql);

$labels = [];
$chartSales = [];
$chartCost = [];
$chartProfit = [];
$chartTax = [];
$tableHtml = '';
$totalSales = 0;
$totalCost = 0;
$totalProfit = 0;
$totalTax = 0;
$c = 1;

while($row = mysqli_fetch_assoc($result)) {
    $username = $row['username'] ?? '-';
    $product = $row['package_name'];
    $pricePerSet = $row['price'];
    $qtySet = $row['qty_set'];

    // Calculate cost from package_desc JSON
$totalItemCost = 0;
if (!empty($row['package_desc'])) {
    $items = json_decode($row['package_desc'], true);
    if (json_last_error() === JSON_ERROR_NONE && is_array($items)) {
        foreach ($items as $item) {
            $itemQty  = $item['qty'] ?? 1;
            $itemCost = $item['cost_price'] ?? 0;
            $totalItemCost += $itemQty * $itemCost;
        }
    }
}


    $subtotal = $pricePerSet * $qtySet;
    $tax = $row['tax'];
    $grandTotal = $row['grand_total'];
    $profit = $grandTotal - $totalItemCost - $tax;

    $totalSales += $grandTotal;
    $totalCost += $totalItemCost;
    $totalProfit += $profit;
    $totalTax += $tax;

    $tableHtml .= '<tr>';
    $tableHtml .= '<td>'.$c++.'</td>';
    $tableHtml .= '<td>'.date("d/m/Y", strtotime($row['created_at'])).'</td>';
    $tableHtml .= '<td>'.$row['invoice_no'].'</td>';
    $tableHtml .= '<td>'.$username.'</td>';
    $tableHtml .= '<td>'.$product.'</td>';
    $tableHtml .= '<td>'.number_format($pricePerSet,2).'</td>';
    $tableHtml .= '<td>'.$qtySet.'</td>';
    $tableHtml .= '<td>'.number_format($subtotal,2).'</td>';
    $tableHtml .= '<td>'.number_format($row['discount'],2).'</td>';
    $tableHtml .= '<td>'.number_format($row['shipping_fee'],2).'</td>';
    $tableHtml .= '<td>'.number_format($tax,2).'</td>';
    $tableHtml .= '<td>'.number_format($grandTotal,2).'</td>';
    $tableHtml .= '<td>'.number_format($profit,2).'</td>';
    $tableHtml .= '</tr>';

    $labelKey = $report_type=='day' ? date("d M Y", strtotime($row['created_at'])) :
                ($report_type=='month' ? date("M Y", strtotime($row['created_at'])) : date("Y", strtotime($row['created_at'])));

    if(!in_array($labelKey, $labels)) $labels[] = $labelKey;

    $chartSales[$labelKey] = ($chartSales[$labelKey] ?? 0) + $grandTotal;
    $chartCost[$labelKey] = ($chartCost[$labelKey] ?? 0) + $totalItemCost;
    $chartProfit[$labelKey] = ($chartProfit[$labelKey] ?? 0) + $profit;
    $chartTax[$labelKey] = ($chartTax[$labelKey] ?? 0) + $tax;
}

$chartLabelsJson = json_encode(array_keys($chartSales));
$chartSalesJson = json_encode(array_values($chartSales));
$chartCostJson = json_encode(array_values($chartCost));
$chartProfitJson = json_encode(array_values($chartProfit));
$chartTaxJson = json_encode(array_values($chartTax));

$year = $month ? date("Y", strtotime($month.'-01')) : date("Y");
?>

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>Sales Report <?php echo $year; ?></title>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<style>
body { font-family: Arial, sans-serif; margin:0; background:#f0f0f0; }
.button-group { margin:15px 20px; }
.button-group button { font-size:16px; font-weight:bold; padding:10px 25px; margin-right:10px; border:none; border-radius:6px; cursor:pointer; }
#printBtn { background:#f39c12; color:#fff; }
#csvBtn { background:#2980b9; color:#fff; }
#reportTitle { text-align:center; font-size:28px; margin:10px 0; }
#reportSubtitle { text-align:center; font-size:16px; color:#555; margin-bottom:15px; }
#printArea { background:#fff; padding:20px; margin:0 20px 20px 20px; border-radius:10px; box-shadow:0 0 10px rgba(0,0,0,0.1); }
table { border-collapse: collapse; width: 100%; font-size:10px; page-break-inside:auto; }
table, th, td { border:1px solid #333; }
th, td { padding:6px; text-align:center; }
th { background:#ecf0f1; }
#salesChart { width: 100% !important; height: 400px !important; }
@media (max-width:768px){ #salesChart { height:300px !important; } }
@media print { .no-print { display:none; } body { margin:0; } #printArea { box-shadow:none; margin:0; } table { font-size:11px; } }
</style>
</head>
<body>

<div class="button-group no-print">
    <button id="printBtn">Print Report</button>
</div>

<div id="reportTitle">Sales Report <?php echo $year; ?></div>
<div id="reportSubtitle"><?php echo ucfirst($report_type); ?> Report<?php if($month) echo " - ".htmlspecialchars($month); ?></div>

<div id="printArea">
    <canvas id="salesChart"></canvas>

    <table>
    <thead>
    <tr class="small">
        <th>#</th><th>Date</th><th>Invoice No</th><th>Customer</th><th>Product</th>
        <th>Price (Per Set)</th><th>Qty Set</th>
        <th>Subtotal</th><th>Discount</th><th>Shipping</th><th>Tax</th><th>Grand Total</th><th>Profit</th>
    </tr>
    </thead>
    <tbody>
        <?php echo $tableHtml; ?>
    </tbody>
    </table>
</div>

<script>
document.getElementById('printBtn').addEventListener('click', function(){ window.print(); });

const ctx = document.getElementById('salesChart').getContext('2d');

new Chart(ctx, {
    type: 'bar',
    data: {
        labels: <?php echo $chartLabelsJson; ?>,
        datasets: [
            { label:'Total Sales (RM <?php echo number_format($totalSales,2); ?>)', data: <?php echo $chartSalesJson; ?>, backgroundColor:'rgba(54,162,235,0.7)', borderColor:'rgba(54,162,235,1)', borderWidth:1 },
            { label:'Total Cost (RM <?php echo number_format($totalCost,2); ?>)', data: <?php echo $chartCostJson; ?>, backgroundColor:'rgba(155,89,182,0.7)', borderColor:'rgba(142,68,173,1)', borderWidth:1 },
            { label:'Total Profit (RM <?php echo number_format($totalProfit,2); ?>)', data: <?php echo $chartProfitJson; ?>, backgroundColor:'rgba(39,174,96,0.7)', borderColor:'rgba(46,204,113,1)', borderWidth:1 },
            { label:'Total Tax (RM <?php echo number_format($totalTax,2); ?>)', data: <?php echo $chartTaxJson; ?>, backgroundColor:'rgba(243,156,18,0.7)', borderColor:'rgba(241,196,15,1)', borderWidth:1 }
        ]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        plugins: {
            legend: { position:'top' },
            tooltip: {
                callbacks: {
                    label: function(context) {
                        return context.dataset.label + ': RM ' + context.raw.toLocaleString(undefined,{minimumFractionDigits:2, maximumFractionDigits:2});
                    }
                }
            }
        },
        scales: {
            y: { beginAtZero:true, title:{ display:true, text:'Amount (RM)' }, ticks:{ callback: function(value){ return 'RM '+value.toLocaleString(); } } },
            x: { title:{ display:true, text:'<?php echo ucfirst($report_type); ?>' } }
        }
    }
});
</script>

</body>
</html>
