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

// Ambil parameter
$month = isset($_GET['month']) ? $_GET['month'] : '';
$report_type = isset($_GET['report_type']) ? $_GET['report_type'] : 'day';

// Build WHERE clause
$where = "WHERE payment_status = 'Paid' ";
if (!empty($month)) {
    if ($report_type == 'day' || $report_type == 'month') {
        $where .= " AND DATE_FORMAT(created_at, '%Y-%m') = '" . mysqli_real_escape_string($con, $month) . "'";
    } elseif ($report_type == 'year') {
        $where .= " AND DATE_FORMAT(created_at, '%Y') = '" . mysqli_real_escape_string($con, substr($month, 0, 4)) . "'";
    }
}

// Ambil semua sales
$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);

$tableHtml   = '';
$labels      = [];
$salesData   = [];
$costData    = [];
$profitData  = [];
$taxData     = [];

$c = 1;
$totalSales  = 0;
$totalCost   = 0;
$totalProfit = 0;
$totalTax    = 0;

while ($row = mysqli_fetch_assoc($result)) {
    $username = $row['username'] ?? '-';

    $status = strtoupper($row['payment_status']);
    $badgeClass = 'badge-secondary';
    if ($status == 'PAID') $badgeClass = 'badge-success';
    elseif ($status == 'PENDING') $badgeClass = 'badge-warning';
    elseif ($status == 'FAILED') $badgeClass = 'badge-danger';
    elseif ($status == 'CANCELLED') $badgeClass = 'badge-dark';

    $lineTotal = $row['price'] * $row['qty_set'];

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

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

    // Table row
    $tableHtml .= '<tr>
        <td>' . $c++ . '</td>
        <td>' . date("d/m/Y", strtotime($row['created_at'])) . '</td>
        <td>' . htmlspecialchars($row['invoice_no']) . '</td>
        <td><code class="text-danger">' . htmlspecialchars($username) . '</code></td>
        <td>' . htmlspecialchars($row['package_name']) . '</td>
        <td>' . number_format($row['price'], 2) . '</td>
        <td>' . $row['qty_set'] . '</td>
        <td>' . number_format($lineTotal, 2) . '</td>
        <td>' . number_format($row['discount'], 2) . '</td>
        <td>' . number_format($row['shipping_fee'], 2) . '</td>
        <td>' . number_format($row['tax'], 2) . '</td>
        <td>' . number_format($totalItemCost, 2) . '</td>
        <td>' . number_format($row['grand_total'], 2) . '</td>
        <td>' . number_format($profit, 2) . '</td>
        <td><i class="fa-solid fa-circle-check text-success"></i> ' . htmlspecialchars($row['payment_method']) . '</td>
        <td style="width:70px;">
            <a target="_blank" href="view_invoice.php?invoice=' . urlencode($row['invoice_no']) . '">
                <i class="fa fa-2x fa-file-invoice-dollar"></i>
            </a>
            <a target="_blank" href="view_receipt.php?invoice=' . urlencode($row['invoice_no']) . '">
                <i class="fa fa-2x fa-receipt"></i>
            </a>
        </td>
    </tr>';

    // Group data for chart
    $label = $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 (!isset($labels[$label])) {
        $labels[$label]     = $label;
        $salesData[$label]  = 0;
        $costData[$label]   = 0;
        $profitData[$label] = 0;
        $taxData[$label]    = 0;
    }

    $salesData[$label]  += $row['grand_total'];
    $costData[$label]   += $totalItemCost;
    $profitData[$label] += $profit;
    $taxData[$label]    += $row['tax'];

    $totalSales  += $row['grand_total'];
    $totalCost   += $totalItemCost;
    $totalProfit += $profit;
    $totalTax    += $row['tax'];
}

// Kirim response JSON bersih
header('Content-Type: application/json; charset=utf-8');
echo json_encode([
    'tableHtml'   => $tableHtml,
    'labels'      => array_values($labels),
    'sales'       => array_values($salesData),
    'cost'        => array_values($costData),
    'profit'      => array_values($profitData),
    'tax'         => array_values($taxData),
    'totalSales'  => $totalSales,
    'totalCost'   => $totalCost,
    'totalProfit' => $totalProfit,
    'totalTax'    => $totalTax
]);
