package db;

import java.util.List;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Optional;

import db.annotation.DbField;
import db.annotation.DbId;
import db.annotation.DbTable;

public abstract class Table<T extends Persistable> {
	
	private Map<Long, T> cacheMap = new HashMap<>();
	
	public T getById(long id) {
		if (cacheMap.containsKey(id)) {
			return cacheMap.get(id);
		}
		Optional<T> opt = getFromDbById(id);
		if(opt.isEmpty()) {
			return null;
		}
		return cache(opt.get());
	}
	
	public Optional<T> findById(long id) {
		T obj = getById(id);
		if (obj == null) {
			return Optional.empty();
		}
		return Optional.of(obj);
	}
	
	public List<T> getByField(String fieldname, Object value) {
		List<T> list = getFromDbByField(fieldname, value);
		list.forEach(elt -> cache(elt));
		return list;
	}
	
	public List<T> getWhere(List<String> fields, List<Object> values) {
		List<T> list = getWhereFromDb(fields, values);
		list.forEach(elt -> cache(elt));
		return list;
	}
	
	public List<T> getAll() {
		List<T> list = getAllFromDb();
		list.forEach(elt -> cache(elt));
		return list;
	}
	
	public T refresh(long id) {
		if (!cacheMap.containsKey(id)) {
			throw new IllegalArgumentException("Entity is not load, can't refresh");
		}
		Optional<T> optObj = getFromDbById(id);
		if(optObj.isEmpty()) {
			throw new IllegalStateException("Unable to find entity in the database");
		}
		T obj = optObj.get();
		cache(obj);
		return obj;
	}
	
	public T refresh(T obj) {
		return refresh(obj.getId());
	}
	
	public void save(T obj) {
		if (cacheMap.containsKey(obj.getId())) {
			update(obj);
		} else {
			insert(obj);
		}
		cache(obj);
	}
	
	public final void delete(long id) {
		DbTable dbTable = getDbTableAnnotation();
		// Cherche l'id
		String idField = null;
		for(Field field : dbTable.entity().getFields()) {
			if(!field.isAnnotationPresent(DbId.class)) {
				continue;
			}
			// Lecture du nom de l'id dans la base
			if(field.isAnnotationPresent(DbField.class)) {
				idField = field.getAnnotation(DbField.class).name();
			} else {
				throw new IllegalStateException("Unable to find DbField annotation on id");
			}
		}
		if (idField == null) {
			throw new IllegalStateException("Unable to find find DbField annotation");
		}
		// Requete SQL
		String sql = "Delete From " + dbTable.name() + " Where " + idField + " = ?";
		List<Object> params = new ArrayList<>();
		params.add(id);
		// Execution requete
		if (!Database.execute(sql, params)) {
			throw new IllegalStateException("Unable to delete data");
		}
		// Retire du cache
		remove(id);
	}
	
	public void delete(T obj) {
		delete(obj.getId());
	}
	
	private T cache(T obj) {
		if (cacheMap.containsKey(obj.getId())) {
			cacheMap.replace(obj.getId(), obj);
		} else {
			cacheMap.put(obj.getId(), obj);
		}
		//System.out.println("Cache size: " + cacheMap.size());
		//cacheMap.forEach((key, val) -> System.out.println("Cache: " + key));
		return obj;
	}
	
	private void remove(long id) {
		if (cacheMap.containsKey(id)) {
			cacheMap.remove(id);
		}
	}
	
	private DbTable getDbTableAnnotation() {
		Class<?> clazz = this.getClass();
		// Recupération info anotation DbTable
		if (!clazz.isAnnotationPresent(DbTable.class)) {
			throw new IllegalStateException("Unable ton find DbTable annotation");
		}
		return clazz.getAnnotation(DbTable.class);
	}
	
