/*
 * MdbSoilDatabase.java
 *
 * Created on April 20, 2007, 2:43 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package usda.weru.soil;


import de.schlichtherle.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.LinkedList;
import java.util.List;
import javax.swing.ProgressMonitor;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.MutableTreeNode;
import javax.swing.tree.TreeNode;
import usda.weru.util.AbstractLazyLoadingTreeNode;


/**
 *
 * @author Joseph Levin
 */
public abstract class JdbcSoilDatabase implements SoilDatabase{
    
    
    protected Connection c_con;
    public static boolean estimateNullValues = true;
    public static double omFractionThreshold;
    
    protected File c_file;
    protected Comparator c_sorter;
    protected String c_name;
    protected DefaultMutableTreeNode c_rootNode;
    
    //private Thread c_indexer;
    /** Creates a new instance of MdbSoilDatabase */
    public JdbcSoilDatabase() {
        c_sorter = new NodeComparator();        
    }
    
    public JdbcSoilDatabase(String name) {
        this();
        c_name = name;
    }               
    
    public MutableTreeNode[] createNodes() {
        if (c_rootNode == null){
            c_rootNode = new DatabaseNode();
        }
        return new MutableTreeNode[]{c_rootNode};
    }
    
    
    private static boolean checkForWarning(SQLWarning warn)throws SQLException  {
        boolean rc = false;
        
        // If a SQLWarning object was given, display the
        // warning messages.  Note that there could be
        // multiple warnings chained together
        
        if (warn != null) {
            while (warn != null) {
                switch(warn.getErrorCode()){
                    case 1: //Readonly
                        warn = warn.getNextWarning();
                        continue;
                    case 63: //Can't read a registry setting
                        warn = warn.getNextWarning();
                        continue;
                }
                if (!rc){
                    //System.err.println("*** JDBC ODBC WARNINGS ***");
                }
                rc = true;
                //System.err.println("SQLState: " +
                //warn.getSQLState());
                //System.err.println("Message:  " +
                //warn.getMessage());
                //System.err.println("Error:   " +
                //warn.getErrorCode());
                //System.err.println("");
                //warn = warn.getNextWarning();
            }
            if (rc){
                //System.err.println("*** *** *** *** *** ***");
            }
        }
        return rc;
    }
    
    private Collection <String> getQuasiQuery(String... columns){
        List <String> temp = new LinkedList <String> ();
        for (String column : columns){
            temp.add(column);
        }
        return temp;
    }
    
    
    private String getString(ResultSet rs, String column){
        try{
            if (rs == null || column == null) return null;
            Object temp = rs.getString(column);
            if (temp != null){
                return temp.toString();
            }
        } catch(SQLException se){
            return null;
        }
        return null;
    }
    
    //this needs to also make sure the node is from the current instance
    public boolean isSoil(TreeNode node) {       
        if (node != null && node instanceof ComponentNode){
            ComponentNode n = (ComponentNode) node;
            return c_rootNode.isNodeDescendant(n);
        }
        return false;
    }
    
    public synchronized IFC getIfcFromNode(TreeNode node, ProgressMonitor progress) {        
        if (isSoil(node)){
            NASIS nasis = new NASIS(c_con, progress);
            boolean valid = nasis.loadSQL(((ComponentNode) node).getCokey());
            if(!valid) {
                return null;
            }
            IFC ifc = new IFC(nasis);
            ifc.sendErrorsToInternalVariable();
            ifc.getLog().mergeLog(nasis.getLog());            
            return ifc;
        } else{
            return null;
        }
    }
    
    public void dispose() {
        if (c_con != null){
            try {
                c_con.close();
            } catch (SQLException ex) {
                
            }
        }
    }
    
    protected abstract Connection createConnection();           
    
    class DatabaseNode extends AbstractLazyLoadingTreeNode{
        private boolean c_beenNotified;
        public DatabaseNode(){
            if (c_name != null){
                setUserObject(c_name);
            }
        }

