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

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import org.codehaus.jettison.json.JSONException;

/**
 *
 * @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
 */
public class SoilComponentTranslator {

    Connection sourceDb;
    Connection destinationDb;

    public SoilComponentTranslator(Connection sourceDb, Connection destinationDb) {
        this.sourceDb = sourceDb;
        this.destinationDb = destinationDb;
    }

    public String translate(ModelArchive modelArchive) throws SQLException, JSONException {
        String ret_val = null;
        ArrayList<MapunitData> foundMapunits = new ArrayList<>();
        String areasymbol = "", muname, mukey, compname = "";
        double muacres = 0.0;

        //  1)  Search for mapunit information for the ssurgo cokey 
        //  2)  Get a list of all SDM mapunits that match areasymbol from that mapunit where muacres is also the same value
        //  3)  Search the list of resulting SDM mapunits for components that may be the same component (Based on what?  name?)
        String query = "SELECT areasymbol, muacres, muname, mapunit.mukey, cokey, compname FROM ssurgo.component, ssurgo.mapunit, ssurgo.soilmu_a WHERE cokey=" + modelArchive.getOldCokey() + " AND component.mukey=mapunit.mukey AND soilmu_a.mukey=component.mukey;";

        try (Statement statement = sourceDb.createStatement();) {
            ResultSet results = statement.executeQuery(query);
            if (results.next()) {
                areasymbol = results.getString("areasymbol");
                muacres = results.getDouble("muacres");
                muname = results.getString("muname");
                mukey = results.getString("mukey");
                compname = results.getString("compname");
            }
            results.close();
        }

        query = "SELECT areasymbol, muacres, muname, mapunit.mukey, cokey, compname FROM mapunit, legend, component WHERE mapunit.lkey=legend.lkey AND mapunit.mukey=component.mukey AND legend.areasymbol='" + areasymbol + "' AND muacres=" + muacres + " AND component.compname='" + compname + "' ORDER BY mapunit.mukey, cokey ;";
        try (Statement statement = destinationDb.createStatement();) {
            ResultSet results = statement.executeQuery(query);
            String lastMukey = "";
            MapunitData lastMapunit = null;

            while (results.next()) {
                String tMukey = results.getString("mukey");
                ComponentData newComponent = new ComponentData();
                newComponent.cokey = results.getString("cokey");
                newComponent.compname = results.getString("compname");

                if ((lastMukey.equals(tMukey)) && (null != lastMapunit) && (lastMapunit.mukey.equals(tMukey))) {
                    lastMapunit.components.add(newComponent);
                } else {
                    MapunitData newMapunit = new MapunitData();
                    newMapunit.areasymbol = results.getString("areasymbol");
                    newMapunit.muacres = results.getDouble("muacres");
                    newMapunit.muname = results.getString("muname");
                    newMapunit.mukey = tMukey;
                    newMapunit.components.add(newComponent);
                    foundMapunits.add(newMapunit);
                    lastMukey = tMukey;
                    lastMapunit = newMapunit;
                }
            }
            results.close();
        }

        if (foundMapunits.size() > 0) {
            for (MapunitData mapunit : foundMapunits) {
                if (mapunit.components.size() > 0) {
                    ret_val = mapunit.components.get(0).cokey;
                    break;
                }
            }
        }

        return ret_val;
    }

    class MapunitData {

        ArrayList<ComponentData> components = new ArrayList<>();
        String areasymbol = "", muname = "", mukey = "";
        double muacres = 0.0;
    }

    class ComponentData {

        String cokey;
        String compname;
    }

}