package nl.opengeogroep.safetymaps.viewer;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import static nl.opengeogroep.safetymaps.server.db.JSONUtils.rowToJson;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

/**
 * Class to export object data for safetymaps-viewer, meant for both online use
 * within safetymaps-server and for exporting to filesystem for offline viewers.
 *
 * @author Matthijs Laan
 */
public class ViewerDataExporter {

    // TODO LogFactory use, so errors get logged both online and when exporting

    private Connection c;

    public ViewerDataExporter(Connection c) {
        this.c = c;
    }

    /**
     * Get a tag with the latest date an object was modified and total object
     * count, to use as caching ETag.
     */
    public String getObjectsETag() throws Exception {
        String key = new QueryRunner().query(c, "select max(\"Datum_Actualisatie\") || '_' || count(*) from wfs.\"DBK\"", new ScalarHandler<String>());
        key += "_v" + new QueryRunner().query(c, "select max(value) from viewer.schema_version", new ScalarHandler<>());
        if(key == null) {
            return "empty_db";
        } else {
            return key;
        }
    }

    /**
     * @return All the objects that should be visible in the viewer with all object
     * properties needed by the viewer as a JSON array, without properties that are
     * null or empty strings.
     */
    public JSONArray getViewerObjectMapOverview() throws Exception {
        List<Map<String,Object>> rows = new QueryRunner().query(c, "select * from viewer.viewer_object_map", new MapListHandler());

        // Not efficient in PostgreSQL: selectieadressen and verdiepingen

        Set<Integer> verdiepingenIds = new HashSet(new QueryRunner().query(c, "select hoofdobject_id from viewer.viewer_object where hoofdobject_id is not null", new ColumnListHandler<>()));

        Map selectieadressen = new QueryRunner().query(c, "select * from viewer.viewer_object_selectieadressen", new KeyedHandler<>("id"));

        JSONArray a = new JSONArray();
        for(Map<String, Object> row: rows) {
            Integer id = (Integer)row.get("id");
            String name = (String)row.get("formele_naam");
            try {
                JSONObject o = rowToJson(row, true, true);
                if(verdiepingenIds.contains(id)) {
                    o.put("heeft_verdiepingen", true);
                }
                Map sa = (Map)selectieadressen.get(id);
                if(sa != null) {
                    o.put("selectieadressen", new JSONArray(sa.get("selectieadressen").toString()));
                }
                a.put(o);
            } catch(Exception e) {
                throw new Exception(String.format("Error processing object id=%d, name \"%s\"", id, name), e);
            }
        }
        return a;
    }

    /**
     * @return All details for the viewer of an objector null
     * if the object is not found or visible in the viewer
     */
    public JSONObject getViewerObjectDetails(long id) throws Exception {
        List<Map<String,Object>> rows = new QueryRunner().query(c, "select * from viewer.viewer_object_details where id = ?", new MapListHandler(), id);

        if(rows.isEmpty()) {
            return null;
        }

        try {
            return rowToJson(rows.get(0), true, true);
        } catch(Exception e) {
            throw new Exception(String.format("Error getting object details for id " + id), e);
        }
    }

    /**
     * Get all ids of objects that should be visible in the viewer.
     */
    public List<Integer> getViewerObjectIds() throws Exception {
        return new QueryRunner().query(c, "select id from viewer.viewer_object_map", new ColumnListHandler<Integer>());
    }

    /**
     * @return All details for viewer objects using a single query
     */
    public List<JSONObject> getAllViewerObjectDetails() throws Exception {
        List<Map<String,Object>> rows = new QueryRunner().query(c, "select * from viewer.viewer_object_details", new MapListHandler());

        List<JSONObject> result = new ArrayList();

        for(Map<String,Object> row: rows) {
            Object id = row.get("id");
            Object name = row.get("name");
            try {
                result.add(rowToJson(row, true, true));
            } catch(Exception e) {
                throw new Exception(String.format("Error converting object details to JSON for id " + id + ", name " + name), e);
            }
        }
        return result;
    }

    /**
     * Get styling information
     */
    public JSONObject getStyles() throws Exception {
        JSONObject o = new JSONObject();

        JSONObject compartments = new JSONObject();
        o.put("compartments", compartments);
        List<Map<String,Object>> rows = new QueryRunner().query(c, "select * from wfs.type_compartment", new MapListHandler());
        for(Map<String,Object> row: rows) {
            String code = (String)row.get("code");
            JSONObject compartment = rowToJson(row, false, false);
            compartments.put(code, compartment);
        }

        JSONObject lines = new JSONObject();
        o.put("custom_lines", lines);
        rows = new QueryRunner().query(c, "select * from wfs.type_custom_line", new MapListHandler());
        for(Map<String,Object> row: rows) {
            String code = (String)row.get("code");
            JSONObject line = rowToJson(row, false, false);
            lines.put(code, line);
        }

        JSONObject polygons = new JSONObject();
        o.put("custom_polygons", polygons);
        rows = new QueryRunner().query(c, "select * from wfs.type_custom_polygon", new MapListHandler());
        for(Map<String,Object> row: rows) {
            String code = (String)row.get("code");
            JSONObject polygon = rowToJson(row, false, false);
            polygons.put(code, polygon);
        }

        return o;
    }
}

@@ -1291,17 +1291,15 @@
 max(
-%5C%22D
+d
 atum_
-A
+a
 ctua
@@ -1305,18 +1305,16 @@
 alisatie
-%5C%22
 ) %7C%7C '_'
@@ -1335,19 +1335,28 @@
 rom 
-wfs.%5C%22DBK%5C%22
+viewer.viewer_object
 %22, n

build the ETag from the view and not the real table