<?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.ler_mst_ler_no,
        l.ler_mst_ler_date,
        l.ler_mst_status,
        l.RowID AS ler_mst_RowID,
        l.audit_date,

        sts.lrr_sts_typ_cd,
        sts.lrr_sts_desc,

        total_lines = ISNULL(sc.total_lines, 0),

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

        done_count = CASE 
                        WHEN l.RowID IS NULL THEN 0
                        ELSE ISNULL(sc.done_count, 0)
                     END,

        short_count = CASE
                        WHEN l.RowID IS NULL THEN 0
                        ELSE ISNULL(sc.short_count, 0)
                      END,

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

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

        fully_issued = CASE
                    WHEN l.RowID IS NULL THEN 0
                    WHEN ISNULL(sc.total_lines,0) = ISNULL(sc.done_count,0)
                    THEN 1
                    ELSE 0
               END,

		partial_issued_count = CASE
                        WHEN l.RowID IS NULL THEN 0
                        ELSE ISNULL(sc.partial_issued_count,0) + ISNULL(sc.done_count,0)
                       END,
					   
		-- signature info
		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.ler_mst_status = 'OPN' THEN 1
                        WHEN l.ler_mst_status = 'ISU' THEN 2
                        WHEN l.ler_mst_status = 'CLO' THEN 9
                        ELSE 5
                     END

    FROM dept_mst d WITH (NOLOCK)

    LEFT JOIN ler_mst l WITH (NOLOCK)
        ON l.site_cd = d.site_cd
       AND l.ler_mst_department = d.dept_mst_dept_cod
       AND (
            ? = '' 
            OR (
                l.ler_mst_ler_date >= ?
                AND l.ler_mst_ler_date < DATEADD(DAY, 1, ?)
            )
       )

    LEFT JOIN lrr_sts sts WITH (NOLOCK)
        ON sts.site_cd = l.site_cd
       AND sts.lrr_sts_status = l.ler_mst_status 
	   
    OUTER APPLY (
			SELECT 
				signature_count = COUNT(1)
			FROM ler_ref ref WITH (NOLOCK)
			WHERE ref.site_cd = 'SMCP'
			  AND ref.mst_RowID = l.RowID
		) sig

    OUTER APPLY (
        SELECT 
            total_lines = COUNT(1),

            done_count = SUM(
                            CASE
                                WHEN ls.ler_ls1_req_qty IS NOT NULL
                                 AND (
                                        ISNULL(ls.ler_ls1_issue1_qty, 0)
                                      + ISNULL(ls.ler_ls1_issue2_qty, 0)
                                     ) >= ISNULL(ls.ler_ls1_req_qty, 0)
                                THEN 1
                                ELSE 0
                            END
                         ),

            short_count = SUM(
                            CASE
                                WHEN ls.ler_ls1_req_qty IS NOT NULL
                                 AND (
                                        ISNULL(ls.ler_ls1_req_qty, 0)
                                      - ISNULL(ls.ler_ls1_issue1_qty, 0)
                                      - ISNULL(ls.ler_ls1_issue2_qty, 0)
                                     ) > 0
                                THEN 1
                                ELSE 0
                            END
                         ),

            fully_issued_count = SUM(
                                    CASE
                                        WHEN ls.ler_ls1_req_qty IS NOT NULL
                                         AND (
                                                ISNULL(ls.ler_ls1_issue1_qty,0)
                                              + ISNULL(ls.ler_ls1_issue2_qty,0)
                                             ) >= ISNULL(ls.ler_ls1_req_qty,0)
                                        THEN 1
                                        ELSE 0
                                    END
                                 ),

            partial_issued_count = SUM(
                                    CASE
                                        WHEN ls.ler_ls1_req_qty IS NOT NULL
                                         AND (
                                                ISNULL(ls.ler_ls1_issue1_qty,0)
                                              + ISNULL(ls.ler_ls1_issue2_qty,0)
                                             ) > 0
                                         AND (
                                                ISNULL(ls.ler_ls1_issue1_qty,0)
                                              + ISNULL(ls.ler_ls1_issue2_qty,0)
                                             ) < ISNULL(ls.ler_ls1_req_qty,0)
                                        THEN 1
                                        ELSE 0
                                    END
                                 )
        FROM ler_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.ler_mst_ler_no IS NOT NULL
      AND (? = '' OR d.dept_mst_dept_cod = ?)

    ORDER BY 
        sort_order ASC,
        d.dept_mst_dept_cod,
        l.ler_mst_ler_date DESC,
        l.ler_mst_ler_no DESC
    ";

    $params = [
        $date,       // ? = '' check
        $date,       // >= ?
        $date,       // < DATEADD(DAY,1,?)
        $site_cd,    // d.site_cd = ?
        $dept_mst_dept_cod, // ? = ''
        $dept_mst_dept_cod  // d.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['ler_mst_ler_date']) && $row['ler_mst_ler_date'] instanceof DateTime) {
            $row['ler_mst_ler_date'] = $row['ler_mst_ler_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();
}
?>