<?php
require_once('config.php');

$site_cd = $_REQUEST['site_cd'] ?? '';
$date = $_REQUEST['date'] ?? '';

try {

    $sql = "
       SELECT
    d.dept_mst_dept_cod AS dept,
    p.dept_par_lin_cd   AS linen_item,

    -- Shortfall / Need-to-issue (Par - (Bal + Issue1 + Issue2))
    SUM(
        CASE
            WHEN (
                ISNULL(p.dept_par_qty,0)
              - ( ISNULL(ls.lri_ls1_bal_qty,0)
                + ISNULL(ls.lri_ls1_issue1_qty,0)
                + ISNULL(ls.lri_ls1_issue2_qty,0)
                )
            ) > 0
            THEN (
                ISNULL(p.dept_par_qty,0)
              - ( ISNULL(ls.lri_ls1_bal_qty,0)
                + ISNULL(ls.lri_ls1_issue1_qty,0)
                + ISNULL(ls.lri_ls1_issue2_qty,0)
                )
            )
            ELSE 0
        END
    ) AS par_qty
	FROM dept_mst d
	JOIN dept_par p WITH (NOLOCK)
	  ON d.site_cd = p.site_cd
	 AND d.RowID   = p.mst_RowID

	-- ✅ Get that dept’s LRI master row for the date
	OUTER APPLY (
		SELECT TOP 1 lm.RowID AS lri_mst_RowID
		FROM lri_mst lm WITH (NOLOCK)
		WHERE lm.site_cd = ?
		  AND lm.lri_mst_department = d.dept_mst_dept_cod
		  AND lm.lri_mst_lri_date >= ?
		  AND lm.lri_mst_lri_date < DATEADD(DAY,1,?)
		ORDER BY lm.lri_mst_lri_date DESC, lm.RowID DESC
	) l

	-- ✅ Match ls1 by mst_RowID + linen (correct)
	LEFT JOIN lri_ls1 ls WITH (NOLOCK)
	  ON ls.site_cd   = ?
	 AND ls.mst_RowID = l.lri_mst_RowID
	 AND ls.lri_ls1_linen = p.dept_par_lin_cd

	WHERE d.site_cd = ?
	  AND d.dept_mst_disable_flag = 0
	  AND d.dept_mst_linen = 1

	GROUP BY d.dept_mst_dept_cod, p.dept_par_lin_cd
	ORDER BY d.dept_mst_dept_cod, p.dept_par_lin_cd";

    $params = [$site_cd,$date,$date,$site_cd,$site_cd];

    $stmt = sqlsrv_query($conn, $sql, $params);
    if ($stmt === false) {
        throw new Exception("SQL error: " . print_r(sqlsrv_errors(), true));
    }

    $json = [];
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $json[] = $row;
    }
    sqlsrv_free_stmt($stmt);
    sqlsrv_close($conn);

    echo json_encode([
        'status'  => 'SUCCESS',
        'message' => 'PAR linen list retrieved successfully',
        'data'    => $json
    ]);
    exit();

} catch (Exception $e) {
    if (isset($conn)) @sqlsrv_close($conn);
    echo json_encode([
        'status'  => 'ERROR',
        'message' => $e->getMessage(),
        'data'    => []
    ]);
    exit();
}
?>