<?php
declare(strict_types=1);
session_start();

require_once '../assets/db_connect.php';

// Start output buffering
ob_start();

require_once '../includes/header.php';
require_once '../includes/sidebar.php';

// Authentication check
if (!isset($_SESSION['user'])) { 
    echo "<div class='col-md-10 ml-sm-auto px-4 py-4'><div class='alert alert-danger'>Access denied.</div></div>";
    require_once '../includes/footer.php';
    exit; 
}

$user_role = $_SESSION['user']['role'] ?? 'cashier';
$user_shop_id = (int)($_SESSION['user']['shop_id'] ?? 0);
$user_id = (int)($_SESSION['user']['id'] ?? 0);

// Get filter parameters with validation
$from = $_GET['from'] ?? date('Y-m-01');
$to = $_GET['to'] ?? date('Y-m-d');

// Validate dates
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $to)) {
    $from = date('Y-m-01');
    $to = date('Y-m-d');
}

// Ensure from <= to
if (strtotime($from) > strtotime($to)) {
    $temp = $from;
    $from = $to;
    $to = $temp;
}

// Shop selection logic
if ($user_role === 'admin') {
    $shop_id = (int)($_GET['shop_id'] ?? 0);
} else {
    $shop_id = $user_shop_id;
}

/* ---- FETCH SHOPS FOR ADMIN ---- */
$shops = [];
if ($user_role === 'admin') {
    $shop_stmt = $pdo->prepare("SELECT id, name FROM shops ORDER BY name");
    $shop_stmt->execute();
    $shops = $shop_stmt->fetchAll(PDO::FETCH_ASSOC);
}

/* ---- MAIN SALES QUERY ---- */
$sql = "
SELECT 
    s.id,
    s.invoice_no,
    s.sale_date,
    s.total as gross_total,
    s.status,
    s.shop_id,
    COALESCE(c.name, 'Walk-in') AS customer,
    u.fullname AS cashier,
    sh.name AS shop_name,
    COALESCE(SUM(p.amount), 0) as paid_amount,
    (s.total - COALESCE(SUM(p.amount), 0)) as balance
FROM sales s
LEFT JOIN customers c ON s.customer_id = c.id
LEFT JOIN users u ON s.created_by = u.id
LEFT JOIN shops sh ON s.shop_id = sh.id
LEFT JOIN payments p ON s.id = p.sale_id
WHERE DATE(s.sale_date) BETWEEN :from_date AND :to_date
    AND s.status != 'cancelled'
";

$params = [
    ':from_date' => $from,
    ':to_date' => $to
];

if ($shop_id > 0) {
    $sql .= " AND s.shop_id = :shop_id";
    $params[':shop_id'] = $shop_id;
} elseif ($user_role !== 'admin' && $user_shop_id > 0) {
    $sql .= " AND s.shop_id = :shop_id";
    $params[':shop_id'] = $user_shop_id;
}

$sql .= " GROUP BY s.id, s.invoice_no, s.sale_date, s.total, s.status, s.shop_id, 
          c.name, u.fullname, sh.name
          ORDER BY s.sale_date DESC, s.id DESC";

try {
    $stmt = $pdo->prepare($sql);
    
    // Bind parameters with explicit types
    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value);
    }
    
    $stmt->execute();
    $sales = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Convert numeric strings to floats
    foreach ($sales as &$sale) {
        $sale['gross_total'] = (float)$sale['gross_total'];
        $sale['paid_amount'] = (float)$sale['paid_amount'];
        $sale['balance'] = (float)$sale['balance'];
    }
    unset($sale); // Break the reference
    
} catch (PDOException $e) {
    error_log("Sales query failed: " . $e->getMessage());
    $sales = [];
}

/* ---- AGGREGATE TOTALS ---- */
$totals = [
    'gross_total' => 0.0,
    'total_paid' => 0.0,
    'total_balance' => 0.0,
    'invoice_count' => 0,
    'customer_count' => 0,
    'open_count' => 0,
    'paid_count' => 0,
    'partial_count' => 0
];

if (!empty($sales)) {
    $totals['invoice_count'] = count($sales);
    $totals['gross_total'] = array_sum(array_column($sales, 'gross_total'));
    $totals['total_paid'] = array_sum(array_column($sales, 'paid_amount'));
    $totals['total_balance'] = array_sum(array_column($sales, 'balance'));
    
    // Count unique customers
    $customers = array_filter(array_column($sales, 'customer'), function($customer) {
        return $customer !== 'Walk-in';
    });
    $totals['customer_count'] = count(array_unique($customers));
    
    // Calculate payment status distribution
    foreach ($sales as $sale) {
        if ($sale['paid_amount'] == 0) {
            $totals['open_count']++;
        } elseif ($sale['paid_amount'] >= $sale['gross_total']) {
            $totals['paid_count']++;
        } else {
            $totals['partial_count']++;
        }
    }
}

