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

try {

    // Read JSON (POST)
    $body = json_decode(file_get_contents("php://input"), true);

    $site_cd             = trim($body['site_cd'] ?? '');
    $date                = trim($body['date'] ?? '');
    $dept_mst_dept_cod   = trim($body['dept_cd'] ?? '');

    if ($site_cd === '') {
        throw new Exception("Missing required parameter: site_cd");
    }

    $json = [];

    $sql = "
        SELECT 
			d.dept_mst_dept_cod,
			d.dept_mst_description,

			l.lrr_mst_lrr_no,
			l.lrr_mst_lrr_date,
			l.lrr_mst_status,
			l.RowID AS lrr_mst_RowID,
			l.lrr_mst_requester,
			l.audit_date,

			sts.lrr_sts_typ_cd,
			sts.lrr_sts_desc,

			total_lines = ISNULL(sc.total_lines, 0),

    status_color = CASE 
                        WHEN l.lrr_mst_status = 'OPN' THEN '#12b728'
                        WHEN l.lrr_mst_status = 'ISU' THEN '#F79009'
                        WHEN l.lrr_mst_status = 'CLO' THEN '#D32F2F'
                        ELSE '#D0D5DD'
                   END,

    -- DONE COUNT
    done_count = CASE 
                    WHEN l.RowID IS NULL THEN 0
                    WHEN l.lrr_mst_status = 'ISU' THEN ISNULL(sc.total_lines, 0)
                    ELSE ISNULL(sc.done_count, 0)
                 END,

    -- SHORT COUNT
    short_count = CASE
                    WHEN l.RowID IS NULL THEN 0
                    WHEN l.lrr_mst_status = 'ISU' THEN 0
                    ELSE ISNULL(sc.short_count, 0)
                  END,

    -- PROGRESS TEXT
    progress_text = CONCAT(
                        CASE 
                            WHEN l.RowID IS NULL THEN 0
                            WHEN l.lrr_mst_status = 'ISU' THEN ISNULL(sc.total_lines, 0)
                            ELSE ISNULL(sc.done_count, 0)
                        END,
                        '/',
                        ISNULL(sc.total_lines, 0)
                    ),

    -- SHORT PROGRESS
    progress = CONCAT(
                        CASE 
                            WHEN l.RowID IS NULL THEN 0
                            WHEN l.lrr_mst_status = 'ISU' THEN 0
                            ELSE ISNULL(sc.short_count, 0)
                        END,
                        '/',
                        ISNULL(sc.total_lines, 0)
                    ),

    -- ✅ FULLY ISSUED (FIXED LOGIC)
   fully_issued = CASE
							WHEN l.RowID IS NULL THEN 0
							WHEN l.lrr_mst_status = 'ISU'  OR l.lrr_mst_status = 'CLO'THEN 1
							
							ELSE 0
					   END,

    -- ✅ PARTIAL ISSUED (FIXED LOGIC)
    partial_issued_count = CASE
                            WHEN l.RowID IS NULL THEN 0
                            WHEN l.lrr_mst_status = 'ISU'
                                
                            THEN 0
                            WHEN ISNULL(sc.partial_issued_counted, 0) > 0 THEN ISNULL(sc.partial_issued_counted, 0)
                            WHEN l.lrr_mst_status <> 'ISU'
                            THEN ISNULL(sc.done_count, 0)
                            ELSE 0
                       END,

    -- SIGNATURE
    signature_done_count = CASE
                                WHEN l.RowID IS NULL THEN 0
                                WHEN ISNULL(sig.signature_count, 0) > 0 THEN 1
                                ELSE 0
                           END,

    signature_not_done_count = CASE
                                    WHEN l.RowID IS NULL THEN 0
                                    WHEN ISNULL(sig.signature_count, 0) > 0 THEN 0
                                    ELSE 1
                               END,

    signature_status = CASE
                            WHEN l.RowID IS NULL THEN 'NO RECORD'
                            WHEN ISNULL(sig.signature_count, 0) > 0 THEN 'SIGNED'
                            ELSE 'NOT SIGNED'
                       END,

    sort_order = CASE
                    WHEN l.RowID IS NULL THEN 0
                    WHEN l.lrr_mst_status = 'OPN' THEN 1
                    WHEN l.lrr_mst_status = 'ISU' THEN 2
                    WHEN l.lrr_mst_status = 'CLO' THEN 3
                    ELSE 4
                 END

    FROM dept_mst d WITH (NOLOCK)

    LEFT JOIN lrr_mst l WITH (NOLOCK)
        ON l.site_cd = d.site_cd
       AND l.lrr_mst_department = d.dept_mst_dept_cod
       AND (
            ? = '' 
            OR (
                l.lrr_mst_lrr_date >= ?
                AND l.lrr_mst_lrr_date < DATEADD(DAY, 1, ?)
            )
       )

    LEFT JOIN lrr_sts sts WITH (NOLOCK)
        ON sts.site_cd = l.site_cd
       AND sts.lrr_sts_status = l.lrr_mst_status 
	   
    OUTER APPLY (
			SELECT 
				signature_count = COUNT(1)
			FROM lrr_ref ref WITH (NOLOCK)
			WHERE ref.site_cd = ?
			  AND ref.mst_RowID = l.RowID
		) sig

    OUTER APPLY (
        SELECT 
            total_lines = COUNT(1),

            done_count = SUM(
                            CASE
                                WHEN ls.lrr_ls1_rej_qty IS NOT NULL
                                 AND (
                                        ISNULL(ls.lrr_ls1_issue1_qty, 0)
                                      + ISNULL(ls.lrr_ls1_issue2_qty, 0)
                                     ) >= ISNULL(ls.lrr_ls1_rej_qty, 0)
                                THEN 1
                                ELSE 0
                            END
                         ),

            short_count = SUM(
                            CASE
                                WHEN ls.lrr_ls1_rej_qty IS NOT NULL
                                 AND (
                                        ISNULL(ls.lrr_ls1_rej_qty, 0)
                                      - ISNULL(ls.lrr_ls1_issue1_qty, 0)
                                      - ISNULL(ls.lrr_ls1_issue2_qty, 0)
                                     ) > 0
                                THEN 1
                                ELSE 0
                            END
                         ),
			partial_issued_counted = SUM(
				CASE
					WHEN ls.lrr_ls1_rej_qty IS NOT NULL
					 AND (
							ISNULL(ls.lrr_ls1_issue1_qty, 0)
						  + ISNULL(ls.lrr_ls1_issue2_qty, 0)
						 ) > 0
					 AND (
							ISNULL(ls.lrr_ls1_issue1_qty, 0)
						  + ISNULL(ls.lrr_ls1_issue2_qty, 0)
						 ) < ISNULL(ls.lrr_ls1_rej_qty, 0)
					THEN 1
					ELSE 0
				END
			)
        FROM lrr_ls1 ls WITH (NOLOCK)
        WHERE ls.site_cd = l.site_cd
          AND ls.mst_RowID = l.RowID
    ) sc

    WHERE d.dept_mst_disable_flag = '0'
      AND d.site_cd = ?
      AND l.lrr_mst_lrr_no IS NOT NULL
      AND (? = '' OR d.dept_mst_dept_cod = ?)

    ORDER BY 
        sort_order ASC,
        d.dept_mst_dept_cod,
        l.lrr_mst_lrr_date DESC,
        l.lrr_mst_lrr_no DESC
    ";

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

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

    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        if (isset($row['lrr_mst_lrr_date']) && $row['lrr_mst_lrr_date'] instanceof DateTime) {
            $row['lrr_mst_lrr_date'] = $row['lrr_mst_lrr_date']->format('Y-m-d H:i:s');
        }

        if (isset($row['audit_date']) && $row['audit_date'] instanceof DateTime) {
            $row['audit_date'] = $row['audit_date']->format('Y-m-d H:i:s');
        }

        $json[] = $row;
    }

    sqlsrv_free_stmt($stmt);
    sqlsrv_close($conn);

    echo json_encode([
        'status' => 'SUCCESS',
        'data'   => $json
    ]);

} catch (Exception $e) {

    if (isset($conn)) {
        @sqlsrv_close($conn);
    }

    echo json_encode([
        'status'  => 'ERROR',
        'message' => $e->getMessage(),
        'data'    => []
    ]);
    exit();
}
?>