<?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;

    case 'tax_wise_report':
        $start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
        $end_date = $_GET['end_date'] ?? date('Y-m-d');

        // Fetch all tax rates
        $tax_rates = [];
        $tax_rate_query = mysqli_query($conn, "SELECT rate FROM tax_rates ORDER BY rate ASC");
        while ($tr = mysqli_fetch_assoc($tax_rate_query)) {
            $tax_rates[] = $tr['rate'];
        }

        $where = "";
        if ($start_date && $end_date) {
            $where = "WHERE s.sale_date BETWEEN '" . mysqli_real_escape_string($conn, $start_date) . " 00:00:00' 
                      AND '" . mysqli_real_escape_string($conn, $end_date) . " 23:59:59'";
        }

        // Build dynamic SQL for each tax rate (handle included/excluded/nil)
        $tax_columns = "";
        foreach ($tax_rates as $rate) {
            $alias = "tax_" . str_replace('.', '_', $rate);
            $tax_columns .= ", SUM(
                CASE 
                    WHEN i.tax_type = 'included' AND si.tax_rate = $rate THEN (si.total_price - (si.total_price / (1 + ($rate/100))))
                    WHEN i.tax_type = 'excluded' AND si.tax_rate = $rate THEN (si.total_price * ($rate/100))
                    ELSE 0
                END
            ) AS `$alias`";
        }

        $query = "
            SELECT
                DATE(s.sale_date) AS sale_date,
                SUM(
                    CASE 
                        WHEN i.tax_type = 'included' THEN si.total_price
                        WHEN i.tax_type = 'excluded' THEN si.total_price + (si.total_price * (si.tax_rate/100))
                        ELSE si.total_price
                    END
                ) AS gross_sales,
                SUM(
                    CASE 
                        WHEN i.tax_type = 'included' THEN si.total_price / (1 + (si.tax_rate/100))
                        ELSE si.total_price
                    END
                ) AS net_sales,
                SUM(
                    CASE 
                        WHEN i.tax_type = 'included' THEN (si.total_price - (si.total_price / (1 + (si.tax_rate/100))))
                        WHEN i.tax_type = 'excluded' THEN (si.total_price * (si.tax_rate/100))
                        ELSE 0
                    END
                ) AS total_tax
                $tax_columns,
                COUNT(DISTINCT s.id) AS total_invoices
            FROM
                sale_items si
                JOIN sales s ON si.sale_id = s.id
                JOIN items i ON si.item_id = i.id
            $where
            GROUP BY
                DATE(s.sale_date)
            ORDER BY
                sale_date ASC
        ";
        $result = mysqli_query($conn, $query);
        if (!$result) {
            die("Query failed: " . mysqli_error($conn));
        }

        // For totals row
        $totals = [
            'gross_sales' => 0,
            'total_tax' => 0,
            'net_sales' => 0,
            'total_invoices' => 0
        ];
        foreach ($tax_rates as $rate) {
            $totals["tax_" . str_replace('.', '_', $rate)] = 0;
        }

        echo "<h3>Tax Wise Sales Report</h3>";
        echo "<table><tr>";
        echo "<th>Date</th><th>Gross Sales (PKR)</th>";
        foreach ($tax_rates as $rate) {
            echo "<th>" . htmlspecialchars($rate) . "% Tax (PKR)</th>";
        }
        echo "<th>Total Tax (PKR)</th><th>Net Sales (PKR)</th><th>Total Invoices</th></tr>";

        while ($row = mysqli_fetch_assoc($result)) {
            $totals['gross_sales'] += $row['gross_sales'];
            foreach ($tax_rates as $rate) {
                $alias = "tax_" . str_replace('.', '_', $rate);
                $totals[$alias] += $row[$alias];
            }
            $totals['total_tax'] += $row['total_tax'];
            $totals['net_sales'] += $row['net_sales'];
            $totals['total_invoices'] += $row['total_invoices'];
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['sale_date']) . "</td>";
            echo "<td>" . number_format($row['gross_sales'], 2) . "</td>";
            foreach ($tax_rates as $rate) {
                $alias = "tax_" . str_replace('.', '_', $rate);
                echo "<td>" . number_format($row[$alias], 2) . "</td>";
            }
            echo "<td>" . number_format($row['total_tax'], 2) . "</td>";
            echo "<td>" . number_format($row['net_sales'], 2) . "</td>";
            echo "<td>" . $row['total_invoices'] . "</td>";
            echo "</tr>";
        }
        echo "<tr style='font-weight:bold;background:#eee;'>";
        echo "<td>Total</td>";
        echo "<td>" . number_format($totals['gross_sales'], 2) . "</td>";
        foreach ($tax_rates as $rate) {
            $alias = "tax_" . str_replace('.', '_', $rate);
            echo "<td>" . number_format($totals[$alias], 2) . "</td>";
        }
        echo "<td>" . number_format($totals['total_tax'], 2) . "</td>";
        echo "<td>" . number_format($totals['net_sales'], 2) . "</td>";
        echo "<td>" . $totals['total_invoices'] . "</td>";
        echo "</tr>";
        echo "</table>";
        break;

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