        public boolean isLeaf() {
            return false;
        }
        

        
        @Override
        public MutableTreeNode[] createChildren() {
            
            try{
                List <MutableTreeNode> temp = new LinkedList <MutableTreeNode> ();
                c_con = createConnection();
                
                if (checkForWarning(c_con.getWarnings())){
                    return new MutableTreeNode[0];
                }
                Statement stmt = c_con.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT lkey,areasymbol,areaname FROM legend ORDER BY areasymbol");
                
                while(rs.next()){
                    temp.add(new LegendNode(rs));
                }

                c_beenNotified = true;
                return temp.toArray(new MutableTreeNode[temp.size()]);
            } catch(Exception e){                
                c_beenNotified = false;
                return null;
            }
            
            
        }
        
    }
    
    class LegendNode extends AbstractLazyLoadingTreeNode{
        private boolean c_beenNotified;
        private String c_areasymbol;
        private String c_areaname;
        private String c_lkey;
        
        public LegendNode(ResultSet rs){
            setAllowsChildren(true);
            c_lkey = getString(rs, "lkey");
            c_areaname = getString(rs, "areaname");
            c_areasymbol = getString(rs, "areasymbol");
            
            setUserObject(c_areasymbol + " - " + c_areaname);
        }
        
        public boolean isLeaf() {
            if (!c_beenNotified){
                return false;
            } else{
                return super.isLeaf();
            }
        }
        
        
        public MutableTreeNode[] createChildren() {
            
            try{
                List <MutableTreeNode> temp = new LinkedList <MutableTreeNode> ();
                Statement stmt = c_con.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT mukey,musym,muname,lkey FROM mapunit WHERE lkey = '" + c_lkey + "' ORDER BY muname");
                
                while(rs.next()){
                    temp.add(new MapUnitNode(rs));
                }
                Collections.sort(temp, c_sorter);
                MutableTreeNode[] nodes = new MutableTreeNode[0];
                c_beenNotified = true;
            return temp.toArray(nodes);
                
            } catch (Exception e){
                c_beenNotified = false;
                return null;
            }
            
        }
        
    }
    
        
    class MapUnitNode extends AbstractLazyLoadingTreeNode{
        private boolean c_beenNotified;
        private String c_musym;
        private String c_muname;
        private String c_mukey;
        private String c_lkey;
        
        public MapUnitNode(ResultSet rs){
            setAllowsChildren(true);
            c_lkey = getString(rs, "lkey");
            c_mukey = getString(rs, "mukey");
            c_muname = getString(rs, "muname");
            c_musym = getString(rs, "musym");
            
            
            setUserObject(c_musym + " - " + c_muname);
        }
        
        public boolean isLeaf() {
            if (!c_beenNotified){
                return false;
            } else{
                return super.isLeaf();
            }
        }
        
        
        public MutableTreeNode[] createChildren() {
            
            try{
                List <MutableTreeNode> temp = new LinkedList <MutableTreeNode> ();
                Statement stmt = c_con.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT cokey,comppct_r,compname,localphase,mukey FROM component WHERE mukey = '" + c_mukey + "'");
                
                while(rs.next()){
                    temp.add(new ComponentNode(rs));
                }
                Collections.sort(temp, c_sorter);
                c_beenNotified = true;
                return temp.toArray(new MutableTreeNode[temp.size()]);
                
            } catch (Exception e){
                c_beenNotified = false;
                return null;
            }
            
        }
        
    }
    
    class ComponentNode extends DefaultMutableTreeNode{
        private String c_cokey;
        private String c_mukey;
        private String c_localphase;
        private String c_compname;
        private String c_comppct_r;
        private int c_row;
        
        public ComponentNode(ResultSet rs){
            setAllowsChildren(true);
            c_cokey = getString(rs, "cokey");
            c_mukey = getString(rs, "mukey");
            c_compname = getString(rs, "compname");
            c_localphase = getString(rs, "localphase");
            c_comppct_r = getString(rs, "comppct_r");
            
            
            if (c_localphase != null && c_localphase.trim().length() > 0){
                setUserObject(c_comppct_r + " - " + c_compname + " - " + c_localphase);
            } else{
                setUserObject(c_comppct_r + " - " + c_compname);
            }
        }
        
        public boolean isLeaf() {
            return true;
        }
        
        public String getCokey(){
            return c_cokey;
        }
        
        
    }
    
    private static class NodeComparator implements Comparator <TreeNode>{
        public int compare(TreeNode o1, TreeNode o2) {
            return o1.toString().compareTo(o2.toString());
        }
        
    }
    
}