package com.approximatrix.draco.io.sql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.approximatrix.draco.datamodel.Constant;
import com.approximatrix.draco.datamodel.DateObject;
import com.approximatrix.draco.datamodel.ParseDate;
import com.approximatrix.draco.datamodel.StatTableModel;
import com.approximatrix.draco.general.ui.InfoWindow;
import com.approximatrix.draco.io.XMLio.AnalysisLoadStore;
import com.approximatrix.draco.io.builders.AnalysisBuilder;
import com.approximatrix.draco.io.builders.PlotBuilder;
import com.approximatrix.draco.io.builders.plot.AxisStore;
import com.approximatrix.draco.io.builders.plot.SeriesStore;
import com.approximatrix.draco.main.ui.MainWindow;

public class DracoSQLRead extends DracoSQL {
	
	public DracoSQLRead(String filename) {
		super(filename);		
	}
	
	public StatTableModel loadModel() {
		StatTableModel model = new StatTableModel(true);
		try {
			
			Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                    							  ResultSet.CONCUR_READ_ONLY);
			ResultSet srs = stmt.executeQuery("SELECT * FROM "+DracoSQLTables.VARIABLES+";");
			
			while (srs.next()) {
		        String name = srs.getString(DracoSQLTables.VAR_NAME);
		        String desc = srs.getString(DracoSQLTables.VAR_DESC);
		        String type = srs.getString(DracoSQLTables.VAR_TYPE);
		        
		        DateObject base = null;
		        // DateObjects require special processing
		        if(type.equals(DateObject.class.getCanonicalName())) {
		        	
		        	String unit = srs.getString(DracoSQLTables.VAR_UNIT);
		        	String b = srs.getString(DracoSQLTables.VAR_BASE);
		        	
		        	base = ParseDate.ParseTextToDate(b.replace(':', '/'), DateObject.getTypeInt(unit));
		    	}
		        
		        // Retrieve data
		        Statement stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                        							  ResultSet.CONCUR_READ_ONLY);
		    	ResultSet datars = stat.executeQuery("SELECT "+name+" FROM "+DracoSQLTables.DATA+";");
			    
		    	ArrayList<Object> data = new ArrayList<Object>();
		    	Class toCreate = Class.forName(type);
		    	
			    while(datars.next()) {
			    	String val = datars.getString(name);
			    	if(val == null)
			    		data.add(null);
			    	
			    	// DateObject processing
			    	else if(base != null) {
			    		try {
			    			data.add(new DateObject(base,new Integer(val).intValue()));
			    		} catch(NumberFormatException nfe) {
			    			data.add(null);
			    		} catch(NullPointerException npe) {
			    			data.add(null);
			    		}
			    		
			    	} else 
			    		data.add(toCreate.getConstructor(String.class).newInstance(val));
			    	
			    }
                model.addColumn(data.toArray(), name);
                model.setDescription(name, desc);
			}
				
			
		} catch (SQLException se) {
			return model;
		} finally {
			return model;
		}
	}
	
	public boolean loadConstants(StatTableModel model) {
		try {
			
			ResultSet srs = retrieveFromTable(DracoSQLTables.PARAMETERS,DracoSQLTables.PARAM_OWNER+"="+Integer.toString(DracoSQLTables.OWNER_CONSTANTS));

			while (srs.next()) {
				
				String name = srs.getString(DracoSQLTables.PARAM_NAME);
				Double val = srs.getDouble(DracoSQLTables.PARAM_VALUE);
				
				if(val != null)
					model.getConstants().add(new Constant(name,val));
			}
				
			
		} catch (SQLException se) {
			return false;
		} finally {
			return true;
		}
	}
	
	public boolean loadTexts(MainWindow parent) {
		try {
			
			ResultSet srs = retrieveFromTable(DracoSQLTables.TEXT);

			while (srs.next()) {
				
				String name = srs.getString(DracoSQLTables.TEXT_NAME);
				String content = srs.getString(DracoSQLTables.TEXT_CONTENT);
				boolean editable = srs.getInt(DracoSQLTables.TEXT_CONTENT) == DracoSQLTables.FLAG_TRUE;
				String type = srs.getString(DracoSQLTables.TEXT_CONTENTTYPE);
				
				parent.openWindow(new InfoWindow(name, content, editable, type)).setIcon(true);
			}
			
		} catch (SQLException se) {
			return false;
		} finally {
			return true;
		}
	}
	
	public boolean loadAnalyses(MainWindow parent, StatTableModel model) {
		try {
			
			ResultSet srs = retrieveFromTable(DracoSQLTables.ANALYSIS);

			while (srs.next()) {
				
				String name = srs.getString(DracoSQLTables.AN_NAME);
				String results = srs.getString(DracoSQLTables.AN_RESULTS);
				String type = srs.getString(DracoSQLTables.AN_TYPE);
				
				int key = srs.getInt(DracoSQLTables.AN_KEY);
				
				AnalysisLoadStore loader = new AnalysisLoadStore(type);
				loader.setName(name);
				loader.setResults(results);
				
				ResultSet drs = retrieveFromTable(DracoSQLTables.PARAMETERS, 
						                          DracoSQLTables.PARAM_OWNER+"="+Integer.toString(key));
				ArrayList<String> vars = new ArrayList<String>();
				while(drs.next()) {
					switch(drs.getInt(DracoSQLTables.PARAM_TYPE)) {
						case DracoSQLTables.TYPE_INDEPENDENT:
							vars.add(drs.getString(DracoSQLTables.PARAM_NAME));
							break;
					}
				}
				loader.setIndependentVariables(vars.toArray(new String[0]));
				
				AnalysisBuilder.BuildAnalysis(loader, parent, model);
			}
			
		} catch (SQLException se) {
			return false;
		} finally {
			return true;
		}
	}
	
	public boolean loadRegressions(MainWindow parent, StatTableModel model) {
		try {
			
			ResultSet srs = retrieveFromTable(DracoSQLTables.REGRESSION);

			while (srs.next()) {
				
				String name = srs.getString(DracoSQLTables.REGR_NAME);
				String results = srs.getString(DracoSQLTables.REGR_RESULTS);
				String type = srs.getString(DracoSQLTables.REGR_TYPE);
				
				String eqn = srs.getString(DracoSQLTables.REGR_EQUATION);
				String subtype = srs.getString(DracoSQLTables.REGR_SUBTYPE);
				
				int key = srs.getInt(DracoSQLTables.REGR_KEY);
				
				AnalysisLoadStore loader = new AnalysisLoadStore(type);
				loader.setName(name);
				loader.setResults(results);
				loader.setEquation(eqn);
				loader.setAnalysisSubType(subtype);
				
				ResultSet drs = retrieveFromTable(DracoSQLTables.PARAMETERS, 
						                          DracoSQLTables.PARAM_OWNER+"="+Integer.toString(key));
				ArrayList<String> indepvars = new ArrayList<String>();
				ArrayList<String> instrvars = new ArrayList<String>();
				ArrayList<String> cnames = new ArrayList<String>();
				ArrayList<Double> cvals = new ArrayList<Double>();
				String depvar = null;
				while(drs.next()) {
					switch(drs.getInt(DracoSQLTables.PARAM_TYPE)) {
						case DracoSQLTables.TYPE_INDEPENDENT:
							indepvars.add(drs.getString(DracoSQLTables.PARAM_NAME));
							break;
						case DracoSQLTables.TYPE_DEPENDENT:
							depvar = drs.getString(DracoSQLTables.PARAM_NAME);
							break;
						case DracoSQLTables.TYPE_INSTRUMENT:
							instrvars.add(drs.getString(DracoSQLTables.PARAM_NAME));
							break;	
						case DracoSQLTables.TYPE_ESTIMATE:
							cnames.add(drs.getString(DracoSQLTables.PARAM_NAME));
							cvals.add(new Double(drs.getDouble(DracoSQLTables.PARAM_VALUE)));
							break;
					}
				}
				loader.setIndependentVariables(indepvars.toArray(new String[0]));
				loader.setDependentVariable(depvar);
				if(instrvars.size() > 0)
					loader.setInstruments(instrvars.toArray(new String[0]));
				loader.setCoefLabels(cnames.toArray(new String[0]));
				loader.setCoefValues(cvals.toArray(new Double[0]));
				
				// Residuals
				drs = retrieveFromTable(DracoSQLTables.ANALYSISDATA, 
										DracoSQLTables.AD_OWNER+"="+Integer.toString(key)+
										" AND "+DracoSQLTables.AD_TYPE+"="+DracoSQLTables.TYPE_RESIDUAL);
				HashMap<Integer, Double> residuals = new HashMap<Integer, Double>();
				while(drs.next()) {
					residuals.put(new Integer(drs.getInt(DracoSQLTables.AD_POSITION)),new Double(drs.getDouble(DracoSQLTables.AD_VALUE)));
				}
				loader.setResiduals(getDoubleArray(residuals));				
				
				// Covariance Matrix
				drs = retrieveFromTable(DracoSQLTables.MATRIXDATA, 
						DracoSQLTables.MD_OWNER+"="+Integer.toString(key)+
						" AND "+DracoSQLTables.MD_TYPE+"="+DracoSQLTables.TYPE_COVARIANCE);
				HashMap<int[], Double> cov = new HashMap<int[], Double>();
				int[] maxes = new int[2]; maxes[0] = 0; maxes[1] = 0;
				while(drs.next()) {
					int[] pos = new int[2];
					pos[0] = drs.getInt(DracoSQLTables.MD_X);
					pos[1] = drs.getInt(DracoSQLTables.MD_Y);
					maxes[0] = Math.max(pos[0], maxes[0]);
					maxes[1] = Math.max(pos[1], maxes[1]);
					cov.put(pos,new Double(drs.getDouble(DracoSQLTables.AD_VALUE)));	
				}
				loader.setCovarianceMatrix(getDoubleArray(cov,maxes));
				
				AnalysisBuilder.BuildAnalysis(loader, parent, model);
			}
			
		} catch (SQLException se) {
			return false;
		} finally {
			return true;
		}
	}

	public boolean loadPlots(MainWindow parent) {
		try {
			
			ResultSet srs = retrieveFromTable(DracoSQLTables.PLOT);

			while (srs.next()) {
				
				String name = srs.getString(DracoSQLTables.PLOT_NAME);
				String title = srs.getString(DracoSQLTables.PLOT_TITLE);
				String type = srs.getString(DracoSQLTables.PLOT_TYPE);
				
				int key = srs.getInt(DracoSQLTables.PLOT_KEY);
				
				// Cycle through axes
				ResultSet ars = retrieveFromTable(DracoSQLTables.PLOTAXISDATA,
												  DracoSQLTables.PAD_OWNER+"="+Integer.toString(key));
				ArrayList<AxisStore> axes = new ArrayList<AxisStore>();
				while(ars.next()) {
					AxisStore as = new AxisStore();
					as.axis = ars.getInt(DracoSQLTables.PAD_TYPE);
					as.scaletype = ars.getInt(DracoSQLTables.PAD_SCALETYPE);
					as.scaleam = ars.getInt(DracoSQLTables.PAD_SCALEMODE);
					as.grid = ars.getInt(DracoSQLTables.PAD_GRID) == DracoSQLTables.FLAG_TRUE;
					as.label = ars.getString(DracoSQLTables.PAD_LABEL);
					axes.add(as);
				}
				
				// Now cycle through series
				ResultSet serrs = retrieveFromTable(DracoSQLTables.PLOTSERIES,
													DracoSQLTables.PS_OWNER+"="+Integer.toString(key));
				ArrayList<SeriesStore> series = new ArrayList<SeriesStore>();
				while(serrs.next()) {
					String serName = serrs.getString(DracoSQLTables.PS_NAME);
					int xkey = serrs.getInt(DracoSQLTables.PS_XDATA);
					int ykey = serrs.getInt(DracoSQLTables.PS_YDATA);
					
					SeriesStore ss = retrieveSeries(xkey,ykey);
					ss.name = serName;
					
					ss.line = serrs.getInt(DracoSQLTables.PS_LINE) == DracoSQLTables.FLAG_TRUE;
					ss.marker = serrs.getInt(DracoSQLTables.PS_MARKER) == DracoSQLTables.FLAG_TRUE;
					ss.color = serrs.getInt(DracoSQLTables.PS_COLOR);
					
					series.add(ss);
				}
				
				PlotBuilder.BuildPlot(name,title,type,axes,series,parent);
			}
			
		} catch (SQLException se) {
			return false;
		} finally {
			return true;
		}
	}

	
	@Override
	public String getError() {
		return null;
	}
	
	private ResultSet retrieveFromTable(String table) throws SQLException {
		return retrieveFromTable(table,null,null);
	}
	
	private ResultSet retrieveFromTable(String table, String whereClause) throws SQLException {
		return retrieveFromTable(table,null,whereClause);
	}
	
	private ResultSet retrieveFromTable(String table, String cols, String whereClause) throws SQLException {
		if(table == null) return null;
		
		StringBuffer query = new StringBuffer("SELECT ");
		if(cols == null) 
			query.append("*");
		else
			query.append(cols);
		
		query.append(" FROM ");
		query.append(table);
		
		if(whereClause != null) {
			query.append(" WHERE ");
			query.append(whereClause);
		}
			
		query.append(';');
		
		Statement stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
				  							  ResultSet.CONCUR_READ_ONLY);
		
		return stat.executeQuery(query.toString());
	}
	
	private double[] getDoubleArray(Map<Integer, Double> values) {
		double[] ret = new double[values.size()];
		Set<Integer> keys = values.keySet();
		for(Integer key : keys) {
			try {
				ret[key.intValue()] = values.get(key).doubleValue();
			} catch(Exception e) {
				// continue
			}
		}
		return ret;
	}
	
	private double[][] getDoubleArray(Map<int[], Double> values, int[] max) {
		double[][] ret = new double[max[0]+1][max[1]+1];
		Set<int[]> keys = values.keySet();
		for(int[] key : keys) {
			try {
				ret[key[0]][key[1]] = values.get(key).doubleValue();
			} catch(Exception e) {
				// continue
			}
		}
		return ret;
	}
	
	private SeriesStore retrieveSeries(int xkey, int ykey) throws SQLException {
		SeriesStore ss = new SeriesStore();
		
		ss.xdata = retrieveSeriesColumn(xkey);
		ss.ydata = retrieveSeriesColumn(ykey);
		
		return ss;
	}
	
	private String[] retrieveSeriesColumn(int key) throws SQLException {

		ResultSet rs = retrieveFromTable(DracoSQLTables.PLOTDATA,
										 DracoSQLTables.PD_OWNER+"="+Integer.toString(key));
		HashMap<Integer, String> map = new HashMap<Integer, String>(); 
		while(rs.next()) {
			Integer pos = new Integer(rs.getInt(DracoSQLTables.PD_POSITION));
			map.put(pos, rs.getString(DracoSQLTables.PD_VALUE));
		}
		
		String[] ret = new String[map.keySet().size()];
		
		for(Integer x : map.keySet()) {
			try {
				ret[x.intValue()] = map.get(x);
			} catch(ArrayIndexOutOfBoundsException aioob) {
				// nothing...
			}
		}
		
		return ret;
	}
}
