/*
 */
package usda.weru.soil;

import java.io.File;
import java.lang.ClassLoader.*;
import java.sql.*;

//main() is in this function
//based on classes from WEPS GUI Java code: extends usda.weru.soil.gui.SoilUI_n implements PropertyChangeListener //implements ActionListener ,ItemListener
//public class SoilUI extends SoilUI_n implements ActionListener ,ItemListener
//,HelpDelegator
//,ItemListener

public class Soil
{
    //protected static String conn = "jdbc:postgresql://129.82.225.74:5432/";
    //protected static String conn = "jdbc:postgresql://localhost:5432/";
    protected static String conn = "jdbc:postgresql://csip.engr.colostate.edu:5435/";
    protected static String database = "ssurgo";
    protected static Connection c_con;
    protected static String componentKey = "1008427:1454414";
    
    /*****************************************************************************/
    public static void main(String[] args) 
    {
        int startingRow = 0;
        try
        {
            if (args.length > 0)
                database = args[0];
            
            if (args.length > 1)
                startingRow = Integer.parseInt(args[1]);
            
            System.out.println("Starting row=" + startingRow);
            
            c_con = DriverManager.getConnection(conn + database, "postgres", "");
            
// OLD soils query before adding new phacil fields            
//            String query = "SELECT soilmu_a.areasymbol as co_fips, soilmu_a.musym, component.mukey, cokey " +
//                           "FROM ssurgo.component join ssurgo.soilmu_a on component.mukey = soilmu_a.mukey " +
////                           "WHERE to_number(component.mukey, '999999999') >= 382040 and to_number(component.mukey, '999999999') <= 382050 " +
//                           "GROUP BY soilmu_a.areasymbol, soilmu_a.musym, component.mukey, component.cokey;";
            
            String query = "SELECT distinct legend.areasymbol as co_fips, mapunit.musym, component.mukey, muname, compname, chorizon.kffact,  component.tfact, component.slope_r, component.cokey " +
                           "FROM ssurgo.component LEFT OUTER JOIN ssurgo.mapunit ON (component.mukey = mapunit.mukey) " +
                           "LEFT OUTER JOIN ssurgo.legend ON (legend.lkey = mapunit.lkey) " +
                           "LEFT OUTER JOIN ssurgo.chorizon ON (component.cokey = chorizon.cokey) " +
                           "LEFT OUTER JOIN ssurgo.chtexturegrp ON (chtexturegrp.chkey = chorizon.chkey) " +
                           "LEFT OUTER JOIN ssurgo.chtexture ON (chtexturegrp.chtgkey = chtexture.chtgkey) " +
                           "where ((component.taxorder = 'Histosols') or " +
                           "((position('Histic' in taxsubgrp) <= 0) or (taxsubgrp is null))) " +
                           "and component.comppct_r > 15 " +
                           // for testing specific cokeys
                           //"and component.cokey ='10374004' " +
                           "and hzdept_r in (select hzdept_r from ssurgo.minhzdept_t where minhzdept_t.cokey=component.cokey and minhzdept_t.areasymbol=legend.areasymbol and minhzdept_t.musym=mapunit.musym and minhzdept_t.mukey=mapunit.mukey) " +
                           "order by co_fips, musym, component.mukey;";
            
            Statement stmt = c_con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);            
            ResultSet rs = stmt.executeQuery(query);  
            rs.relative(startingRow);
            
            int i=1;
            String cokey = "";
            System.out.println("any records?");
            while (rs.next())  {
                try
                {
                    NASIS nasis = new NASIS(c_con);
                    cokey = rs.getString(9);
                    String filename = convertCoKey(rs.getString(9));
                    String stateAbbrev = rs.getString(1).substring(0, 2);
                    System.out.println("stateAbbrev=" + stateAbbrev);
                    System.out.println("Processing soil " + i + " with cokey=" + cokey);

                    i++;
                    //if (i>100)  break;
                    boolean valid = nasis.loadSQL(cokey);
                    if(!valid) {
                        System.err.println("error reading soil record");
                        return;
                    }
                    IFC ifc = new IFC(nasis);
                    ifc.sendErrorsToInternalVariable();
                    ifc.getLog().mergeLog(nasis.getLog());

                    // comment this out for testing to avoid writing soils ifcs and generating insert statements, supports counting errors in the data:
                    File thedir = new File("soils/" + stateAbbrev);
                    if (!thedir.exists())
                        thedir.mkdirs();
                    
                    if (ifc.writeNewIfc("soils/" + stateAbbrev + "/" + filename))
                        System.out.println("INSERT into MAP_SOILS_WEPS values ('" + rs.getString(1) + "','" + rs.getString(2) + "','" + rs.getString(3) + 
                                                                               "','soils/" + stateAbbrev + "','" + filename + "', '" + rs.getString(4) +
                                                                               "','" + rs.getString(5) + "','" + rs.getString(6) + "','" + rs.getString(7) + 
                                                                               "','" + rs.getString(8) + "','" + rs.getString(9) + "');");
                }
                catch (OrganicSoilException ose)
                {
                    System.err.println(ose.getMessage());
                    System.err.println("Organic Soil Exception caught.\nIgnoring error and skipping .ifc file generation for soil component=" + cokey);
                    System.err.println("Will generate pointer to default organic soil .ifc file instead.");
                    System.out.println("INSERT into MAP_SOILS_WEPS values ('" + rs.getString(1) + "','" + rs.getString(2) + "','" + rs.getString(3) + 
                                                                            "','soils/NRCS%20Generic%20Soils/" + "','Organic%20Soil', '" + rs.getString(4) +
                                                                            "','" + rs.getString(5) + "','" + rs.getString(6) + "','" + rs.getString(7) + 
                                                                            "','" + rs.getString(8) + "','" + rs.getString(9) + "');");
                    ose.printStackTrace(System.err);
                }
                catch (InvalidSoilException ise)
                {
                    System.err.println(ise.getMessage());
                    System.err.println("Invalid Soil Exception caught.\nIgnoring error and skipping soil component=" + cokey);
                    ise.printStackTrace(System.err);
                }
            }
            System.out.println("end processing.");
        }
        catch (SQLException se)
        {
            System.err.println("Error connecting to ssurgo db.");
            System.err.println(se.toString());
        }
    }


    // Converts (soil) component key to a format to use for the output (ifc) file
    // Removes the "_"
    private static String convertCoKey(String coKeyIn)
    {
        return coKeyIn.replace(":","_");
    }
}