<?php

include("db.php");
include("layout.php");
include 'auth.php';
require_right('Sales Report');

// Fetch payment method options from database
$payment_options = [];
$payment_options_result = mysqli_query($conn, "SELECT method_name, display_name FROM payment_methods WHERE is_active = 1 ORDER BY sort_order, method_name");
if ($payment_options_result) {
    while ($row = mysqli_fetch_assoc($payment_options_result)) {
        $payment_options[] = $row;
    }
} else {
    // Fallback if payment_methods table doesn't exist - use hardcoded values
    $payment_options = [
        ['method_name' => 'cash', 'display_name' => 'Cash'],
        ['method_name' => 'card', 'display_name' => 'Card'],
        ['method_name' => 'upi', 'display_name' => 'UPI'],
        ['method_name' => 'bank', 'display_name' => 'Bank'],
        ['method_name' => 'credit', 'display_name' => 'Credit']
    ];
}

// Use the new timezone system - handle combined datetime parameters
$start_datetime = $_GET['start_datetime'] ?? '';
$end_datetime = $_GET['end_datetime'] ?? '';

if ($start_datetime) {
    // Convert datetime-local format to MySQL datetime format
    $start_datetime_formatted = date('Y-m-d H:i:s', strtotime($start_datetime));
} else {
    // Default to yesterday 9:00 AM
    $start_datetime_formatted = date('Y-m-d H:i:s', strtotime('-1 day 09:00:00'));
}

if ($end_datetime) {
    // Convert datetime-local format to MySQL datetime format
    $end_datetime_formatted = date('Y-m-d H:i:s', strtotime($end_datetime));
} else {
    // Default to today 1:00 AM
    $end_datetime_formatted = date('Y-m-d H:i:s', strtotime('today 01:00:00'));
}

// Get other filter parameters
$selected_customer = $_GET['customer_id'] ?? '';
$selected_payment_method = $_GET['payment_method'] ?? '';
$selected_order_mode = $_GET['order_mode'] ?? '';

// Pagination parameters
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 50; // Default 50 records per page
$per_page = max(10, min(200, $per_page)); // Limit between 10 and 200 records
$offset = ($page - 1) * $per_page;

// Use the properly formatted datetime values for SQL queries
$start_datetime_sql = mysqli_real_escape_string($conn, $start_datetime_formatted);
$end_datetime_sql = mysqli_real_escape_string($conn, $end_datetime_formatted);
$customer_sql = mysqli_real_escape_string($conn, $selected_customer);
$payment_sql = mysqli_real_escape_string($conn, $selected_payment_method);
$order_mode_sql = mysqli_real_escape_string($conn, $selected_order_mode);

// Build the WHERE clause
$conditions = [];
if ($start_datetime && $end_datetime) {
    $conditions[] = "s.sale_date BETWEEN '$start_datetime_sql' AND '$end_datetime_sql'";
}
if ($selected_customer !== '') {
    $conditions[] = "s.customer_id = '$customer_sql'";
}
if ($selected_payment_method !== '') {
    $conditions[] = "s.payment_method = '$payment_sql'";
}
if ($selected_order_mode !== '') {
    $conditions[] = "s.order_mode = '$order_mode_sql'";
}

$where_clause = count($conditions) ? "WHERE " . implode(" AND ", $conditions) : "";

// First, get total count for pagination
$count_sql = "
    SELECT COUNT(*) as total_count
    FROM sales s
    LEFT JOIN shifts sh ON s.shift_id = sh.id
    LEFT JOIN users u ON sh.user_id = u.id
    LEFT JOIN customers c ON s.customer_id = c.id
    WHERE s.void_id IS NULL
    " . (count($conditions) ? "AND " . implode(" AND ", $conditions) : "") . "
";

$count_result = mysqli_query($conn, $count_sql);
$total_records = mysqli_fetch_assoc($count_result)['total_count'];
$total_pages = ceil($total_records / $per_page);

// Query to fetch paginated transactions (exclude voids)
$sql = "
    SELECT 
        s.invoice_number,
        s.sale_date,
        s.customer_name,
        s.customer_id,
        s.tax_amount,
        s.discount_amount,
        s.net_amount,
        s.total_amount,
        s.payment_method,
        s.order_mode,
        u.username AS shift_user,
        c.phone AS customer_phone
    FROM sales s
    LEFT JOIN shifts sh ON s.shift_id = sh.id
    LEFT JOIN users u ON sh.user_id = u.id
    LEFT JOIN customers c ON s.customer_id = c.id
    WHERE s.void_id IS NULL
    " . (count($conditions) ? "AND " . implode(" AND ", $conditions) : "") . "
    ORDER BY s.sale_date DESC
    LIMIT $per_page OFFSET $offset