	private Optional<T> getFromDbById(long id) {
		DbTable dbTable = getDbTableAnnotation();
		// Cherche l'id
		String idField = null;
		for(Field field : dbTable.entity().getFields()) {
			if(!field.isAnnotationPresent(DbId.class)) {
				continue;
			}
			// Lecture du nom de l'id dans la base
			if(field.isAnnotationPresent(DbField.class)) {
				idField = field.getAnnotation(DbField.class).name();
			} else {
				throw new IllegalStateException("Unable to find DbField annotation on id");
			}
		}
		if (idField == null) {
			throw new IllegalStateException("Unable to find find DbField annotation");
		}
		// Requete sql
		String sql = "Select * From " + dbTable.name() + " Where " + idField + " = ?";
		List<Object> params = new ArrayList<>();
		params.add(id);
		Optional<T> result = Database.query(sql, params, rs -> {
			try {
				T obj = (T) dbTable.entity().getConstructor().newInstance();
				rs.next();
				for(Field field : dbTable.entity().getFields()) {
					if(!field.isAnnotationPresent(DbField.class)) {
						continue;
					}
					obj.getClass().getField(field.getName()).set(obj, rs.getObject(field.getAnnotation(DbField.class).name()));
				}
				return obj;
			} catch (InstantiationException | IllegalAccessException | IllegalArgumentException | InvocationTargetException | NoSuchMethodException | SecurityException | NoSuchFieldException | SQLException e) {
				throw new IllegalStateException("Unable to map value to the entity");
			}
		});
		// Return
		return result;
	}
	
	private List<T> getFromDbByField(String fieldname, Object value) {
		DbTable dbTable = getDbTableAnnotation();
		// Requete sql
		String sql = "Select * From " + dbTable.name() + " Where " + fieldname + " = ?";
		List<Object> params = new ArrayList<>();
		params.add(value);
		Optional<List<T>> result = Database.query(sql, params, rs -> {
			try {
				List<T> list = new ArrayList<>();
				while(rs.next()) {
					T obj = (T) dbTable.entity().getConstructor().newInstance();
					for(Field field : dbTable.entity().getFields()) {
						if(!field.isAnnotationPresent(DbField.class)) {
							continue;
						}
						obj.getClass().getField(field.getName()).set(obj, rs.getObject(field.getAnnotation(DbField.class).name()));
					}
					list.add(obj);
				}
				return list;
			} catch (InstantiationException | IllegalAccessException | IllegalArgumentException | InvocationTargetException | NoSuchMethodException | SecurityException | NoSuchFieldException | SQLException e) {
				throw new IllegalStateException("Unable to map value to the entity");
			}
		});
		// Return
		if(result.isEmpty()) {
			return new ArrayList<>();
		}
		return result.get();
	}
	
	private List<T> getWhereFromDb(List<String> where, List<Object> params) {
		DbTable dbTable = getDbTableAnnotation();
		// Requete sql
		StringBuilder sql = new StringBuilder();
		sql.append("Select * From " + dbTable.name() + "Where 1=1");
		where.forEach(elt -> sql.append(" And " + elt + " = ?"));
		// Execution requete
		Optional<List<T>> result = Database.query(sql.toString(), params, rs -> {
			try {
				List<T> list = new ArrayList<>();
				while(rs.next()) {
					T obj = (T) dbTable.entity().getConstructor().newInstance();
					for(Field field : dbTable.entity().getFields()) {
						if(!field.isAnnotationPresent(DbField.class)) {
							continue;
						}
						obj.getClass().getField(field.getName()).set(obj, rs.getObject(field.getAnnotation(DbField.class).name()));
					}
					list.add(obj);
				}
				return list;
			} catch (InstantiationException | IllegalAccessException | IllegalArgumentException | InvocationTargetException | NoSuchMethodException | SecurityException | NoSuchFieldException | SQLException e) {
				throw new IllegalStateException("Unable to map value to the entity");
			}
		});
		// Return
		if(result.isEmpty()) {
			return new ArrayList<>();
		}
		return result.get();
	}
	
