package ca.jacobparry;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Logger;

import javax.sql.DataSource;

import org.json.JSONArray;
import org.json.JSONObject;

/**
 * Utility methods for working with JDBC {@link Connection}s.
 * 
 * @author Jacob
 */
public class DBUtils {
	
	private static boolean	profileQueries	= false;
	private static Logger	queryLogger		= Logger.getLogger("DBUtils");
	
	public static PreparedStatement buildPreparedStatement(String sql, Connection conn, Object... os) {
		try {
			
			PreparedStatement prep = StatementFactory.getStatement(conn, sql, DBUtils.profileQueries);
			
			for (int i = 0; i < os.length; i++)
				prep = setAutoType(prep, i + 1, os[i]);
			
			return prep;
		} catch (SQLException se) {
			se.printStackTrace();
			return null;
		}
	}
	
	public static PreparedStatement buildPreparedStatement(String sql, DataSource ds, Object... os) {
		try (Connection conn = ds.getConnection()) {
			return buildPreparedStatement(sql, conn, os);
		} catch (SQLException se) {
			se.printStackTrace();
			return null;
		}
	}
	
	public static boolean createTable(Class<? extends DBObject<?>> clz, DataSource ds) throws Exception {
		
		if (clz.isAnnotationPresent(DBSkipTableCreation.class)) return false;
		
		DBObject<? extends DBObject<?>> instance = getBlankInstance(clz);
		
		// Uses the annotation
		String tableName = instance.getTable();
		String primaryKey = instance.getPrimaryKey();
		
		String sql = "CREATE TABLE `" + tableName + "` (";
		
		for (String s : instance.getFields()) {
			Field f = DBObject.getField(s, clz);
			f.setAccessible(true);
			
			if (f.isAnnotationPresent(DBRelationship.class)) continue;
			
			String type = "INTEGER";
			String length = "";
			
			Class<?> ft = f.getType();
			
			if (ft.isAnnotationPresent(DBText.class)) {
				type = "TEXT";
				length = "";
			} else if (ft == String.class || ft.isEnum() || Enum.class.isAssignableFrom(ft)
					|| ft.isAssignableFrom(Enum.class)) {
				type = "CHARACTER VARYING";
				length = "255";
			} else if (ft == int.class || ft == long.class) {
				type = "INTEGER";
				length = "21"; // No length on int fields
			} else if (ft == float.class || ft == double.class) {
				type = "DOUBLE PRECISION";
				length = "30,10";
			} else if (ft == BigDecimal.class) {
				type = "NUMERIC";
				if (f.isAnnotationPresent(DBDecimal.class)) {
					DBDecimal dbd = f.getAnnotation(DBDecimal.class);
					length = dbd.precision() + "," + dbd.scale();
				} else length = "20,2";
			} else if (ft == boolean.class) {
				type = "SMALLINT";
				length = "1";
			} else if (ft == JSONObject.class || ft == JSONArray.class) type = "TEXT";
			else if (DBObject.class.isAssignableFrom(ft)) type = "TEXT";
			
			if (f.isAnnotationPresent(DBLength.class)) {
				DBLength dbl = f.getAnnotation(DBLength.class);
				length = dbl.value();
			}
			
			sql += "\n`" + s + "` " + type;
			if (length != "") sql += "(" + length + ")";
			
			if (f.isAnnotationPresent(DBNotNull.class) || primaryKey.equalsIgnoreCase(s)) sql += " NOT NULL";
			
			if (primaryKey.equalsIgnoreCase(s)) sql += " PRIMARY KEY AUTO_INCREMENT";
			
			if (f.isAnnotationPresent(DBUnique.class)) sql += " UNIQUE";
			
			sql += ",";
			
		}
		
		if (sql.endsWith(",")) sql = sql.substring(0, sql.length() - 1) + "\n";
		
		sql += ");";
		
		try (Connection conn = ds.getConnection()) {
			PreparedStatement prep = StatementFactory.getStatement(conn, sql, DBUtils.profileQueries);
			prep.execute();
		} catch (SQLException se) {
			return false;
		}
		
		// Insert default data
		Field ddf = null;
		try {
			ddf = clz.getDeclaredField("DEFAULT_DATA");
		} catch (NoSuchFieldException nsfe) {
			return true;
		}
		
		if (ddf == null) return true;
		ddf.setAccessible(true);
		
		if (!ddf.getType().isArray()) {
			System.err.println("DEFAULT_DATA for " + clz.getSimpleName() + " is not an array!");
			return false;
		}
		Object[] val = (Object[]) ddf.get(null);
		if (val == null) return true;
		if (val.length == 0) return true;
		
		for (Object o : val)
			if (!DBUtils.insert((DBObject<?>) o, ds)) return false;
		
		return true;
		
	}
	
