/*
 * MdbSoilDatabase.java
 *
 * Created on April 20, 2007, 2:43 PM
 *
 */
package usda.weru.soil;

import de.schlichtherle.truezip.file.TFile;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.Arrays;
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 TFile c_file;

    /**
     *
     */
    protected Comparator<? super MutableTreeNode> 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();
    }

    /**
     *
     * @param name
     */
    public JdbcSoilDatabase(String name) {
        this();
        c_name = name;
    }

    @Override
    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) {
                int code = warn.getErrorCode();
                switch (code) {
                    case 0:
                        warn = warn.getNextWarning();
                        continue;
                    case 1: //Readonly
                        warn = warn.getNextWarning();
                        continue;
                    case 63: //Can't read a registry setting
                        warn = warn.getNextWarning();
                        continue;
                    default:
                }
                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>();
        temp.addAll(Arrays.asList(columns));
        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
    /**
     *
     * @param node
     * @return
     */
    @Override
    public boolean isSoil(TreeNode node) {
        if (node != null && node instanceof ComponentNode) {
            ComponentNode n = (ComponentNode) node;
            return c_rootNode.isNodeDescendant(n);
        }
        return false;
    }

    /**
     *
     * @param node
     * @param progress
     * @return
     */
    @Override
    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 synchronized IFC getIfcFromKnownNode(TreeNode node, ProgressMonitor progress) {
        
        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;
    }
    
    /**
     *
     */
    @Override
    public void dispose() {
        if (c_con != null) {
            try {
                c_con.close();
            } catch (SQLException ex) {

            }
        }
    }

    /**
     *
     * @return
     */
    protected abstract Connection createConnection();

    class DatabaseNode extends AbstractLazyLoadingTreeNode {

        private static final long serialVersionUID = 1L;

        private boolean c_beenNotified;

        public DatabaseNode() {
            if (c_name != null) {
                setUserObject(c_name);
            }
        }

        @Override
        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 (SQLException e) {
                c_beenNotified = false;
                return null;
            }

        }

    }

    class LegendNode extends AbstractLazyLoadingTreeNode {

        private static final long serialVersionUID = 1L;

        private boolean c_beenNotified;
        private final String c_areasymbol;
        private final String c_areaname;
        private final 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);
        }

        @Override
        public boolean isLeaf() {
            if (!c_beenNotified) {
                return false;
            } else {
                return super.isLeaf();
            }
        }

        @Override
        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 (SQLException e) {
                c_beenNotified = false;
                return null;
            }

        }
        
        public JdbcSoilDatabase getDatabase() { return JdbcSoilDatabase.this; }

    }

    class MapUnitNode extends AbstractLazyLoadingTreeNode {

        private static final long serialVersionUID = 1L;

        private boolean c_beenNotified;
        private final String c_musym;
        private final String c_muname;
        private final String c_mukey;
        private final 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);
        }

        @Override
        public boolean isLeaf() {
            if (!c_beenNotified) {
                return false;
            } else {
                return super.isLeaf();
            }
        }

        @Override
        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 (SQLException e) {
                c_beenNotified = false;
                return null;
            }

        }
        public JdbcSoilDatabase getDatabase() { return JdbcSoilDatabase.this; }

    }

    class ComponentNode extends DefaultMutableTreeNode {

        private static final long serialVersionUID = 1L;

        private final String c_cokey;
        private final String c_mukey;
        private final String c_localphase;
        private final String c_compname;
        private final 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);
            }
        }

        @Override
        public boolean isLeaf() {
            return true;
        }

        public String getCokey() {
            return c_cokey;
        }
        public JdbcSoilDatabase getDatabase() { return JdbcSoilDatabase.this; }

    }

    private static class NodeComparator implements Comparator<TreeNode> {

        @Override
        public int compare(TreeNode o1, TreeNode o2) {
            return o1.toString().compareTo(o2.toString());
        }

    }

}