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

// 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 PRODUCT SALES QUERY WITH SUBTOTALS ---- */
$sql = "
WITH sales_summary AS (
    SELECT 
        p.id as product_id,
        p.name as product_name,
        p.sku as product_sku,
        p.category as product_category,
        p.unit as product_unit,
        sh.id as shop_id,
        sh.name as shop_name,
        SUM(sl.qty) as total_quantity,
        SUM(sl.qty * sl.unit_price) as gross_revenue,
        SUM(sl.discount) as total_discount,
        SUM(sl.line_total) as net_revenue,
        COUNT(DISTINCT s.id) as invoice_count
    FROM sale_lines sl
    INNER JOIN sales s ON sl.sale_id = s.id AND s.status != 'cancelled'
    INNER JOIN products p ON sl.product_id = p.id
    LEFT JOIN shops sh ON s.shop_id = sh.id
    WHERE DATE(s.sale_date) BETWEEN :from_date AND :to_date
";

$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 p.id, p.name, p.sku, p.category, p.unit, sh.id, sh.name
)
SELECT 
    *,
    ROUND(net_revenue / NULLIF(total_quantity, 0), 2) as avg_price_per_unit,
    ROUND(net_revenue / NULLIF(invoice_count, 0), 2) as avg_revenue_per_invoice
FROM sales_summary
ORDER BY net_revenue DESC, total_quantity DESC
";

try {
    $stmt = $pdo->prepare($sql);
    
    // Bind parameters
    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value);
    }
    
    $stmt->execute();
    $product_sales = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Convert numeric strings to floats
    foreach ($product_sales as &$item) {
        $item['total_quantity'] = (float)$item['total_quantity'];
        $item['gross_revenue'] = (float)$item['gross_revenue'];
        $item['total_discount'] = (float)$item['total_discount'];
        $item['net_revenue'] = (float)$item['net_revenue'];
        $item['avg_price_per_unit'] = (float)$item['avg_price_per_unit'];
        $item['avg_revenue_per_invoice'] = (float)$item['avg_revenue_per_invoice'];
    }
    unset($item); // Break the reference
    
} catch (PDOException $e) {
    error_log("Product sales query failed: " . $e->getMessage());
    $product_sales = [];
}

/* ---- CALCULATE TOTALS AND SUMMARIES ---- */
$summary = [
    'total_products' => 0,
    'total_quantity' => 0.0,
    'total_gross_revenue' => 0.0,
    'total_discount' => 0.0,
    'total_net_revenue' => 0.0,
    'total_invoices' => 0,
    'categories' => [],
    'top_product' => null
];

if (!empty($product_sales)) {
    $summary['total_products'] = count($product_sales);
    $summary['total_quantity'] = array_sum(array_column($product_sales, 'total_quantity'));
    $summary['total_gross_revenue'] = array_sum(array_column($product_sales, 'gross_revenue'));
    $summary['total_discount'] = array_sum(array_column($product_sales, 'total_discount'));
    $summary['total_net_revenue'] = array_sum(array_column($product_sales, 'net_revenue'));
    $summary['total_invoices'] = array_sum(array_column($product_sales, 'invoice_count'));
    
    // Get top product
    $summary['top_product'] = $product_sales[0] ?? null;
    
    // Calculate category distribution
    foreach ($product_sales as $item) {
        $category = $item['product_category'] ?? 'Uncategorized';
        if (!isset($summary['categories'][$category])) {
            $summary['categories'][$category] = [
                'count' => 0,
                'quantity' => 0.0,
                'revenue' => 0.0
            ];
        }
        $summary['categories'][$category]['count']++;
        $summary['categories'][$category]['quantity'] += $item['total_quantity'];
        $summary['categories'][$category]['revenue'] += $item['net_revenue'];
    }
    
    // Sort categories by revenue
    uasort($summary['categories'], function($a, $b) {
        return $b['revenue'] <=> $a['revenue'];
    });
}

/* ---- 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=product_sales_' . $from . '_to_' . $to . '.csv');
    
    // Output BOM for UTF-8 compatibility
    echo "\xEF\xBB\xBF";
    
    $output = fopen('php://output', 'w');
    
    // Add report header
    fputcsv($output, ['Product 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, [
        'SKU',
        'Product Name',
        'Category',
        'Unit',
        'Quantity Sold',
        'Gross Revenue',
        'Discount',
        'Net Revenue',
        'Avg Price/Unit',
        'Shop',
        'Invoices'
    ]);
    
    // Data rows
    foreach ($product_sales as $item) {
        fputcsv($output, [
            $item['product_sku'] ?? '',
            $item['product_name'] ?? '',
            $item['product_category'] ?? '',
            $item['product_unit'] ?? '',
            $item['total_quantity'],
            $item['gross_revenue'],
            $item['total_discount'],
            $item['net_revenue'],
            $item['avg_price_per_unit'],
            $item['shop_name'] ?? '',
            $item['invoice_count'] ?? 0
        ]);
    }
    
    // Summary rows
    fputcsv($output, []); // Empty line
    fputcsv($output, ['SUMMARY', '', '', '', '', '', '', '', '', '', '']);
    fputcsv($output, [
        'Total Products',
        '',
        '',
        '',
        $summary['total_quantity'],
        $summary['total_gross_revenue'],
        $summary['total_discount'],
        $summary['total_net_revenue'],
        '',
        '',
        $summary['total_invoices']
    ]);
    
    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: 11px;
    }
    .no-print { 
        display: none !important; 
    }
    .summary-cards {
        display: none;
    }
}

/* 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;
}

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

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

/* Badges for categories */
.category-badge {
    display: inline-block;
    padding: 4px 10px;
    margin: 2px;
    background-color: #e9ecef;
    border-radius: 15px;
    font-size: 12px;
    color: #495057;
}

