/*
 *  Draco Econometrics
 *  Copyright (C) 2008-2009 Approximatrix, LLC
 *  http://www.approximatrix.com
 *  
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package com.approximatrix.draco.io.sql;

import java.awt.Color;
import java.sql.*;
import java.util.Iterator;
import java.util.List;

import com.approximatrix.draco.application.plugin.PluginData;
import com.approximatrix.draco.application.plugin.PluginType;
import com.approximatrix.draco.application.plugin.ui.PluginDataProvidingWindow;
import com.approximatrix.draco.application.plugin.ui.PluginWindow;
import com.approximatrix.charting.model.ChartDataModel;
import com.approximatrix.charting.model.MultiScatterDataModel;
import com.approximatrix.charting.render.AbstractChartRenderer;
import com.approximatrix.charting.render.RowColorModel;
import com.approximatrix.charting.swing.AbstractChartPanel;
import com.approximatrix.draco.analysis.ui.AnalysisChildDialog;
import com.approximatrix.draco.datamodel.Constant;
import com.approximatrix.draco.datamodel.DateObject;
import com.approximatrix.draco.datamodel.StatTableModel;
import com.approximatrix.draco.editor.ui.RubyEditorPanel;
import com.approximatrix.draco.editor.ui.RubyEditorWindow;
import com.approximatrix.draco.fit.RunNLLS;
import com.approximatrix.draco.fit.RunTSLS;
import com.approximatrix.draco.fit.SimpleMLEFit;
import com.approximatrix.draco.fit.ui.FitChildDialog;
import com.approximatrix.draco.general.ui.InfoWindow;
import com.approximatrix.draco.plot.ui.PlotDialog;

public class DracoSQLWrite extends DracoSQL {
	
	private int keyCount = -1;
	
	public DracoSQLWrite(String filename) {
		super(filename);
		
		initialize();
	}
	
	private boolean initialize() {
		boolean ret = true;
		
		if(conn == null) return false;
		
		ret = ret && createParametersTable();
		ret = ret && createAnalysisDataTable();
		ret = ret && createMatrixDataTable();
		ret = ret && createPlotAxisDataTable();
		ret = ret && createPlotSeriesTable();
		ret = ret && createPlotDataTable();
		ret = ret && createRegressionTable();
		ret = ret && createAnalysisTable();
		ret = ret && createPluginTables();
		
		return ret;
	}
	
	private boolean createPluginTables() {
	    try {
	        dropTable(DracoSQLTables.PLUGIN);
	        dropTable(DracoSQLTables.REGRESSION_PLUGIN);
	        dropTable(DracoSQLTables.ANALYSIS_PLUGIN);
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
		    StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PLUGIN);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PI_KEY);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PI_NAME);
			statPrepare.append(" text not null, ");

			statPrepare.append(DracoSQLTables.PI_CLASS);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.PI_TYPE);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.PI_VERSION);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PI_ORIGIN);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.PI_DATA);
			statPrepare.append(" text);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}

		try {
		    StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.REGRESSION_PLUGIN);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.REGRPI_REGRESSION);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.REGRPI_PLUGIN);
			statPrepare.append(" integer not null);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}

		try {
		    StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.ANALYSIS_PLUGIN);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.ANPI_ANALYSIS);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.ANPI_PLUGIN);
			statPrepare.append(" integer not null);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		
		return true;
	}
			

	private boolean createAnalysisDataTable() {
		try {
			dropTable(DracoSQLTables.ANALYSISDATA);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.ANALYSISDATA);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.AD_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.AD_POSITION);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.AD_TYPE);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.AD_VALUE);
			statPrepare.append(" real);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}
	
	private boolean createPlotSeriesTable() {
		try {
			dropTable(DracoSQLTables.PLOTSERIES);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PLOTSERIES);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PS_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.PS_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PS_XDATA);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PS_YDATA);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PS_COLOR);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PS_MARKER);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PS_LINE);
			statPrepare.append(" integer);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}	
	
	private boolean createPlotDataTable() {
		try {
			dropTable(DracoSQLTables.PLOTDATA);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PLOTDATA);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PD_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PD_POSITION);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PD_VALUE);
			statPrepare.append(" text); ");
					
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}
	
	private boolean createParametersTable() {
		try {
			dropTable(DracoSQLTables.PARAMETERS);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PARAMETERS);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PARAM_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.PARAM_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PARAM_TYPE);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PARAM_VALUE);
			statPrepare.append(" real);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}
	
	private boolean createPlotAxisDataTable() {
		try {
			dropTable(DracoSQLTables.PLOTAXISDATA);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PLOTAXISDATA);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PAD_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_LABEL);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.PAD_TYPE);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_GRID);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_SCALETYPE);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_SCALEMODE);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_SCALEMIN);
			statPrepare.append(" real, ");
			
			statPrepare.append(DracoSQLTables.PAD_SCALEMAX);
			statPrepare.append(" real);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		
		return true;
	}
	
	private boolean createMatrixDataTable() {
		try {
			dropTable(DracoSQLTables.MATRIXDATA);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.MATRIXDATA);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.MD_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.MD_X);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.MD_Y);
			statPrepare.append(" integer, ");

			statPrepare.append(DracoSQLTables.MD_TYPE);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.MD_VALUE);
			statPrepare.append(" real);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}

	private boolean createRegressionTable() {
		if(conn == null) return false;
		
		try {
			dropTable(DracoSQLTables.REGRESSION);
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		}
		
		try {
			// Create the regression table
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.REGRESSION);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.REGR_KEY);
			statPrepare.append(" integer not null unique, ");
			
			statPrepare.append(DracoSQLTables.REGR_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.REGR_RESULTS);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.REGR_TYPE);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.REGR_EQUATION);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.REGR_SUBTYPE);
			statPrepare.append(" text);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in createRegression");
				se.printStackTrace();
			}
			return true;
		}
		
		
	}
	
	private boolean createAnalysisTable() {
		if(conn == null) return false;
		
		try {
			dropTable(DracoSQLTables.ANALYSIS);
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		}
		
		try {
			// Create the analysis table
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.ANALYSIS);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.AN_KEY);
			statPrepare.append(" integer not null unique, ");
			
			statPrepare.append(DracoSQLTables.AN_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.AN_RESULTS);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.AN_TYPE);
			statPrepare.append(" text not null);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in createAnalysisTable");
				se.printStackTrace();
			}
			return true;
		}
	}
	
	public boolean saveWorksheet(StatTableModel model) {
		if(conn == null) return false;
		
		Savepoint sp = setSavepoint();
		try {
			
			dropTable(DracoSQLTables.VARIABLES);
			dropTable(DracoSQLTables.DATA);
			
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			createAndFillVarTable(model);
			createAndFillDataTable(model);
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return true;
	}
	
	public boolean saveConstants(StatTableModel model) {
		
		try {
			
			for(Constant value : model.getConstants())
				insertParameterValue(DracoSQLTables.OWNER_CONSTANTS,value.getName(),DracoSQLTables.TYPE_CONSTANT,value.getValue());
			
			
		} catch(SQLException se) {
			System.err.println("Error writing constants");
			//se.printStackTrace();
		} finally {
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveConstants");
				se.printStackTrace();
			}
		}
		
		return true;
	}
	
	public boolean saveTexts(List<InfoWindow> textWindows, List<RubyEditorWindow> editorWindows) {
		if(conn == null) return false;
		
		try {
			dropTable(DracoSQLTables.TEXT);
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		if(textWindows.size() + editorWindows.size() == 0) return true;
		
		Savepoint sp = setSavepoint();
		
		try {

			// Create the texts table
			Statement stat = conn.createStatement();
			stat.execute("CREATE TABLE "+DracoSQLTables.TEXT+" ("+DracoSQLTables.TEXT_NAME+" text not null, "+DracoSQLTables.TEXT_CONTENT+" text, "+DracoSQLTables.TEXT_EDITABLE+" integer not null, "+DracoSQLTables.TEXT_CONTENTTYPE+" text);");
			
			// First, save all the text windows
			for(InfoWindow window : textWindows) {
				
				PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.TEXT+" VALUES(?, ?, ?, ?);");
				
				pstat.setString(1, window.getTitle());
				if(window.getText() == null)
					pstat.setNull(2, Types.VARCHAR);
				else
					pstat.setString(2, window.getText());
				
				if(window.isEditable())
					pstat.setInt(3, DracoSQLTables.FLAG_TRUE);
				else
					pstat.setInt(3, DracoSQLTables.FLAG_FALSE);
				
				pstat.setString(4,window.getContentType());
				
				pstat.execute();
			}
			
			// Next, write out appropriate editor tabs
			// There really should only be one editor window, but
			// we'll leave ope the possibility of more...
			for(RubyEditorWindow window : editorWindows) {
				for(RubyEditorPanel panel : window.getEditorPanels()) {
					if(!panel.isSavedWithWorksheet())
						continue;
					
					PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.TEXT+" VALUES(?, ?, ?, ?);");
					
					pstat.setString(1, panel.getName());
					if(panel.getText() == null)
						pstat.setNull(2, Types.VARCHAR);
					else
						pstat.setString(2, panel.getText());
					
					pstat.setInt(3, DracoSQLTables.FLAG_TRUE);
					
					pstat.setString(4,panel.getMimeType());
					
					pstat.execute();
				}
			}
			
			
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return true;
	}
	
	public boolean savePlugins(Object[] pWindows) {
		if(conn == null) return false;
		if(pWindows == null) return true;
		if(pWindows.length == 0) return true;
		
		boolean ret = true;
	    for(int i=0;i<pWindows.length && ret;i++) {
            Savepoint sp = setSavepoint();
	        try {
	    
    	        if(!(pWindows[i] instanceof PluginDataProvidingWindow))
	                continue;
	            PluginData pi = ((PluginDataProvidingWindow)pWindows[i]).getPluginData();
	            
	            // Unexecuted plugins should not be saved.
	            if(pi.getResults() == null)
	            	continue;
	       
	            int key = getKey();
	       
           	    PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PLUGIN+" VALUES(?, ?, ?, ?, ?, ?, ?);");
	            
	            pstat.setInt(1,key);
	            pstat.setString(2,pi.getScriptClass());
	            pstat.setString(3,pi.getName());
	            
	            pstat.setString(4,pi.getType().toString());
	            
	            pstat.setInt(5, pi.getVersion());
	            
	            if(pi.getOrigin() == null)
	            	pstat.setNull(6,Types.VARCHAR);
	            else
	            	pstat.setString(6,pi.getOrigin());
	            
	            pstat.setNull(7,Types.VARCHAR); // For now...
	                
	            pstat.execute();
	            
                if(pi.getType() == PluginType.regression)
                    ret = saveRegressionPlugin(pi, ((PluginDataProvidingWindow)pWindows[i]).getTitle(), key);
                else if(pi.getType() == PluginType.analysis)
                	ret = saveAnalysisPlugin(pi, ((PluginDataProvidingWindow)pWindows[i]).getTitle(), key);
                    
            } catch(SQLException se) {
	      		rollback(sp);
                System.err.println("Error writing db");
			    se.printStackTrace();
	        } finally {
	            releaseSavepoint(sp);
    			try {
	    			if(!conn.getAutoCommit())
		    			conn.commit();
			    } catch(SQLException se) {
				    System.err.println("Commit failed in savePlugin");
				    se.printStackTrace();
			    }
	        }
	    }
	
	    return ret;
	}
	
	public boolean saveRegressions(Object[] aWindows) {
		if(conn == null) return false;
		
		if(aWindows == null) return true;
		if(aWindows.length == 0) return true;
		
		Savepoint sp = setSavepoint();
		
		try {
			
			for(int i=0;i<aWindows.length;i++) {
				if(!(aWindows[i] instanceof FitChildDialog)) continue;
				
				FitChildDialog window = (FitChildDialog)aWindows[i];
				int key = getKey();
				
				PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.REGRESSION+" VALUES(?, ?, ?, ?, ?, ?);");
				
				// Key
				pstat.setInt(1,key);
				
				// Name
				pstat.setString(2,window.getTitle());
				
				// Results
				if(window.getAnalysis().getResults() == null)
					pstat.setNull(3,Types.VARCHAR);
				else
					pstat.setString(3,window.getAnalysis().getResults());
				
				// Type
				String type = window.getAnalysis().getClass().getName();
				pstat.setString(4,type);
				
				// Equation
				if(window.getAnalysis() instanceof RunNLLS)
					pstat.setString(5,((RunNLLS)window.getAnalysis()).getEquation());
				else
					pstat.setNull(5, Types.VARCHAR);
				
				// Subtype
				if(window.getAnalysis() instanceof SimpleMLEFit)
					pstat.setString(6,((SimpleMLEFit)window.getAnalysis()).getFunction().getClass().getName());
				else
					pstat.setNull(6, Types.VARCHAR);				
				
				pstat.execute();
				
				// Independent variables
				String[] indep = window.getAnalysis().getIndependentVariables();
				String dep = window.getAnalysis().getDependentVariable();
				String[] instr = null;
				if(window.getAnalysis() instanceof RunTSLS)
					instr = ((RunTSLS)window.getAnalysis()).getInstruments();
				if(indep != null) {
					for(int j=0;j<indep.length;j++)
						insertParameter(key,indep[j],DracoSQLTables.TYPE_INDEPENDENT);
				}
				insertParameter(key,dep,DracoSQLTables.TYPE_DEPENDENT);
				if(instr != null) {
					for(int j=0;j<instr.length;j++)
						insertParameter(key,instr[j],DracoSQLTables.TYPE_INSTRUMENT);
				}
				
				// Now save estimates and covariance
				for(int j=0;j<window.getAnalysis().getCoefLabels().length;j++) {
					insertParameterValue(key,window.getAnalysis().getCoefLabels()[j],DracoSQLTables.TYPE_ESTIMATE,window.getAnalysis().getCoef()[j]);
				}
				
				double[][] covariance = window.getAnalysis().getCovarianceMatrix();
				if(covariance != null) {
					for(int j=0;j<covariance.length;j++) {
						for(int k=0;k<covariance[j].length;k++) {
							insertMatrixValue(key,j,k,DracoSQLTables.TYPE_COVARIANCE,covariance[j][k]);
						}
					}
				}
				
			}
			
			
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return true;
	}
	
	public boolean saveAnalyses(Object[] aWindows) {
		if(conn == null) return false;
		
		if(aWindows == null) return true;
		if(aWindows.length == 0) return true;
		
		Savepoint sp = setSavepoint();
		
		try {
			
			for(int i=0;i<aWindows.length;i++) {
				if(!(aWindows[i] instanceof AnalysisChildDialog)) continue;
				
				AnalysisChildDialog window = (AnalysisChildDialog)aWindows[i];
				int key = getKey();
				
				PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.ANALYSIS+" VALUES(?, ?, ?, ?);");
				
				// Key
				pstat.setInt(1,key);
				
				// Name
				pstat.setString(2,window.getTitle());
				
				// Results
				if(window.getAnalysis().getResults() == null)
					pstat.setNull(3, Types.VARCHAR);
				else
					pstat.setString(3,window.getAnalysis().getResults());
				
				// Type
				String type = window.getAnalysis().getClass().getName();
				pstat.setString(4,type);
				pstat.execute();
				
				// Now save estimates
				for(int j=0;j<window.getAnalysis().getVariables().length;j++) {
					insertParameter(key,window.getAnalysis().getVariables()[j],DracoSQLTables.TYPE_INDEPENDENT);
				}
				
			}
			
			
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return true;
	}
	
	public boolean savePlots(Object[] aWindows) {
		if(conn == null) return false;
		
		try {
			dropTable(DracoSQLTables.PLOT);
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		}
		
		if(aWindows == null) return true;
		if(aWindows.length == 0) return true;
		
		boolean ret = true;
		
		Savepoint sp = setSavepoint(); 
		
		try {

			// Create the regression table
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PLOT);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PLOT_KEY);
			statPrepare.append(" integer not null unique, ");
			
			statPrepare.append(DracoSQLTables.PLOT_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.PLOT_TITLE);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.PLOT_TYPE);
			statPrepare.append(" text not null);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
			for(int i=0;i<aWindows.length;i++) {
				if(!(aWindows[i] instanceof PlotDialog)) continue;
				
				PlotDialog window = (PlotDialog)aWindows[i];
				int key = getKey();
				
				PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PLOT+" VALUES(?, ?, ?, ?);");
				
				AbstractChartPanel panel = window.getPlotter().getPlotPanel();
				
				// Key
				pstat.setInt(1,key);
				
				// Name
				pstat.setString(2,window.getTitle());
				
				// Results
				if(panel.getTitle() == null)
					pstat.setNull(3, Types.VARCHAR);
				else
					pstat.setString(3,panel.getTitle().getText());
				
				// Type
				String type = null;
			    RowColorModel rcm = null;
				for(Iterator<AbstractChartRenderer>iter = panel.getChartRenderer().values().iterator();iter.hasNext();) {
				    AbstractChartRenderer r = iter.next();
		        	type = r.getClass().getName();
		        	if(rcm == null) rcm = r.getRowColorModel();
		        }
				if(type == null) {
					ret = false;
					continue;
				} else
		        	pstat.setString(4,type);
				
				pstat.execute();
				
				int scaling = DracoSQLTables.TYPE_AXIS_SCALE_NONE;
				if(panel.getChartDataModel().isAutoScale())
					scaling = DracoSQLTables.TYPE_AXIS_SCALE_AUTO;
				else if(panel.getChartDataModel().isManualScale())
					scaling = DracoSQLTables.TYPE_AXIS_SCALE_MANUAL;
				
				// Save the axes
				insertPlotAxis(key, panel.getCoordSystem().getXAxisUnit(), 
						DracoSQLTables.TYPE_AXIS_X, 
						panel.getCoordSystem().getPaintGrid() ? DracoSQLTables.FLAG_TRUE : DracoSQLTables.FLAG_FALSE, 
						DracoSQLTables.TYPE_AXIS_LINEAR, 
						scaling, 
						panel.getChartDataModel().getManualMinimumX(), panel.getChartDataModel().getManualMaximumX());
				
				insertPlotAxis(key, panel.getCoordSystem().getYAxisUnit(),
						DracoSQLTables.TYPE_AXIS_Y, 
						panel.getCoordSystem().getPaintGrid() ? DracoSQLTables.FLAG_TRUE : DracoSQLTables.FLAG_FALSE, 
						DracoSQLTables.TYPE_AXIS_LINEAR, 
						scaling,
						panel.getChartDataModel().getManualMinimumY(), panel.getChartDataModel().getManualMaximumY());
				
				// Now save plot data
				ChartDataModel model = panel.getChartDataModel();
				for(int j=0;j<model.getDataSetNumber();j++) {
					insertSeries(key,model,rcm.getColor(j),j);
				}
			}
			
			
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return ret;
	}

    private boolean saveRegressionPlugin(PluginData pi, String window, int pluginkey) throws SQLException {
    
		int key = getKey();
		
	    PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.REGRESSION_PLUGIN+" VALUES(?, ?);");
	    pstat.setInt(1,key);
	    pstat.setInt(2,pluginkey);
	    pstat.execute();
				
		pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.REGRESSION+" VALUES(?, ?, ?, ?, ?, ?);");
				
    	// Key
		pstat.setInt(1,key);
				
		// Name
		pstat.setString(2,window);
				
		// Results
		if(pi.getResults() == null)
	        pstat.setNull(3,Types.VARCHAR);
		else
			pstat.setString(3,pi.getResults());
				
		// Type
		pstat.setString(4,DracoSQLTables.TYPE_PLUGIN);

        // Unneeded
		pstat.setNull(5, Types.VARCHAR);
		pstat.setNull(6, Types.VARCHAR);				
				
		pstat.execute();
    
        return true;
    }
	
    private boolean saveAnalysisPlugin(PluginData pi, String window, int pluginkey) throws SQLException {
        
		int key = getKey();
		
	    PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.ANALYSIS_PLUGIN+" VALUES(?, ?);");
	    pstat.setInt(1,key);
	    pstat.setInt(2,pluginkey);
	    pstat.execute();
				
		pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.ANALYSIS+" VALUES(?, ?, ?, ?);");
				
    	// Key
		pstat.setInt(1,key);
				
		// Name
		pstat.setString(2,window);
				
		// Results
		if(pi.getResults() == null)
	        pstat.setNull(4,Types.VARCHAR);
		else
			pstat.setString(4,pi.getResults());
				
		// Type
		pstat.setString(3,DracoSQLTables.TYPE_PLUGIN);			
				
		pstat.execute();
    
        return true;
    }
    
	private boolean createAndFillVarTable(StatTableModel model) throws SQLException {
		boolean ret = true;
		
		// Create the variables table
		Statement stat = conn.createStatement();
		stat.execute("CREATE TABLE "+DracoSQLTables.VARIABLES+" ("+DracoSQLTables.VAR_NAME+" text not null unique, "+ 
				                                                   DracoSQLTables.VAR_DESC+" text, "+
				                                                   DracoSQLTables.VAR_TYPE+" text, "+
				                                                   DracoSQLTables.VAR_UNIT+" text, "+
				                                                   DracoSQLTables.VAR_BASE+" text, "+
				                                                   DracoSQLTables.VAR_FORMAT+" text);");
		
		// Fill the table
		for(int i=0;i<model.getColumnCount() && ret;i++) {

			PreparedStatement varstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.VARIABLES+" VALUES(?, ?, ?, ?, ?, ?);");
			varstat.setString(1, model.getColumnName(i));
			varstat.setString(3, model.getVariable(i).getContentsClass().getCanonicalName());
			
			if(model.getDescription(i) == null)
				varstat.setNull(2,Types.VARCHAR);
			else
				varstat.setString(2, model.getDescription(i));
			
			if(i == model.getDateColumn()) {
				varstat.setString(4, DateObject.getTypeString(model.getDateType()));
				varstat.setString(5, model.getDateBase().toString().replace('/', ':'));
			} else {
				varstat.setNull(4,Types.VARCHAR);
				varstat.setNull(5,Types.VARCHAR);
			}
			if(model.getVariable(i).getDecimalFormatting() == null)
				varstat.setNull(6,Types.VARCHAR);
			else
				varstat.setString(6, model.getVariable(i).getDecimalFormatting().toPattern());
			
			varstat.execute();
		}

		return ret;
	}
	
	
	
	private boolean createAndFillDataTable(StatTableModel model) throws SQLException {
		boolean ret = true;
		
		StringBuffer statementPrep = new StringBuffer("CREATE TABLE "+DracoSQLTables.DATA+" (");
		for(int i=0;i<model.getColumnCount() && ret;i++) {
			statementPrep.append(model.getColumnName(i));
			statementPrep.append(" text, ");
		}
		statementPrep.deleteCharAt(statementPrep.length()-1);
		statementPrep.deleteCharAt(statementPrep.length()-1);
		statementPrep.append(");");
		
		// Create the variables table
		Statement stat = conn.createStatement();
		stat.execute(statementPrep.toString());
		
		// Fill the table
		for(int i=0;i<model.getRowCount() && ret;i++) {
			
			StringBuffer insertStatement = new StringBuffer("INSERT INTO "+DracoSQLTables.DATA+" VALUES(");
			
			for(int j=0;j<model.getColumnCount();j++) {
				Object valueAt = model.getValueAt(i, j);
				if(valueAt != null) {
					
					// For dates, just save the integer representation of it.  We we can rebuild
					// this fine since the base is stored in the Variables table
					if(valueAt instanceof DateObject)
						insertStatement.append(Integer.toString(((DateObject)valueAt).intValue()));
					else 
						insertStatement.append(valueAt.toString().replace("/",":"));
				} else
					insertStatement.append("NULL");
				insertStatement.append(", ");
			}
			insertStatement.deleteCharAt(insertStatement.length()-1);
			insertStatement.deleteCharAt(insertStatement.length()-1);
			insertStatement.append(");");
			
			Statement varstat = conn.createStatement();
			varstat.execute(insertStatement.toString());
		}

		return ret;
	}
	
	private void rollback(Savepoint sp) {
		if(sp == null) return;
		try {
			conn.rollback(sp);
			conn.releaseSavepoint(sp);
		} catch(SQLException se) {
			System.err.println("Rollback failed");
			return;
		}
	}
	
	private void releaseSavepoint(Savepoint sp) {
		if(sp == null) return;
		try {
			conn.releaseSavepoint(sp);
		} catch(SQLException se) {
			System.err.println("Rollback failed");
			return;
		}
	}
	
	private Savepoint setSavepoint() {		
		try {
			return conn.setSavepoint();
		} catch(SQLException se) {
			System.err.println("Could not create a savepoint");
			return null;
		}
	}
	
	private boolean dropTable(String table) throws SQLException {
		Statement stat = conn.createStatement();
		return stat.execute("DROP TABLE "+table+";");
	}

	private int getKey() {
		if(this.keyCount < 0)
			keyCount = DracoSQLTables.KEY_START_COUNT;
		
		return ++this.keyCount;
	}
	
	private boolean insertParameterValue(int owner, String name, int type, double value) throws SQLException {
		return insertParameterValue(owner,name,type,new Double(value));
	}
	
	private boolean insertParameter(int owner, String name, int type) throws SQLException {
		return insertParameterValue(owner,name,type,null);
	}
	
	private boolean insertParameterValue(int owner, String name, int type, Number value) throws SQLException {
		
		PreparedStatement paramstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PARAMETERS+" VALUES(?, ?, ?, ?);");
		paramstat.setString(1,name);
		paramstat.setInt(2,owner);
		paramstat.setInt(3,type);
		if(value == null)
			paramstat.setNull(4, Types.VARCHAR);
		else
			paramstat.setDouble(4, value.doubleValue());
		paramstat.execute();
		
		return true;
	}
	
	private boolean insertMatrixValue(int owner, int x, int y, int type, double value) throws SQLException {
		
		PreparedStatement paramstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.MATRIXDATA+" VALUES(?, ?, ?, ?, ?);");
		paramstat.setInt(1, owner);
		paramstat.setInt(2, x);
		paramstat.setInt(3, y);
		paramstat.setInt(4, type);
		paramstat.setDouble(5, value);
		paramstat.execute();
		
		return true;
	}
	
	private boolean insertPlotDataValue(int owner, Object value, int pos) throws SQLException {
		PreparedStatement istat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PLOTDATA+" VALUES(?,?,?);");

		istat.setInt(1, owner);
		istat.setInt(2, pos);
		istat.setString(3, value.toString());

		istat.execute();
		return true;
	}
	
	private boolean insertSeries(int owner, ChartDataModel model, Color seriesColor, int series) throws SQLException {
		PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PLOTSERIES+" VALUES(?,?,?,?,?,?,?);");
		
		int xkey = getKey();
		int ykey = getKey();
		
		pstat.setString(1, model.getDataSetName(series));
		pstat.setInt(2, owner);
		pstat.setInt(3, xkey); 
		pstat.setInt(4, ykey);
		pstat.setInt(5, seriesColor.getRGB());
		if(model instanceof MultiScatterDataModel) {
			pstat.setInt(7,((MultiScatterDataModel)model).getSeriesLine(series) ? DracoSQLTables.FLAG_TRUE : DracoSQLTables.FLAG_FALSE);
			pstat.setInt(6,((MultiScatterDataModel)model).getSeriesMarker(series) ? DracoSQLTables.FLAG_TRUE : DracoSQLTables.FLAG_FALSE);
		} else {
			pstat.setInt(7,DracoSQLTables.FLAG_FALSE);
			pstat.setInt(6,DracoSQLTables.FLAG_FALSE);
		}
		
		pstat.execute();
		
		for(int i=0; i<model.getDataSetLength(series); i++) {
			insertPlotDataValue(xkey,model.getColumnValueAt(series, i), i);
			insertPlotDataValue(ykey,model.getValueAt(series, i), i);
		}
		
		return true;
	}

	private boolean insertPlotAxis(int owner, String label, int type, int grid, int scaletype, int scalemode, Number min, Number max) throws SQLException {
		PreparedStatement pastat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PLOTAXISDATA+" VALUES(?, ?, ?, ?, ?, ?, ?, ?);");
		
		pastat.setInt(1,owner);
		pastat.setString(2,label);
		pastat.setInt(3,type);
		pastat.setInt(4,grid);
		pastat.setInt(5, scaletype);
		pastat.setInt(6, scalemode);
		
		if(min == null)
			pastat.setNull(7,Types.REAL);
		else
			pastat.setDouble(7,min.doubleValue());
		
		if(max == null)
			pastat.setNull(8,Types.REAL);
		else
			pastat.setDouble(8,max.doubleValue());
		
		pastat.execute();
		return true;
	}

	@Override
	public String getError() {
		return null;
	}
	
}