/* ---- CSV EXPORT ---- */
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    // Clean output buffer
    ob_clean();
    
    // Set headers
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=sales_report_' . $from . '_to_' . $to . '.csv');
    
    // Output BOM for UTF-8 compatibility
    echo "\xEF\xBB\xBF";
    
    $output = fopen('php://output', 'w');
    
    // Add report header
    fputcsv($output, ['Sales Report']);
    fputcsv($output, ['Period:', $from . ' to ' . $to]);
    
    // Get shop name for CSV
    $shop_name = 'All Shops';
    if ($shop_id > 0) {
        foreach ($shops as $shop) {
            if ($shop['id'] == $shop_id) {
                $shop_name = $shop['name'];
                break;
            }
        }
    }
    fputcsv($output, ['Shop:', $shop_name]);
    fputcsv($output, ['Generated:', date('Y-m-d H:i:s')]);
    fputcsv($output, []); // Empty line
    
    // Column headers
    fputcsv($output, [
        'Invoice No',
        'Sale Date',
        'Customer',
        'Gross Total',
        'Amount Paid',
        'Balance',
        'Status',
        'Cashier',
        'Shop'
    ]);
    
    // Data rows
    foreach ($sales as $s) {
        fputcsv($output, [
            $s['invoice_no'],
            $s['sale_date'],
            $s['customer'],
            (float)$s['gross_total'], // Ensure float for CSV
            (float)$s['paid_amount'],
            (float)$s['balance'],
            $s['status'],
            $s['cashier'] ?? 'System',
            $s['shop_name'] ?? ''
        ]);
    }
    
    // Summary row
    fputcsv($output, [
        'TOTALS',
        '',
        '',
        $totals['gross_total'],
        $totals['total_paid'],
        $totals['total_balance'],
        "Invoices: {$totals['invoice_count']}",
        '',
        ''
    ]);
    
    // Payment summary
    fputcsv($output, [
        'PAYMENT SUMMARY',
        '',
        '',
        '',
        '',
        '',
        "Paid: {$totals['paid_count']}, Partial: {$totals['partial_count']}, Open: {$totals['open_count']}",
        '',
        ''
    ]);
    
    fclose($output);
    exit;
}
?>

<!-- PRINT CSS -->
<style>
@media print {
    body * { 
        visibility: hidden; 
        margin: 0;
        padding: 0;
    }
    #print-area, #print-area * { 
        visibility: visible; 
    }
    #print-area { 
        position: absolute; 
        left: 0; 
        top: 0; 
        width: 100%; 
        font-size: 12px;
    }
    .no-print { 
        display: none !important; 
    }
    table {
        page-break-inside: auto;
    }
    tr {
        page-break-inside: avoid;
        page-break-after: auto;
    }
    thead {
        display: table-header-group;
    }
}

/* Back button styling */
.back-btn {
    display: inline-flex;
    align-items: center;
    padding: 8px 16px;
    background-color: #6c757d;
    color: white;
    text-decoration: none;
    border-radius: 4px;
    border: none;
    cursor: pointer;
    font-size: 14px;
    margin-bottom: 15px;
    transition: background-color 0.2s;
    gap: 5px;
}

.back-btn:hover {
    background-color: #5a6268;
    color: white;
    text-decoration: none;
}

/* Status badges */
.status-badge {
    padding: 3px 8px;
    border-radius: 12px;
    font-size: 12px;
    font-weight: 500;
}

