/*
 * 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 rfactorpopulator;

import java.io.IOException;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.DOMException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;


/**
 *
 * @author wlloyd
 */
public class RFactorPopulator {

    private static String confString = "jdbc:postgresql://csip.engr.colostate.edu:5435/r2gis";
    private static String error_msg;    
    private static Connection conn;
    private static Statement statement;

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws Exception {
        String rFactor;  

        conn = getConnection();
        statement = conn.createStatement();

        String gisQuery="SELECT m.co_fips, r2_path, r2_name, m.ei_rang, r_factor FROM map_climates AS m;";
        ResultSet results = statement.executeQuery(gisQuery);
        while(results.next()){
            String r2path = escapeSingleQuotes(results.getString("r2_path"));
            String r2name = escapeSingleQuotes(results.getString("r2_name"));
            String cofips = results.getString("co_fips");
            String eirang = results.getString("ei_rang");
            String urlName = getR2GISFileURL( results.getString("r2_path"), results.getString("r2_name") );                                
            rFactor=getAndProcessXml(urlName);
            
            String updateSql = "";
            
            if (!rFactor.equals("-1"))
                if (eirang == null)
                    updateSql = "UPDATE map_climates SET r_factor=" + rFactor + " WHERE " +
                                       "co_fips = '" + cofips + "' AND " +
                                       "ei_rang is null AND " +
                                       "r2_path = '" + r2path + "' AND " +
                                       "r2_name = '" + r2name + "';";
                else
                    updateSql = "UPDATE map_climates SET r_factor=" + rFactor + " WHERE " +
                                       "co_fips = '" + cofips + "' AND " +
                                       "ei_rang = '" + eirang + "' AND " +
                                       "r2_path = '" + r2path + "' AND " +
                                       "r2_name = '" + r2name + "';";
            
            System.out.println(updateSql);
        }
        
    }
    
    
    public static synchronized Connection getConnection() throws Exception {
        Connection ret_val = null;
        if(confString == null || confString.isEmpty())
        {
            System.out.println("Connection string not provided.  Please configure wqm.db configuration parameter.");            
            throw new Exception("Unable to connect to r2gis database.  Please check the connection string configuration parameter");
        }
        ret_val  = DriverManager.getConnection(confString, "postgres", "postgres");    
        return ret_val;
    }

    public static String getDatabaseHostname(  ) throws Exception{
        String ret_val = new URL(confString.replace("jdbc:postgresql", "http")).getHost();
        return ret_val;
    }
    
    public static String getR2GISFileURL( String path, String name ) throws Exception{
        return ( (("http://" + getDatabaseHostname() + ":81/r2/" + path + "/" + name + ".xml")).replace(" ","%20")).replace("\\","/");                                      
    } 

    public static String escapeSingleQuotes(String text)
    {
        String sFull = "";
        sFull = text.replace("'", "''");
        return sFull;
    }
    private static String getAndProcessXml(String url){ 
        String ret_val = null;
        
        try{
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
        DocumentBuilder db = dbf.newDocumentBuilder();

        URL Url = new URL(url);
        URLConnection urlConnection = Url.openConnection();
        Document document = db.parse(urlConnection.getInputStream());
        document.getDocumentElement().normalize();
        Element root=document.getDocumentElement();	
        NodeList nList= document.getElementsByTagName("Flt");

        for (int temp = 0; temp < nList.getLength(); temp++){
            Node node = nList.item(temp);

            if (node.getNodeType() == Node.ELEMENT_NODE){
                Element eElement = (Element) node;
                String Name=eElement.getElementsByTagName("Name").item(0).getTextContent();
                if(Name.equals("R_FACTOR")){            
                    ret_val =  eElement.getElementsByTagName("Data").item(0).getTextContent();
                }
            }
        }
        }
        catch( ParserConfigurationException | IOException | SAXException | DOMException ex ){
            error_msg = "Cannot parse the returned XML: " + ex.getMessage();
            System.err.println(error_msg );
            return "-1";
        }
        
    return ret_val;
    }
    
    
    
}