package org.mpi.openmind.scripts;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.NotImplementedException;
import org.apache.commons.lang.StringUtils;
import org.mpi.openmind.repository.bo.Node;

/**
 * 
 * This script find the relation that are mark as "CURRENT_VERSION", 
 * however the time_modification does not meet either the time modification of the source, of target or both.
 * 
 * This script does not work well, when there is twice entities with the state "CURRENT_VERSION" and with the same id.
 * This problem should be corrected before running this script. Use before class: DoubleEntity
 * 
 * 
 * @author jurzua
 *
 */
public class TimeModification {

	//selects
	private static String queryEntListByClass = "select * from openmind.node where object_class = '##' and system_status = 'CURRENT_VERSION'";
	private static String queryAllEntList = "select * from openmind.node where node_type = 'ENTITY' and system_status = 'CURRENT_VERSION' and type = 'ABox'";
	private static String queryTarRels = "select * from openmind.node where target_id = '##' and system_status = 'CURRENT_VERSION'";
	private static String querySrcRels = "select * from openmind.node where source_id = '##' and system_status = 'CURRENT_VERSION' and node_type = 'RELATION'";
	
	//updates
	private static String updateTarRel = "UPDATE openmind.node SET target_modif = '##' WHERE row_id = '**'";
	private static String updateSrcRel = "UPDATE openmind.node SET source_modif = '##' WHERE row_id = '**'";
	
	private static List<ClassRelation> classRelList = new ArrayList<ClassRelation>();
	
	static{
		classRelList.add(new ClassRelation("WITNESS", "src:is_part_of"));
		classRelList.add(new ClassRelation("WITNESS", "src:is_exemplar_of"));
		classRelList.add(new ClassRelation("CODEX", "src:is_part_of"));
		classRelList.add(new ClassRelation("COLLECTION", "src:is_part_of"));
		classRelList.add(new ClassRelation("REPOSITORY", "src:is_in"));
		classRelList.add(new ClassRelation("CODEX", "tar:is_part_of"));
		classRelList.add(new ClassRelation("COLLECTION", "tar:is_part_of"));
		classRelList.add(new ClassRelation("REPOSITORY", "tar:is_part_of"));
		classRelList.add(new ClassRelation("PLACE", "tar:is_in"));
		classRelList.add(new ClassRelation("TEXT", "tar:is_exemplar_of"));
		classRelList.add(new ClassRelation("TEXT", "src:was_created_by"));
		classRelList.add(new ClassRelation("TEXT", "tar:is_prime_alias_title_of"));
		classRelList.add(new ClassRelation("TEXT", "tar:is_alias_title_of"));
		classRelList.add(new ClassRelation("TEXT", "tar:is_translation_of"));
		classRelList.add(new ClassRelation("PERSON", "tar:was_created_by"));
		classRelList.add(new ClassRelation("PERSON", "src:lived_in"));
		classRelList.add(new ClassRelation("PERSON", "tar:is_reference_of"));
		classRelList.add(new ClassRelation("PERSON", "tar:is_alias_name_of"));
		classRelList.add(new ClassRelation("PERSON", "src:has_role"));
		classRelList.add(new ClassRelation("PERSON", "tar:was_copied_by"));
		classRelList.add(new ClassRelation("ALIAS", "src:is_prime_alias_title_of"));
		classRelList.add(new ClassRelation("ALIAS", "src:is_alias_title_of"));
		classRelList.add(new ClassRelation("ALIAS", "tar:has_title_written_as"));
	}
	

	/**
	 * 
	 * @param conn
	 * @param entId
	 * @param entModif
	 * @param entOC
	 * @param action show/solve
	 * @throws SQLException
	 */
	public static void executeEntity(Connection conn, Long entId, Long entModif, String entOC, String relacion, String action) throws Exception{
		
		if(StringUtils.isEmpty(relacion)){
			executeSources(conn, entId, entModif, entOC, null, action);
			executeTargets(conn, entId, entModif, entOC, null, action);
		}else{
			String[] array = relacion.split(":");
			String relLabel = array[1];
			if(array[0].equals("src")){
				executeSources(conn, entId, entModif, entOC, relLabel, action);
			}else if(array[0].equals("tar")){
				executeTargets(conn, entId, entModif, entOC, relLabel, action);	
			}else{
				throw new Exception("The parameter relation is not valid: " + relacion);
			}
		}
	}
	
