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

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

$where = " WHERE i.payment_status='Paid' ";

if($month){
    if($report_type=='month'){
        $where .= " AND DATE_FORMAT(i.created_at,'%Y-%m')='".mysqli_real_escape_string($con,$month)."'";
    } elseif($report_type=='day'){
        $where .= " AND DATE(i.created_at)='".mysqli_real_escape_string($con,$month)."'";
    } elseif($report_type=='year'){
        $where .= " AND DATE_FORMAT(i.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 i.created_at DESC";

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

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="sales_report.csv"');

$output = fopen('php://output','w');
fputcsv($output, ['#','Date','Invoice No','Receipt No','Customer','Product','Subtotal','Discount','Shipping','Tax','Grand Total','Profit']);

$count = 1;
while($row = mysqli_fetch_assoc($result)){
    $subtotal = $row['price'] * $row['qty_set'];

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

    $tax = $row['tax'];
    $grandTotal = $row['grand_total'];
    $profit = $grandTotal - $totalItemCost - $tax;

    fputcsv($output, [
        $count++,
        $row['created_at'],
        $row['invoice_no'],
        $row['receipt_file'],
        $row['username'],
        $row['package_name'],
        number_format($subtotal,2),
        number_format($row['discount'],2),
        number_format($row['shipping_fee'],2),
        number_format($tax,2),
        number_format($grandTotal,2),
        number_format($profit,2)
    ]);
}

fclose($output);
exit;