";

$result = mysqli_query($conn, $sql);
if (!$result) die("Query failed: " . mysqli_error($conn));

// Calculate totals for ALL records (not just current page) for accurate summary
$totals_sql = "
    SELECT 
        COUNT(*) as transaction_count,
        SUM(s.tax_amount) as total_tax,
        SUM(s.discount_amount) as total_discount,
        SUM(s.net_amount) as total_net,
        SUM(s.total_amount) as total_gross
    FROM sales s
    LEFT JOIN shifts sh ON s.shift_id = sh.id
    LEFT JOIN users u ON sh.user_id = u.id
    LEFT JOIN customers c ON s.customer_id = c.id
    WHERE s.void_id IS NULL
    " . (count($conditions) ? "AND " . implode(" AND ", $conditions) : "") . "
";

$totals_result = mysqli_query($conn, $totals_sql);
$totals_data = mysqli_fetch_assoc($totals_result);
$total_tax = $totals_data['total_tax'] ?? 0;
$total_discount = $totals_data['total_discount'] ?? 0;
$total_net = $totals_data['total_net'] ?? 0;
$total_gross = $totals_data['total_gross'] ?? 0;
$transaction_count = $totals_data['transaction_count'] ?? 0;

// Store paginated results in array for display
$transactions = [];
while ($row = mysqli_fetch_assoc($result)) {
    $transactions[] = $row;
}

// Get customer list for filter
$customers_result = mysqli_query($conn, "SELECT id, name FROM customers WHERE active = 1 ORDER BY name");
$customers = [];
if ($customers_result) {
    while ($row = mysqli_fetch_assoc($customers_result)) {
        $customers[] = $row;
    }
}

// Get order mode options
$order_modes = ['dine_in', 'take_away', 'delivery'];
?>

<div class="page-wrapper">
    <div class="content">
        <div class="page-header d-flex justify-content-between align-items-center">
            <div class="page-title">
                <h4>Sales Report</h4>
                <h6>Detailed view of each sale transaction</h6>
            </div>
        </div>

        <!-- Filter Form -->
        <div class="card mb-4">
            <div class="card-header">
                <h5 class="mb-0">Filters</h5>
            </div>
            <div class="card-body">
                <form method="get" class="row g-3">
                    <div class="col-md-3">
                        <label for="start_datetime" class="form-label small">Start Date & Time</label>
                        <input type="datetime-local" name="start_datetime" id="start_datetime" class="form-control form-control-sm" value="<?= htmlspecialchars(date('Y-m-d\TH:i', strtotime($start_datetime_formatted))) ?>" required>
                    </div>
                    <div class="col-md-3">
                        <label for="end_datetime" class="form-label small">End Date & Time</label>
                        <input type="datetime-local" name="end_datetime" id="end_datetime" class="form-control form-control-sm" value="<?= htmlspecialchars(date('Y-m-d\TH:i', strtotime($end_datetime_formatted))) ?>" required>
                    </div>
                    <div class="col-md-2">
                        <label for="customer_id" class="form-label small">Customer</label>
                        <select name="customer_id" id="customer_id" class="form-control form-control-sm">
                            <option value="">All Customers</option>
                            <?php foreach ($customers as $customer): ?>
                                <option value="<?= $customer['id'] ?>" <?= $selected_customer == $customer['id'] ? 'selected' : '' ?>>
                                    <?= htmlspecialchars($customer['name']) ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label for="payment_method" class="form-label small">Payment Method</label>
                        <select name="payment_method" id="payment_method" class="form-control form-control-sm">
                            <option value="">All Methods</option>
                            <?php foreach ($payment_options as $payment): ?>
                                <option value="<?= $payment['method_name'] ?>" <?= $selected_payment_method == $payment['method_name'] ? 'selected' : '' ?>>
                                    <?= htmlspecialchars($payment['display_name']) ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label for="order_mode" class="form-label small">Order Mode</label>
                        <select name="order_mode" id="order_mode" class="form-control form-control-sm">
                            <option value="">All Modes</option>
                            <?php foreach ($order_modes as $mode): ?>
                                <option value="<?= $mode ?>" <?= $selected_order_mode == $mode ? 'selected' : '' ?>>
                                    <?= ucwords(str_replace('_', ' ', $mode)) ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label for="per_page" class="form-label small">Records per page</label>
                        <select name="per_page" id="per_page" class="form-control form-control-sm">
                            <option value="25" <?= $per_page == 25 ? 'selected' : '' ?>>25</option>
                            <option value="50" <?= $per_page == 50 ? 'selected' : '' ?>>50</option>
                            <option value="100" <?= $per_page == 100 ? 'selected' : '' ?>>100</option>
                            <option value="200" <?= $per_page == 200 ? 'selected' : '' ?>>200</option>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <button type="submit" class="btn btn-primary btn-sm">Apply Filters</button>
                        <a href="sales_report.php" class="btn btn-secondary btn-sm ms-2">Clear Filters</a>
                    </div>
                    <div class="col-md-6">
                        <small class="text-muted">Default: Yesterday 9:00 AM to Today 1:00 AM | Showing <?= number_format($total_records) ?> total records</small>
                    </div>
                </form>
            </div>
        </div>