	public static boolean analyseSrcRels(Connection conn, List<Relation> rels, Long entId, Long entModif, String entOC, String relLabel, String action) throws Exception{
		
		boolean ok = true;
		
		for(Relation rel : rels){
			if(!rel.srcModif.equals(entModif)){
				ok = false;
				break;
			}
		}
		
		if(!ok){
			
			printRels(entId, entOC, entModif, rels, "SRC");
			
			if(	StringUtils.equals(action, "solve") && (
					(StringUtils.equals(entOC, "WITNESS") && (StringUtils.equals(relLabel, "is_part_of"))) ||
					(StringUtils.equals(entOC, "WITNESS") && (StringUtils.equals(relLabel, "is_exemplar_of"))) ||
					(StringUtils.equals(entOC, "COLLECTION") && (StringUtils.equals(relLabel, "is_part_of"))) ||
					(StringUtils.equals(entOC, "TEXT") && (StringUtils.equals(relLabel, "was_created_by"))) ||
					(StringUtils.equals(entOC, "ALIAS") && (StringUtils.equals(relLabel, "is_prime_alias_title_of"))) ||
					(StringUtils.equals(entOC, "ALIAS") && (StringUtils.equals(relLabel, "is_alias_title_of")))
					)
				){
				solveUniqueSourceRel(conn, rels, entId, entModif);
			}
			
			
			if(	StringUtils.equals(action, "solve") && (
					(StringUtils.equals(entOC, "PERSON") && (StringUtils.equals(relLabel, "lived_in"))) ||
					(StringUtils.equals(entOC, "PERSON") && (StringUtils.equals(relLabel, "has_role")))
					)
				){
				solveMultipleSourceRels(conn, rels, entId, entModif);
				removeDoubleRelations(conn, rels);
			}
			
		}
		return ok;
	}
	
	public static boolean analyseTarRels(Connection conn, List<Relation> rels, Long entId, Long entModif, String entOC, String relLabel, String action) throws Exception{
		
		boolean ok = true;
		
		for(Relation rel : rels){
			if(!rel.tarModif.equals(entModif)){
				ok = false;
				break;
			}
		}
		
		if(!ok){
			
			printRels(entId, entOC, entModif, rels, "TAR");

			
			if(	StringUtils.equals(action, "solve") && (
					(StringUtils.equals(entOC, "CODEX") && (StringUtils.equals(relLabel, "is_part_of"))) ||
					(StringUtils.equals(entOC, "TEXT") && (StringUtils.equals(relLabel, "is_exemplar_of"))) ||
					(StringUtils.equals(entOC, "TEXT") && (StringUtils.equals(relLabel, "is_alias_title_of"))) ||
					(StringUtils.equals(entOC, "PERSON") && (StringUtils.equals(relLabel, "was_created_by"))) ||
					(StringUtils.equals(entOC, "PERSON") && (StringUtils.equals(relLabel, "is_reference_of"))) ||
					(StringUtils.equals(entOC, "PERSON") && (StringUtils.equals(relLabel, "is_alias_name_of"))) ||
					(StringUtils.equals(entOC, "PERSON") && (StringUtils.equals(relLabel, "was_copied_by"))) ||
					(StringUtils.equals(entOC, "COLLECTION") && (StringUtils.equals(relLabel, "is_part_of"))) ||
					(StringUtils.equals(entOC, "REPOSITORY") && (StringUtils.equals(relLabel, "is_part_of"))) ||
					(StringUtils.equals(entOC, "PLACE") && (StringUtils.equals(relLabel, "is_in")))
					)
				){
				solveMultipleTargetRels(conn, rels, entId, entModif);
				removeDoubleRelations(conn, rels);
			}
			
			if(	StringUtils.equals(action, "solve") && (
					(StringUtils.equals(entOC, "TEXT") && (StringUtils.equals(relLabel, "is_prime_alias_title_of"))) ||
					(StringUtils.equals(entOC, "TEXT") && (StringUtils.equals(relLabel, "is_translation_of"))) ||
					(StringUtils.equals(entOC, "ALIAS") && (StringUtils.equals(relLabel, "has_title_written_as")))
					)
				){
				solveUniqueTargetRel(conn, rels, entId, entModif);
			}
		}
		return ok;
	}
	
	private static void printRels(Long entId, String entOC, Long entModif, List<Relation> rels, String direction){
		System.out.println("\n" + direction + ") Entity [id=" + entId + ", oc=" + entOC + ", modif=" + entModif + "] - Relation " + rels.get(0).label);
		System.out.println("\trowId\tid\tlabel\tsrcModif\ttarModif");
		for(Relation rel : rels){
			System.out.println("\t" + rel.toString());
		}
	}
	
