import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CosineSimilarity {
	//jdbc variables
	private static final String URL = "jdbc:mysql://localhost/hadoop";
    private static final String USER = "hadoop";
    private static final String PASSWORD = "binavgd";

	public static void main(String[] args) {
		//for every 0 rating
		try {
            Connection connect = DriverManager.getConnection(URL, USER, PASSWORD);
            Statement stmt = connect.createStatement();
            Statement stmt1 = connect.createStatement();
            ResultSet rs, rs1;
            int uid, similarUid, predictedRating = 0;
            double similarity, mostSimilarity = -1;
            String isbn;
            rs = stmt.executeQuery("SELECT * FROM `BX-Book-Ratings` WHERE `Book-Rating` = 0");
            while(rs.next()){
            	predictedRating = 0;
				uid = rs.getInt("User-ID");
				isbn = rs.getString("ISBN");
				/*search similarity among users
				* see what other users have rated this book
				*/
				rs1 = stmt1.executeQuery("SELECT * FROM `BX-Book-Ratings` WHERE `ISBN` ='" + isbn +
						"' AND `Book-Rating`>0");
				while(rs1.next()){
					similarUid = rs1.getInt("User-ID");
					similarity = getSimilarity(uid, similarUid);
					if(similarity > 0.8 && similarity > mostSimilarity){
						mostSimilarity = similarity;
						predictedRating = rs1.getInt("Book-Rating");
					}
				}//while rs1 ends
				rs1 = stmt1.executeQuery("UPDATE `BX-Book-Ratings` SET `Book-Rating`=" + predictedRating +
						" WHERE `ISBN`='" + isbn + "' AND `User-ID`=" + uid);
			}//while rs ends
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	private static double getSimilarity(int uid, int similarUid)  {
		double age1 =0, age2=0;
		double lat1, lat2=0, lng1, lng2=0, num=0, denom=1;
		try {
            Connection connect = DriverManager.getConnection(URL, USER, PASSWORD);
            Statement stmt = connect.createStatement();
            Statement stmt1 = connect.createStatement();
            ResultSet rs, rs1;
            rs = stmt.executeQuery("SELECT * from `Users_Normalized` WHERE `User-ID`="+uid);
            while (rs.next()) {
            	age1 = rs.getDouble("Age");
            	lat1 = rs.getDouble("Latitude");
            	lng1 = rs.getDouble("Longitude");
				rs1 = stmt1.executeQuery("SELECT * from `Users_Normalized` WHERE `User-ID`=" + similarUid);
				while(rs1.next()){
					age2 = rs1.getDouble("Age");
	            	lat2 = rs1.getDouble("Latitude");
	            	lng2 = rs1.getDouble("Longitude");
				}//rs1 ends
				num = (age1*age2)+(lat1*lat2)+(lng1*lng2);
				denom = Math.sqrt((age1*age1)+(lat1*lat1)+(lng1*lng1));
				denom *= Math.sqrt((age2*age2)+(lat2*lat2)+(lng2*lng2));
			}//rs ends
		}catch(Exception e){
			e.printStackTrace();
		}
		return num/denom;
	}

}