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.Arrays;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.mpi.openmind.repository.utils.NormalizerUtils;

public class DoubleRelations {
	
	private static String query;
	
	static{
		query = 
			"select e1.id as id1, e2.id as id2, e1.own_value as own_value, e1.target_id, e1.source_id, e1.row_id as row_id1, e2.row_id as row_id2, " +
			"e1.modification_time as time1, e2.modification_time as time2,  " +
			"e1.version as v1, e2.version as v2  " +
			"from node as e1, node as e2 " +
			"where " +
			"e1.node_type = 'RELATION' AND " +
			"e2.node_type = 'RELATION' AND " +
			"e1.system_status = 'CURRENT_VERSION' AND " +
			"e2.system_status = 'CURRENT_VERSION' AND " +
			"e1.source_id = e2.source_id AND " +
			"e1.target_id = e2.target_id AND " +
			"e1.own_value = e2.own_value AND " +
			//"e1.id > e2.id " +
			"e1.row_id > e2.row_id " +
			"group by e1.id";
	}

	public static class DoubleRelation{
		
		public Long rowId1;
		public Long id1;
		public Integer v1;
		
		public Long rowId2;
		public Long id2;
		public Integer v2;
		
		public String ov;
		public Long srcId;
		public Long tarId;
		
		public Long time1;
		public Long time2;
		
		
		public DoubleRelation(ResultSet rs) throws SQLException{
			this.id1 = rs.getLong("id1");
	        this.id2 = rs.getLong("id2");
	        this.rowId1 = rs.getLong("row_id1");
	        this.rowId2 = rs.getLong("row_id2");
	        this.ov = rs.getString("own_value");
	        this.srcId = rs.getLong("source_id");
	        this.tarId = rs.getLong("target_id");
	        this.time1  = rs.getLong("time1");
	        this.time2  = rs.getLong("time2");
	        this.v1 = rs.getInt("v1");
	        this.v2 = rs.getInt("v2");
		}
		
		@Override
		public String toString(){
			StringBuilder sb = new StringBuilder();
			
			sb.append("["+ srcId +", " + tarId + "]\t\t\t");
			sb.append("["+ id1 +", " + id2 + "]\t");
			sb.append("["+ rowId1 +", " + rowId2 + "]\t"  + ov );
    		sb.append("\t["+ time1 +", " + time2 + "]");
    		sb.append("\t["+ v1 +", " + v2 + "]");
    		return sb.toString();
		}
	}
	