	/**
	 * This method should be used only when the entity is WITNESS and from the source domain.
	 * @param conn
	 * @param rels
	 * @param entId
	 * @param entModif
	 * @throws SQLException
	 */
	private static void solveUniqueSourceRel(Connection conn, List<Relation> rels, Long entId, Long entModif) throws SQLException{
		System.out.println("\t-----------------------");
		Statement stmt = conn.createStatement() ;


		Relation correctRel = null;
		
		for(Relation rel : rels){
			if(rel.srcModif.equals(entModif)){
				correctRel = rel;
				break;
			}
		}
		
		// if correctRel is not null, it means that there is at least one relation with the right modification time.
		// if correctRel is not, we will use the last modified relation
		
		if(correctRel == null){
			correctRel = getLastModifiedRel(rels);
			String update = updateSrcRel.replace("##", entModif.toString()).replace("**", correctRel.rowId.toString());
			System.out.println("\t" + update);
			int rows = stmt.executeUpdate( update ) ;
		}
		
		
		// All other relations will be made Node.SYS_STATUS_PREVIOUS_VERSION
		if(correctRel != null){
			for(Relation rel : rels){
				if(!rel.equals(correctRel)){
					
					String update = "UPDATE openmind.node SET system_status = '" + Node.SYS_STATUS_PREVIOUS_VERSION + "' WHERE row_id = '" + rel.rowId + "'";
					System.out.println("\t" + update);
					int rows = stmt.executeUpdate( update ) ;
					
				}
			}
		}		
		
		stmt.close();
		System.out.println("\t-----------------------\n");
	}
	
	private static void solveUniqueTargetRel(Connection conn, List<Relation> rels, Long entId, Long entModif) throws SQLException{
		
		System.out.println("\t-----------------------");
		Statement stmt = conn.createStatement() ;

		Relation correctRel = null;
		
		for(Relation rel : rels){
			if(rel.tarModif.equals(entModif)){
				correctRel = rel;
				break;
			}
		}
		// if correctRel is not null, it means that there is at least one relation with the right modification time.
		// if correctRel is not, we will use the last modified relation
		if(correctRel == null){
			correctRel = getLastModifiedRel(rels);
			String update = updateTarRel.replace("##", entModif.toString()).replace("**", correctRel.rowId.toString());
			System.out.println("\t" + update);
			int rows = stmt.executeUpdate( update ) ;
		}
		
		// All other relations will be made Node.SYS_STATUS_PREVIOUS_VERSION
		if(correctRel != null){
			for(Relation rel : rels){
				if(!rel.equals(correctRel)){
					
					String update = "UPDATE openmind.node SET system_status = '" + Node.SYS_STATUS_PREVIOUS_VERSION + "' WHERE row_id = '" + rel.rowId + "'";
					System.out.println("\t" + update);
					int rows = stmt.executeUpdate( update ) ;
					
				}
			}
		}
		
		stmt.close();
		System.out.println("\t-----------------------\n");
	}
	
	private static void removeDoubleRelations(Connection conn, List<Relation> rels) throws Exception{
		Map<String, List<Relation>> doubleRelMal = new HashMap<String, List<Relation>>();
		
		for(Relation rel : rels){
			String key = rel.srcId + "-" + rel.tarId;
			if(!doubleRelMal.containsKey(key)){
				doubleRelMal.put(key, new ArrayList<Relation>());
			}
			doubleRelMal.get(key).add(rel);
		}
		
		for(String key : doubleRelMal.keySet()){
			
			List<Relation> list = doubleRelMal.get(key);
			
			//found double relations
			
			if(list.size() > 1){
				Statement stmt = conn.createStatement() ;
				System.out.println("\t-----------------------");
				System.out.println("\tFound double relations:");
				
				Relation correctRel = getLastModifiedRel(list);
				for(Relation rel : list){
					System.out.println("\t" + rel.toString());
					if(!rel.equals(correctRel)){
						String update = "UPDATE openmind.node SET system_status = '" + Node.SYS_STATUS_PREVIOUS_VERSION + "' WHERE row_id = '" + rel.rowId + "'";
						System.out.println("\t" + update);
						int rows = stmt.executeUpdate( update ) ;
					}
				}
				stmt.close();
				System.out.println("\t-----------------------\n");
			}
			
		}
		
	}
	
	private static Relation getLastModifiedRel(List<Relation> rels){
		Relation lastModified = null;
		
		for(Relation rel : rels){
			if(lastModified == null || lastModified.modifTime < rel.modifTime){
				lastModified = rel;
			}
		}
		return lastModified;
	}
	
