<?php
include 'includes/config.php';

if (isset($_POST['id'])) {
    $product_id = $_POST['id'];

    $query = $conn->prepare("
        SELECT 
            p.name AS product_name, 
            p.id AS product_id,
            p.sku,
            v.default_purchase_price AS purchase_price,
            v.dpp_inc_tax AS unit_cost_before_tax,
            v.profit_percent AS profit_margin,
            v.sell_price_inc_tax AS sell_price,
            p.unit_id AS unit_id,
            COALESCE(SUM(pl.quantity), 0) AS total_quantity,
            COALESCE(ss.quantity, 0) AS stock_quantity,
            COALESCE(sale.total_sale_qty, 0) AS total_sale_qty,
            -- Calculation for current stock considering sales
            CASE
                WHEN ss.product_id IS NOT NULL THEN 
                    (COALESCE(SUM(pl.quantity), 0) - (COALESCE(SUM(pl.quantity), 0) - COALESCE(ss.quantity, 0)) - COALESCE(sale.total_sale_qty, 0))
                ELSE 
                    COALESCE(SUM(pl.quantity), 0) - COALESCE(sale.total_sale_qty, 0)
            END AS current_stock
        FROM products p
        LEFT JOIN purchase_lines pl ON p.id = pl.product_id
        LEFT JOIN stock_setting ss ON p.id = ss.product_id
        INNER JOIN variations v ON p.id = v.product_id
        LEFT JOIN (
            SELECT 
                product_id, 
                SUM(sale_qty) AS total_sale_qty
            FROM 
                stock_sale
            GROUP BY 
                product_id
        ) sale ON p.id = sale.product_id
        WHERE p.business_id = 3 AND p.id = ?
        GROUP BY p.id
    ");
    $query->execute([$product_id]);
    $product = $query->fetch();

    if ($product) {
        $unitQuery = $conn->prepare("
            SELECT `id`, `business_id`, `actual_name`, `short_name`
            FROM units
            WHERE `business_id` = 3 AND `id` = ?
        ");
        $unitQuery->execute([$product['unit_id']]);
        $units = $unitQuery->fetchAll();

        $unitOptions = '';
        foreach ($units as $unit) {
            $unitOptions .= '<option value="' . htmlspecialchars($unit['id']) . '">'
                            . htmlspecialchars($unit['actual_name']) .  '</option>';
        }

        $html = '
        <tr>
            <td>1</td>
            <td style="display:none;"><input type="text" name="product_id[]" class="form-control" value="' . htmlspecialchars($product['product_id']) . '"></td>
            <td class="product-name">' . htmlspecialchars($product['product_name']) . '</td>
            <td style="display:none;" class="product-barcode">' . htmlspecialchars($product['sku']) . '</td>
            <td><input type="number" class="form-control quantity_unit" name="quantity[]" value="1"><br> <select name="UnitName[]" class="form-control UnitName">' . $unitOptions . '</select></td>
            <td style="display:none;"><input type="text" class="form-control unit-selling-price" name="sell_price[]" value="' . htmlspecialchars($product['sell_price']) . '"></td>
            <td style="display:none;" class="form-control stock_unit">Current Stock: ' . htmlspecialchars($product['current_stock']) . '</td>
            <td><button type="button" class="btn btn-danger btn-sm remove-row"><ion-icon name="trash-outline"></ion-icon></button></td>

        </tr>';

        echo $html;
    } else {
        echo '<tr><td colspan="11">Product not found.</td></tr>';
    }
}
?>