	public static ResultSet select(Connection conn, String arg){
		ResultSet rs = null;
		try
	    {
			System.out.println("[sourceId, targetId]\t[id1, id2]\t[row_id1, row_id2]\townValue\t[time1, time2]\t[version1, version2]");
	      Statement st = conn.createStatement();
	      rs = st.executeQuery(query);
	      System.out.println();
	      System.out.println(query);
	      System.out.println();
	      int count = 0;
	      
	      while (rs.next()){
	    	  count++;
	    	  
	    	  DoubleRelation dr = new DoubleRelation(rs);
	    	  
	    	  /*
	        String id1 = rs.getString("id1");
	        String id2 = rs.getString("id2");
	        String row_id1 = rs.getString("row_id1");
	        String row_id2 = rs.getString("row_id2");
	        String ownValue = rs.getString("own_value");
	        String sourceId = rs.getString("source_id");
	        String targetId = rs.getString("target_id");
	        String stime1  = rs.getString("time1");
	        String stime2  = rs.getString("time2");
	        String v1 = rs.getString("v1");
	        String v2 = rs.getString("v2");
	        */
	        System.out.println(dr.toString());
	        
			if(StringUtils.isNotEmpty(arg) && arg.equals("REDUCE")){
				Statement st0 = conn.createStatement();
				//st0.executeUpdate("DELETE FROM node WHERE row_id = '"+ row_id1 + "'");
				/*
				Long time1 = null;
		        Long time2 = null;
		        Integer version1 = null;
		        Integer version2 = null;
		        try{
		        	time1 = new Long(stime1);
			        time2 = new Long(stime2);	
		        }catch (Exception e) {}
		        
		        try{
		        	version1 = new Integer(v1);
		        	version2 = new Integer(v2);
		        }catch (Exception e) {}
		        */
		        
		        //the oldest row should be deleted
		        if(dr.time1 != null && dr.time2 != null){
		        	if(dr.time1 > dr.time2){
		        		st0.executeUpdate("DELETE FROM node WHERE row_id = '"+ dr.rowId2 + "'");
		        		updateRelation(conn, dr.rowId1, dr.srcId, dr.tarId);
		        	}else{
		        		st0.executeUpdate("DELETE FROM node WHERE row_id = '"+ dr.rowId1 + "'");
		        		updateRelation(conn, dr.rowId2, dr.srcId, dr.tarId);
		        	}
		        }else if(dr.v1 != null && dr.v2 != null){
		        	if(dr.v1 > dr.v2){
		        		st0.executeUpdate("DELETE FROM node WHERE row_id = '"+ dr.rowId2 + "'");
		        		updateRelation(conn, dr.rowId1, dr.srcId, dr.tarId);
		        	}else{
		        		st0.executeUpdate("DELETE FROM node WHERE row_id = '"+ dr.rowId1 + "'");
		        		updateRelation(conn, dr.rowId2, dr.srcId, dr.tarId);
		        	}
		        }else{
		        	st0.executeUpdate("DELETE FROM node WHERE row_id = '"+ dr.rowId1 + "'");
		        	updateRelation(conn, dr.rowId2, dr.srcId, dr.tarId);
		        }
			}
	      }
	      System.out.println();
	      System.out.println(count + " relations to reduce!");
	    }
	    catch (SQLException ex){
	    	ex.printStackTrace();
	    }
	    return rs;
	}
	
	public static void updateRelation(Connection conn, Long relRowId, Long srcId, Long tarId) throws SQLException{
		Long srcTime = getModTimeOfEntity(conn, srcId);
		Long tarTime = getModTimeOfEntity(conn, tarId);
		String query = "UPDATE node SET source_modif='"+srcTime+"', target_modif='"+ tarTime +"' WHERE row_id = '"+ relRowId + "'";
		System.out.println(query);
		Statement st = conn.createStatement();
		st.executeUpdate(query);
	}
	
	public static long getModTimeOfEntity(Connection conn, Long id) throws SQLException{
		Long time = null;
		String query = "select modification_time from openmind.node where node_type = 'ENTITY' and system_status = 'CURRENT_VERSION' and id = '"+id+"'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		
		while (rs.next()){
			time = rs.getLong("modification_time");
		}
		return time;
		
	}
	
	/*
	public static void reduce(Connection conn, ResultSet rs){
		System.out.println("Reducing...");
		try {
			while (rs.next()){
				Statement st = conn.createStatement();
				
		        Long time1 = null;
		        Long time2 = null;
		        try{
		        	time1 = new Long(rs.getString("time1"));
			        time2 = new Long(rs.getString("time2"));	
		        }catch (Exception e) {}
		        
		        //the oldest row should be deleted
		        if(time1 != null && time2 != null){
		        	if(time1 > time2){
		        		st.executeUpdate("DELETE node WHERE row_id='"+ rs.getString("row_id2") + "'");
		        	}else{
		        		st.executeUpdate("DELETE node WHERE row_id='"+ rs.getString("row_id1") + "'");
		        	}
		        }else{
		        	st.executeUpdate("DELETE node WHERE row_id='"+ rs.getString("row_id1") + "'");	
		        }
		        
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}*/
	
	public static void execute(String mode, String mysqlUser, String mysqlPass) {
		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);
			//conn = DriverManager.getConnection(url, "root", "admin");
			
			ResultSet rs = select(conn, mode);
							
			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) {
		if(args.length == 3){
			DoubleRelations.execute(args[0], args[1], args[2]);
		}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);
	}
}