	private static void solveMultipleTargetRels(Connection conn, List<Relation> rels, Long entId, Long entModif) throws SQLException{
		System.out.println("\t-----------------------");
		Statement stmt = conn.createStatement() ;
		
		
		for(Relation rel : rels){
			String update = updateTarRel.replace("##", entModif.toString()).replace("**", rel.rowId.toString());
			System.out.println("\t" + update);
			int rows = stmt.executeUpdate( update ) ;	
		}
		
		stmt.close();
		System.out.println("\t-----------------------\n");
	}
	
	private static void solveMultipleSourceRels(Connection conn, List<Relation> rels, Long entId, Long entModif) throws SQLException{
		System.out.println("\t-----------------------");
		Statement stmt = conn.createStatement() ;
		
		
		for(Relation rel : rels){
			String update = updateSrcRel.replace("##", entModif.toString()).replace("**", rel.rowId.toString());
			System.out.println("\t" + update);
			int rows = stmt.executeUpdate( update ) ;	
		}
		
		stmt.close();
		System.out.println("\t-----------------------\n");
	}
	
	
	private static void executeSources(Connection conn, Long entId, Long entModif, String entOC, String relLabel, String action) throws Exception{
		Map<String, List<Relation>> srcRelMap = new HashMap<String, List<Relation>>();
		
		ResultSet srcRels = getSrcRels(conn, entId);
		try {
			  while (srcRels.next()) {
				  Relation rel = new Relation(srcRels);
				  if(!srcRelMap.containsKey(rel.label)){
					  srcRelMap.put(rel.label, new ArrayList<Relation>());
				  }
				  srcRelMap.get(rel.label).add(rel);
			  }
		} finally {
			srcRels.close();
		}
		
		if(StringUtils.isEmpty(relLabel)){
			for(String rel : srcRelMap.keySet()){
				analyseSrcRels(conn, srcRelMap.get(rel), entId, entModif, entOC, rel, action);
			}
		}else if(srcRelMap.containsKey(relLabel)){
			analyseSrcRels(conn, srcRelMap.get(relLabel), entId, entModif, entOC, relLabel, action);
		}
	}
	
	private static void executeTargets(Connection conn, Long entId, Long entModif, String entOC, String relLabel, String action) throws Exception{
		Map<String, List<Relation>> tarRelMap = new HashMap<String, List<Relation>>();
		
		ResultSet tarRels = getTarRels(conn, entId);
		try {
			  while (tarRels.next()) {
				  Relation rel = new Relation(tarRels);
				  if(!tarRelMap.containsKey(rel.label)){
					  tarRelMap.put(rel.label, new ArrayList<Relation>());
				  }
				  tarRelMap.get(rel.label).add(rel);
			  }
		} finally {
			tarRels.close();
		}
		
		if(StringUtils.isEmpty(relLabel)){
			for(String rel : tarRelMap.keySet()){
				analyseTarRels(conn, tarRelMap.get(rel), entId, entModif, entOC, rel, action);
			}
		}else if(tarRelMap.containsKey(relLabel)){
			analyseTarRels(conn, tarRelMap.get(relLabel), entId, entModif, entOC, relLabel, action);
		}
	}
	
	/*
	public static void solveTarRels(Connection conn, List<Relation> rels, Long entId, Long entModif) throws SQLException{
		Statement stmt = conn.createStatement() ;
		for(Relation rel : rels){
			String update = updateTarRel.replace("##", entModif.toString()).replace("**", rel.rowId.toString());
			System.out.println(update);
			int rows = stmt.executeUpdate( update ) ;
		}
		stmt.close();
		System.out.println("Problem solved!");
	}
	
	public static void solveSrcRels(Connection conn, List<Relation> rels, Long entId, Long entModif) throws SQLException{
		Statement stmt = conn.createStatement() ;
		for(Relation rel : rels){
			String update = updateSrcRel.replace("##", entModif.toString()).replace("**", rel.rowId.toString());
			System.out.println(update);
			int rows = stmt.executeUpdate( update ) ;
		}
		stmt.close();
		System.out.println("Problem solved!");
	}*/

	public static ResultSet getTarRels(Connection conn, Long entId)
			throws SQLException {
		String query = queryTarRels.replace("##", entId.toString());
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		return rs;
	}

	public static ResultSet getSrcRels(Connection conn, Long entId)
			throws SQLException {
		String query = querySrcRels.replace("##", entId.toString());
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		return rs;
	}