/* Progress bar for revenue contribution */
.progress-thin {
    height: 6px;
    margin-top: 5px;
}

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

.text-end {
    text-align: right !important;
}

/* Chart container */
.chart-container {
    height: 300px;
    margin: 20px 0;
}
</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">Product Sales Analysis</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_by_product.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($product_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">Products Sold</h6>
            <h3 class="text-primary mb-0"><?= number_format($summary['total_products']) ?></h3>
            <small class="text-muted">Categories: <?= count($summary['categories']) ?></small>
        </div>
    </div>
    <div class="col-md-3">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Total Quantity</h6>
            <h3 class="text-info mb-0"><?= number_format($summary['total_quantity']) ?></h3>
            <small class="text-muted">Avg/Product: <?= number_format($summary['total_quantity'] / max($summary['total_products'], 1), 1) ?></small>
        </div>
    </div>
    <div class="col-md-3">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Net Revenue</h6>
            <h3 class="text-success mb-0"><?= number_format($summary['total_net_revenue'], 2) ?></h3>
            <small class="text-muted">Discount: <?= number_format($summary['total_discount'], 2) ?></small>
        </div>
    </div>
    <div class="col-md-3">
        <div class="summary-card text-center">
            <h6 class="text-muted mb-2">Top Product</h6>
            <h5 class="mb-1"><?= htmlspecialchars($summary['top_product']['product_name'] ?? 'N/A') ?></h5>
            <small class="text-muted">
                <?= number_format($summary['top_product']['net_revenue'] ?? 0, 2) ?> 
                (<?= number_format($summary['top_product']['total_quantity'] ?? 0) ?> units)
            </small>
        </div>
    </div>
</div>

<!-- CATEGORY DISTRIBUTION -->
<?php if (!empty($summary['categories'])): ?>
<div class="row mb-4 no-print">
    <div class="col-12">
        <div class="card">
            <div class="card-header bg-light">
                <h6 class="mb-0">Revenue by Category</h6>
            </div>
            <div class="card-body p-3">
                <div class="row">
                    <?php 
                    $counter = 0;
                    foreach ($summary['categories'] as $category => $data): 
                        if ($counter >= 6) break; // Show top 6 categories
                        $percentage = ($data['revenue'] / max($summary['total_net_revenue'], 1)) * 100;
                    ?>
                    <div class="col-md-4 mb-2">
                        <div class="d-flex justify-content-between align-items-center">
                            <span class="text-truncate" style="max-width: 120px;" title="<?= htmlspecialchars($category) ?>">
                                <?= htmlspecialchars($category ?: 'Uncategorized') ?>
                            </span>
                            <span class="text-success fw-bold">
                                <?= number_format($percentage, 1) ?>%
                            </span>
                        </div>
                        <div class="progress progress-thin">
                            <div class="progress-bar bg-success" role="progressbar" 
                                 style="width: <?= $percentage ?>%" 
                                 aria-valuenow="<?= $percentage ?>" 
                                 aria-valuemin="0" 
                                 aria-valuemax="100"></div>
                        </div>
                        <small class="text-muted d-block">
                            <?= number_format($data['revenue'], 2) ?> 
                            (<?= number_format($data['quantity']) ?> units)
                        </small>
                    </div>
                    <?php 
                        $counter++;
                    endforeach; 
                    ?>
                </div>
            </div>
        </div>
    </div>
</div>
<?php endif; ?>
<?php endif; ?>

