GetStationsApi.java

package com.workbenchclassic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.ws.rs.Consumes;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;

import org.json.JSONObject;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

/**
 * REST endpoint that fetches station data from DB and returns jsTree JSON.
 */

@Path("/getStations")
public class GetStationsApi {

    private static final DBService DB_SERVICE = new DBService("dsMesMiiNJTA");
    private static final ObjectMapper MAPPER = new ObjectMapper();

    /**
     * Fetches station data via dbConnectAndGetRows, builds a hierarchical
     * structure, and returns as jsTree-friendly JSON.
     *
     * @return JAX-RS Response with JSON array
     */
    @POST
    @Consumes(MediaType.APPLICATION_JSON)
    @Produces(MediaType.APPLICATION_JSON)
    public Response getStations(String jsonBody) {
        JSONObject json = new JSONObject(jsonBody);
        String station = json.getString("station");

        String query = "SELECT mk.kap_nr as kap_nr, kap_bez as kap_bez, "
                + "mg.ma_grp_nr as ma_grp_nr, mg.ma_grp_bez as ma_grp_bez, "
                + "li.line_nr as line_nr, li.line_bez as line_bez "
                + "FROM bde.kast mk "
                + "JOIN bde.ma_grp mg ON (mg.ma_grp_id = mk.ma_grp_id) "
                + "JOIN bde.line_ma_grp lm ON (lm.ma_grp_id = mk.ma_grp_id) "
                + "JOIN bde.line li ON (li.line_id = lm.line_id) "
                + "JOIN bde.werk we ON (li.werk_id = we.werk_id) "
                + "WHERE Line_typ ='T' "
                + "and mk.werk_id IN (SELECT werk_id FROM bde.kast WHERE kap_nr = ? )";

        List<Map<String, Object>> rows;
        try (Connection conn = DB_SERVICE.getConnection();
                PreparedStatement ps = conn.prepareStatement(query)) {
            ps.setString(1, station);

            rows = DB_SERVICE.preparedDbConnectAndGetRows(ps);
        } catch (SQLException e) {
            String errJson = String.format("{\"error\":\"DB error: %s\"}", e.getMessage());
            return Response.status(Response.Status.INTERNAL_SERVER_ERROR)
                    .entity(errJson)
                    .build();
        }

        List<JsTreeNode> jsTreeData = buildJsTree(rows);

        // Convert to JSON
        String jsonResult;
        try {
            jsonResult = MAPPER.writeValueAsString(jsTreeData);
        } catch (JsonProcessingException e) {
            return Response.status(Response.Status.INTERNAL_SERVER_ERROR)
                    .entity("{\"error\":\"JSON parsing failed\"}")
                    .build();
        }

        return Response.ok(jsonResult).build();
    }

    /**
     * Converts database rows into a jsTree-compatible structure.
     *
     * @param rows List of maps representing DB rows.
     * @return List of jsTree nodes.
     */
    private List<JsTreeNode> buildJsTree(List<Map<String, Object>> rows) {
        Map<String, LineNode> lineMap = new HashMap<>();

        for (Map<String, Object> row : rows) {
            String lineNr = String.valueOf(row.get("line_nr"));
            String lineBez = String.valueOf(row.get("line_bez"));
            String maGrpNr = String.valueOf(row.get("ma_grp_nr"));
            String maGrpBez = String.valueOf(row.get("ma_grp_bez"));
            String kapNr = String.valueOf(row.get("kap_nr"));
            String kapBez = String.valueOf(row.get("kap_bez"));

            // 1) Get or create line node
            LineNode lineNode = lineMap.computeIfAbsent(lineNr,
                    ln -> new LineNode("" + lineNr, lineBez));

            // 2) Get or create ma group node
            MaGrpNode maNode = lineNode.getMaGroups().computeIfAbsent(maGrpNr,
                    mg -> new MaGrpNode("" + maGrpNr, maGrpBez));

            // 3) Add kap child
            KapNode kapNode = new KapNode("" + kapNr, kapBez);
            maNode.getKaps().add(kapNode);
        }

        return transformToJsTree(lineMap);
    }

    /**
     * Transforms the hierarchical structure into jsTree format.
     *
     * @param lineMap lines mapped by their lineNr
     * @return a list of top-level jsTree nodes
     */
    private List<JsTreeNode> transformToJsTree(Map<String, LineNode> lineMap) {
        List<JsTreeNode> result = new ArrayList<>();
        for (LineNode line : lineMap.values()) {
            JsTreeNode lineJs = new JsTreeNode(line.getId(), line.getText());
            lineJs.setChildren(new ArrayList<>());

            for (MaGrpNode ma : line.getMaGroups().values()) {
                JsTreeNode maJs = new JsTreeNode(ma.getId(), ma.getText());
                maJs.setChildren(new ArrayList<>());

                for (KapNode kap : ma.getKaps()) {
                    JsTreeNode kapJs = new JsTreeNode(kap.getId(), kap.getText());
                    kapJs.setChildren(Collections.emptyList());
                    maJs.getChildren().add(kapJs);
                }
                lineJs.getChildren().add(maJs);
            }
            result.add(lineJs);
        }
        return result;
    }

    // -------------------- Model Classes --------------------

    private static class LineNode {
        private final String id;
        private final String text;
        private final Map<String, MaGrpNode> maGroups = new LinkedHashMap<>();

        LineNode(String id, String text) {
            this.id = id;
            this.text = text;
        }

        public String getId() {
            return id;
        }

        public String getText() {
            return text;
        }

        public Map<String, MaGrpNode> getMaGroups() {
            return maGroups;
        }
    }

    private static class MaGrpNode {
        private final String id;
        private final String text;
        private final List<KapNode> kaps = new ArrayList<>();

        MaGrpNode(String id, String text) {
            this.id = id;
            this.text = text;
        }

        public String getId() {
            return id;
        }

        public String getText() {
            return text;
        }

        public List<KapNode> getKaps() {
            return kaps;
        }
    }

    private static class KapNode {
        private final String id;
        private final String text;

        KapNode(String id, String text) {
            this.id = id;
            this.text = text;
        }

        public String getId() {
            return id;
        }

        public String getText() {
            return text;
        }
    }

    private static class JsTreeNode {
        private final String id;
        private final String text;
        private List<JsTreeNode> children;

        JsTreeNode(String id, String text) {
            this.id = id;
            this.text = text;
        }

        public String getId() {
            return id;
        }

        public String getText() {
            return text;
        }

        public List<JsTreeNode> getChildren() {
            return children;
        }

        public void setChildren(List<JsTreeNode> children) {
            this.children = children;
        }
    }
}