.status-open { background-color: #ffc107; color: #212529; }
.status-paid { background-color: #28a745; color: white; }
.status-partial { background-color: #17a2b8; color: white; }
.status-cancelled { background-color: #dc3545; color: white; }

/* Summary cards */
.summary-card {
    border: 1px solid #dee2e6;
    border-radius: 8px;
    padding: 15px;
    margin-bottom: 15px;
    background: #f8f9fa;
    transition: transform 0.2s;
}

.summary-card:hover {
    transform: translateY(-2px);
    box-shadow: 0 4px 6px rgba(0,0,0,0.1);
}

/* Table styling */
.table-hover tbody tr:hover {
    background-color: rgba(0,0,0,0.025);
}

/* Ensure numeric columns are right-aligned */
.text-end {
    text-align: right !important;
}
</style>

<div class="col-md-10 ml-sm-auto px-4 py-4">

<!-- BACK BUTTON 1: Top of the page -->
<div class="no-print mb-3">
    <a href="daily_sales.php" class="back-btn">
        <i class="fas fa-arrow-left"></i> Back to Reports
    </a>
</div>

<!-- FILTER FORM -->
<div class="no-print">
    <h3 class="text-success mb-4">Sales Between Dates</h3>
    
    <form method="GET" class="row g-3 mb-4">
        <div class="col-md-3">
            <label for="from" class="form-label">From Date</label>
            <input type="date" id="from" name="from" value="<?= htmlspecialchars($from) ?>" 
                   class="form-control form-control-sm" required>
        </div>
        
        <div class="col-md-3">
            <label for="to" class="form-label">To Date</label>
            <input type="date" id="to" name="to" value="<?= htmlspecialchars($to) ?>" 
                   class="form-control form-control-sm" required>
        </div>
        
        <?php if ($user_role === 'admin'): ?>
        <div class="col-md-3">
            <label for="shop_id" class="form-label">Shop</label>
            <select id="shop_id" name="shop_id" class="form-select form-select-sm">
                <option value="0">All Shops</option>
                <?php foreach($shops as $shop): ?>
                    <option value="<?= (int)$shop['id'] ?>" 
                        <?= ($shop_id === (int)$shop['id']) ? 'selected' : '' ?>>
                        <?= htmlspecialchars($shop['name']) ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>
        <?php else: ?>
            <input type="hidden" name="shop_id" value="<?= $shop_id ?>">
        <?php endif; ?>
        
        <div class="col-md-3 d-flex align-items-end">
            <div class="btn-group w-100" role="group">
                <button type="submit" class="btn btn-primary btn-sm">
                    <i class="fas fa-filter"></i> Filter
                </button>
                <a href="sales_range.php" class="btn btn-secondary btn-sm">
                    <i class="fas fa-sync"></i> Reset
                </a>
                <a href="?from=<?= $from ?>&to=<?= $to ?>&shop_id=<?= $shop_id ?>&export=csv" 
                   class="btn btn-success btn-sm">
                    <i class="fas fa-download"></i> CSV
                </a>
                <button type="button" onclick="window.print();" class="btn btn-dark btn-sm">
                    <i class="fas fa-print"></i> Print
                </button>
            </div>
        </div>
    </form>
</div>

<!-- SUMMARY CARDS -->
<?php if (!empty($sales)): ?>
<div class="row mb-4 no-print">
    <div class="col-md-3">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Total Invoices</h6>
            <h3 class="text-primary mb-0"><?= number_format($totals['invoice_count']) ?></h3>
            <small class="text-muted">Customers: <?= number_format($totals['customer_count']) ?></small>
        </div>
    </div>
    <div class="col-md-3">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Gross Revenue</h6>
            <h3 class="text-success mb-0"><?= number_format($totals['gross_total'], 2) ?></h3>
            <small class="text-muted">Average: <?= number_format($totals['gross_total'] / max($totals['invoice_count'], 1), 2) ?></small>
        </div>
    </div>
    <div class="col-md-3">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Amount Collected</h6>
            <h3 class="text-info mb-0"><?= number_format($totals['total_paid'], 2) ?></h3>
            <small class="text-muted">Collection Rate: <?= number_format(($totals['total_paid'] / max($totals['gross_total'], 1)) * 100, 1) ?>%</small>
        </div>
    </div>
    <div class="col-md-3">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Outstanding</h6>
            <h3 class="<?= $totals['total_balance'] > 0 ? 'text-warning' : 'text-success' ?> mb-0">
                <?= number_format($totals['total_balance'], 2) ?>
            </h3>
            <small class="text-muted">Open: <?= $totals['open_count'] ?>, Partial: <?= $totals['partial_count'] ?></small>
        </div>
    </div>
</div>
<?php endif; ?>

<!-- PRINT AREA START -->
<div id="print-area">
    <div class="text-center mb-4">
        <h4 class="mb-1"><strong>Sales Report</strong></h4>
        <p class="mb-1"><?= date('d M Y', strtotime($from)) ?> — <?= date('d M Y', strtotime($to)) ?></p>
        <p class="text-muted mb-0">
            <?php if ($shop_id == 0): ?>
                <em>All Shops</em>
            <?php else: 
                $shop_name = '';
                if (!empty($sales)) {
                    $shop_name = $sales[0]['shop_name'] ?? '';
                } elseif (!empty($shops)) {
                    foreach ($shops as $shop) {
                        if ($shop['id'] == $shop_id) {
                            $shop_name = $shop['name'];
                            break;
                        }
                    }
                }
            ?>
                <em>Shop: <?= htmlspecialchars($shop_name) ?></em>
            <?php endif; ?>
        </p>
        <p class="text-muted"><small>Generated: <?= date('d-M-Y H:i') ?></small></p>
    </div>

    <div class="table-responsive">
        <table class="table table-bordered table-sm table-hover">
            <thead class="table-light">
                <tr>
                    <th>#</th>
                    <th>Invoice</th>
                    <th>Date & Time</th>
                    <th>Customer</th>
                    <th class="text-end">Gross Total</th>
                    <th class="text-end">Paid</th>
                    <th class="text-end">Balance</th>
                    <th>Status</th>
                    <th>Cashier</th>
                    <?php if ($shop_id == 0): ?>
                    <th>Shop</th>
                    <?php endif; ?>
                </tr>
            </thead>

            <tbody>
                <?php if (empty($sales)): ?>
                    <tr>
                        <td colspan="<?= $shop_id == 0 ? 10 : 9 ?>" class="text-center py-4">
                            <div class="text-muted">
                                <i class="fas fa-inbox fa-2x mb-2"></i><br>
                                No sales found for the selected period
                            </div>
                        </td>
                    </tr>
                <?php else: ?>
                    <?php foreach($sales as $index => $s): 
                        // Determine payment status
                        $payment_status = 'open';
                        if ($s['paid_amount'] >= $s['gross_total']) {
                            $payment_status = 'paid';
                        } elseif ($s['paid_amount'] > 0) {
                            $payment_status = 'partial';
                        }
                    ?>
                        <tr>
                            <td><?= $index + 1 ?></td>
                            <td>
                                <a href="../sales/view_sale.php?id=<?= (int)$s['id'] ?>" 
                                   class="text-decoration-none" target="_blank">
                                    <?= htmlspecialchars($s['invoice_no']) ?>
                                </a>
                            </td>
                            <td><?= date('d-M-Y H:i', strtotime($s['sale_date'])) ?></td>
                            <td><?= htmlspecialchars($s['customer']) ?></td>
                            <td class="text-end"><?= number_format((float)$s['gross_total'], 2) ?></td>
                            <td class="text-end"><?= number_format((float)$s['paid_amount'], 2) ?></td>
                            <td class="text-end <?= $s['balance'] > 0 ? 'text-warning fw-bold' : '' ?>">
                                <?= number_format((float)$s['balance'], 2) ?>
                            </td>
                            <td>
                                <span class="status-badge status-<?= htmlspecialchars($s['status']) ?>">
                                    <?= ucfirst(str_replace('_', ' ', $s['status'])) ?>
                                </span>
                                <br>
                                <small class="text-muted">
                                    <?php if ($payment_status === 'paid'): ?>
                                        <span class="text-success">✓ Paid</span>
                                    <?php elseif ($payment_status === 'partial'): ?>
                                        <span class="text-info">↯ Partial</span>
                                    <?php else: ?>
                                        <span class="text-warning">○ Open</span>
                                    <?php endif; ?>
                                </small>
                            </td>
                            <td><?= htmlspecialchars($s['cashier'] ?? 'System') ?></td>
                            <?php if ($shop_id == 0): ?>
                            <td><?= htmlspecialchars($s['shop_name'] ?? '') ?></td>
                            <?php endif; ?>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>

            <?php if (!empty($sales)): ?>
            <tfoot class="table-active">
                <tr>
                    <th colspan="3" class="text-end">Summary:</th>
                    <th class="text-end"><?= number_format($totals['invoice_count']) ?> invoices</th>
                    <th class="text-end"><?= number_format($totals['gross_total'], 2) ?></th>
                    <th class="text-end"><?= number_format($totals['total_paid'], 2) ?></th>
                    <th class="text-end <?= $totals['total_balance'] > 0 ? 'text-warning fw-bold' : 'text-success' ?>">
                        <?= number_format($totals['total_balance'], 2) ?>
                    </th>
                    <th>
                        <small>
                            System: Paid: <?= $totals['paid_count'] ?>, 
                            Partial: <?= $totals['partial_count'] ?>,
                            Open: <?= $totals['open_count'] ?>
                        </small>
                    </th>
                    <th colspan="<?= $shop_id == 0 ? 2 : 1 ?>"></th>
                </tr>
            </tfoot>
            <?php endif; ?>
        </table>
    </div>
</div>
<!-- PRINT AREA END -->

<!-- BACK BUTTON 2: Bottom of the page -->
<div class="no-print mt-4">
    <a href="daily_sales.php" class="back-btn">
        <i class="fas fa-arrow-left"></i> Back to Reports
    </a>
</div>

</div>

<?php 
require_once '../includes/footer.php'; 
ob_end_flush();
?>