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

public class q1  {
  static String getRanking = "SELECT season, name, count(*) AS games,"
       +" sum(CASE WHEN points = 3 THEN 1 ELSE 0 END) AS won,"
       +" sum(CASE WHEN points = 1 THEN 1 ELSE 0 END) AS draw,"
       +" sum(CASE WHEN points = 0 THEN 1 ELSE 0 END) AS lost,"
       +" sum(points) AS points, sum(goalsFor) AS scoredGoals,"
       +" sum(goalsAgainst) AS concededGoals"
+" FROM"
+" (SELECT season, club, goalsFor, goalsAgainst,"
+" (CASE WHEN goalsFor > goalsAgainst THEN 3"
+"      WHEN goalsFor = goalsAgainst THEN 1"
      +" ELSE 0 END) AS points "
+" FROM"
 +" ((SELECT season, clubIDHome AS club,"
  +"     homeGoals As goalsFor, visitorGoals AS goalsAgainst"
+" FROM game)"
 +" UNION ALL"
+" (SELECT season, clubIDvisitor AS club,"
 +"      visitorGoals AS goalsFor, homeGoals AS goalsAgainst"
+" FROM game)) t1) t2, club"
+" WHERE season = ? AND club = clubID "
+" GROUP BY season, club"
+" ORDER BY season, points DESC"; 
  // TODO: put the ranking query here, selecting only one season
  //       you must have a ? for the season parameter
  //       (e.g. WHERE season = ?)   

  static String getGame = 
    " SELECT g.gameID FROM game g"
   + " JOIN club h ON h.clubID = g.clubIDhome"
   + " JOIN club v ON v.clubID = g.clubIDvisitor"
   + " WHERE h.name = ? and v.name = ? AND g.season = ?";

  static String getPerson = 
    " SELECT personID FROM person WHERE name=?";

  // TODO: some query strings you might write
  static String updateGame = "";
  static String maxEvent = "";  // used to get a unique fresh eventID
  static String insertEvent = ""; 
  static String deleteEvents = "";

  static Connection connect() {
    Connection conn = null;	   
    try{
      Class.forName("com.mysql.jdbc.Driver").newInstance();
    } catch (InstantiationException ie){
  	  System.out.println("Class Instantiation Exception: " + ie);
    } catch (ClassNotFoundException cnf){
  	  System.out.println("Class Not Found Exception: " + cnf);
    } catch (IllegalAccessException iae){
  	  System.out.println("Illegal Access Exception: " + iae);
    }
    try {
    	conn = DriverManager.getConnection( "jdbc:mysql://mysql1.few.vu.nl/db1-06-dbs?user=db1-06-usr&password=db1-06-pwd", "db1-06-usr", "db1-06-pwd");
      // TODO: set auto-commit off
    } catch (SQLException sqe){
  	  System.out.println("Caught SQL Exception: " + sqe);
    }
    return conn;
  }
    
  static boolean printRanking(Connection conn, String season) {
	System.out.printf ("#rank%12s%14s%14s%14s%14s%14s%14s%14s\n", "club",
    "games", "points", "won", "draw", "lost", "scoredGoals", "concededGoals");
    try {
      PreparedStatement st = conn.prepareStatement(getRanking);
      st.setString(1, season);
      ResultSet rs = st.executeQuery();
  	  for(int j=1; rs.next(); j++){
        System.out.printf ("% 3d", j);
    	for(int i=1; i<=8; i++) 
    	  System.out.printf ("%14s", rs.getString(i));
    	System.out.printf ("\n");
      }
    } catch (SQLException sqe){
      System.out.println("Caught SQL Exception: " + sqe);
      return false;
    }
    return true;
   }

  // falsify the database to make it appear a player got a red card:
  // * insert a red card event (must find a unique eventID first)
  // * remove all later events of that player
  // * adjust the game result if he had scored
  static boolean zapPlayer(Connection conn, int gameID, 
		                   int playerOut, int minute) {
    return true;
  }
 
  static int getGame(Connection conn, 
		             String home, String visitor, String season) {
    int gameID = -1;
    try {
      PreparedStatement st = conn.prepareStatement(getGame);
      st.setString(1,home);
      st.setString(2,visitor);
      st.setString(3,season);
      ResultSet rs = st.executeQuery();
      if (rs.next()) {
    	gameID = rs.getInt(1);
      }
    } catch (SQLException sqe) {
      System.out.println("Caught SQL Exception: " + sqe);
    }
    return gameID;
  }

  static int getPerson(Connection conn, String name) {
    int personID = -1;
    try {
      PreparedStatement st = conn.prepareStatement(getPerson);
      st.setString(1,name);
      ResultSet rs = st.executeQuery();	      
      if (rs.next()) {
    	personID = rs.getInt(1);
      }
    } catch (SQLException sqe){
      System.out.println("Caught SQL Exception: " + sqe);
    } 
    return personID;
  }
  
  public static void main(String[] args)   {
    Connection conn = connect();
    if (conn == null) return;
    int gameID = getGame(conn, "Ajax", "FC Twente", "2010-2011");
    int DeJong = getPerson(conn, "Siem de Jong");
    
    if (zapPlayer(conn,gameID,DeJong,20)) {
     //  TODO: make sure the results get committed
      printRanking(conn, "2010-2011");
    }
  }
}