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

try {

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

    $site_cd = trim($body['site_cd'] ?? '');
    $job_cd  = trim($body['job_cd'] ?? '');

    if ($site_cd === '' || $job_cd === '') {
        throw new Exception("Missing required parameters: site_cd or job_cd");
    }

    /* ================= HEADER QUERY ================= */
    $sql_header = "
        SELECT
            RowID AS job_RowID,
            site_cd,
            job_mst_job_cd,
            job_mst_desc
        FROM job_mst
        WHERE site_cd = ?
          AND job_mst_job_cd = ?
    ";

    $stmt_header = sqlsrv_query($conn, $sql_header, [$site_cd, $job_cd]);

    if ($stmt_header === false) {
        throw new Exception("Header query error: " . print_r(sqlsrv_errors(), true));
    }

    $header = sqlsrv_fetch_array($stmt_header, SQLSRV_FETCH_ASSOC);

    if (!$header) {
        throw new Exception("Record not found");
    }

    $job_RowID = (int)$header['job_RowID'];

    sqlsrv_free_stmt($stmt_header);

    /* ================= SECTION QUERY ================= */
    $sql_sections = "
        SELECT
            RowID AS sec_RowID,
            mst_RowID,
            site_cd,
            sec_no,
            sec_desc
        FROM sec_mst
        WHERE site_cd = ?
          AND mst_RowID = ?
        ORDER BY RowID
    ";

    $stmt_sections = sqlsrv_query($conn, $sql_sections, [$site_cd, $job_RowID]);

    if ($stmt_sections === false) {
        throw new Exception("Section query error: " . print_r(sqlsrv_errors(), true));
    }

    $sections = [];
    $secRowIds = [];

    while ($row = sqlsrv_fetch_array($stmt_sections, SQLSRV_FETCH_ASSOC)) {
        $row['sec_RowID'] = (int)$row['sec_RowID'];
        $sections[] = $row;
        $secRowIds[] = $row['sec_RowID'];
    }

    sqlsrv_free_stmt($stmt_sections);

    /* ================= STEP DETAILS QUERY ================= */
    $details = [];
    $stepRowIds = [];

    if (!empty($secRowIds)) {
        $placeholders = implode(',', array_fill(0, count($secRowIds), '?'));

        $sql_details = "
            SELECT
                RowID AS step_RowID,
                site_cd,
                sec_RowID,
                stp_no,
                stp_desc,
                stp_datatype,
				stp_mst_max_thr,
				stp_mst_min_thr
            FROM stp_mst
            WHERE site_cd = ?
              AND sec_RowID IN ($placeholders)
            ORDER BY sec_RowID, RowID
        ";

        $params = array_merge([$site_cd], $secRowIds);

        $stmt_details = sqlsrv_query($conn, $sql_details, $params);

        if ($stmt_details === false) {
            throw new Exception("Step detail query error: " . print_r(sqlsrv_errors(), true));
        }

        while ($row = sqlsrv_fetch_array($stmt_details, SQLSRV_FETCH_ASSOC)) {
            $row['step_RowID'] = (int)$row['step_RowID'];
            $row['sec_RowID']  = (int)$row['sec_RowID'];
            $row['zoom_list']  = []; // default empty array

            $details[] = $row;
            $stepRowIds[] = $row['step_RowID'];
        }

        sqlsrv_free_stmt($stmt_details);
    }

    /* ================= ZOOM LIST QUERY ================= */
    $zoomMap = [];

    if (!empty($stepRowIds)) {
        $placeholders = implode(',', array_fill(0, count($stepRowIds), '?'));

        $sql_zoom = "
            SELECT
                RowID AS zoom_RowID,
                mst_RowID,
                site_cd,
                stp_zom_data,
                stp_zom_desc,
                audit_user,
                audit_date
            FROM stp_zom
            WHERE site_cd = ?
              AND mst_RowID IN ($placeholders)
            ORDER BY mst_RowID, RowID
        ";

        $params = array_merge([$site_cd], $stepRowIds);

        $stmt_zoom = sqlsrv_query($conn, $sql_zoom, $params);

        if ($stmt_zoom === false) {
            throw new Exception("Zoom query error: " . print_r(sqlsrv_errors(), true));
        }

        while ($row = sqlsrv_fetch_array($stmt_zoom, SQLSRV_FETCH_ASSOC)) {
            $row['zoom_RowID'] = (int)$row['zoom_RowID'];
            $row['mst_RowID']  = (int)$row['mst_RowID'];

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

            $zoomMap[$row['mst_RowID']][] = $row;
        }

        sqlsrv_free_stmt($stmt_zoom);
    }

    /* ================= ATTACH ZOOM LIST INSIDE DETAILS ================= */
    foreach ($details as &$detail) {
        $stepId = $detail['step_RowID'];
        $detail['zoom_list'] = $zoomMap[$stepId] ?? [];
    }
    unset($detail);

    sqlsrv_close($conn);

    /* ================= RESPONSE ================= */
    echo json_encode([
        "status"   => "SUCCESS",
        "header"   => $header,
        "sections" => $sections,
        "details"  => $details
    ]);

} catch (Exception $e) {

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

    echo json_encode([
        "status"   => "ERROR",
        "message"  => $e->getMessage(),
        "header"   => null,
        "sections" => [],
        "details"  => []
    ]);
}
?>