import java.awt.List;
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.HashMap;
import java.util.LinkedList;
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));

			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();

			//System.out.println("Successfully added CSV rows to database!");
		} 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 {
			// maximum distance between a pair of MIDs (used in the SimCalculation formula)
			int maxDistance = 0;

			// up to which index to loop through (max MID)
			int endMIDLoop = 0;

			// find endMIDLoop value
			String endMIDLoopQuery =
					"SELECT MAX (MID) "
					+ "FROM MEDIAITEMS";

			PreparedStatement preparedStatement = this.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 = this.conn.prepareCall("{ ? = call MaximalDistance() }");

			// MaximalDistance output value
			callableStatement.registerOutParameter(1, OracleTypes.NUMBER);

			callableStatement.execute();

			maxDistance = callableStatement.getInt(1);
			//System.out.println(maxDistance);

			callableStatement.close();

			// calculate similarity between every MID pair using the similarity function
			for(int i = 0; i <= endMIDLoop; i++) {
				for(int j = i + 1; j <= endMIDLoop; j++)
				{
					// don't compare identical MIDs
					if(i != j) {
						// calculate distance between MIDi & MIDj
						float MIDiMIDjDistance = 0;
						CallableStatement callableStatementSim = this.conn.prepareCall("{ ? = call SimCalculation(?,?,?)  }");

						// SimCalculation output value
						callableStatementSim.registerOutParameter(1, OracleTypes.FLOAT);

						// SimCalculation arguments: MIDi, MIDj, maxDistance
						callableStatementSim.setInt(2, i);
						callableStatementSim.setInt(3, j);
						callableStatementSim.setInt(4, maxDistance);

						callableStatementSim.execute();

						MIDiMIDjDistance = callableStatementSim.getFloat(1);

						callableStatementSim.close();

						// get current similarity value of MIDi and MIDj in Similarity table
						PreparedStatement preparedStatementSim = this.conn.prepareStatement(
								"SELECT * "
								+ "FROM SIMILARITY "
								+ "WHERE MID1 = ? AND MID2 = ?");

						preparedStatementSim.setInt(1, i);
						preparedStatementSim.setInt(2, j);

						ResultSet resultSim = preparedStatementSim.executeQuery();

						// if there already is an out-dated value in the table, then update it according to MIDiMIDjDistance
						if(resultSim.next()) {
							PreparedStatement update = this.conn.prepareStatement(
									"UPDATE SIMILARITY "
									+ "SET SIMILARITY = ? "
									+ "WHERE MID1 = ? AND MID2 = ?");

							update.setFloat(1, MIDiMIDjDistance);
							update.setInt(2, i);
							update.setInt(3, j);

							update.executeUpdate();

							update.close();

							//System.out.println("Successfully updated database for MID" + i + " and MID " + j +"!");
						}
						// otherwise, just add MIDiMIDjDistance to the table
						else
						{
							PreparedStatement update = this.conn.prepareStatement(
									"INSERT INTO SIMILARITY "
									+ "(MID1, MID2, SIMILARITY) "
									+ "VALUES (?,?,?)");

							update.setInt(1, i);
							update.setInt(2, j);
							update.setFloat(3, MIDiMIDjDistance);

							update.executeUpdate();

							update.close();

							//System.out.println("Successfully added similarity of MID " + i + " MID " + j + " to database!");
						}

						preparedStatementSim.close();
						resultSim.close();
					}
				}
			}

			// overall 33*32 = 1056 rows
			this.conn.commit();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* The function retrieves in ascending order from the database all the items that the similarity
	between them and the given item is at least 0.3. */
	public void printSimilarItems(long mid) throws SQLException {
		// [MID, Similarity]
		HashMap<Integer, Float> similarities = new HashMap<Integer, Float>();
		ArrayList<Integer> orderedMIDs = new ArrayList<Integer>();

		String query =
				"SELECT MID1, MID2, SIMILARITY "
				+ "From Similarity "
				+ "WHERE SIMILARITY >= 0.3 AND MID1 = ? OR MID2 = ? AND MID1 != MID2 "
				+ "ORDER BY SIMILARITY ASC";

		PreparedStatement preparedStatement = this.conn.prepareStatement(query);
		preparedStatement.setInt(1, (int)mid);
		preparedStatement.setInt(2, (int)mid);

		ResultSet result = preparedStatement.executeQuery();

		while(result.next()) {
			int mid1 = result.getInt(1);
			int mid2 = result.getInt(2);
			float sim = result.getFloat(3);

			if(mid1 != (int)mid && !similarities.containsKey(mid1)) {
				similarities.put(mid1, sim);
				orderedMIDs.add(mid1);
			}

			if(mid2 != (int)mid && !similarities.containsKey(mid2)) {
				similarities.put(mid2, sim);
				orderedMIDs.add(mid2);
			}
		}

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

		// The function prints all the titles and the similarity value of the similar items
		for(int i = 0; i < orderedMIDs.size(); i++) {
			PreparedStatement preparedStatementTitles = conn.prepareStatement("SELECT TITLE FROM MEDIAITEMS WHERE MID = ?");
			preparedStatementTitles.setInt(1, orderedMIDs.get(i));

			ResultSet resultTitles = preparedStatementTitles.executeQuery();

			if(resultTitles.next()) {
				// print title of the MID & the similarity
				System.out.println(resultTitles.getString(1) + ", " + similarities.get(orderedMIDs.get(i)));
			}

			preparedStatementTitles.close();
			resultTitles.close();
		}

		//System.out.println(similarities);
	}

	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();

		//1.2.3
		//task.printSimilarItems(1);
	}
}