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.List;

import org.apache.commons.lang.StringUtils;

/**
 * Some relation, which are marked as CURRENT_VERSION,
 * has not the correct srcTimeModif, 
 * which is necessary to be found from the corresponding entity associated.
 * This script does the following:
 * -Find the relation with incorrect srcModifTime
 * -
 * @author jurzua
 *
 */
public class CurrentVersionSrcRelation {
	
	public static String WITNESS = "WITNESS";
	public static String TEXT = "TEXT";
	public static String CODEX = "CODEX";
	public static String PLACE = "PLACE";
	public static String PERSON = "PERSON";
	public static String REPOSITORY = "REPOSITORY";
	public static String COPY_EVENT = "COPY_EVENT";
	public static String COLLECTION = "COLLECTION";
	public static String REFERENCE = "REFERENCE";
	public static String ROLE = "ROLE";
	public static String STUDY_EVENT = "STUDY_EVENT";
	public static String SUBJECT = "SUBJECT";
	public static String TRANSFER_EVENT = "TRANSFER_EVENT";
	
	private static String query;
	private static List<Relation> uniqueRelations;
	
	static{
		query = 
			"select " +
		"entity.id as entId, entity.own_value as entOW, entity.modification_time as entModif, entity.version as entVersion,   " +
		"relation.id as relId, relation.source_modif as relSrcModif, relation.version as relVersion, relation.own_value as relOW, " +
		"relation.source_obj_class as relSrcOC, relation.target_obj_class as relTarOC, " +
		"relation.source_id as relSrcId, " +
		"relation.target_id as relTarId, " +
		"relation.row_id as relRowId " +
		"from node as entity, node as relation   " +
		"where   " +
		"relation.node_type = 'RELATION' AND   " +
		"entity.node_type = 'ENTITY' AND   " +
		"relation.system_status = 'CURRENT_VERSION' AND   " +
		"entity.system_status = 'CURRENT_VERSION' AND   " +
		"relation.source_id = entity.id AND " +
		"relation.source_modif != entity.modification_time ";// +
		//"group by entity.id ";
		
		CurrentVersionSrcRelation tthis = new CurrentVersionSrcRelation();
		uniqueRelations = new ArrayList<CurrentVersionSrcRelation.Relation>();
		uniqueRelations.add(tthis.new Relation(WITNESS, "is_exemplar_of", TEXT));
		uniqueRelations.add(tthis.new Relation(WITNESS, "is_part_of", CODEX));
		uniqueRelations.add(tthis.new Relation(WITNESS, "was_copied_by", PERSON));
		uniqueRelations.add(tthis.new Relation(WITNESS, "was_copied_in", PLACE));
		uniqueRelations.add(tthis.new Relation(WITNESS, "was_created_by", PERSON));
		uniqueRelations.add(tthis.new Relation(WITNESS, "was_created_in", PLACE));
		
		uniqueRelations.add(tthis.new Relation(PLACE, "is_part_of", PLACE));
		
		uniqueRelations.add(tthis.new Relation(REPOSITORY, "is_in", PLACE));
		
		uniqueRelations.add(tthis.new Relation(COLLECTION, "is_part_of", REPOSITORY));
		
		uniqueRelations.add(tthis.new Relation(CODEX, "is_part_of", COLLECTION));
		uniqueRelations.add(tthis.new Relation(CODEX, "is_part_of", REPOSITORY));
		
		uniqueRelations.add(tthis.new Relation(PERSON, "was_born_in", PLACE));
		uniqueRelations.add(tthis.new Relation(PERSON, "died_in", PLACE));
		
		uniqueRelations.add(tthis.new Relation(COPY_EVENT, "is_a_copy_of", WITNESS));
		uniqueRelations.add(tthis.new Relation(COPY_EVENT, "was_copied_in", PLACE));
		uniqueRelations.add(tthis.new Relation(COPY_EVENT, "was_copied_in", REPOSITORY));
		uniqueRelations.add(tthis.new Relation(COPY_EVENT, "was_copied_for", PERSON));
		
		uniqueRelations.add(tthis.new Relation(STUDY_EVENT, "is_a_study_of", WITNESS));
		uniqueRelations.add(tthis.new Relation(STUDY_EVENT, "was_advised_by", PERSON));
		uniqueRelations.add(tthis.new Relation(STUDY_EVENT, "was_studied_by", PERSON));
		uniqueRelations.add(tthis.new Relation(STUDY_EVENT, "was_studied_in", REPOSITORY));
		uniqueRelations.add(tthis.new Relation(STUDY_EVENT, "was_studied_in", PLACE));
				
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "has_new_location", PLACE));
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "has_new_location", REPOSITORY));
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "has_original_location", PLACE));
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "has_original_location", REPOSITORY));
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "was_transferred_from", PLACE));
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "was_transferred_from", REPOSITORY));
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "was_transferred_in", PLACE));
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "was_transferred_in", REPOSITORY));
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "was_transferred_to", PLACE));
		uniqueRelations.add(tthis.new Relation(TRANSFER_EVENT, "was_transferred_to", REPOSITORY));
				
		uniqueRelations.add(tthis.new Relation(TEXT, "is_version_of", TEXT));
		uniqueRelations.add(tthis.new Relation(TEXT, "was_created_by", PERSON));
		uniqueRelations.add(tthis.new Relation(TEXT, "was_created_in", PLACE));
		uniqueRelations.add(tthis.new Relation(TEXT, "is_translation_of", TEXT));
		uniqueRelations.add(tthis.new Relation(TEXT, "was_dedicated_to", TEXT));
		uniqueRelations.add(tthis.new Relation(TEXT, "is_commentary_on", TEXT));
	}			
				
	public static ResultSet select(Connection conn, String arg){
		CurrentVersionSrcRelation tthis = new CurrentVersionSrcRelation();
		ResultSet rs = null;
		try{	
			Statement st = conn.createStatement();
			rs = st.executeQuery(query);
			int count = 0;
			int case1 =0;
			int case2 =0;
			int case3 =0;
			while (rs.next()){
				Entity ent = tthis.new Entity(rs);
				Relation rel = tthis.new Relation(rs);
				
				System.out.println(rel.toString());
				
				if(rel.srcId.equals(new Long(51270))){
					System.out.println("*****************");					
				}
				
				Relation otherRel = getCurrentRelation(ent, rel, conn);
				if(otherRel == null){
					System.out.println("\tCASE1");
					case1++;
					makeRelationCurrentVersion(conn, rel, ent);
				}else if(shouldBeRelationUniqueForMetadaten(rel)){
					System.out.println("\tCASE2");
					case2++;
					makeRelationPreviuosVersion(conn, rel);
				}else{
					System.out.println("\tCASE3");
					case3++;
					makeRelationCurrentVersion(conn, rel, ent);
				}
				count++;
			}
			System.out.println(	"*****************");
			System.out.println("CASE1=" + case1 + " no exist other similar relation, the old should be refreshed");
			System.out.println("CASE2=" + case2 + " this relation should be unique, but there is more than one (DELETING).");
			System.out.println("CASE3=" + case3 + " this relation can exist more tha one instance");
			System.out.println("TOTAL=" + count);
			System.out.println("*****************");
	    }catch (SQLException ex){
	    	ex.printStackTrace();
	    }		
	    return rs;
	}
	
	
	public static void makeRelationPreviuosVersion(Connection conn, Relation rel) throws SQLException{
		System.out.println("Making PREVIOUS...");
		Statement st = conn.createStatement();
		//st.executeUpdate("DELETE FROM node WHERE row_id = '"+ rel.rowId + "'");
		st.executeUpdate("UPDATE node SET system_status='PREVIOUS_VERSION' WHERE row_id='"+ rel.rowId +"'");
	}
	
	public static void makeRelationCurrentVersion(Connection conn, Relation rel, Entity ent) throws SQLException{
		System.out.println("Making Current Version...");
		Statement st = conn.createStatement();
		st.executeUpdate("UPDATE node SET source_modif='" + ent.modif + "' WHERE row_id='"+ rel.rowId +"'");
	}
	
	public static boolean shouldBeRelationUniqueForMetadaten(Relation rel){
		if(uniqueRelations.contains(rel)){
			return true;
		}
		return false;
	}
	
	public static Relation getCurrentRelation(Entity ent, Relation rel, Connection conn){
		ResultSet rs = null;
		CurrentVersionSrcRelation tthis = new CurrentVersionSrcRelation();
		
		String relQuery = 
		"select " +
		"relation.id as relId, " +
		"relation.source_modif as relSrcModif, " +
		"relation.version as relVersion, " +
		"relation.own_value as relOW, " +
		"relation.source_id as relSrcId, " +
		"relation.target_id as relTarId, " +
		"relation.source_obj_class as relSrcOC, " +
		"relation.target_obj_class as relTarOC, " +
		"relation.row_id as relRowId " +
		"from node as relation " +
		"where   " +
		"relation.node_type = 'RELATION' AND   " +
		"relation.system_status = 'CURRENT_VERSION' AND   " +
		"relation.source_id = '" + rel.srcId + "' AND " +
		//"relation.target_id = '" + rel.tarId + "' AND " +
		"relation.source_modif = '" + ent.modif + "' ";
		
		try{
			Statement st = conn.createStatement();
			rs = st.executeQuery(relQuery);
			Relation otherRel = null;
			while(rs.next()){
				otherRel = tthis.new Relation(rs);	
				break;
			}
			return otherRel;
		}catch (SQLException ex){
	    	ex.printStackTrace();
	    }
		
		return null;
	}
	
	
	
	public static void execute() {
		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");
			
			ResultSet rs = select(conn, "");
						
			conn.close();
		} catch (ClassNotFoundException ex) {
			System.err.println(ex.getMessage());
		} catch (IllegalAccessException ex) {
			System.err.println(ex.getMessage());
		} catch (InstantiationException ex) {
			System.err.println(ex.getMessage());
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}
	
	public static void main(String[] args) {
		
		CurrentVersionSrcRelation.execute();
		System.exit(0);
	}
	
	private class Entity{
		public Long id;
		public Long modif;
		public String ow;
		public Entity(ResultSet rs){
			try {
				//"entity.id as entId, entity.own_value as entOW, entity.modification_time as entModif, entity.version as entVersion,   " +
				this.modif = rs.getLong("entModif");
				this.id = rs.getLong("entId");
				this.ow = rs.getString("entOW");				
			} catch (SQLException e) {
				System.out.println();
				e.printStackTrace();
				System.out.println();
			}
		}
	}
	
	private class Relation{
		public Long id;
		public Long srcModif;
		public Long srcId;
		public Long tarId;
		public String ow;
		public String srcOC;
		public String tarOC;
		public String rowId;
		
		public Relation(String srcOC, String ow, String tarOC){
			this.ow = ow;
			this.srcOC = srcOC;
			this.tarOC = tarOC;
		}
		
		public Relation(ResultSet rs){
			//s relId, relation.source_modif as relSrcModif, relation.version as relVersion, relation.own_value as relOW
			try {
				this.srcModif = rs.getLong("relSrcModif");
				this.id = rs.getLong("relId");
				this.ow = rs.getString("relOW");
				this.srcOC = rs.getString("relSrcOC");
				this.tarOC = rs.getString("relTarOC");
				this.rowId = rs.getString("relRowId");
				this.srcId = rs.getLong("relSrcId");
				this.tarId = rs.getLong("relTarId");
			} catch (SQLException e) {
				System.out.println();
				e.printStackTrace();
				System.out.println();
			}			
		}
		@Override
		public boolean equals(Object obj){
			if(obj != null){
				if(obj instanceof Relation){
					Relation other = (Relation)obj;
					if(StringUtils.equals(this.ow, other.ow) &&
							StringUtils.equals(this.srcOC, other.srcOC) &&
							StringUtils.equals(this.tarOC, other.tarOC)){
						return true;
					}
				}
			}
			return false;
		}
		
		@Override
		public String toString(){
			StringBuilder sb = new StringBuilder();
			sb.append("ROW_ID=" + this.rowId + "\t");
			sb.append(this.srcOC + " [" + this.ow + "] " + this.tarOC + "\t SrcID=" + this.srcId + "\t TarID=" + this.tarId);
			return sb.toString();
		}
	}
}