<?php
include 'includes/config.php';
ini_set('display_errors', 1);
error_reporting(E_ALL);

error_log("search_filter_products.php loaded");

function safe($value) {
    return htmlspecialchars($value ?? '', ENT_QUOTES, 'UTF-8');
}

$business_id = 3;
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$currentPage = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$currentSearch = $search;

$sql = "SELECT 
    p.id AS product_id,
    p.name AS product_name,
    p.business_id,
    p.image,
    p.barcode,
    p.type,
    p.brand_id,
    b.name AS brand_name,
    p.category_id,
    p.sub_category_id,
    c1.name AS category_name,
    c2.name AS sub_category_name,
    p.tax,
    p.sku,
    p.alert_quantity,
    p.product_custom_field1,
    p.product_custom_field2,
    p.product_custom_field3,
    p.product_custom_field4,
    p.product_description,
    p.unit_id,
    p.tax_type,
    v.profit_percent,
    u.actual_name,
    v.default_purchase_price,
    v.default_sell_price,
    COALESCE(v.dpp_inc_tax, 0) AS purchase_price,
    COALESCE(v.sell_price_inc_tax, 0) AS sale_price,
    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,
    COALESCE(st_transfer.total_quantity_transferred, 0) AS total_quantity_transferred,
    (
        SELECT MAX(ss2.created_at) 
        FROM stock_setting ss2 
        WHERE ss2.product_id = p.id
    ) AS last_stock_setting_date,
    (
        SELECT COALESCE(SUM(pl2.quantity), 0)
        FROM purchase_lines pl2
        WHERE pl2.product_id = p.id 
        AND (
            (
                SELECT MAX(ss3.created_at) 
                FROM stock_setting ss3 
                WHERE ss3.product_id = p.id
            ) IS NULL OR pl2.created_at > (
                SELECT MAX(ss3.created_at) 
                FROM stock_setting ss3 
                WHERE ss3.product_id = p.id
            )
        )
    ) AS new_purchase_quantity,
    (
        COALESCE(ss.quantity, 0)  
        + (
            SELECT COALESCE(SUM(pl2.quantity), 0)
            FROM purchase_lines pl2
            WHERE pl2.product_id = p.id 
            AND (
                (
                    SELECT MAX(ss3.created_at) 
                    FROM stock_setting ss3 
                    WHERE ss3.product_id = p.id
                ) IS NULL OR pl2.created_at > (
                    SELECT MAX(ss3.created_at) 
                    FROM stock_setting ss3 
                    WHERE ss3.product_id = p.id
                )
            )
        )  
        - COALESCE(sale.total_sale_qty, 0)  
        - COALESCE(st_transfer.total_quantity_transferred, 0)  
    ) AS current_stock
FROM 
    products p
LEFT JOIN 
    (SELECT product_id, SUM(quantity) AS quantity FROM purchase_lines GROUP BY product_id) pl ON p.id = pl.product_id
LEFT JOIN brands b ON p.brand_id = b.id
LEFT JOIN categories c1 ON p.category_id = c1.id
LEFT JOIN categories c2 ON p.sub_category_id = c2.id
LEFT JOIN units u ON p.unit_id = u.id
LEFT JOIN variations v ON p.id = v.product_id
LEFT JOIN stock_setting ss ON p.id = ss.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
LEFT JOIN (SELECT product_id, SUM(QuantityTransferred) AS total_quantity_transferred FROM stocktransfer GROUP BY product_id) st_transfer ON p.id = st_transfer.product_id
WHERE 
    p.business_id = :business_id
    AND (p.name LIKE :search OR p.barcode LIKE :search OR :search = '')
GROUP BY 
    p.id
ORDER BY 
    p.name ASC";

$stmt = $conn->prepare($sql);
$stmt->bindValue(':business_id', $business_id, PDO::PARAM_INT);
$stmt->bindValue(':search', '%' . $search . '%', PDO::PARAM_STR);
$stmt->execute();
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

$response = ['html' => '', 'products' => $products];

if ($products) {
    $rows = [];

    foreach ($products as $product) {
        $rows[] = '<tr>
            <td>
                <a href="img/' . safe($product['image']) . '" target="_blank">
                    <img src="img/' . safe($product['image']) . '" style="width:4em; height:5em;">
                </a>
            </td>
            <td>
                <div class="custom_select">
                    <button class="select-button">
                        <span class="selected-option">Actions</span>
                        <span class="arrow">&#9660;</span>
                    </button>
                    <div class="select-options">
                        <a href="print_label.php?product_id=' . safe($product['product_id']) . '" target="_blank" rel="noopener noreferrer">
                            <ion-icon name="barcode-outline"></ion-icon> &nbsp; Labels
                        </a>
                        <a href="action/delete_product.php?del=' . safe($product['product_id']) . '&page=' . $currentPage . '&search=' . urlencode($currentSearch) . '" 
                            class="delete-link btn btn-danger"
                            style="display:inline-block; padding:6px 10px; background:#dc3545; color:#fff; border-radius:6px; text-align:center; margin-top:4px;"
                            onclick="return confirm(\'Are you sure you want to delete this item?\');">
                            <ion-icon name="trash-outline"></ion-icon> &nbsp; Delete
                        </a>
                        <a href="#" class="view-link" data-product-id="' . safe($product['product_id']) . '">
                            <ion-icon name="eye-outline"></ion-icon> &nbsp; View
                        </a>
                        <a href="edit_products.php?product_id=' . safe($product['product_id']) . '" target="_blank" rel="noopener noreferrer">
                            <ion-icon name="create-outline"></ion-icon> &nbsp; Edit
                        </a>
                    </div>
                </div>
            </td>
            <td data-product-id="' . safe($product['product_id']) . '">' . safe($product['product_name']) . '</td>
            <td style="display:none;">' . safe($product['profit_percent']) . '</td>
            <td style="display:none;">' . safe($product['total_sale_qty']) . '</td>
            <td style="display:none;">Ishfaq Autos</td>
            <td>' . number_format($product['default_purchase_price'], 2) . ' RS</td>
            <td style="display:none;">' . number_format($product['default_purchase_price'], 2) . ' RS</td>
            <td style="display:none;">' . number_format($product['default_sell_price'], 2) . ' RS</td>
            <td>' . number_format($product['sale_price'], 2) . ' RS</td>
            <td>' . safe($product['category_name']) . '</td>
            <td>' . safe($product['brand_name']) . '</td>
            <td>' . safe($product['barcode']) . '</td>
        </tr>';
    }

    $response['html'] = implode('', $rows);
} else {
    $response['html'] = '<tr><td colspan="12">No products found</td></tr>';
}

// ✅ Clean any previous output (prevents JSON breakage)
if (ob_get_length()) ob_clean();

header('Content-Type: application/json');
echo json_encode($response, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
exit;
?>
