import GeometryClasses.Feature;
import GeometryClasses.FeatureCollection;
import GeometryClasses.Geometry;
import GeometryClasses.Utils;
import oracle.spatial.geometry.JGeometry;
import oracle.sql.STRUCT;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;


public class DBFeatureCollectionInserter {

    private static String url = "jdbc:oracle:thin:@//10.100.100.144:1521/develdb.napr.local";
    private static String username = "navigation";
    private static String password = "test";

    static {
        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

        } catch (ClassNotFoundException e) {

            System.out.println("Where is your Oracle JDBC Driver?");
            e.printStackTrace();

        }
    }

    private static Connection getConnection(){
        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void insertRoadsInDB(FeatureCollection roadFeatures){

        Connection connection = null;
        List<Feature> featureList = roadFeatures.getFeatures();
        int k = 0;
        while (k < featureList.size()) {
            connection = getConnection();

            if (connection != null) {

                Feature curFeature = featureList.get(k);
                insertRoadGeometry(curFeature.getGeometry(), connection, k);

                System.out.println("Inserted Road: " + k);
                k++;

                try {connection.close();} catch (SQLException e) {e.printStackTrace();}
            }
        }
        k = 0;

        while (k < featureList.size()) {


            connection = getConnection();

            if (connection != null) {

                Feature curFeature = featureList.get(k);
                insertRoadProperties(curFeature.getProperties(), connection, k);

                System.out.println("Inserted Propertie: " + k);
                k++;

                try {connection.close();} catch (SQLException e) {e.printStackTrace();}
            }
        }
    }

    private static void insertRoadProperties(Map<String, Object> properties, Connection connection, int id) {
        try (PreparedStatement ps = connection.prepareStatement("INSERT INTO ROAD_PROPERTIES VALUES (?, ?, ?, 0, 3, NULL, NULL, ?, ?)")) {
            ps.setInt(1, id);
            ps.setString(2, (String) properties.get("RoadObID"));
            ps.setString(3, (String) properties.get("RoadGrID"));
            ps.setString(4, (String) properties.get("RoadsName"));
            ps.setInt(5, ((Double) properties.get("RoadCount")).intValue());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    private static void insertRoadGeometry(Geometry g, Connection connection, int id) {
        JGeometry geometry = Utils.geometryToJGeometry(g);
        String updateQuery = "INSERT INTO road_geometries VALUES (" + id + ",?) ";
        try (PreparedStatement ps = connection.prepareStatement(updateQuery)) {
            STRUCT obj = null;
            try {
                obj = JGeometry.store(connection, geometry);
            } catch (Exception e) {
                e.printStackTrace();
            }
            ps.setObject(1, obj);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void insertGridsInDB(FeatureCollection gridFeatures){

        Connection connection = null;

        List<Feature> featureList = gridFeatures.getFeatures();
        int k = 0;
        while (k < featureList.size()) {


            connection = getConnection();

            if (connection != null) {

                Feature curFeature = featureList.get(k);
                insertGridGeometry(curFeature.getGeometry(), connection, k);

                System.out.println("Inserted Geometry: " + k);
                k++;

                try {connection.close();} catch (SQLException e) {e.printStackTrace();}
            }
        }
        k = 0;
        while (k < featureList.size()) {


            connection = getConnection();

            if (connection != null) {

                Feature curFeature = featureList.get(k);
                insertGridProperties(curFeature.getProperties(), connection, k);

                System.out.println("Inserted Propertie: " + k);
                k++;

                try {connection.close();} catch (SQLException e) {e.printStackTrace();}
            }
        }
    }


    public static void insertGridGeometry(Geometry g, Connection connection, int id){

        JGeometry geometry = Utils.geometryToJGeometry(g);
        String updateQuery = "INSERT INTO grid_geometries VALUES (" + id + ",?) ";
        try (PreparedStatement ps = connection.prepareStatement(updateQuery)) {
            STRUCT obj = null;
                try {
                    obj = JGeometry.store(connection, geometry);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                ps.setObject(1, obj);
                ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void insertGridProperties(Map<String, Object> properties, Connection connection, int id){

        String updateQuery = "INSERT INTO GRID_PROPERTIES VALUES (?, ?, 0, 3, NULL, NULL, ?, ?)";
        //TODO insert parameters
        try (PreparedStatement ps = connection.prepareStatement(updateQuery)) {
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws IOException {
//        insertGridsInDB(GeoJsonReader.readGeoJson("/home/kokadva/IdeaProjects/GeoJsonUploader/src/main/resources/grid.json"));
        insertRoadsInDB(GeoJsonReader.readGeoJson("/home/kokadva/IdeaProjects/GeoJsonUploader/src/main/resources/roads.geojson"));
    }
}