import java.sql.*; // JDBC stuff.
import java.util.Properties;

public class PortalConnection {

    // For connecting to the portal database on your local machine
    static final String DATABASE = "jdbc:postgresql://localhost/portal";
    static final String USERNAME = "postgres";
    static final String PASSWORD = "postgres";

    // For connecting to the chalmers database server (from inside chalmers)
    // static final String DATABASE = "jdbc:postgresql://ate.ita.chalmers.se/";
    // static final String USERNAME = "tda357_nnn";
    // static final String PASSWORD = "yourPasswordGoesHere";


    // This is the JDBC connection object you will be using in your methods.
    private Connection conn;

    public PortalConnection() throws SQLException, ClassNotFoundException {
        this(DATABASE, USERNAME, PASSWORD);
    }

    // Initializes the connection, no need to change anything here
    public PortalConnection(String db, String user, String pwd) throws SQLException, ClassNotFoundException {
        Class.forName("org.postgresql.Driver");
        Properties props = new Properties();
        props.setProperty("user", user);
        props.setProperty("password", pwd);
        conn = DriverManager.getConnection(db, props);
    }

    // Register a student on a course, returns a tiny JSON document (as a String)
    public String register(String student, String courseCode){

        try  {
            PreparedStatement ps = conn.prepareStatement("INSERT INTO Registrations VALUES(?, ?)");
            ps.setString(1,student);
            ps.setString(2,courseCode);
            ps.executeUpdate();
        }  catch (SQLException e) {
               return "{\"success\":false, \"error\":\""+getError(e)+"\"}";
            }
            //what does success \false mean? Make this statement shorter
      return "{\"success\":true, \"error\":\"Student registered successfully :)\"}";
    }
/*
    // Unregister a student from a course, returns a tiny JSON document (as a String)
    public String unregister(String student, String courseCode){

      try{
        PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*) FROM Registrations WHERE student = ? AND course = ?");
        ps.setString(1, student);
        ps.setString(2, courseCode);
        ResultSet rs = ps.executeQuery();

        if(rs.next() && rs.getInt(1) >= 1){
          try {
            PreparedStatement pps = conn.prepareStatement("DELETE FROM Registrations WHERE student = ? AND course = ?");
            pps.setString(1,student);
            pps.setString(2,courseCode);
            pps.executeUpdate();
          } catch (SQLException e) {
              return "{\"success\":false, \"error\":\""+getError(e)+"\"}";
          }
        } else {
          return "{\"success\":false, \"error\":\"Student is not registered :)\"}";
        }
      } catch (SQLException e) {
        return "{\"success\":false, \"error\":\""+getError(e)+"\"}";
    }
        //no error msg with incomplete unregistrations
      return "{\"success\":true, \"error\":\"Student unregistered successfully :)\"}";
    }
*/
        // Unregister a student from a course, HACK EDITION
        public String unregister(String student, String courseCode){

          String sid = student;
          String code = courseCode;
          String query = "DELETE FROM Registered WHERE student ='"+sid+"' AND course = '"+code+"'";

          try{
                Statement s = conn.createStatement();
                int r = s.executeUpdate(query);
                System.out.println("Deleted " + r + " registrations");

          } catch (SQLException e) {
            return "{\"success\":false, \"error\":\""+getError(e)+"\"}";
        }
            //no error msg with incomplete unregistrations
          return "{\"success\":true, \"error\":\"Student unregistered successfully :)\"}";
        }

    // Return a JSON document containing lots of information about a student, it should validate against the schema found in information_schema.json
    public String getInfo(String student) throws SQLException{

        try(PreparedStatement st = conn.prepareStatement(
            "SELECT jsonb_build_object(" +
"            'student', idnr," +
"            'name', name," +
"            'login', login," +
"            'program', program," +
"            'branch', branch," +
"            'finished', (SELECT COALESCE(jsonb_agg(jsonb_build_object( " +
"                        'course', (SELECT name FROM Courses WHERE code = course)," +
"                        'code', course," +
"                        'credits', (SELECT credits FROM Courses WHERE code = course)," +
"                        'grade', grade)" +
"                        ), '[]') FROM Taken WHERE (student = BasicInformation.idnr))," +
"            'registered', (SELECT COALESCE(jsonb_agg(jsonb_build_object(" +
"                        'course', (SELECT name FROM Courses WHERE code = course)," +
"                        'code', course," +
"                        'status', status," +
"                        'position', (SELECT position FROM WaitingList WHERE course = Registrations.course AND student = Registrations.student))" +
"                        ), '[]') FROM Registrations WHERE (student = BasicInformation.idnr))," +
"            'seminarCourses', (SELECT seminarCourses FROM PathtoGraduation WHERE student = BasicInformation.idnr)," +
"            'mathCredits', (SELECT mathCredits FROM PathtoGraduation WHERE student = BasicInformation.idnr)," +
"            'researchCredits', (SELECT researchCredits FROM PathtoGraduation WHERE student = BasicInformation.idnr)," +
"            'totalCredits', (SELECT totalCredits FROM PathtoGraduation WHERE student = BasicInformation.idnr)," +
"            'canGraduate', (SELECT qualified FROM PathtoGraduation WHERE student = BasicInformation.idnr) " +
"            ) as jsondata" +
"        FROM BasicInformation WHERE idnr = ?;"

            );){

            st.setString(1, student);

            ResultSet rs = st.executeQuery();

            if(rs.next())
              return rs.getString("jsondata");
            else
              return "{\"student\":\"does not exist :(\"}";
        } catch (SQLException e) {
          return "{\"success\":false, \"error\":\""+getError(e)+"\"}";
      }

    }

    // This is a hack to turn an SQLException into a JSON string error message. No need to change.
    public static String getError(SQLException e){
       String message = e.getMessage();
       int ix = message.indexOf('\n');
       if (ix > 0) message = message.substring(0, ix);
       message = message.replace("\"","\\\"");
       return message;
    }
}