<?php
require_once __DIR__ . '/../core/init.php';

header('Content-Type: application/json');

function terminate_with_error($message) {
    die(json_encode(['status' => 'error', 'message' => $message, 'data' => [], 'draw' => intval($_POST['draw'] ?? 0), 'recordsTotal' => 0, 'recordsFiltered' => 0]));
}

// === Đọc các tham số ===
$draw = intval($_POST['draw'] ?? 0);
$start = intval($_POST['start'] ?? 0);
$length = intval($_POST['length'] ?? 10);
$search_term = $_POST['q'] ?? '';
$category_id = intval($_POST['category'] ?? 0);
$manufacturer_id = intval($_POST['manufacturer'] ?? 0);
$store_id = intval($_POST['store'] ?? 1);
$from_date = $_POST['from'] ?? date('Y-m-01');
$to_date = $_POST['to'] ?? date('Y-m-t');

$from_datetime = $from_date . ' 00:00:00';
$to_datetime = $to_date . ' 23:59:59';

// === BƯỚC 1: LẤY SẢN PHẨM VÀ TỒN KHO "CHUẨN" ===
$sql_products = "SELECT p.id, p.prd_code, p.prd_name, p.prd_quycach, p.prd_origin_price, 
                        COALESCE(inv.quantity, p.prd_sls, 0) as current_trusted_stock
                 FROM cms_products p
                 LEFT JOIN cms_inventory inv ON p.id = inv.product_id AND inv.store_id = ?";
$params_products = [$store_id];
$types_products = 'i';

$whereClauses = ["p.deleted = 0"];
if (!empty($search_term)) {
    $whereClauses[] = "(p.prd_name LIKE ? OR p.prd_code LIKE ?)";
    $search_like = "%{$search_term}%";
    array_push($params_products, $search_like, $search_like);
    $types_products .= 'ss';
}
if ($category_id > 0) { $whereClauses[] = "p.prd_group_id = ?"; $params_products[] = $category_id; $types_products .= 'i'; }
if ($manufacturer_id > 0) { $whereClauses[] = "p.prd_manufacture_id = ?"; $params_products[] = $manufacturer_id; $types_products .= 'i'; }
$sql_products .= " WHERE " . implode(' AND ', $whereClauses);

$stmt_products = $conn->prepare($sql_products);
if ($stmt_products === false) { terminate_with_error("Lỗi SQL[Lấy sản phẩm]: " . $conn->error); }
if ($types_products) { $stmt_products->bind_param($types_products, ...$params_products); }
$stmt_products->execute();
$products_result = $stmt_products->get_result();
$all_products_map = [];
while($row = $products_result->fetch_assoc()) {
    $all_products_map[$row['id']] = $row;
}
$stmt_products->close();

// === BƯỚC 2: TÍNH TOÁN CÁC GIAO DỊCH ===
$imports_in_period = []; $exports_in_period = [];
$imports_after_period = []; $exports_after_period = [];

