<?php
require_once '../includes/auth.php';
require_once '../db.php';

$draw   = $_POST['draw'] ?? 1;
$start  = $_POST['start'] ?? 0;
$length = $_POST['length'] ?? 25;
$search = $_POST['search']['value'] ?? "";

$from = $_POST['from'] ?? "";
$to   = $_POST['to'] ?? "";

$where = " WHERE p.company_id = $cid ";

if (!empty($from) && !empty($to)) {
    $where .= " AND DATE(sm.created_at) BETWEEN '$from' AND '$to' ";
}

if (!empty($search)) {
    $s = $conn->real_escape_string($search);
    $where .= " AND (p.name LIKE '%$s%' OR sm.type LIKE '%$s%') ";
}

$totalRes = $conn->query("
    SELECT COUNT(DISTINCT sm.product_id) AS total
    FROM stock_movements sm
    JOIN products p ON p.id = sm.product_id
    $where
")->fetch_assoc();

$totalRecords = $totalRes['total'] ?? 0;

$sql = "
SELECT 
    sm.product_id,
    p.name AS product_name,

    SUM(CASE WHEN sm.type='IN' THEN sm.quantity ELSE 0 END) AS total_in,
    SUM(CASE WHEN sm.type='OUT' THEN sm.quantity ELSE 0 END) AS total_out,
    SUM(CASE WHEN sm.type='RETURN' THEN sm.quantity ELSE 0 END) AS total_return,

    MAX(sm.created_at) AS last_movement
FROM stock_movements sm
JOIN products p ON p.id = sm.product_id
$where
GROUP BY sm.product_id
ORDER BY last_movement DESC
LIMIT $start, $length
";

$res = $conn->query($sql);

$data = [];
while ($row = $res->fetch_assoc()) {

    $balance = ($row['total_in'] + $row['total_return']) - $row['total_out'];

    $data[] = [
        "created_at"   => date("d-m-Y", strtotime($row['last_movement'])),
        "product_name" => $row['product_name'],
        "batch_no"     => "-",
        "type"         => "SUMMARY",
        "quantity"     => "IN: {$row['total_in']} | OUT: {$row['total_out']} | RETURN: {$row['total_return']} | BAL: $balance",
        "reference"    => "Product Wise Summary"
    ];
}

echo json_encode([
    "draw" => intval($draw),
    "recordsTotal" => intval($totalRecords),
    "recordsFiltered" => intval($totalRecords),
    "data" => $data
]);
