package edu.umass.nrc.warren.cavitynesting;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Modifier;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public abstract class CavityDBObject {
	
	public static <T extends CavityDBObject> Collection<T> loadAll(Class<T> cls, Statement stmt) throws SQLException { 
		LinkedList<T> keyed = new LinkedList<T>();
		
		try {
			Constructor<T> constructor = cls.getConstructor();
			Constructor<T> resultConstructor = cls.getConstructor(ResultSet.class);
			
			T template = constructor.newInstance();			
			String queryString = template.queryString();
			
			ResultSet rs = stmt.executeQuery(queryString);
			while(rs.next()) {
				keyed.add(resultConstructor.newInstance(rs));
			}
			rs.close();
			
		} catch (IllegalAccessException e) {
			throw new IllegalArgumentException(e);
		} catch (NoSuchMethodException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		} catch (InstantiationException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		} catch (InvocationTargetException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		}
		
		return keyed;
		
	}
	
	public static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	
	public static String asSQL(Object v) {
		if(v == null) { 
			return "NULL";
		} else if(v instanceof String) { 
			return String.format("'%s'", v.toString().replace(";", "\\;").replace("'", "\\'"));
		} else if(v instanceof Date) { 
			DateFormat df = dateFormat;
			return df.format((Date)v);
		} else { 
			return v.toString();
		}
	}
	
	public static boolean isSubclass(Class<?> c1, Class<?> c2) {
		return c2.isAssignableFrom(c1);
	}
	
	public CavityDBObject() {}
	
	public CavityDBObject(ResultSet rs) throws SQLException { 
		ResultSetMetaData data = rs.getMetaData();
		//Map<String,Field> fieldMap = createFieldNameMap();

		for(int i = 1; i <= data.getColumnCount(); i++) { 
			String columnName = data.getColumnName(i);
			try {
				Field f = getClass().getField(columnName);
				int mod = f.getModifiers();
				
				if(Modifier.isPublic(mod) && !Modifier.isStatic(mod)) { 
					Object value = rs.getObject(i);
					if(value != null) { 
						if(isSubclass(f.getType(), Date.class)) {  
							value = dateFormat.parse(value.toString());
						} else if(isSubclass(f.getType(), Double.class)) { 
							value = Double.parseDouble(value.toString());
						}
					} 
					f.set(this, value);
				}
				
			} catch (NoSuchFieldException e) {
				e.printStackTrace(System.err);
			} catch (IllegalAccessException e) {
				e.printStackTrace(System.err);
			} catch (ParseException e) {
				e.printStackTrace(System.err);
			}
		}
	}
	
	public Map<String,Field> createFieldNameMap() { 
		Map<String,Field> map = new TreeMap<String,Field>();
		
		for(Field f : getClass().getFields()) { 
			int mod = f.getModifiers();
			if(Modifier.isPublic(mod) && !Modifier.isStatic(mod)) { 
				String name = f.getName();
				String upper = name.toUpperCase(), lower = name.toLowerCase();
				if(map.containsKey(upper) || map.containsKey(lower)) { 
					throw new IllegalStateException(String.format("Field %s occurs twice, varying only by case.", name));
				}
				map.put(name, f);
				map.put(upper, f);
				map.put(lower, f);
			}
		}
		
		return map;
	}
	
	public boolean isAutoGenerated(String fieldName) { return false; }
	
	public String getTableName() { 
		return String.format("CAVITYDATA_%s", getClass().getSimpleName().toUpperCase());
	}
	
	public String saveString() { 
		throw new UnsupportedOperationException(String.format(
				"No saveString() method defined in class %s", 
				getClass().getName()));
	}
	
	public String insertString() { 
		String tableName = getTableName();
		StringBuilder fields = new StringBuilder();
		StringBuilder values = new StringBuilder();
		
		for(Field f : getClass().getFields()) { 
			int mod = f.getModifiers();
			if(!isAutoGenerated(f.getName()) && Modifier.isPublic(mod) && !Modifier.isStatic(mod)) { 
				try {
					Object value = f.get(this);
					String name = f.getName();
					
					if(fields.length() > 0) { 
						fields.append(", ");
						values.append(", ");
					}
					
					fields.append(name);
					values.append(asSQL(value));
					
				} catch (IllegalAccessException e) {
					e.printStackTrace(System.err);
				}
			}
		}
		
		String fieldString = fields.toString(), valueString = values.toString();
		return String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, fieldString, valueString);
	}	
	
	public <T extends CavityDBObject> Collection<T> loadByKey(String myFieldName, Class<T> cls, String otherFieldName, Statement stmt) throws SQLException { 
		LinkedList<T> keyed = new LinkedList<T>();
		
		try {
			Field myField = getClass().getField(myFieldName);
			Object myValue = myField.get(this);
			if(myValue == null) { throw new IllegalArgumentException(String.format("%s has null value", myFieldName)); }
			
			Field otherField = cls.getField(otherFieldName);
			Constructor<T> constructor = cls.getConstructor();
			Constructor<T> resultConstructor = cls.getConstructor(ResultSet.class);
			
			T template = constructor.newInstance();
			otherField.set(template, myValue);
			
			String queryString = template.queryString();
			ResultSet rs = stmt.executeQuery(queryString);
			while(rs.next()) {
				keyed.add(resultConstructor.newInstance(rs));
			}
			rs.close();
			
		} catch (NoSuchFieldException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		} catch (IllegalAccessException e) {
			throw new IllegalArgumentException(e);
		} catch (NoSuchMethodException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		} catch (InstantiationException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		} catch (InvocationTargetException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		}
		
		return keyed;
	}
	
	public <T extends CavityDBObject> T loadFirstByKey(String myFieldName, Class<T> cls, String otherFieldName, Statement stmt) throws SQLException {
		T keyedValue = null;
		try {
			Field myField = getClass().getField(myFieldName);
			Object myValue = myField.get(this);
			if(myValue == null) { throw new IllegalArgumentException(String.format("%s has null value", myFieldName)); }
			
			Field otherField = cls.getField(otherFieldName);
			Constructor<T> constructor = cls.getConstructor();
			Constructor<T> resultConstructor = cls.getConstructor(ResultSet.class);
			
			T template = constructor.newInstance();
			otherField.set(template, myValue);
			
			String queryString = template.queryString();
			ResultSet rs = stmt.executeQuery(queryString);
			if(rs.next()) {
				keyedValue = resultConstructor.newInstance(rs);
			}
			rs.close();
			
		} catch (NoSuchFieldException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		} catch (IllegalAccessException e) {
			throw new IllegalArgumentException(e);
		} catch (NoSuchMethodException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		} catch (InstantiationException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		} catch (InvocationTargetException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		}
		
		return keyedValue;
	}
	
	public String queryString() { 
		String tableName = getTableName();
		StringBuilder where = new StringBuilder();
		
		for(Field f : getClass().getFields()) { 
			int mod = f.getModifiers();
			if(Modifier.isPublic(mod) && !Modifier.isStatic(mod)) { 
				try {
					Object value = f.get(this);
					if(value != null) { 
						String name = f.getName();

						if(where.length() > 0) { 
							where.append(" AND ");
						}

						where.append(String.format("%s=%s", name, asSQL(value)));
					}
					
				} catch (IllegalAccessException e) {
					e.printStackTrace(System.err);
				}
			}
		}
		
		String whereString = where.toString();
		return String.format("SELECT * FROM %s %s", tableName, 
				(whereString.length() > 0 ? String.format("WHERE %s", whereString) : ""));
	}
	
	public String asString() { 
		StringBuilder sb = new StringBuilder();
		for(Field f : getClass().getFields()) { 
			int mod = f.getModifiers();
			if(Modifier.isPublic(mod) && !Modifier.isStatic(mod)) {
				try {
					Object value = f.get(this);
					if(value != null) { 
						sb.append(String.format("<h3>%s</h3>", f.getName()));
						sb.append(String.format("%s", String.valueOf(value).replace("\n", "<br>")));
					}
				} catch (IllegalAccessException e) {
					// do nothing.
				}
			}
		}
		return sb.toString();
	}
}
