<?php
include("db.php");

$report = $_GET['report'] ?? '';

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename={$report}_" . date('Y-m-d') . ".xls");
header("Pragma: no-cache");
header("Expires: 0");

echo "<style>td, th { border:1px solid #000; padding:5px; }</style>";

switch ($report) {
    // ================== DAILY SALES REPORT ===================
    case 'daily_sales':
        $start_date = $_GET['start_date'] ?? '';
        $end_date = $_GET['end_date'] ?? '';
        $tax_rate_id = $_GET['tax_rate'] ?? '';

        $conditions = [];
        if ($start_date && $end_date) {
            $conditions[] = "s.sale_date BETWEEN '$start_date 00:00:00' AND '$end_date 23:59:59'";
        }
        if ($tax_rate_id !== '') {
            $conditions[] = "i.tax_rate_id = '$tax_rate_id'";
        }
        $where_clause = count($conditions) ? "WHERE " . implode(" AND ", $conditions) : "";

        $sql = "
            SELECT 
                DATE(s.sale_date) AS sale_day,
                COUNT(DISTINCT s.id) AS invoice_count,
                SUM(si.total_price) AS net_sale,
                si.tax_rate,
                SUM(si.total_price * si.tax_rate / 100) AS tax_value
            FROM sales s
            JOIN sale_items si ON s.id = si.sale_id
            JOIN items i ON si.item_id = i.id
            $where_clause
            GROUP BY sale_day, si.tax_rate
            ORDER BY sale_day DESC, si.tax_rate
        ";
        $result = mysqli_query($conn, $sql);

        echo "<h3>Daily Sales Report</h3>";
        echo "<table>
                <tr>
                    <th>Date</th>
                    <th>No. of Invoices</th>
                    <th>Net Sale</th>
                    <th>Tax Rate</th>
                    <th>Tax Value</th>
                    <th>Gross Sale</th>
                </tr>";

        $total_invoices = 0;
        $total_net = 0;
        $total_tax = 0;

        while ($row = mysqli_fetch_assoc($result)) {
            $gross_sale = $row['net_sale'] + $row['tax_value'];
            $total_invoices += $row['invoice_count'];
            $total_net += $row['net_sale'];
            $total_tax += $row['tax_value'];

            echo "<tr>
                    <td>{$row['sale_day']}</td>
                    <td>{$row['invoice_count']}</td>
                    <td>" . number_format($row['net_sale'], 2) . "</td>
                    <td>" . number_format($row['tax_rate'], 2) . "%</td>
                    <td>" . number_format($row['tax_value'], 2) . "</td>
                    <td>" . number_format($gross_sale, 2) . "</td>
                </tr>";
        }

        echo "<tr style='font-weight:bold;background:#eee;'>
                <td>Total:</td>
                <td>{$total_invoices}</td>
                <td>" . number_format($total_net, 2) . "</td>
                <td>-</td>
                <td>" . number_format($total_tax, 2) . "</td>
                <td>" . number_format($total_net + $total_tax, 2) . "</td>
            </tr>";
        echo "</table>";
        break;

    // ================== SALES REPORT ===================
    case 'sales_report':
        $start_date = $_GET['start_date'] ?? '';
        $end_date = $_GET['end_date'] ?? '';

        $conditions = [];
        if ($start_date && $end_date) {
            $conditions[] = "s.sale_date BETWEEN '$start_date 00:00:00' AND '$end_date 23:59:59'";
        }
        $where_clause = count($conditions) ? "WHERE " . implode(" AND ", $conditions) : "";

        $sql = "
            SELECT 
                s.invoice_number,
                s.sale_date,
                s.customer_name,
                s.tax_amount,
                s.discount_amount,
                s.net_amount,
                s.payment_method,
                u.username AS shift_user
            FROM sales s
            LEFT JOIN shifts sh ON s.shift_id = sh.id
            LEFT JOIN users u ON sh.user_id = u.id
            $where_clause
            ORDER BY s.sale_date DESC
        ";

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

        echo "<h3>Sales Report</h3>";
        echo "<table>
                <tr>
                    <th>Invoice #</th>
                    <th>Sale Date</th>
                    <th>Customer</th>
                    <th>Tax</th>
                    <th>Discount</th>
                    <th>Net Amount</th>
                    <th>Payment</th>
                    <th>Shift User</th>
                </tr>";

        $total_tax = 0;
        $total_discount = 0;
        $total_net = 0;

        while ($row = mysqli_fetch_assoc($result)) {
            $total_tax += $row['tax_amount'];
            $total_discount += $row['discount_amount'];
            $total_net += $row['net_amount'];

            echo "<tr>
                    <td>{$row['invoice_number']}</td>
                    <td>" . date('Y-m-d H:i', strtotime($row['sale_date'])) . "</td>
                    <td>{$row['customer_name']}</td>
                    <td>{$row['tax_amount']}</td>
                    <td>{$row['discount_amount']}</td>
                    <td>{$row['net_amount']}</td>
                    <td>{$row['payment_method']}</td>
                    <td>" . ($row['shift_user'] ?? 'N/A') . "</td>
                </tr>";
        }

        echo "<tr style='font-weight:bold;background:#eee;'>
                <td colspan='3' align='right'>Totals:</td>
                <td>{$total_tax}</td>
                <td>{$total_discount}</td>
                <td>{$total_net}</td>
                <td colspan='2'></td>
            </tr>";
        echo "</table>";
        break;

    default:
        echo "Invalid report type.";
}
?>