	/**
	 * Delete a {@link DBObject} object in a JDBC database
	 * 
	 * @param object
	 *            The {@link DBObject} to be deleted
	 * @param ds
	 *            The DataSource to use
	 * @return True if it was deleted, false otherwise
	 */
	public static boolean delete(DBObject<?> object, DataSource ds) {
		if (object.getPrimaryKey() == null || object.getPrimaryKey().trim().equals("") || ds == null) return false;
		
		try (Connection conn = ds.getConnection()) {
			
			String table = object.getTable();
			String primaryKey = object.getPrimaryKey();
			
			Field f = DBObject.getField(primaryKey, object.getClass());
			f.setAccessible(true);
			
			String sql = "DELETE FROM `" + table + "` WHERE `" + primaryKey + "`=?";
			
			PreparedStatement prep = StatementFactory.getStatement(conn, sql, DBUtils.profileQueries);
			
			prep = setAutoType(prep, 1, f.get(object));
			
			prep.execute();
			
			return true;
			
		} catch (SQLException | NoSuchFieldException | IllegalAccessException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public static JSONObject get(int id, Class<? extends DBObject<?>> jsonType, Connection conn) {
		try {
			JSONObject[] res = DBUtils.getMultiple(getPrimaryKey(jsonType), id, jsonType, conn);
			
			if (res.length < 1) return null;
			return res[0];
			
		} catch (IllegalArgumentException | SecurityException se) {
			se.printStackTrace();
			return null;
		}
	}
	
	/**
	 * Return a {@link JSONObject} from a JDBC {@link Connection}
	 * 
	 * @param id
	 *            Primary key value
	 * @param pkey
	 *            Name of the primary key
	 * @param table
	 *            Name of the database table
	 * @param ds
	 *            JDBC database connection
	 * @return {@link JSONObject} representing the row
	 */
	public static JSONObject get(int id, Class<? extends DBObject<?>> jsonType, DataSource ds) {
		try (Connection conn = ds.getConnection()) {
			return get(id, jsonType, conn);
		} catch (IllegalArgumentException | SecurityException | SQLException se) {
			se.printStackTrace();
			return null;
		}
	}
	
	@SuppressWarnings("unchecked")
	public static DBObject<? extends DBObject<?>> getBlankInstance(Class<? extends DBObject<?>> clz) {
		try {
			return (DBObject<? extends DBObject<?>>) clz.getConstructor(JSONObject.class).newInstance((Object) null);
		} catch (Exception e) {
			return null;
		}
	}
	
	public static JSONObject[] getMultiple(PreparedStatement prep, Class<? extends DBObject<?>> jsonType,
			Connection conn) throws Exception {
		
		ResultSet res = prep.executeQuery();
		
		ArrayList<JSONObject> alist = new ArrayList<>();
		
		while (res.next()) {
			// Build base object
			ResultSetMetaData rsmd = res.getMetaData();
			
			JSONObject baseObj = new JSONObject();
			
			for (int i = 1; i <= rsmd.getColumnCount(); i++)
				baseObj.put(rsmd.getColumnName(i), res.getObject(i));
			
			// Check for fields that have the DBRelationship annotation
			for (Field f : jsonType.getDeclaredFields()) {
				DBRelationship dbRelat = f.getAnnotation(DBRelationship.class);
				if (dbRelat != null) {
					Class<? extends DBObject<?>> clazz = dbRelat.type();
					String foreignKey = dbRelat.foreignKey();
					String primaryKey = dbRelat.primaryKey();
					
					if (baseObj.has(primaryKey)) {
						JSONObject[] relatResult = DBUtils
								.getMultiple(foreignKey, baseObj.get(primaryKey), clazz, conn);
						
						JSONArray arr = new JSONArray();
						for (JSONObject o : relatResult)
							arr.put(o);
						baseObj.put(f.getName(), arr);
					}
				} else if (DBObject.class.isAssignableFrom(f.getType())) baseObj.put(f.getName(), new JSONObject(
						baseObj.getString(f.getName())));
			}
			
			alist.add(jsonType.getConstructor(JSONObject.class).newInstance(baseObj).toJSON());
		}
		
		return alist.toArray(new JSONObject[0]);
	}
	
	public static JSONObject[] getMultiple(PreparedStatement prep, Class<? extends DBObject<?>> jsonType, DataSource ds)
			throws Exception {
		try (Connection conn = ds.getConnection()) {
			return getMultiple(prep, jsonType, conn);
		} catch (Exception e) {
			throw e;
		}
	}
	
	public static JSONObject[] getMultiple(String sql, Class<? extends DBObject<?>> jsonType, Connection conn) {
		if (!sql.toLowerCase().startsWith("select")) return null;
		
		try {
			return getMultiple(buildPreparedStatement(sql, conn, new Object[] {}), jsonType, conn);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static JSONObject[] getMultiple(String sql, Class<? extends DBObject<?>> jsonType, DataSource ds) {
		try (Connection conn = ds.getConnection()) {
			return getMultiple(sql, jsonType, conn);
		} catch (SQLException se) {
			se.printStackTrace();
			return null;
		}
	}
	
	public static JSONObject[] getMultiple(String key, Object value, Class<? extends DBObject<?>> jsonType,
			Connection conn) {
		return getMultiple(key, value, jsonType, -1, -1, conn);
	}
	
	/**
	 * Return an array of {@link JSONObject} from a database
	 * 
	 * @param key
	 *            Column to filter by
	 * @param value
	 *            Value to filter for
	 * @param table
	 *            Name of the database table
	 * @param ds
	 *            JDBC database connection
	 * @return
	 * @return An array of {@link JSONObject} representing the rows, or null
	 */
	public static JSONObject[] getMultiple(String key, Object value, Class<? extends DBObject<?>> jsonType,
			DataSource ds) {
		return getMultiple(key, value, jsonType, -1, -1, ds);
	}
	
	public static JSONObject[] getMultiple(String key, Object value, Class<? extends DBObject<?>> jsonType, int limit,
			Connection conn) {
		return getMultiple(key, value, jsonType, -1, limit, conn);
	}
	
	public static JSONObject[] getMultiple(String key, Object value, Class<? extends DBObject<?>> jsonType, int limit,
			DataSource ds) {
		return getMultiple(key, value, jsonType, -1, limit, ds);
	}
	
	public static JSONObject[] getMultiple(String key, Object value, Class<? extends DBObject<?>> jsonType, int start,
			int limit, Connection conn) {
		
		try {
			
			String sql = "SELECT * FROM `" + getTableName(jsonType) + "`";
			if (key != null) sql += " WHERE `" + key + "`=?";
			
			if (limit > 0) if (start > 0) sql += " LIMIT " + start + "," + limit;
			else sql += " LIMIT " + limit;
			
			PreparedStatement prep = StatementFactory.getStatement(conn, sql, DBUtils.profileQueries);
			
			if (key != null) prep = setAutoType(prep, 1, value);
			
			return getMultiple(prep, jsonType, conn);
			
		} catch (Exception se) {
			se.printStackTrace();
			return null;
		}
	}
	
	public static JSONObject[] getMultiple(String key, Object value, Class<? extends DBObject<?>> jsonType, int start,
			int limit, DataSource ds) {
		try (Connection conn = ds.getConnection()) {
			return getMultiple(key, value, jsonType, start, limit, conn);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static String getPrimaryKey(Class<? extends DBObject<?>> clz) {
		if (clz.isAnnotationPresent(DBTable.class)) {
			DBTable t = clz.getAnnotation(DBTable.class);
			return t.primaryKey();
		}
		DBObject<? extends DBObject<?>> instance = getBlankInstance(clz);
		if (instance == null) return null;
		return instance.getPrimaryKey();
	}
	
	public static boolean getProfileQueries() {
		return DBUtils.profileQueries;
	}
	
	public static Logger getQueryLogger() {
		return DBUtils.queryLogger;
	}
	
	public static String getTableName(Class<? extends DBObject<?>> clz) {
		if (clz.isAnnotationPresent(DBTable.class)) {
			DBTable t = clz.getAnnotation(DBTable.class);
			return t.tableName();
		}
		DBObject<? extends DBObject<?>> instance = getBlankInstance(clz);
		if (instance == null) return null;
		return instance.getTable();
	}
	
	/**
	 * Insert a {@link DBObject} into a JDBC database
	 * 
	 * @param object
	 *            The {@link DBObject} to be inserted
	 * @param table
	 *            The name of the table to insert into
	 * @param ds
	 *            The JDBC connection
	 * @return True if the object was inserted successfully, false otherwise
	 */
	public static boolean insert(DBObject<?> object, DataSource ds) {
		if (object == null) return false;
		
		try (Connection conn = ds.getConnection()) {
			
			String table = object.getTable();
			
			if (JSONObject.getNames(object.toJSON()).length == 0) return false;
			
			String primaryKey = object.getPrimaryKey();
			
			String sql = "INSERT INTO `" + table + "` (";
			for (int i = 0; i < object.getFields().length; i++) {
				if (object.getFields()[i].equals(primaryKey)) continue;
				
				try {
					Field f = DBObject.getField(object.getFields()[i], object.getClass());
					if (f.isAnnotationPresent(DBRelationship.class)) continue;
				} catch (NoSuchFieldException e) {
					continue;
				}
				
				if (sql.charAt(sql.length() - 1) != "(".charAt(0)) sql += ",";
				sql += "`" + object.getFields()[i] + "`";
			}
			sql += ") VALUES(";
			
			for (int i = 0; i < object.getFields().length; i++) {
				if (object.getFields()[i].equals(primaryKey)) continue;
				
				try {
					Field f = DBObject.getField(object.getFields()[i], object.getClass());
					if (f.isAnnotationPresent(DBRelationship.class)) continue;
				} catch (NoSuchFieldException e) {
					continue;
				}
				
				if (sql.charAt(sql.length() - 1) != "(".charAt(0)) sql += ",";
				sql += "?";
			}
			sql += ")";
			
			PreparedStatement prep = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			int index = 1;
			for (int i = 0; i < object.getFields().length; i++) {
				if (object.getFields()[i].equals(object.getPrimaryKey())) continue;
				
				Field f = DBObject.getField(object.getFields()[i], object.getClass());
				if (f.isAnnotationPresent(DBRelationship.class)) continue;
				f.setAccessible(true);
				
				prep = setAutoType(prep, index, f.get(object));
				index++;
			}
			
			prep.executeUpdate();
			
			// Set the primary key
			ResultSet rs = prep.getGeneratedKeys();
			if (rs.next()) {
				String pkey = object.getPrimaryKey();
				int newId = rs.getInt(1);
				if (pkey != null && pkey.length() > 0 && newId > 0) {
					Field f = DBObject.getField(pkey, object.getClass());
					f.setAccessible(true);
					f.set(object, newId);
				}
				if (profileQueries) queryLogger.info("INSERT " + object.getPrimaryKey() + ":"
						+ object.getPrimaryKeyValue());
			}
			
			return true;
			
		} catch (SQLException | NoSuchFieldException | SecurityException | IllegalArgumentException
				| IllegalAccessException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public static JSONObject jsonFromResultSet(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		JSONObject o = new JSONObject();
		for (int i = 1; i <= rsmd.getColumnCount(); i++)
			o.put(rsmd.getColumnName(i), rs.getObject(i));
		return o;
	}
	
	/**
	 * Set the value of a variable in a PreparedStatement, automatically
	 * detecting the proper type to use based on the class of the value
	 * 
	 * @param prep
	 *            {@link PreparedStatement} to use
	 * @param i
	 *            Index of variable in <code>prep</code>
	 * @param value
	 *            Value to insert
	 * @return <code>prep</code>, but with <code>value</code> inserted
	 * @throws SQLException
	 */
	private static PreparedStatement setAutoType(PreparedStatement prep, int i, Object value) throws SQLException {
		if (value == null) prep.setObject(i, value);
		else if (value.getClass().equals(int.class)) prep.setInt(i, (int) value);
		else if (value.getClass().equals(String.class)) prep.setString(i, (String) value);
		else if (value.getClass().equals(boolean.class)) prep.setBoolean(i, (boolean) value);
		else if (value.getClass().equals(long.class)) prep.setLong(i, (long) value);
		else if (value.getClass().equals(short.class)) prep.setShort(i, (short) value);
		else if (value.getClass().equals(float.class)) prep.setFloat(i, (float) value);
		else if (value.getClass().equals(double.class)) prep.setDouble(i, (double) value);
		else if (value.getClass().equals(JSONObject.class)) prep.setString(i, ((JSONObject) value).toString());
		else if (value.getClass().equals(JSONArray.class)) prep.setString(i, ((JSONArray) value).toString());
		else if (value.getClass().isEnum()) prep.setString(i, ((Enum<?>) value).toString());
		else if (DBObject.class.isAssignableFrom(value.getClass())) prep.setString(i, ((DBObject<?>) value).toJSON()
				.toString());
		else prep.setObject(i, value);
		
		return prep;
	}
	
	public static void setProfileQueries(boolean profileQueries) {
		DBUtils.profileQueries = profileQueries;
	}
	
	public static void setQueryLogger(Logger logger) {
		DBUtils.queryLogger = logger;
	}
	
	/**
	 * Update a {@link DBObject} object in a JDBC database
	 * 
	 * @param object
	 *            The {@link DBObject} to be updated
	 * @param table
	 *            The name of the table to update
	 * @param ds
	 *            The JDBC connection
	 * @return True if the object was updated, false otherwise
	 */
	public static boolean update(DBObject<?> object, DataSource ds) {
		try (Connection conn = ds.getConnection()) {
			
			// Only handle changes
			JSONObject changes = object.getChanges();
			ArrayList<String> fields = new ArrayList<>();
			for (Object k : changes.keySet())
				fields.add((String) k);
			
			if (fields.size() == 0) return true;
			
			// Filter fields
			HashMap<String, Object> values = new HashMap<>();
			for (String k : fields) {
				if (k.equals(object.getPrimaryKey())) continue;
				
				Field f = DBObject.getField(k, object.getClass());
				if (f == null) continue;
				f.setAccessible(true);
				
				if (f.isAnnotationPresent(DBRelationship.class)) continue;
				
				values.put(k, f.get(object));
			}
			
			// Add parameters
			ArrayList<String> params = new ArrayList<>();
			for (String k : values.keySet())
				params.add("`" + k + "`=?");
			
			// Setup the SQL query
			String table = object.getTable();
			String sql = "UPDATE `" + table + "` SET ";
			for (String p : params.toArray(new String[0]))
				sql += p + ", ";
			sql = sql.trim();
			if (sql.endsWith(",")) sql = sql.substring(0, sql.length() - 1);
			sql += " WHERE `" + object.getPrimaryKey() + "`=?";
			
			// Set values
			PreparedStatement prep = StatementFactory.getStatement(conn, sql, DBUtils.profileQueries);
			int index = 1;
			for (String k : values.keySet()) {
				prep = setAutoType(prep, index, values.get(k));
				index++;
			}
			
			// Set the primary key
			Field f = DBObject.getField(object.getPrimaryKey(), object.getClass());
			f.setAccessible(true);
			prep = setAutoType(prep, index, f.get(object));
			
			prep.execute();
			
			return true;
			
		} catch (SQLException | NoSuchFieldException | IllegalArgumentException | IllegalAccessException e) {
			e.printStackTrace();
			return false;
		}
	}
}
