/*
 * Second Assessed Coursework Database Systems 3
 * @author: Nelly Temelkova
 * @date 15/03/2016
 * GUID: 2091751t
 * */

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

public class Lev3152091751t {

	static Connection connection = null;
	static Statement statement;

	// Methods for Queries
	public static void method3A(){

		try {
			int i=1;

			//Fetch SQL statement for first query
			ResultSet result = statement.executeQuery("SELECT AVG(release.rating)"
					+ "FROM release "
					+ "WHERE release.rating >= 8;");

			//Display results, loop around Resultset
			System.out.println("	Results for Method3A:");
			while(result.next()){
				System.out.println("[" + i + "] " +
			result.getString("AVG"));
				i++;
			}
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void method3B(){
		try {
			int i=1;

			//Fetch SQL statement for second query
			ResultSet result=statement.executeQuery("SELECT member.name "
					+ "FROM member "
					+ "INNER JOIN memberof "
					+ "ON member.mid = memberof.mid "
					+ "AND (member.stillalive = 'Y') AND (memberof.endyear IS NULL) "
					+ "GROUP BY member.name "
					+ "HAVING (COUNT(DISTINCT memberof.bid) > 1) AND (COUNT( DISTINCT memberof.instrument) > 1);");

			//Display results, loop around Resultset
			System.out.println("	Results for Method3b:");
			while(result.next()){
				System.out.println("[" + i + "] " +
			result.getString("name"));
				i++;
			}
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void method3C(){
		try {
			int i=1;

			//Fetch SQL statement for third query
			ResultSet result=statement.executeQuery("SELECT band.name, D.num_of_members "
					+ "FROM( "
						+ "SELECT bid, COUNT(DISTINCT memberof.mid) AS num_of_members "
						+ "FROM memberof "
						+ "WHERE memberof.endyear IS NULL "
						+ "GROUP BY memberof.bid) AS D NATURAL JOIN band "
							+ "WHERE D.num_of_members = (SELECT MAX(num) "
							+ "FROM (SELECT COUNT(DISTINCT mid) AS num "
								+ "FROM memberof"
								+ " WHERE memberof.endyear IS NULL "
								+ "GROUP BY bid) count); ");

			//Display results, loop around Resultset
			System.out.println("	Results for Method3C:");
			while(result.next()){
				System.out.println("[" + i + "] " +
			result.getString("name") + "	" +  result.getShort("num_of_members"));
				i++;
			}
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] argv) {

		// load the JDBC driver
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("Could not find JDBC Driver");
			e.printStackTrace();
			return;
		} // try-catch exception
		// the driver is loaded...

		System.out.println("PostgreSQL JDBC Driver found!");

		// proceed with a database connection
		// connect to the yacata.dcs.gla.ac.uk server, on port:5432, with
		// username: lev3_15_username
		// password: username
		try {
			connection = DriverManager.getConnection("jdbc:postgresql://yacata.dcs.gla.ac.uk:5432/", "lev3_15_2091751t",
					"2091751t");
		} catch (SQLException e) {
			System.out.println("Connection Failed!");
			e.printStackTrace();
			return;
		} // try-catch exception
		// connection to the database is done!

		if (connection != null) {
			try {
				statement= connection.createStatement();
				System.out.println("Controlling your database...");
				method3A();
				method3B();
				method3C();


				statement.close(); // close statement
				connection.close(); // do not forget to close the connection to the database!
			} catch (SQLException e) {
				e.printStackTrace();
			} // try-catch exception
		} else {
			System.out.println("Failed to establish connection!");
		} // if-else
	}// main
}