<a href="export_excel.php?report=sales_report&start_datetime=<?= urlencode($start_datetime) ?>&end_datetime=<?= urlencode($end_datetime) ?>" 
   class="btn btn-success">
    Export to Excel
</a>

        <!-- Summary Cards -->
        <?php if ($transaction_count > 0): ?>
        <div class="row mb-4 mt-4">
            <div class="col-lg-2 col-md-6 col-sm-6">
                <div class="card text-center">
                    <div class="card-body">
                        <div class="d-flex align-items-center justify-content-center mb-2">
                            <div class="icon-wrapper bg-primary bg-opacity-10 rounded-circle p-2 me-2">
                                <i data-feather="file-text" class="text-primary"></i>
                            </div>
                        </div>
                        <h4 class="text-primary mb-1"><?= number_format($transaction_count) ?></h4>
                        <p class="text-muted mb-0">Total Transactions</p>
                    </div>
                </div>
            </div>
            <div class="col-lg-2 col-md-6 col-sm-6">
                <div class="card text-center">
                    <div class="card-body">
                        <div class="d-flex align-items-center justify-content-center mb-2">
                            <div class="icon-wrapper bg-info bg-opacity-10 rounded-circle p-2 me-2">
                                <i data-feather="trending-up" class="text-info"></i>
                            </div>
                        </div>
                        <h4 class="text-info mb-1"><?= number_format($total_gross, 2) ?></h4>
                        <p class="text-muted mb-0">Gross Sales</p>
                    </div>
                </div>
            </div>
            <div class="col-lg-2 col-md-6 col-sm-6">
                <div class="card text-center">
                    <div class="card-body">
                        <div class="d-flex align-items-center justify-content-center mb-2">
                            <div class="icon-wrapper bg-success bg-opacity-10 rounded-circle p-2 me-2">
                                <i data-feather="dollar-sign" class="text-success"></i>
                            </div>
                        </div>
                        <h4 class="text-success mb-1"><?= number_format($total_net, 2) ?></h4>
                        <p class="text-muted mb-0">Net Sales</p>
                    </div>
                </div>
            </div>
            <div class="col-lg-2 col-md-6 col-sm-6">
                <div class="card text-center">
                    <div class="card-body">
                        <div class="d-flex align-items-center justify-content-center mb-2">
                            <div class="icon-wrapper bg-warning bg-opacity-10 rounded-circle p-2 me-2">
                                <i data-feather="percent" class="text-warning"></i>
                            </div>
                        </div>
                        <h4 class="text-warning mb-1"><?= number_format($total_tax, 2) ?></h4>
                        <p class="text-muted mb-0">Total Tax</p>
                    </div>
                </div>
            </div>
            <div class="col-lg-2 col-md-6 col-sm-6">
                <div class="card text-center">
                    <div class="card-body">
                        <div class="d-flex align-items-center justify-content-center mb-2">
                            <div class="icon-wrapper bg-danger bg-opacity-10 rounded-circle p-2 me-2">
                                <i data-feather="tag" class="text-danger"></i>
                            </div>
                        </div>
                        <h4 class="text-danger mb-1"><?= number_format($total_discount, 2) ?></h4>
                        <p class="text-muted mb-0">Total Discount</p>
                    </div>
                </div>
            </div>
        </div>
        <?php endif; ?>

        <!-- Sales Table -->
        <div class="card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <strong>Sales Transactions</strong>
                <div class="text-muted small">
                    Showing <?= number_format($offset + 1) ?>-<?= number_format(min($offset + $per_page, $total_records)) ?> of <?= number_format($total_records) ?> records
                </div>
            </div>
            <div class="card-body table-responsive">
                <table class="table table-bordered table-striped">
                    <thead class="table-light">
                        <tr>
                            <th>Invoice #</th>
                            <th>Sale Date</th>
                            <th>Customer</th>
                            <th>Order Mode</th>
                            <th>Tax</th>
                            <th>Discount</th>
                            <th>Gross Amount</th>
                            <th>Net Amount</th>
                            <th>Payment</th>
                            <th>Shift User</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (count($transactions) > 0): ?>
                            <?php foreach ($transactions as $row): ?>
                                <tr>
                                    <td><?= htmlspecialchars($row['invoice_number']) ?></td>
                                    <td><?= date('M d, Y H:i', strtotime($row['sale_date'])) ?></td>
                                    <td>
                                        <div>
                                            <strong><?= htmlspecialchars($row['customer_name'] ?: 'Walk-in') ?></strong>
                                            <?php if ($row['customer_phone']): ?>
                                                <br><small class="text-muted"><?= htmlspecialchars($row['customer_phone']) ?></small>
                                            <?php endif; ?>
                                        </div>
                                    </td>
                                    <td>
                                        <span class="badge bg-<?= $row['order_mode'] == 'dine_in' ? 'success' : ($row['order_mode'] == 'delivery' ? 'warning' : 'info') ?>">
                                            <?= ucwords(str_replace('_', ' ', $row['order_mode'] ?: 'walk_in')) ?>
                                        </span>
                                    </td>
                                    <td><?= number_format($row['tax_amount'], 2) ?></td>
                                    <td><?= number_format($row['discount_amount'], 2) ?></td>
                                    <td><?= number_format($row['total_amount'], 2) ?></td>
                                    <td><strong><?= number_format($row['net_amount'], 2) ?></strong></td>
                                    <td>
                                        <span class="badge bg-<?= $row['payment_method'] == 'cash' ? 'success' : 'primary' ?>">
                                            <?php 
                                            // Get payment method display name
                                            $payment_display_name = ucfirst($row['payment_method']);
                                            foreach ($payment_options as $payment) {
                                                if ($payment['method_name'] === $row['payment_method']) {
                                                    $payment_display_name = $payment['display_name'] ?: ucfirst($row['payment_method']);
                                                    break;
                                                }
                                            }
                                            echo htmlspecialchars($payment_display_name);
                                            ?>
                                        </span>
                                    </td>
                                    <td><?= htmlspecialchars($row['shift_user'] ?? 'N/A') ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr><td colspan="10" class="text-center">No records found.</td></tr>
                        <?php endif; ?>
                    </tbody>
                    <?php if (count($transactions) > 0): ?>
                    <tfoot class="table-secondary fw-bold">
                        <tr>
                            <td colspan="4" class="text-end">Page Totals (<?= count($transactions) ?> records):</td>
                            <td><?= number_format(array_sum(array_column($transactions, 'tax_amount')), 2) ?></td>
                            <td><?= number_format(array_sum(array_column($transactions, 'discount_amount')), 2) ?></td>
                            <td><?= number_format(array_sum(array_column($transactions, 'total_amount')), 2) ?></td>
                            <td><strong><?= number_format(array_sum(array_column($transactions, 'net_amount')), 2) ?></strong></td>
                            <td colspan="2"></td>
                        </tr>
                        <tr class="table-success">
                            <td colspan="4" class="text-end">Grand Totals (<?= number_format($transaction_count) ?> records):</td>
                            <td><?= number_format($total_tax, 2) ?></td>
                            <td><?= number_format($total_discount, 2) ?></td>
                            <td><?= number_format($total_gross, 2) ?></td>
                            <td><strong><?= number_format($total_net, 2) ?></strong></td>
                            <td colspan="2"></td>
                        </tr>
                    </tfoot>
                    <?php endif; ?>
                </table>
            </div>
            
            <!-- Pagination Controls -->
            <?php if ($total_pages > 1): ?>
            <div class="card-footer">
                <nav aria-label="Sales report pagination">
                    <ul class="pagination pagination-sm justify-content-center mb-0">
                        <!-- Previous Page -->
                        <li class="page-item <?= $page <= 1 ? 'disabled' : '' ?>">
                            <a class="page-link" href="?<?= http_build_query(array_merge($_GET, ['page' => max(1, $page - 1)])) ?>">
                                <i class="fas fa-chevron-left"></i> Previous
                            </a>
                        </li>
                        
                        <!-- Page Numbers -->
                        <?php
                        $start_page = max(1, $page - 2);
                        $end_page = min($total_pages, $page + 2);
                        
                        // Show first page if not in range
                        if ($start_page > 1) {
                            echo '<li class="page-item"><a class="page-link" href="?' . http_build_query(array_merge($_GET, ['page' => 1])) . '">1</a></li>';
                            if ($start_page > 2) {
                                echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                            }
                        }
                        
                        // Show page numbers in range
                        for ($i = $start_page; $i <= $end_page; $i++) {
                            $active_class = $i == $page ? 'active' : '';
                            echo '<li class="page-item ' . $active_class . '">';
                            echo '<a class="page-link" href="?' . http_build_query(array_merge($_GET, ['page' => $i])) . '">' . $i . '</a>';
                            echo '</li>';
                        }
                        
                        // Show last page if not in range
                        if ($end_page < $total_pages) {
                            if ($end_page < $total_pages - 1) {
                                echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                            }
                            echo '<li class="page-item"><a class="page-link" href="?' . http_build_query(array_merge($_GET, ['page' => $total_pages])) . '">' . $total_pages . '</a></li>';
                        }
                        ?>
                        
                        <!-- Next Page -->
                        <li class="page-item <?= $page >= $total_pages ? 'disabled' : '' ?>">
                            <a class="page-link" href="?<?= http_build_query(array_merge($_GET, ['page' => min($total_pages, $page + 1)])) ?>">
                                Next <i class="fas fa-chevron-right"></i>
                            </a>
                        </li>
                    </ul>
                </nav>
                
                <!-- Quick Page Jump -->
                <div class="text-center mt-2">
                    <small class="text-muted">
                        Go to page: 
                        <select class="form-select form-select-sm d-inline-block w-auto ms-1" onchange="if(this.value) window.location.href='?<?= http_build_query(array_merge($_GET, ['page' => ''])) ?>' + this.value">
                            <option value="">Select page</option>
                            <?php for ($i = 1; $i <= $total_pages; $i++): ?>
                                <option value="<?= $i ?>" <?= $i == $page ? 'selected' : '' ?>><?= $i ?></option>
                            <?php endfor; ?>
                        </select>
                    </small>
                </div>
            </div>
            <?php endif; ?>
        </div>
    </div>
