<?php
/**
 * RUHONDO BEACH RESORT HMS + POS System
 * Product Sales Report - FIXED DATABASE FUNCTIONS
 * Developer: JOACHIM'S INNOTECH
 */

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Calculate the correct path
$base_path = dirname(dirname(__DIR__)); // This goes up to /Applications/XAMPP/xamppfiles/htdocs/HMSPOS/

if (!file_exists($base_path . '/config/config.php')) {
    $base_path = dirname(__DIR__, 2);
}

if (!file_exists($base_path . '/config/config.php')) {
    $base_path = '/Applications/XAMPP/xamppfiles/htdocs/HMSPOS';
}

// Load required files
require_once $base_path . '/config/config.php';
require_once $base_path . '/config/database.php';
require_once $base_path . '/config/auth.php';

// Load helpers if they exist
if (file_exists($base_path . '/config/helpers.php')) {
    require_once $base_path . '/config/helpers.php';
}

// Check if Dompdf is available
$dompdf_available = false;
$dompdf_path = $base_path . '/vendor/autoload.php';
if (file_exists($dompdf_path)) {
    require_once $dompdf_path;
    if (class_exists('Dompdf\Dompdf')) {
        $dompdf_available = true;
    }
}

// Require authentication
require_auth();

// Define helper functions if they don't exist
if (!function_exists('escape_html')) {
    function escape_html($text) {
        return htmlspecialchars($text ?? '', ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('format_currency')) {
    function format_currency($amount) {
        return 'RWF ' . number_format($amount ?? 0, 0);
    }
}

if (!function_exists('format_date')) {
    function format_date($date, $format = 'M d, Y') {
        if (empty($date)) return '';
        return date($format, strtotime($date));
    }
}

// Set page title
$page_title = 'Product Sales Report';

// Get current user
$current_user = current_user();
$user_id = $current_user['user_id'] ?? 0;

// Get filter parameters
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$category_id = isset($_GET['category_id']) ? (int)$_GET['category_id'] : 0;
$outlet_id = isset($_GET['outlet_id']) ? (int)$_GET['outlet_id'] : 0;
$sort_by = $_GET['sort_by'] ?? 'profit';
$export = $_GET['export'] ?? '';

// Get categories for filter
$categories = [];
if (function_exists('db_fetch_all')) {
    $categories = db_fetch_all("SELECT category_id, category_name FROM pos_categories WHERE is_active = 1 ORDER BY category_name") ?: [];
}

// Get outlets for filter
$outlets = [];
if (function_exists('db_fetch_all')) {
    $outlets = db_fetch_all("SELECT outlet_id, outlet_name FROM pos_outlets WHERE is_active = 1 ORDER BY outlet_name") ?: [];
}

// Get report data
$report = get_product_sales_report($date_from, $date_to, $category_id, $outlet_id, $sort_by);

// Handle export
if ($export === 'pdf' && $dompdf_available) {
    export_pdf($report, $date_from, $date_to);
} elseif ($export === 'excel') {
    export_excel($report, $date_from, $date_to);
} elseif ($export === 'csv') {
    export_csv($report, $date_from, $date_to);
}

// Include header
$header_path = $base_path . '/views/partials/header.php';
if (file_exists($header_path)) {
    require_once $header_path;
} else {
    ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Product Sales Report</title>
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
        <script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
        <style>
            body { background-color: #f4f6f9; font-family: 'Segoe UI', sans-serif; }
            .content-wrapper { padding: 20px; }
            .card { border-radius: 0.5rem; box-shadow: 0 0 10px rgba(0,0,0,0.1); margin-bottom: 20px; }
            .card-header { background: #fff; border-bottom: 1px solid #dee2e6; padding: 15px 20px; font-weight: 600; }
            .small-box { border-radius: 0.5rem; box-shadow: 0 0 10px rgba(0,0,0,0.1); margin-bottom: 20px; position: relative; overflow: hidden; color: #fff; padding: 15px; }
            .small-box .inner { padding: 10px; }
            .small-box h3 { font-size: 2rem; font-weight: 700; margin: 0 0 10px 0; }
            .small-box p { font-size: 1rem; margin: 0; }
            .bg-info { background-color: #17a2b8 !important; }
            .bg-success { background-color: #28a745 !important; }
            .bg-warning { background-color: #ffc107 !important; color: #000; }
            .bg-danger { background-color: #dc3545 !important; }
            .bg-primary { background-color: #007bff !important; }
            .table th { background-color: #f8f9fa; }
            .text-success { color: #28a745 !important; }
            .text-danger { color: #dc3545 !important; }
            .profit-positive { color: #28a745; font-weight: bold; }
            .movement-in { color: #28a745; font-weight: bold; }
            .movement-out { color: #dc3545; font-weight: bold; }
            .stock-tracked-header { background-color: #28a745; color: white; padding: 10px 15px; margin: 20px 0 10px 0; border-radius: 5px; font-weight: bold; }
            .nonstock-header { background-color: #ffc107; color: black; padding: 10px 15px; margin: 20px 0 10px 0; border-radius: 5px; font-weight: bold; }
            .badge-stock { background-color: #28a745; color: white; padding: 3px 8px; border-radius: 3px; font-size: 0.7rem; }
            .badge-nonstock { background-color: #ffc107; color: black; padding: 3px 8px; border-radius: 3px; font-size: 0.7rem; }
        </style>
    </head>
    <body>
        <div class="wrapper">
            <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
                <div class="container-fluid">
                    <a class="navbar-brand" href="#">Product Sales Report</a>
                    <div class="navbar-nav ms-auto">
                        <span class="nav-item nav-link text-white">
                            <i class="fas fa-user"></i> <?php echo htmlspecialchars($current_user['username'] ?? 'User'); ?>
                        </span>
                        <a class="nav-item nav-link" href="/HMSPOS/public/logout.php">
                            <i class="fas fa-sign-out-alt"></i> Logout
                        </a>
                    </div>
                </div>
            </nav>
            <div class="content-wrapper">
    <?php
}
?>

<!-- Content Header -->
<div class="content-header">
    <div class="container-fluid">
        <div class="row mb-2">
            <div class="col-sm-6">
                <h1 class="m-0">Product Sales Report with Stock Movement</h1>
            </div>
            <div class="col-sm-6">
                <ol class="breadcrumb float-sm-right">
                    <li class="breadcrumb-item"><a href="/HMSPOS/public/dashboard.php">Home</a></li>
                    <li class="breadcrumb-item"><a href="/HMSPOS/pos/dashboard.php">POS</a></li>
                    <li class="breadcrumb-item active">Product Sales Report</li>
                </ol>
            </div>
        </div>
    </div>
</div>

<!-- Main content -->
<section class="content">
    <div class="container-fluid">
        <!-- Filters -->
        <div class="card">
            <div class="card-body">
                <form method="GET" class="row g-3">
                    <div class="col-md-2">
                        <label for="date_from" class="form-label">From:</label>
                        <input type="date" class="form-control" id="date_from" name="date_from" value="<?php echo $date_from; ?>">
                    </div>
                    
                    <div class="col-md-2">
                        <label for="date_to" class="form-label">To:</label>
                        <input type="date" class="form-control" id="date_to" name="date_to" value="<?php echo $date_to; ?>">
                    </div>
                    
                    <div class="col-md-2">
                        <label for="category_id" class="form-label">Category:</label>
                        <select class="form-select" id="category_id" name="category_id">
                            <option value="">All Categories</option>
                            <?php foreach ($categories as $cat): ?>
                                <option value="<?php echo $cat['category_id'] ?? 0; ?>" <?php echo $category_id == ($cat['category_id'] ?? 0) ? 'selected' : ''; ?>>
                                    <?php echo escape_html($cat['category_name'] ?? ''); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    
                    <div class="col-md-2">
                        <label for="outlet_id" class="form-label">Outlet:</label>
                        <select class="form-select" id="outlet_id" name="outlet_id">
                            <option value="">All Outlets</option>
                            <?php foreach ($outlets as $outlet): ?>
                                <option value="<?php echo $outlet['outlet_id'] ?? 0; ?>" <?php echo $outlet_id == ($outlet['outlet_id'] ?? 0) ? 'selected' : ''; ?>>
                                    <?php echo escape_html($outlet['outlet_name'] ?? ''); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    
                    <div class="col-md-2">
                        <label for="sort_by" class="form-label">Sort by:</label>
                        <select class="form-select" id="sort_by" name="sort_by">
                            <option value="profit" <?php echo $sort_by == 'profit' ? 'selected' : ''; ?>>Profit</option>
                            <option value="quantity" <?php echo $sort_by == 'quantity' ? 'selected' : ''; ?>>Quantity Sold</option>
                            <option value="revenue" <?php echo $sort_by == 'revenue' ? 'selected' : ''; ?>>Revenue</option>
                            <option value="name" <?php echo $sort_by == 'name' ? 'selected' : ''; ?>>Product Name</option>
                        </select>
                    </div>
                    
                    <div class="col-md-2 d-flex align-items-end">
                        <button type="submit" class="btn btn-primary w-100">
                            <i class="fas fa-search"></i> View Report
                        </button>
                    </div>
                    
                    <div class="col-12 mt-3">
                        <button type="button" class="btn btn-danger me-2" onclick="exportReport('pdf')" <?php echo !$dompdf_available ? 'disabled title="Dompdf not installed"' : ''; ?>>
                            <i class="fas fa-file-pdf"></i> PDF
                        </button>
                        
                        <button type="button" class="btn btn-success me-2" onclick="exportReport('excel')">
                            <i class="fas fa-file-excel"></i> Excel
                        </button>
                        
                        <button type="button" class="btn btn-info" onclick="exportReport('csv')">
                            <i class="fas fa-file-csv"></i> CSV
                        </button>
                        
                        <?php if (!$dompdf_available): ?>
                            <small class="text-muted ms-2">PDF export requires Dompdf. Run: composer require dompdf/dompdf</small>
                        <?php endif; ?>
                    </div>
                </form>
            </div>
        </div>

        <!-- Summary Cards -->
        <div class="row">
            <div class="col-lg-3 col-6">
                <div class="small-box bg-info">
                    <div class="inner">
                        <h3><?php echo number_format($report['summary']['total_products'] ?? 0); ?></h3>
                        <p>Products Sold</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-box"></i>
                    </div>
                </div>
            </div>
            
            <div class="col-lg-3 col-6">
                <div class="small-box bg-success">
                    <div class="inner">
                        <h3><?php echo number_format($report['summary']['total_quantity'] ?? 0); ?></h3>
                        <p>Units Sold</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-cubes"></i>
                    </div>
                </div>
            </div>
            
            <div class="col-lg-3 col-6">
                <div class="small-box bg-warning">
                    <div class="inner">
                        <h3><?php echo format_currency($report['summary']['total_revenue'] ?? 0); ?></h3>
                        <p>Total Revenue</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-dollar-sign"></i>
                    </div>
                </div>
            </div>
            
            <div class="col-lg-3 col-6">
                <div class="small-box bg-primary">
                    <div class="inner">
                        <h3><?php echo format_currency($report['summary']['total_profit'] ?? 0); ?></h3>
                        <p>Total Profit</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-chart-line"></i>
                    </div>
                </div>
            </div>
        </div>

        <!-- Stock-Tracked Categories Section -->
        <div class="stock-tracked-header">
            <i class="fas fa-beer"></i> STOCK-TRACKED CATEGORIES: SOFT DRINKS & ALCOHOLIC DRINKS
            <span class="badge-stock ms-2">Full Stock Movement & Profit Analysis</span>
        </div>
        
        <div class="row">
            <div class="col-12">
                <div class="card">
                    <div class="card-header bg-success text-white">
                        <h3 class="card-title">
                            <i class="fas fa-chart-bar me-1"></i>
                            Products with Stock Tracking
                        </h3>
                    </div>
                    
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-striped table-hover">
                                <thead>
                                    <tr>
                                        <th>Code</th>
                                        <th>Product</th>
                                        <th>Category</th>
                                        <th class="text-center">Open</th>
                                        <th class="text-center">Rec</th>
                                        <th class="text-center">Sold</th>
                                        <th class="text-center">Close</th>
                                        <th class="text-end">Price</th>
                                        <th class="text-end">Cost</th>
                                        <th class="text-end">Revenue</th>
                                        <th class="text-end">COGS</th>
                                        <th class="text-end">Profit</th>
                                        <th class="text-center">Margin</th>
                                        <th class="text-end">Stock Val</th>
                                        <th class="text-end">Potential</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if (empty($report['stock_tracked_products'])): ?>
                                        <tr>
                                            <td colspan="15" class="text-center text-muted py-5">
                                                <i class="fas fa-beer fa-3x mb-3"></i>
                                                <h5>No products from tracked categories sold in this period</h5>
                                            </td>
                                        </tr>
                                    <?php else: ?>
                                        <?php 
                                        $total_stock_revenue = 0;
                                        $total_stock_cost = 0;
                                        $total_stock_profit = 0;
                                        $total_stock_opening = 0;
                                        $total_stock_received = 0;
                                        $total_stock_closing = 0;
                                        $total_stock_value = 0;
                                        $total_stock_potential = 0;
                                        
                                        foreach ($report['stock_tracked_products'] as $product): 
                                            $margin = ($product['total_revenue'] ?? 0) > 0 
                                                ? (($product['total_profit'] ?? 0) / ($product['total_revenue'] ?? 1)) * 100 
                                                : 0;
                                            
                                            $profit_class = ($product['total_profit'] ?? 0) >= 0 ? 'profit-positive' : '';
                                            
                                            $total_stock_revenue += $product['total_revenue'] ?? 0;
                                            $total_stock_cost += $product['total_cost'] ?? 0;
                                            $total_stock_profit += $product['total_profit'] ?? 0;
                                            $total_stock_opening += $product['opening_stock'] ?? 0;
                                            $total_stock_received += $product['stock_received'] ?? 0;
                                            $total_stock_closing += $product['closing_stock'] ?? 0;
                                            $total_stock_value += $product['stock_cost_value'] ?? 0;
                                            $total_stock_potential += $product['potential_profit'] ?? 0;
                                        ?>
                                        <tr>
                                            <td><?php echo escape_html($product['product_code'] ?? ''); ?></td>
                                            <td><?php echo escape_html($product['product_name'] ?? ''); ?></td>
                                            <td><?php echo escape_html($product['category_name'] ?? ''); ?></td>
                                            <td class="text-center"><?php echo number_format($product['opening_stock'] ?? 0); ?></td>
                                            <td class="text-center movement-in"><?php echo number_format($product['stock_received'] ?? 0); ?></td>
                                            <td class="text-center movement-out"><?php echo number_format($product['total_quantity'] ?? 0); ?></td>
                                            <td class="text-center"><strong><?php echo number_format($product['closing_stock'] ?? 0); ?></strong></td>
                                            <td class="text-end"><?php echo format_currency($product['unit_price'] ?? 0); ?></td>
                                            <td class="text-end"><?php echo format_currency($product['cost_price'] ?? 0); ?></td>
                                            <td class="text-end"><?php echo format_currency($product['total_revenue'] ?? 0); ?></td>
                                            <td class="text-end"><?php echo format_currency($product['total_cost'] ?? 0); ?></td>
                                            <td class="text-end <?php echo $profit_class; ?>">
                                                <?php echo format_currency($product['total_profit'] ?? 0); ?>
                                            </td>
                                            <td class="text-center">
                                                <span class="badge <?php echo $margin >= 30 ? 'bg-success' : ($margin >= 15 ? 'bg-warning' : 'bg-danger'); ?>">
                                                    <?php echo number_format($margin, 1); ?>%
                                                </span>
                                            </td>
                                            <td class="text-end"><?php echo format_currency($product['stock_cost_value'] ?? 0); ?></td>
                                            <td class="text-end profit-positive">
                                                <?php echo format_currency($product['potential_profit'] ?? 0); ?>
                                            </td>
                                        </tr>
                                        <?php endforeach; ?>
                                    <?php endif; ?>
                                </tbody>
                                <?php if (!empty($report['stock_tracked_products'])): ?>
                                <tfoot class="table-light">
                                    <tr>
                                        <th colspan="3" class="text-end">Stock Tracked Totals:</th>
                                        <th class="text-center"><?php echo number_format($total_stock_opening); ?></th>
                                        <th class="text-center"><?php echo number_format($total_stock_received); ?></th>
                                        <th class="text-center"><?php echo number_format($report['stock_tracked_summary']['total_quantity'] ?? 0); ?></th>
                                        <th class="text-center"><?php echo number_format($total_stock_closing); ?></th>
                                        <th colspan="2"></th>
                                        <th class="text-end"><?php echo format_currency($total_stock_revenue); ?></th>
                                        <th class="text-end"><?php echo format_currency($total_stock_cost); ?></th>
                                        <th class="text-end profit-positive"><?php echo format_currency($total_stock_profit); ?></th>
                                        <th class="text-center"><?php echo number_format($report['stock_tracked_summary']['avg_margin'] ?? 0, 1); ?>%</th>
                                        <th class="text-end"><?php echo format_currency($total_stock_value); ?></th>
                                        <th class="text-end profit-positive"><?php echo format_currency($total_stock_potential); ?></th>
                                    </tr>
                                </tfoot>
                                <?php endif; ?>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Non-Stock Categories Section -->
        <div class="nonstock-header">
            <i class="fas fa-box-open"></i> NON-STOCK CATEGORIES: All Other Products
            <span class="badge-nonstock ms-2">Sales Summary Only</span>
        </div>
        
        <div class="row">
            <div class="col-12">
                <div class="card">
                    <div class="card-header bg-warning">
                        <h3 class="card-title">
                            <i class="fas fa-chart-simple me-1"></i>
                            Products without Stock Tracking
                        </h3>
                    </div>
                    
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-striped table-hover">
                                <thead>
                                    <tr>
                                        <th>Code</th>
                                        <th>Product</th>
                                        <th>Category</th>
                                        <th class="text-center">Quantity Sold</th>
                                        <th class="text-end">Unit Price</th>
                                        <th class="text-end">Revenue</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if (empty($report['non_stock_products'])): ?>
                                        <tr>
                                            <td colspan="6" class="text-center text-muted py-5">
                                                <i class="fas fa-box-open fa-3x mb-3"></i>
                                                <h5>No products from other categories sold in this period</h5>
                                            </td>
                                        </tr>
                                    <?php else: 
                                        $total_nonstock_revenue = 0;
                                        $total_nonstock_quantity = 0;
                                        
                                        foreach ($report['non_stock_products'] as $product): 
                                            $total_nonstock_revenue += $product['total_revenue'] ?? 0;
                                            $total_nonstock_quantity += $product['total_quantity'] ?? 0;
                                        ?>
                                        <tr>
                                            <td><?php echo escape_html($product['product_code'] ?? ''); ?></td>
                                            <td><?php echo escape_html($product['product_name'] ?? ''); ?></td>
                                            <td><?php echo escape_html($product['category_name'] ?? ''); ?></td>
                                            <td class="text-center"><?php echo number_format($product['total_quantity'] ?? 0); ?></td>
                                            <td class="text-end"><?php echo format_currency($product['unit_price'] ?? 0); ?></td>
                                            <td class="text-end"><?php echo format_currency($product['total_revenue'] ?? 0); ?></td>
                                        </tr>
                                        <?php endforeach; ?>
                                    <?php endif; ?>
                                </tbody>
                                <?php if (!empty($report['non_stock_products'])): ?>
                                <tfoot class="table-light">
                                    <tr>
                                        <th colspan="3" class="text-end">Non-Stock Totals:</th>
                                        <th class="text-center"><?php echo number_format($total_nonstock_quantity); ?></th>
                                        <th></th>
                                        <th class="text-end"><?php echo format_currency($total_nonstock_revenue); ?></th>
                                    </tr>
                                </tfoot>
                                <?php endif; ?>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

<script>
function exportReport(format) {
    const from = $('#date_from').val();
    const to = $('#date_to').val();
    const category = $('#category_id').val();
    const outlet = $('#outlet_id').val();
    const sort = $('#sort_by').val();
    window.location.href = '?date_from=' + from + '&date_to=' + to + '&category_id=' + category + '&outlet_id=' + outlet + '&sort_by=' + sort + '&export=' + format;
}
</script>

<?php
// Include footer
$footer_path = $base_path . '/views/partials/footer.php';
if (file_exists($footer_path)) {
    require_once $footer_path;
} else {
    echo '</div></div></body></html>';
}

/**
 * Get product sales report data - FIXED without db_escape
 */
function get_product_sales_report($date_from, $date_to, $category_id = 0, $outlet_id = 0, $sort_by = 'profit') {
    $report = [
        'summary' => [
            'total_products' => 0,
            'total_quantity' => 0,
            'total_revenue' => 0,
            'total_profit' => 0,
            'avg_margin' => 0,
            'total_stock_value' => 0,
            'potential_profit' => 0
        ],
        'stock_tracked_products' => [],
        'non_stock_products' => [],
        'stock_tracked_summary' => [
            'total_quantity' => 0,
            'total_revenue' => 0,
            'total_profit' => 0,
            'avg_margin' => 0
        ]
    ];
    
    try {
        // Get database connection using db_fetch_all if available
        if (!function_exists('db_fetch_all')) {
            return $report;
        }
        
        // Determine stock-tracked categories (SOFT DRINKS and Alcoholic Drinks)
        $stock_tracked_category_ids = [];
        $categories = db_fetch_all("SELECT category_id, category_name FROM pos_categories WHERE is_active = 1");
        
        if ($categories) {
            foreach ($categories as $cat) {
                $cat_name = strtolower($cat['category_name'] ?? '');
                if (strpos($cat_name, 'soft drink') !== false || 
                    strpos($cat_name, 'soft drinks') !== false ||
                    strpos($cat_name, 'soda') !== false ||
                    strpos($cat_name, 'alcoholic') !== false || 
                    strpos($cat_name, 'alcohol') !== false ||
                    strpos($cat_name, 'beer') !== false ||
                    strpos($cat_name, 'wine') !== false ||
                    strpos($cat_name, 'spirit') !== false) {
                    $stock_tracked_category_ids[] = (int)$cat['category_id'];
                }
            }
        }
        
        // Build query with parameterized approach using db_fetch_all's array parameter
        $sql = "
            SELECT 
                p.product_id, 
                p.product_code, 
                p.product_name, 
                p.unit_price,
                COALESCE(p.cost_price, p.unit_price * 0.7) as cost_price,
                c.category_name,
                c.category_id,
                COALESCE(p.current_stock, 0) as current_stock,
                COALESCE(SUM(si.quantity), 0) as total_quantity,
                COALESCE(SUM(si.total_amount), 0) as total_revenue,
                COALESCE(SUM(si.quantity * COALESCE(p.cost_price, p.unit_price * 0.7)), 0) as total_cost,
                COALESCE(SUM(si.total_amount - (si.quantity * COALESCE(p.cost_price, p.unit_price * 0.7))), 0) as total_profit,
                COALESCE(p.current_stock, 0) * COALESCE(p.cost_price, p.unit_price * 0.7) as stock_cost_value,
                COALESCE(p.current_stock, 0) * (p.unit_price - COALESCE(p.cost_price, p.unit_price * 0.7)) as potential_profit
            FROM pos_products p
            LEFT JOIN pos_categories c ON p.category_id = c.category_id
            LEFT JOIN pos_sale_items si ON p.product_id = si.product_id
            LEFT JOIN pos_sales s ON si.sale_id = s.sale_id AND s.payment_status = 'completed' 
                AND DATE(s.sale_time) BETWEEN ? AND ?
            WHERE p.is_active = 1";
        
        $params = [$date_from, $date_to];
        
        if ($outlet_id > 0) {
            $sql .= " AND s.outlet_id = ?";
            $params[] = $outlet_id;
        }
        
        if ($category_id > 0) {
            $sql .= " AND p.category_id = ?";
            $params[] = $category_id;
        }
        
        $sql .= " GROUP BY p.product_id";
        
        // Use db_fetch_all with parameters if supported
        if (function_exists('db_fetch_all') && method_exists('db_fetch_all', 'bind_param')) {
            $all_products = db_fetch_all($sql, $params) ?: [];
        } else {
            // Fallback to simple query without parameters
            $simple_sql = str_replace('?', "'$date_from' and '$date_to'", $sql);
            if ($outlet_id > 0) {
                $simple_sql = str_replace('?', $outlet_id, $simple_sql);
            }
            if ($category_id > 0) {
                $simple_sql = str_replace('?', $category_id, $simple_sql);
            }
            $all_products = db_fetch_all($simple_sql) ?: [];
        }
        
        // Separate products
        foreach ($all_products as $product) {
            $product['category_id'] = (int)$product['category_id'];
            if (in_array($product['category_id'], $stock_tracked_category_ids)) {
                // Calculate stock movement for tracked products
                $current_stock = $product['current_stock'] ?? 0;
                
                // Simplified stock calculation
                $product['opening_stock'] = $current_stock;
                $product['stock_received'] = 0;
                $product['closing_stock'] = $current_stock;
                
                $report['stock_tracked_products'][] = $product;
            } else {
                $report['non_stock_products'][] = $product;
            }
        }
        
        // Sort stock-tracked products
        if (!empty($report['stock_tracked_products'])) {
            usort($report['stock_tracked_products'], function($a, $b) use ($sort_by) {
                if ($sort_by == 'profit') {
                    return ($b['total_profit'] ?? 0) <=> ($a['total_profit'] ?? 0);
                } elseif ($sort_by == 'quantity') {
                    return ($b['total_quantity'] ?? 0) <=> ($a['total_quantity'] ?? 0);
                } elseif ($sort_by == 'revenue') {
                    return ($b['total_revenue'] ?? 0) <=> ($a['total_revenue'] ?? 0);
                } elseif ($sort_by == 'name') {
                    return strcasecmp($a['product_name'] ?? '', $b['product_name'] ?? '');
                }
                return 0;
            });
            
            // Calculate stock-tracked summary
            $report['stock_tracked_summary']['total_quantity'] = array_sum(array_column($report['stock_tracked_products'], 'total_quantity'));
            $report['stock_tracked_summary']['total_revenue'] = array_sum(array_column($report['stock_tracked_products'], 'total_revenue'));
            $report['stock_tracked_summary']['total_profit'] = array_sum(array_column($report['stock_tracked_products'], 'total_profit'));
            $report['stock_tracked_summary']['avg_margin'] = $report['stock_tracked_summary']['total_revenue'] > 0 
                ? ($report['stock_tracked_summary']['total_profit'] / $report['stock_tracked_summary']['total_revenue']) * 100 
                : 0;
        }
        
        // Calculate overall summary
        $all_products_combined = array_merge($report['stock_tracked_products'], $report['non_stock_products']);
        $report['summary']['total_products'] = count($all_products_combined);
        $report['summary']['total_quantity'] = array_sum(array_column($all_products_combined, 'total_quantity'));
        $report['summary']['total_revenue'] = array_sum(array_column($all_products_combined, 'total_revenue'));
        $report['summary']['total_profit'] = $report['stock_tracked_summary']['total_profit'];
        $report['summary']['total_stock_value'] = array_sum(array_column($report['stock_tracked_products'], 'stock_cost_value'));
        $report['summary']['potential_profit'] = array_sum(array_column($report['stock_tracked_products'], 'potential_profit'));
        $report['summary']['avg_margin'] = $report['stock_tracked_summary']['avg_margin'];
        
    } catch (Exception $e) {
        error_log("Error in get_product_sales_report: " . $e->getMessage());
    }
    
    return $report;
}

/**
 * Export as Excel
 */
function export_excel($report, $date_from, $date_to) {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="product_sales_report_' . date('Y-m-d', strtotime($date_from)) . '_to_' . date('Y-m-d', strtotime($date_to)) . '.xls"');
    
    echo "Product Sales Report\n";
    echo "Period: " . format_date($date_from) . " to " . format_date($date_to) . "\n\n";
    
    echo "SUMMARY\n";
    echo "Products Sold\t" . ($report['summary']['total_products'] ?? 0) . "\n";
    echo "Units Sold\t" . ($report['summary']['total_quantity'] ?? 0) . "\n";
    echo "Total Revenue\t" . ($report['summary']['total_revenue'] ?? 0) . "\n";
    echo "Total Profit\t" . ($report['summary']['total_profit'] ?? 0) . "\n\n";
    
    // Stock-Tracked Products
    if (!empty($report['stock_tracked_products'])) {
        echo "STOCK-TRACKED CATEGORIES: SOFT DRINKS & ALCOHOLIC DRINKS\n";
        echo "Code\tName\tCategory\tOpening\tReceived\tSold\tClosing\tPrice\tCost\tRevenue\tCOGS\tProfit\tMargin%\tStock Value\tPotential\n";
        foreach ($report['stock_tracked_products'] as $product) {
            $margin = ($product['total_revenue'] ?? 0) > 0 
                ? (($product['total_profit'] ?? 0) / ($product['total_revenue'] ?? 1)) * 100 
                : 0;
            
            echo ($product['product_code'] ?? '') . "\t";
            echo ($product['product_name'] ?? '') . "\t";
            echo ($product['category_name'] ?? '') . "\t";
            echo ($product['opening_stock'] ?? 0) . "\t";
            echo ($product['stock_received'] ?? 0) . "\t";
            echo ($product['total_quantity'] ?? 0) . "\t";
            echo ($product['closing_stock'] ?? 0) . "\t";
            echo ($product['unit_price'] ?? 0) . "\t";
            echo ($product['cost_price'] ?? 0) . "\t";
            echo ($product['total_revenue'] ?? 0) . "\t";
            echo ($product['total_cost'] ?? 0) . "\t";
            echo ($product['total_profit'] ?? 0) . "\t";
            echo number_format($margin, 1) . "%\t";
            echo ($product['stock_cost_value'] ?? 0) . "\t";
            echo ($product['potential_profit'] ?? 0) . "\n";
        }
        echo "\n";
    }
    
    // Non-Stock Products
    if (!empty($report['non_stock_products'])) {
        echo "NON-STOCK CATEGORIES: All Other Products\n";
        echo "Code\tName\tCategory\tQuantity Sold\tUnit Price\tRevenue\n";
        foreach ($report['non_stock_products'] as $product) {
            echo ($product['product_code'] ?? '') . "\t";
            echo ($product['product_name'] ?? '') . "\t";
            echo ($product['category_name'] ?? '') . "\t";
            echo ($product['total_quantity'] ?? 0) . "\t";
            echo ($product['unit_price'] ?? 0) . "\t";
            echo ($product['total_revenue'] ?? 0) . "\n";
        }
    }
    exit;
}

/**
 * Export as CSV
 */
function export_csv($report, $date_from, $date_to) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="product_sales_report_' . date('Y-m-d', strtotime($date_from)) . '_to_' . date('Y-m-d', strtotime($date_to)) . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    fputcsv($output, ['Product Sales Report - ' . format_date($date_from) . ' to ' . format_date($date_to)]);
    fputcsv($output, []);
    
    fputcsv($output, ['SUMMARY']);
    fputcsv($output, ['Products Sold', $report['summary']['total_products'] ?? 0]);
    fputcsv($output, ['Units Sold', $report['summary']['total_quantity'] ?? 0]);
    fputcsv($output, ['Total Revenue', $report['summary']['total_revenue'] ?? 0]);
    fputcsv($output, ['Total Profit', $report['summary']['total_profit'] ?? 0]);
    fputcsv($output, []);
    
    // Stock-Tracked Products
    if (!empty($report['stock_tracked_products'])) {
        fputcsv($output, ['STOCK-TRACKED CATEGORIES: SOFT DRINKS & ALCOHOLIC DRINKS']);
        fputcsv($output, ['Code', 'Name', 'Category', 'Opening', 'Received', 'Sold', 'Closing', 'Price', 'Cost', 'Revenue', 'COGS', 'Profit', 'Margin%', 'Stock Value', 'Potential']);
        foreach ($report['stock_tracked_products'] as $product) {
            $margin = ($product['total_revenue'] ?? 0) > 0 
                ? (($product['total_profit'] ?? 0) / ($product['total_revenue'] ?? 1)) * 100 
                : 0;
            
            fputcsv($output, [
                $product['product_code'] ?? '',
                $product['product_name'] ?? '',
                $product['category_name'] ?? '',
                $product['opening_stock'] ?? 0,
                $product['stock_received'] ?? 0,
                $product['total_quantity'] ?? 0,
                $product['closing_stock'] ?? 0,
                $product['unit_price'] ?? 0,
                $product['cost_price'] ?? 0,
                $product['total_revenue'] ?? 0,
                $product['total_cost'] ?? 0,
                $product['total_profit'] ?? 0,
                number_format($margin, 1) . '%',
                $product['stock_cost_value'] ?? 0,
                $product['potential_profit'] ?? 0
            ]);
        }
        fputcsv($output, []);
    }
    
    // Non-Stock Products
    if (!empty($report['non_stock_products'])) {
        fputcsv($output, ['NON-STOCK CATEGORIES: All Other Products']);
        fputcsv($output, ['Code', 'Name', 'Category', 'Quantity Sold', 'Unit Price', 'Revenue']);
        foreach ($report['non_stock_products'] as $product) {
            fputcsv($output, [
                $product['product_code'] ?? '',
                $product['product_name'] ?? '',
                $product['category_name'] ?? '',
                $product['total_quantity'] ?? 0,
                $product['unit_price'] ?? 0,
                $product['total_revenue'] ?? 0
            ]);
        }
    }
    
    fclose($output);
    exit;
}

// PDF export function placeholder
if (!function_exists('export_pdf')) {
    function export_pdf($report, $date_from, $date_to) {
        header('Content-Type: text/html');
        echo "<h1>PDF Export</h1>";
        echo "<p>PDF export requires Dompdf. Please install it using: composer require dompdf/dompdf</p>";
        echo "<p><a href='?date_from=$date_from&date_to=$date_to&export=excel'>Click here to download Excel instead</a></p>";
        exit;
    }
}
?>