/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package ohdmrenderfilterpolygons;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author michael
 */
public class OhdmRenderFilterPolygons {
   private static final int AMOUNT_THREADS = 16;

    public static Connection connectDB( String DB) throws SQLException {

        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your PostgreSQL JDBC Driver? " + "Include in your library path!");
            e.printStackTrace();
            return null;
        }

    //System.out.println("PostgreSQL JDBC Driver Registered!");

    Connection conn = null;

        try{
            conn = DriverManager.getConnection(DB, "*****", "********" );
            return conn;
        }catch(SQLException e){
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return conn;
        }
    }


    /**
     * @param args the command line arguments
     * @throws java.sql.SQLException
     * @throws java.lang.InterruptedException
     */
    public static void main(String[] args) throws SQLException, InterruptedException {

        final Counter counterNumber = new Counter();
        long timeStart;
        long timeEnd;
        //final ProgressBar progressbar = new ProgressBar();

        timeStart = System.currentTimeMillis();



        System.out.println("Rendering Filter start...");


        Runnable runnable;
        runnable = new Runnable() {
           @Override
           public void run() {
               while(counterNumber.getCheck()){
                   String db_ohdm = "jdbc:postgresql://ohm.f4.htw-berlin.de:5432/ohdm";
                   String db_rendering = "jdbc:postgresql://ohm.f4.htw-berlin.de:5432/ohdm_rendering";
                   String key = "";
                   Statement st = null;
                   int counterFrom  = counterNumber.getCounterFrom();
                   int counterTo    = counterNumber.getCounterTo();
                   //System.out.println("Connect to DB: " + db);

                   Connection conn = null;
                   try {
                       conn = connectDB(db_ohdm);
                   } catch (SQLException ex) {
                       Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                   }

                   if (conn != null) {
                       //System.out.println("Database connection successful");
                   } else {
                       System.out.println("Failed to make connection!");
                   }

                   Statement stmt = null;
                   try {
                       stmt = conn.createStatement();
                   } catch (SQLException ex) {
                       Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                   }


                   String sql =    "SELECT ST_AsBinary(mp.multipolygon_geom) as geometrie, gt.key as key, gt.value as value, gt.valid_until as until, gt.valid_since as since "
                           +   "FROM ohdm.multipolygon_geom mp "
                           +   "JOIN ohdm.geographic_geom_dates gd ON gd.id_multipolygon_geom = mp.id "
                           +   "JOIN ohdm.geographic_objects gobjects  ON gobjects.id = gd.id_geographic_object_source "
                           +   "JOIN ohdm.geographic_tag gt ON gt.id_geographic_objects = gobjects.id "
                           +   "WHERE   mp.id > " + counterFrom + " and mp.id < " + counterTo + " "
                           +   "AND gt.key in ('admin_level', 'boundary', 'highway', 'landuse', 'natural')" ;


                   ResultSet rs = null;
                   try {
                       rs = stmt.executeQuery(sql);
                   } catch (SQLException ex) {
                       Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                   }
                   try {
                       conn.close();
                   } catch (SQLException ex) {
                       Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                   }

                   try {
                   conn = connectDB(db_rendering);
                   } catch (SQLException ex) {
                   Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                   }

                   if (conn != null) {
                   //System.out.println("Database connection successful");
                   } else {
                   System.out.println("Failed to make connection!");
                   }
                   try {
                   stmt = conn.createStatement();
                   } catch (SQLException ex) {
                   Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                   }



                   try {
                    while(rs.next()){
                         sql = "SELECT id FROM public.polygons WHERE way = ST_GeomFromWKB('" + rs.getString("geometrie") + "') LIMIT 1";

                         ResultSet rs_select = null;
                         try {
                             rs_select = stmt.executeQuery(sql);
                         } catch (SQLException ex) {
                             Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                         }
                         if(rs.getString("key").matches("natural")){
                                  key = "natural_";
                              }else {
                                  key = rs.getString("key");
                              }
                         if(rs_select.isBeforeFirst()){
                              //System.out.println("Update");

                              sql =    "UPDATE public.polygons SET " + key +""
                                    + " = '" + rs.getString("value") + "' WHERE way = ST_GeomFromWKB('" + rs.getString("geometrie") + "')";
                              //System.out.println(sql);
                              st = conn.createStatement();
                              conn.setAutoCommit(false);
                              st.executeUpdate(sql);
                              conn.commit();
                         }else{
                              //System.out.println("INSERT");
                              conn.setAutoCommit(true);
                              sql =    "INSERT INTO public.polygons (" + key + ", way, since, until)"
                                    + " VALUES (?, ST_GeomFromWKB('" + rs.getString("geometrie") + "'), ?, ?)";


                              PreparedStatement pst = conn.prepareStatement(sql);
                              pst.setString(1, rs.getString("value"));
                              pst.setDate(2, rs.getDate("since"));
                              pst.setDate(3, rs.getDate("until"));
                              //System.out.println(pst.toString());
                              pst.executeUpdate();
                         }
                         rs_select.close();
                    }  } catch (SQLException ex) {
                    Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                    }

                   try {
                   rs.close();
                   } catch (SQLException ex) {
                   Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                   }

                   try {
                   conn.close();
                   } catch (SQLException ex) {
                   Logger.getLogger(OhdmRenderFilterPolygons.class.getName()).log(Level.SEVERE, null, ex);
                   }

                   //System.out.println("connection closed");
                   //System.out.println("connection closed");
               }
           }
       };

        for (int i = 0; i < AMOUNT_THREADS; i++) {
            new Thread(runnable).start();
        }

        while (counterNumber.getCheck()) {
           //progressbar.update(counterNumber.getCounter(), 198623546);
           System.out.printf(counterNumber.getCounter() + " von 198623546 erledigt");
        }
        timeEnd = System.currentTimeMillis();
        System.out.println("\nZeit benötigt: " + (((timeEnd - timeStart)/1000))/60 + " min");
        System.out.println("Rendering Filter Stop...");
    }
}