	private List<T> getAllFromDb() {
		DbTable dbTable = getDbTableAnnotation();
		// Requete sql
		String sql = "Select * From " + dbTable.name();
		Optional<List<T>> result = Database.query(sql, rs -> {
			try {
				List<T> list = new ArrayList<>();
				while(rs.next()) {
					T obj = (T) dbTable.entity().getConstructor().newInstance();
					for(Field field : dbTable.entity().getFields()) {
						if(!field.isAnnotationPresent(DbField.class)) {
							continue;
						}
						obj.getClass().getField(field.getName()).set(obj, rs.getObject(field.getAnnotation(DbField.class).name()));
					}
					list.add(obj);
				}
				return list;
			} catch (InstantiationException | IllegalAccessException | IllegalArgumentException | InvocationTargetException | NoSuchMethodException | SecurityException | NoSuchFieldException | SQLException e) {
				throw new IllegalStateException("Unable to map value to the entity");
			}
		});
		// Return
		if(result.isEmpty()) {
			return new ArrayList<>();
		}
		return result.get();
	}
	
	private void insert(T obj) {
		DbTable dbTable = getDbTableAnnotation();
		// Requete SQL
		StringBuilder sql = new StringBuilder();
		StringBuilder values = new StringBuilder();
		String dbIdName = null; // Nom de l'id en base
		String attrIdName = null; // Nom de l'id dans la class
		List<Object> params = new ArrayList<>();
		sql.append("Insert Into " + dbTable.name() + "(");
		boolean first = true;
		try {
			for(Field field : obj.getClass().getFields()) {
				if (!field.isAnnotationPresent(DbField.class)) {
					continue;
				}
				// Cherche l'id
				if (dbIdName == null && field.isAnnotationPresent(DbId.class)) {
					dbIdName = field.getAnnotation(DbField.class).name();
					attrIdName = field.getName();
					continue;
				}
				// Recupere la valeur des autres attributs
				if (!first) {
					sql.append(",");
					values.append(",");
				}
				sql.append(field.getAnnotation(DbField.class).name());
				values.append("?");
				params.add(field.get(obj));
				first = false;
			}
		} catch (IllegalArgumentException | IllegalAccessException e) {
			throw new IllegalStateException("Unable to access to the attribute", e);
		}	
		sql.append(") Values (");
		sql.append(values);
		sql.append(")");
		// Verif que l'on a bien le nom de l'id
		if(dbIdName == null) {
			throw new IllegalStateException("Unable to find id field");
		}
		// Execution de la requete
		Map<String, Object> newId = Database.insert(sql.toString(), params, new String[]{dbIdName});
		if (newId.isEmpty()) {
			throw new IllegalStateException("Unable to save data");
		}
		// Application nouvelle id
		Object val = newId.get(dbIdName);
		try {
			if (val instanceof BigDecimal) {
				BigDecimal b = (BigDecimal) val;
				obj.getClass().getField(attrIdName).set(obj, b.longValue());
			} else {
				obj.getClass().getField(attrIdName).set(obj, val);
			}
		} catch (IllegalArgumentException | IllegalAccessException | NoSuchFieldException | SecurityException e) {
			throw new IllegalStateException("Unable to set id", e);
		}
	}
	
	private void update(T obj) {
		DbTable dbTable = getDbTableAnnotation();
		// Requete SQL
		StringBuilder sql = new StringBuilder();
		StringBuilder where = new StringBuilder();
		List<Object> params = new ArrayList<>();
		sql.append("Update " + dbTable.name() + " Set ");
		where.append(" Where ");
		boolean first = true;
		for (Field field : obj.getClass().getFields()) {
			try {
				if (!field.isAnnotationPresent(DbField.class)) {
					continue;
				}
				// Si c'est l'id
				if (field.isAnnotationPresent(DbId.class)) {
					where.append(field.getAnnotation(DbField.class).name() + " = ?");
					continue;
				}
				// Sinon ajoute dans le set
				if (!first) {
					sql.append(",");
				}
				sql.append(field.getAnnotation(DbField.class).name() + " = ?");
				params.add(field.get(obj));
				first = false;
			} catch (IllegalArgumentException | IllegalAccessException e) {
				throw new IllegalStateException("Unable to access to the attribute", e);
			}
		}
		sql.append(where);
		params.add(obj.getId());
		// Execution requete
		if(!Database.execute(sql.toString(), params)) {
			throw new IllegalStateException("Unable to save data");
		}
	}

}