</div>

<script>
// Add datetime validation
document.addEventListener('DOMContentLoaded', function() {
    const startDatetimeInput = document.getElementById('start_datetime');
    const endDatetimeInput = document.getElementById('end_datetime');
    
    // Add tooltips
    startDatetimeInput.title = 'Start date and time';
    endDatetimeInput.title = 'End date and time';
    
    // Validate datetime range
    function validateDatetimeRange() {
        const startDatetime = startDatetimeInput.value;
        const endDatetime = endDatetimeInput.value;
        
        if (startDatetime && endDatetime) {
            const start = new Date(startDatetime);
            const end = new Date(endDatetime);
            
            if (end <= start) {
                endDatetimeInput.title = 'End datetime should be after start datetime';
                endDatetimeInput.setCustomValidity('End datetime must be after start datetime');
            } else {
                endDatetimeInput.title = 'End datetime is valid';
                endDatetimeInput.setCustomValidity('');
            }
        }
    }
    
    startDatetimeInput.addEventListener('change', validateDatetimeRange);
    endDatetimeInput.addEventListener('change', validateDatetimeRange);
    
    // Auto-submit on filter changes
    document.getElementById('customer_id').addEventListener('change', function() {
        if (startDatetimeInput.value && endDatetimeInput.value) {
            this.form.submit();
        }
    });
    
    document.getElementById('payment_method').addEventListener('change', function() {
        if (startDatetimeInput.value && endDatetimeInput.value) {
            this.form.submit();
        }
    });
    
    document.getElementById('order_mode').addEventListener('change', function() {
        if (startDatetimeInput.value && endDatetimeInput.value) {
            this.form.submit();
        }
    });
});
</script>