<!-- PRINT AREA START -->
<div id="print-area">
    <div class="text-center mb-4">
        <h4 class="mb-1"><strong>Product 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($product_sales)) {
                    $shop_name = $product_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>
        <?php if (!empty($product_sales)): ?>
        <p class="text-muted mb-0">
            <small>
                Showing <?= number_format($summary['total_products']) ?> products, 
                <?= number_format($summary['total_quantity']) ?> units, 
                <?= number_format($summary['total_net_revenue'], 2) ?> revenue
            </small>
        </p>
        <?php endif; ?>
    </div>

    <div class="table-responsive">
        <table class="table table-bordered table-sm table-hover">
            <thead class="table-light">
                <tr>
                    <th>#</th>
                    <th>Product Details</th>
                    <th class="text-end">Qty Sold</th>
                    <th class="text-end">Gross Revenue</th>
                    <th class="text-end">Discount</th>
                    <th class="text-end">Net Revenue</th>
                    <th class="text-end">Avg Price</th>
                    <?php if ($shop_id == 0): ?>
                    <th>Shop</th>
                    <?php endif; ?>
                    <th>Invoices</th>
                </tr>
            </thead>

            <tbody>
                <?php if (empty($product_sales)): ?>
                    <tr>
                        <td colspan="<?= $shop_id == 0 ? 9 : 8 ?>" class="text-center py-4">
                            <div class="text-muted">
                                <i class="fas fa-box-open fa-2x mb-2"></i><br>
                                No product sales found for the selected period
                            </div>
                        </td>
                    </tr>
                <?php else: ?>
                    <?php foreach($product_sales as $index => $item): 
                        $revenue_percentage = ($item['net_revenue'] / max($summary['total_net_revenue'], 1)) * 100;
                    ?>
                        <tr>
                            <td><?= $index + 1 ?></td>
                            <td>
                                <div class="fw-bold"><?= htmlspecialchars($item['product_name']) ?></div>
                                <div class="small text-muted">
                                    SKU: <?= htmlspecialchars($item['product_sku'] ?? 'N/A') ?> | 
                                    Category: <?= htmlspecialchars($item['product_category'] ?? 'Uncategorized') ?> | 
                                    Unit: <?= htmlspecialchars($item['product_unit'] ?? 'N/A') ?>
                                </div>
                                <div class="progress progress-thin mt-1">
                                    <div class="progress-bar bg-info" role="progressbar" 
                                         style="width: <?= min($revenue_percentage, 100) ?>%" 
                                         aria-valuenow="<?= $revenue_percentage ?>" 
                                         aria-valuemin="0" 
                                         aria-valuemax="100"></div>
                                </div>
                                <small class="text-muted"><?= number_format($revenue_percentage, 1) ?>% of total revenue</small>
                            </td>
                            <td class="text-end fw-bold"><?= number_format((float)$item['total_quantity']) ?></td>
                            <td class="text-end"><?= number_format((float)$item['gross_revenue'], 2) ?></td>
                            <td class="text-end text-danger"><?= number_format((float)$item['total_discount'], 2) ?></td>
                            <td class="text-end fw-bold text-success"><?= number_format((float)$item['net_revenue'], 2) ?></td>
                            <td class="text-end"><?= number_format((float)$item['avg_price_per_unit'], 2) ?></td>
                            <?php if ($shop_id == 0): ?>
                            <td><?= htmlspecialchars($item['shop_name'] ?? '') ?></td>
                            <?php endif; ?>
                            <td class="text-center">
                                <span class="badge bg-secondary"><?= (int)($item['invoice_count'] ?? 0) ?></span>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>

            <?php if (!empty($product_sales)): ?>
            <tfoot class="table-active">
                <tr>
                    <th colspan="2" class="text-end">TOTALS (<?= number_format($summary['total_products']) ?> products):</th>
                    <th class="text-end"><?= number_format($summary['total_quantity']) ?></th>
                    <th class="text-end"><?= number_format($summary['total_gross_revenue'], 2) ?></th>
                    <th class="text-end"><?= number_format($summary['total_discount'], 2) ?></th>
                    <th class="text-end fw-bold"><?= number_format($summary['total_net_revenue'], 2) ?></th>
                    <th class="text-end">
                        <?= number_format($summary['total_net_revenue'] / max($summary['total_quantity'], 1), 2) ?>
                    </th>
                    <?php if ($shop_id == 0): ?>
                    <th></th>
                    <?php endif; ?>
                    <th class="text-center"><?= number_format($summary['total_invoices']) ?></th>
                </tr>
            </tfoot>
            <?php endif; ?>
        </table>
    </div>
    
    <!-- CATEGORY SUMMARY FOR PRINT -->
    <?php if (!empty($summary['categories']) && count($summary['categories']) <= 10): ?>
    <div class="mt-4">
        <h6 class="border-bottom pb-2">Category Summary</h6>
        <div class="row">
            <?php foreach($summary['categories'] as $category => $data): 
                $percentage = ($data['revenue'] / max($summary['total_net_revenue'], 1)) * 100;
            ?>
            <div class="col-6 mb-2">
                <div class="d-flex justify-content-between">
                    <span class="text-truncate" style="max-width: 150px;">
                        <?= htmlspecialchars($category ?: 'Uncategorized') ?>
                    </span>
                    <span class="fw-bold">
                        <?= number_format($data['revenue'], 2) ?> 
                        (<?= number_format($percentage, 1) ?>%)
                    </span>
                </div>
            </div>
            <?php endforeach; ?>
        </div>
    </div>
    <?php endif; ?>
</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();
?>