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

$records_per_page = isset($_GET['records_per_page']) ? (int)$_GET['records_per_page'] : 50;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $records_per_page;
$search = isset($_GET['search']) ? $_GET['search'] : '';
$search = htmlspecialchars($search);
$business_id = 3;

// Prepare total records query
$total_records_query = "
    SELECT COUNT(*) 
    FROM products p
    LEFT JOIN purchase_lines pl ON p.id = pl.product_id
    WHERE (p.name LIKE :search)
    AND p.business_id = :business_id
";
$total_records_stmt = $conn->prepare($total_records_query);
$total_records_stmt->bindValue(':search', '%' . $search . '%', PDO::PARAM_STR);
$total_records_stmt->bindValue(':business_id', $business_id, PDO::PARAM_INT);
$total_records_stmt->execute();
$total_records = $total_records_stmt->fetchColumn();
$total_pages = ceil($total_records / $records_per_page);

// Prepare main products query
$sql = "SELECT 
p.id AS product_id,
p.name AS product_name,
p.business_id,
p.image,
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 :search = '')
GROUP BY 
p.id
ORDER BY 
p.id DESC
LIMIT :limit OFFSET :offset";

// Prepare and execute the main query
$stmt = $conn->prepare($sql);
$stmt->bindValue(':search', '%' . $search . '%', PDO::PARAM_STR);
$stmt->bindValue(':business_id', $business_id, PDO::PARAM_INT);
$stmt->bindValue(':limit', $records_per_page, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Return JSON response
header('Content-Type: application/json');
echo json_encode(['products' => $products, 'total_records' => $total_records, 'total_pages' => $total_pages]);
