import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Scanner;

import oracle.jdbc.OracleTypes;

public class Task1 {

	private Connection conn = null;
	private String driver = "oracle.jdbc.driver.OracleDriver";

	// 1.2.1
	// constructor
	public Task1(String connectionUrl, String username, String password) throws ClassNotFoundException, SQLException {
			connect(connectionUrl, username, password);
	}

	private void connect(String connectionUrl, String username, String password) {
		try {
			Class.forName(this.driver);
			this.conn = DriverManager.getConnection(connectionUrl, username, password);
			this.conn.setAutoCommit(false);
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}

	// 1.2.2
	// The function reads the file content and inserts it to the MediaItems table
	public void fileToDataBase(String csvFilePath) throws IOException, SQLException, SQLSyntaxErrorException {
		try {
			// csv file: [title, production year]
			BufferedReader reader = new BufferedReader(new FileReader(csvFilePath ));//+ "\\films.csv"));

			String line = null;
			Scanner scanner = null;

			while ((line = reader.readLine()) != null) {
				// comma separated values
				scanner = new Scanner(line);
				scanner.useDelimiter(",");

				// extract title and year from media item
				String title = null;
				String year = null;

				while (scanner.hasNext()) {
					title = scanner.next();
					year = scanner.next();
				}

				/*
				System.out.println(line);
				System.out.println(title);
				System.out.println(year);
				*/

				// insert values to MediaItems table
				String query = "INSERT INTO MEDIAITEMS (TITLE, PROD_YEAR) VALUES (?,?)";

				PreparedStatement preparedStatement = this.conn.prepareStatement(query);

				preparedStatement.setString(1, title);
				preparedStatement.setInt(2, Integer.parseInt(year));

				preparedStatement.executeUpdate();

				this.conn.commit();

				preparedStatement.close();
			}

			reader.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 1.2.3
	// The function calculates the similarity between every pair of items in the MediaItems table
	public void calculateSimilarity() {
		try {
			int maxDistance = 0;

			// find up to which index to loop through
			int startMIDLoop = 0;
			int endMIDLoop = 0;

			String endMIDLoopQuery = "SELECT MAX (MID) FROM MEDIAITEMS";
			PreparedStatement preparedStatement = conn.prepareStatement(endMIDLoopQuery);
			ResultSet result = preparedStatement.executeQuery();

			if(result.next()) {
				endMIDLoop = result.getInt(1);
				//System.out.println(result.getInt(1));
			}

			result.close();
			preparedStatement.close();

			// calculate max for the similarity function
			CallableStatement callableStatement = conn.prepareCall("{ ? = call MaximalDistance() }");
			callableStatement.registerOutParameter(1, OracleTypes.NUMBER);
			callableStatement.execute();
			maxDistance = callableStatement.getInt(1);
			//System.out.println(maxDistance);

			callableStatement.close();

			// calculate similarity between every pair using the similarity function
			for(int i = 0; i <= endMIDLoop; i++) {
				for(int j = 0; j <= endMIDLoop; j++)
				{
					if(i != j) {
						// calculate distance between i & j
						CallableStatement callableStatementSim = this.conn.prepareCall("{ ? = call SimCalculation(?,?,?)  }");
						callableStatementSim.registerOutParameter(1, OracleTypes.FLOAT);
						callableStatementSim.setInt(2, i);
						callableStatementSim.setInt(3, j);
						callableStatementSim.setInt(4, maxDistance);
						callableStatementSim.execute();
						float distance = callableStatementSim.getFloat(1);
						callableStatementSim.close();

						PreparedStatement preparedStatementSim = conn.prepareStatement("SELECT * FROM SIMILARITY WHERE MID1 = ? AND MID2 = ?");
						preparedStatementSim.setInt(1, i);
						preparedStatementSim.setInt(2, j);
						resultSim = preparedStatementSim.executeQuery();
						if(rs.next()) {
							//UPDATE THE SIMILARITY BETWEEN MID1 MID2
							PreparedStatement update = conn.prepareStatement("UPDATE SIMILARITY SET SIMILARITY = ? WHERE MID1 = ? AND MID2 = ?");
							update.setFloat(1, distance);
							update.setInt(2, i);
							update.setInt(3, j);
							update.executeUpdate();
							update.close();
						}
						else
						{
							//INSERT SIMILARITY
							PreparedStatement update = conn.prepareStatement("INSERT INTO SIMILARITY (MID1, MID2, SIMILARITY) VALUES (?,?,?)");
							update.setInt(1, i);
							update.setInt(2, j);
							update.setFloat(3, distance);
							update.executeUpdate();
							update.close();
						}

						preparedStatementSim.close();
						rs.close();
					}
				}
			}

		} catch (Exception e) {
			e.printStackTrace();;
		}
		/*

			for(int i=0; i < maxInd; i++) {
				for(int j = i+1; j <= maxInd ; j++)
				{
					if(i == j)
						continue;
					CallableStatement csLoop = conn.prepareCall("{ ? = call SimCalculation(?,?,?)  }");
					csLoop.registerOutParameter(1, OracleTypes.FLOAT);
					csLoop.setInt(2, i);
					csLoop.setInt(3, j);
					csLoop.setInt(4, maxDist);
					csLoop.execute();
					float midsDistance = csLoop.getFloat(1);
					csLoop.close();

					PreparedStatement psLoop = conn.prepareStatement("SELECT * FROM SIMILARITY WHERE MID1 = ? AND MID2 = ?");
					psLoop.setInt(1, i);
					psLoop.setInt(2, j);
					rs = psLoop.executeQuery();
					if(rs.next()) {
						//UPDATE THE SIMILARITY BETWEEN MID1 MID2
						PreparedStatement update = conn.prepareStatement("UPDATE SIMILARITY SET SIMILARITY = ? WHERE MID1 = ? AND MID2 = ?");
						update.setFloat(1, midsDistance);
						update.setInt(2, i);
						update.setInt(3, j);
						update.executeUpdate();
						update.close();
					}
					else
					{
						//INSERT SIMILARITY
						PreparedStatement update = conn.prepareStatement("INSERT INTO SIMILARITY (MID1, MID2, SIMILARITY) VALUES (?,?,?)");
						update.setInt(1, i);
						update.setInt(2, j);
						update.setFloat(3, midsDistance);
						update.executeUpdate();
						update.close();
					}
					psLoop.close();
					rs.close();
				}
			}
		} catch (Exception e) {
			System.out.println("Exception Catched!");
		}*/

	}

	public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException{
		final String username = "shacharo";
		final String password = "abcd";
		final String connectionUrl = "jdbc:oracle:thin:@ora1.ise.bgu.ac.il:1521/oracle";

		Task1 task = new Task1(connectionUrl, username, password);
		// 1.2.1
		//task.fileToDataBase("d:\\documents\\users\\rinag\\Documents\\films.csv");

		// 1.2.2
		task.calculateSimilarity();
	}
}