	/**
	 * TODO reduces double entities
	 * @param conn
	 * @param objectClass
	 * @return
	 * @throws SQLException
	 */
	public static ResultSet getEntitiesByClass(Connection conn,
			String objectClass) throws SQLException {
		
		String query = null;
		
		if(StringUtils.isEmpty(objectClass)){
			query = queryAllEntList;
		}else{
			query = queryEntListByClass.replace("##", objectClass);
		}
		
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		System.out.println("Entities had been loaded...");
		return rs;
	}

	public static void execute(String mysqlUser,
			String mysqlPass, String action, String objectClass, String relation) {
		try {
			Connection conn;

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/openmind?characterEncoding=UTF-8";
			conn = DriverManager.getConnection(url, mysqlUser, mysqlPass);

			if(StringUtils.equals("solve", action) && StringUtils.isEmpty(objectClass) && StringUtils.isEmpty(relation)){
				
				for(ClassRelation classRel : classRelList){
					System.out.println("#######################################");
					System.out.println(classRel.toString());
					ResultSet ents =  getEntitiesByClass(conn, classRel.objectClass);
					int count = 0;
					
					try {
						  while (ents.next()) {
							  executeEntity(conn, ents.getLong("id"), ents.getLong("modification_time"), ents.getString("object_class"), classRel.relName, action);
							  count++;
							  
							  if(count % 50 == 0){
								  System.out.print("*");
							  }
							  if(count % 300 == 0){
								  System.out.println(count);
							  }
						  }
					} finally {
						ents.close();
					}
					System.out.println();
				}
				
				
			}else{
				ResultSet ents =  getEntitiesByClass(conn, objectClass);
				int count = 0;
				
				try {
					  while (ents.next()) {
						  executeEntity(conn, ents.getLong("id"), ents.getLong("modification_time"), ents.getString("object_class"), relation, action);
						  count++;
						  
						  if(count % 50 == 0){
							  System.out.print("*");
						  }
						  if(count % 300 == 0){
							  System.out.println(count);
						  }
					  }
				} finally {
					ents.close();
				}
			}
			

			System.out.println("#### Finished ####");

			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	public static void test(){
		try {
			Connection conn;

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/openmind?characterEncoding=UTF-8";
			conn = DriverManager.getConnection(url, "ismi", "ismipw");

			executeEntity(conn, Long.parseLong("297238"), Long.parseLong("1405366164815"), "WITNESS", "show", null);

			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * 
	 * @param args
	 *            objectClass, mysqlUser, mysqlPassword, action
	 *            
	 * action: 
	 * 	- show: show all
	 *  - showExemplarOf: for witnesses, it shows the relation "is_exemplar_of" with problems
	 *  - solveExemplarOf: for witnesses, it solves the relation "is_exemplar_of" with problems
	 *  - showPartOf
	 *  - solvePartOf
	 */
	public static void main(String[] args) {
		
		//test();
		if (args.length == 3) {
			execute(args[0], args[1], args[2], null, null);
		}else if(args.length == 4){
			execute(args[0], args[1], args[2], args[3], null);
		}else if(args.length == 5){
			execute(args[0], args[1], args[2], args[3], args[4]);
		} else {
			System.out
					.println("Parameter/s no found: They should be: mode(SHOW/REDUCE), mysql_user, mysql_password");
			System.out.println("args.length= " + args.length);
			System.out.println(Arrays.toString(args));

		}
	
		System.exit(0);
	}
	
	public static class ClassRelation{
		public String objectClass;
		public String relName;
		
		public ClassRelation(String objectClass, String relName){
			this.objectClass = objectClass;
			this.relName = relName;
		}
		
		@Override
		public String toString(){
			return this.objectClass + " [" + relName + "]";
		}
	}
	
	public static class Relation{
		
		public Long id;
		public Long rowId;
		public Long srcModif;
		public Long tarModif;
		public Long srcId;
		public Long tarId;
		public String label;
		public Long modifTime;
		
		public Relation(ResultSet rs) throws SQLException{
			this.id = rs.getLong("id");
			this.rowId = rs.getLong("row_id");
			this.srcModif = rs.getLong("source_modif");
			this.tarModif = rs.getLong("target_modif");
			this.srcId = rs.getLong("source_id");
			this.tarId = rs.getLong("target_id");
			this.label = rs.getString("object_class");
			this.modifTime = rs.getLong("modification_time");
		}
		
		@Override
		public String toString(){
			return this.rowId + "\t" + this.id + "\t" + this.label + "\t" + this.srcId + "\t" + this.tarId + "\t" + this.srcModif + "\t" + this.tarModif;
		}
	}
}