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;

public class WitnessCreatedByText {

	private static String query = "select row_id, source_id, target_id, id from node "
			+ "where node_type = 'RELATION' "
			+ "and source_obj_class = 'WITNESS' "
			+ "and target_obj_class = 'PERSON' "
			+ "and own_value = 'was_created_by' "
			+ "and system_status = 'CURRENT_VERSION'";

	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, "root", "e1nste1n");

			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 ResultSet select(Connection conn) {
		ResultSet rs = null;
		try {
			System.out
					.println("\tID\t[sourceId, targetId]");
			Statement st = conn.createStatement();
			rs = st.executeQuery(query);
			int count = 0;
			while (rs.next()) {
				Statement st0 = conn.createStatement();
				String id = rs.getString("id");
				String source_id = rs.getString("source_id");
				String target_id = rs.getString("target_id");
				String row_id = rs.getString("row_id");
				st0.executeUpdate("DELETE FROM node WHERE row_id = '"+ row_id + "'");
				count++;
				System.out.println(count + "\t" + id + "\t" + "[" + source_id + ", " + target_id + "]");
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return rs;
	}

	public static void main(String[] args) {

		WitnessCreatedByText.execute();
		System.exit(0);
	}

}