if (!empty($all_products_map)) {
    function sum_items_from_json($json_string, $product_map) {
        $totals = [];
        $items = json_decode($json_string, true);
        if (is_array($items)) {
            foreach ($items as $item) {
                $id = intval($item['id'] ?? 0);
                if ($id > 0 && isset($product_map[$id])) {
                    $qty = floatval($item['quantity'] ?? 0);
                    $totals[$id] = ($totals[$id] ?? 0) + $qty;
                }
            }
        }
        return $totals;
    }

    // --- A. Lấy giao dịch TRONG KỲ báo cáo ($from_datetime -> $to_datetime) ---
    $stmt = $conn->prepare("SELECT detail_input FROM cms_input WHERE store_id = ? AND input_date BETWEEN ? AND ?");
    if($stmt){ $stmt->bind_param("iss", $store_id, $from_datetime, $to_datetime); $stmt->execute(); $result = $stmt->get_result();
        while($row = $result->fetch_assoc()) { foreach(sum_items_from_json($row['detail_input'], $all_products_map) as $id => $qty) $imports_in_period[$id] = ($imports_in_period[$id] ?? 0) + $qty; } $stmt->close();
    }
    $stmt = $conn->prepare("SELECT detail_order FROM cms_orders WHERE store_id = ? AND deleted = 0 AND order_status > 0 AND sell_date BETWEEN ? AND ?");
    if($stmt){ $stmt->bind_param("iss", $store_id, $from_datetime, $to_datetime); $stmt->execute(); $result = $stmt->get_result();
        while($row = $result->fetch_assoc()) { foreach(sum_items_from_json($row['detail_order'], $all_products_map) as $id => $qty) $exports_in_period[$id] = ($exports_in_period[$id] ?? 0) + $qty; } $stmt->close();
    }
    $stmt = $conn->prepare("SELECT from_store, to_store, detail_transfer FROM cms_transfer WHERE created BETWEEN ? AND ?");
    if($stmt){ $stmt->bind_param("ss", $from_datetime, $to_datetime); $stmt->execute(); $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) { foreach(sum_items_from_json($row['detail_transfer'], $all_products_map) as $id => $qty) { if($row['to_store'] == $store_id) $imports_in_period[$id] = ($imports_in_period[$id] ?? 0) + $qty; if($row['from_store'] == $store_id) $exports_in_period[$id] = ($exports_in_period[$id] ?? 0) + $qty; }} $stmt->close();
    }

    // --- B. Lấy giao dịch SAU KỲ báo cáo ($to_datetime -> NOW) ---
    $stmt = $conn->prepare("SELECT detail_input FROM cms_input WHERE store_id = ? AND input_date > ?");
    if($stmt){ $stmt->bind_param("is", $store_id, $to_datetime); $stmt->execute(); $result = $stmt->get_result();
        while($row = $result->fetch_assoc()) { foreach(sum_items_from_json($row['detail_input'], $all_products_map) as $id => $qty) $imports_after_period[$id] = ($imports_after_period[$id] ?? 0) + $qty; } $stmt->close();
    }
    $stmt = $conn->prepare("SELECT detail_order FROM cms_orders WHERE store_id = ? AND deleted = 0 AND order_status > 0 AND sell_date > ?");
    if($stmt){ $stmt->bind_param("is", $store_id, $to_datetime); $stmt->execute(); $result = $stmt->get_result();
        while($row = $result->fetch_assoc()) { foreach(sum_items_from_json($row['detail_order'], $all_products_map) as $id => $qty) $exports_after_period[$id] = ($exports_after_period[$id] ?? 0) + $qty; } $stmt->close();
    }
    $stmt = $conn->prepare("SELECT from_store, to_store, detail_transfer FROM cms_transfer WHERE created > ?");
    if($stmt){ $stmt->bind_param("s", $to_datetime); $stmt->execute(); $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) { foreach(sum_items_from_json($row['detail_transfer'], $all_products_map) as $id => $qty) { if($row['to_store'] == $store_id) $imports_after_period[$id] = ($imports_after_period[$id] ?? 0) + $qty; if($row['from_store'] == $store_id) $exports_after_period[$id] = ($exports_after_period[$id] ?? 0) + $qty; }} $stmt->close();
    }
}

// === BƯỚC 3: TÍNH TOÁN DỮ LIỆU CUỐI CÙNG THEO PHƯƠNG PHÁP "LAI" ===
$full_data = [];
foreach ($all_products_map as $product_id => $product) {
    $cost_price = (float)($product['prd_origin_price'] ?? 0);
    $current_stock = (float)($product['current_trusted_stock'] ?? 0);
    
    $imported_in_period_qty = (float)($imports_in_period[$product_id] ?? 0);
    $exported_in_period_qty = (float)($exports_in_period[$product_id] ?? 0);
    
    $imported_after_period_qty = (float)($imports_after_period[$product_id] ?? 0);
    $exported_after_period_qty = (float)($exports_after_period[$product_id] ?? 0);

    $closing_stock_qty = $current_stock - $imported_after_period_qty + $exported_after_period_qty;
    $opening_stock_qty = $closing_stock_qty - $imported_in_period_qty + $exported_in_period_qty;
    
    if ($opening_stock_qty != 0 || $imported_in_period_qty != 0 || $exported_in_period_qty != 0 || $closing_stock_qty != 0) {
        $full_data[] = [
            "prd_code" => $product['prd_code'], "prd_name" => $product['prd_name'], "prd_quycach" => $product['prd_quycach'],
            "opening_stock_qty" => $opening_stock_qty, "opening_stock_amount" => $opening_stock_qty * $cost_price,
            "imported_qty" => $imported_in_period_qty, "imported_amount" => $imported_in_period_qty * $cost_price,
            "exported_qty" => $exported_in_period_qty, "exported_amount" => $exported_in_period_qty * $cost_price,
            "closing_stock_qty" => $closing_stock_qty, "closing_stock_amount" => $closing_stock_qty * $cost_price,
        ];
    }
}

// Tính tổng các cột và trả về JSON
$total_opening = array_sum(array_column($full_data, 'opening_stock_qty'));
$total_imported = array_sum(array_column($full_data, 'imported_qty'));
$total_exported = array_sum(array_column($full_data, 'exported_qty'));
$total_closing = array_sum(array_column($full_data, 'closing_stock_qty'));
$totalFiltered = count($full_data);
$data_for_page = array_slice($full_data, $start, $length);

echo json_encode([
    "draw" => $draw,
    "recordsTotal" => $totalFiltered,
    "recordsFiltered" => $totalFiltered,
    "data" => $data_for_page,
    "total_opening" => $total_opening,
    "total_imported" => $total_imported,
    "total_exported" => $total_exported,
    "total_closing" => $total_closing,
]);
?>