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 org.mpi.openmind.cache.WrapperService;
import org.mpi.openmind.repository.bo.Entity;
import org.mpi.openmind.repository.services.ServiceRegistry;

public class SicilyCase {

	//public static String USER = "script-repo-name";
	
/*

select pl1.source_id as PL1, pl1.id, pl2.id, pl1.own_value from openmind.`node` as pl1, openmind.`node` as pl2
where

pl1.node_type = 'RELATION' and
pl2.node_type = 'RELATION' and
pl1.system_status = 'CURRENT_VERSION' and
pl2.system_status = 'CURRENT_VERSION' and
pl1.source_obj_class = 'PLACE' and
pl2.source_obj_class = 'PLACE' and
pl1.target_obj_class = 'PLACE' and
pl2.target_obj_class = 'PLACE' and
pl1.source_id = pl2.source_id and
pl1.id != pl2.id
 	
 */
	
	
	private WrapperService ontology;
	private static String sql =
			"select " + 
			"pl1.source_id as srcId, " + 
			"pl1.id as rel1Id, " + 
			"pl2.id as rel2Id, " + 
			"pl1.own_value, " +
			"pl1.target_id as tar1Id, " +
			"pl2.target_id as tar2Id " +
			"from openmind.`node` as pl1, openmind.`node` as pl2 " +
			"where " +
			"pl1.node_type = 'RELATION' and " +
			"pl2.node_type = 'RELATION' and " +
			"pl1.system_status = 'CURRENT_VERSION' and " +
			"pl2.system_status = 'CURRENT_VERSION' and " +
			"pl1.source_obj_class = 'PLACE' and " +
			"pl2.source_obj_class = 'PLACE' and " +
			"pl1.target_obj_class = 'PLACE' and " +
			"pl2.target_obj_class = 'PLACE' and " +
			"pl1.source_id = pl2.source_id and " +
			"pl1.id != pl2.id";
	
	public SicilyCase(WrapperService ot){
		this.ontology = ot;
	}
	
	public 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");
			
			
			Statement st = conn.createStatement();
			
			
			 
			
			System.out.println("Executing:");
			System.out.println(sql);
			ResultSet rs = st.executeQuery(sql);
			
			System.out.println("srcId\ttar1\ttar2");
			
			while(rs.next()){
				 Long srcId = rs.getLong("srcId");
				 Long tar1Id = rs.getLong("tar1Id");
				 Long tar2Id = rs.getLong("tar2Id");
				 Long rel1Id = rs.getLong("rel1Id");
				 Long rel2Id = rs.getLong("rel2Id");
				 
				 Entity src = ontology.getEntityByIdReadOnly(srcId);
				 Entity tar1 = ontology.getEntityByIdReadOnly(tar1Id);
				 Entity tar2 = ontology.getEntityByIdReadOnly(tar2Id);
				 
				 System.out.println(src.getOwnValue() + " [" +srcId + "]\t" + tar1.getOwnValue() + " [" + tar1Id + "]\t" + tar2.getOwnValue() + " [" + tar2Id + "]");				 
			 }
			
			
			conn.close();
		
		} catch (ClassNotFoundException ex) {
			System.err.println(ex.getMessage());
			ex.printStackTrace();
		} catch (IllegalAccessException ex) {
			System.err.println(ex.getMessage());
			ex.printStackTrace();
		} catch (InstantiationException ex) {
			System.err.println(ex.getMessage());
			ex.printStackTrace();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
			ex.printStackTrace();
		}
	}
	
	
	public static void main(String[] args) {
		ServiceRegistry services = new ServiceRegistry();
		SicilyCase script = new SicilyCase(services.getWrapper());
		script.execute();
		System.exit(0);
	}
}