package flyway.paikkis;

import fi.nls.oskari.log.LogFactory;
import fi.nls.oskari.log.Logger;
import org.flywaydb.core.api.migration.jdbc.JdbcMigration;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import fi.nls.oskari.util.JSONHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Note to English speakers: "Selkokartta" is a background layer for cognitively impaired users

public class V2_18__link_selkokartta_to_bglayer_selector implements JdbcMigration {
    private final Logger LOG = LogFactory.getLogger(this.getClass());

    private static final String EXTERNAL_ID = "base_selko";
    private static final String PLUGIN_ID = "Oskari.mapframework.bundle.mapmodule.plugin.BackgroundLayerSelectionPlugin";

    public void migrate(Connection connection) throws Exception {
        Integer selkokarttaId = getSelkoLayer(connection);
        if (selkokarttaId == null) {
            LOG.warn("No Selkokartta layer found, skipping rest of migration!");
            return;
        }
        updateLayerExternalId(connection, selkokarttaId, EXTERNAL_ID);

        updateViewsWithBaselayerSelector(connection, EXTERNAL_ID);
    }

    private Integer getSelkoLayer(Connection conn) throws SQLException {
        // find first Selkokartta layer
        final String sql = "SELECT id FROM oskari_maplayer WHERE name = 'selkokartta' AND url LIKE 'https://karttamoottori.maanmittauslaitos.fi%' LIMIT 1";
        try (PreparedStatement statement = conn.prepareStatement(sql)) {
            try (ResultSet rs = statement.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("id");
                }
            }
        }
        return null;
    }

    private void updateLayerExternalId(Connection conn, int layerId, String externalId) throws SQLException {
        final String sql = "UPDATE oskari_maplayer SET externalid=? WHERE id=?";
        try (PreparedStatement statement = conn.prepareStatement(sql)) {
            statement.setString(1, externalId);
            statement.setInt(2, layerId);
            statement.execute();
        }
    }

    private void updateViewsWithBaselayerSelector(Connection conn, String externalId) throws SQLException {
        final String sql = "SELECT view_id, seqno, config FROM portti_view_bundle_seq WHERE bundleinstance = 'mapfull'";
        try (PreparedStatement statement = conn.prepareStatement(sql)) {
            try (ResultSet rs = statement.executeQuery()) {
                while (rs.next()) {
                    JSONObject config = JSONHelper.createJSONObject(rs.getString("config"));
                    updateOneViewIfNeeded(conn, rs.getInt("view_id"), rs.getInt("seqno"), config, externalId);
                }
            }
        }
    }

    private void updateOneViewIfNeeded(Connection conn, int viewId, int seqNo, JSONObject config, String externalId) throws SQLException {
        JSONArray plugins = JSONHelper.getJSONArray(config, "plugins");
        for (int i = 0; i < plugins.length(); i++) {
            try {
                JSONObject plugin = (JSONObject) plugins.get(i);
                if (PLUGIN_ID.equals(JSONHelper.getStringFromJSON(plugin, "id", null))) {
                    JSONObject pluginConfig = JSONHelper.getJSONObject(plugin, "config");
                    if (pluginConfig == null) {
                        continue;
                    }
                    JSONArray baseLayers = JSONHelper.getJSONArray(pluginConfig, "baseLayers");
                    if (baseLayers == null) {
                        continue;
                    }

                    boolean hasSelkoLayer = false;
                    for (int j = 0; j < baseLayers.length(); j++) {
                        if (externalId.equals(baseLayers.getString(j))) {
                            hasSelkoLayer = true;
                        }
                    }
                    if (hasSelkoLayer) {
                        continue;
                    }
                    baseLayers.put(externalId);

                    final String sql = "UPDATE portti_view_bundle_seq SET config=? WHERE view_id=? AND seqno=?";
                    try (PreparedStatement statement = conn.prepareStatement(sql)) {
                        statement.setString(1, config.toString(2));
                        statement.setInt(2, viewId);
                        statement.setInt(3, seqNo);
                        statement.execute();
                    }
                }
            } catch (JSONException e) {
                LOG.error("Error in migration: {}!", e.getMessage());
            }
        }
    }
}
@@ -649,68 +649,8 @@
 );%0A%0A
-    private static final String EXTERNAL_ID = %22base_selko%22;%0A
     
@@ -1044,79 +1044,8 @@
    %7D
-%0A        updateLayerExternalId(connection, selkokarttaId, EXTERNAL_ID);
 %0A%0A  
@@ -1099,19 +1099,21 @@
 on, 
-EXTERNAL_ID
+selkokarttaId
 );%0A 
@@ -1671,412 +1671,8 @@
  %7D%0A%0A
-    private void updateLayerExternalId(Connection conn, int layerId, String externalId) throws SQLException %7B%0A        final String sql = %22UPDATE oskari_maplayer SET externalid=? WHERE id=?%22;%0A        try (PreparedStatement statement = conn.prepareStatement(sql)) %7B%0A            statement.setString(1, externalId);%0A            statement.setInt(2, layerId);%0A            statement.execute();%0A        %7D%0A    %7D%0A%0A
     
@@ -1734,31 +1734,31 @@
 n conn, 
-String external
+int selkokartta
 Id) thro
@@ -1877,15 +1877,53 @@
 ndle
-instanc
+_id = (select id from portti_bundle WHERE nam
 e = 
@@ -1931,16 +1931,17 @@
 mapfull'
+)
 %22;%0A     
@@ -2295,24 +2295,27 @@
 config, 
-external
+selkokartta
 Id);%0A   
@@ -2462,23 +2462,23 @@
 ig, 
-String external
+int selkokartta
 Id) 
@@ -3338,53 +3338,274 @@
    i
-f (externalId.equals(baseLayers.getString(j))
+nt current;%0A                        try %7B%0A                            current = baseLayers.getInt(j);%0A                        %7D catch (Exception e) %7B%0A                            continue;%0A                        %7D%0A                        if (selkokarttaId == current
 ) %7B%0A
@@ -3838,16 +3838,19 @@
 put(
-external
+selkokartta
 Id);
@@ -4397,12 +4397,8 @@
 ion:
- %7B%7D!
 %22, e

Use layer id instead of external_id