/*
 */
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 String username = "postgres";
    protected static String password = "";
    protected static Connection c_con;
    protected static String componentKey = "1008427:1454414";

    /**
     * **************************************************************************
     */
    public static void main(String[] args) {
        int startingRow = 0;
        String connectString = "";

        try {
            if (args.length > 0) {
                database = args[0];
            }

            if (args.length > 1) {
                startingRow = Integer.parseInt(args[1]);
                if (args.length > 2) {
                    connectString = args[2];
                }
            }

            System.out.println("Starting row=" + startingRow);
            if (connectString.isEmpty()) {
                c_con = DriverManager.getConnection(conn + database, "postgres", "");
            } else {
                c_con = DriverManager.getConnection(connectString);
            }

// 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 = "";
            if (c_con.getMetaData().getDatabaseProductName().contains("Microsoft")) {
                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 "
                        + "( (NOT ( taxsubgrp like '%Histic%')) 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;";
            } else {
                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(":", "_");
    }
}