import java.sql.*;
import java.util.List;
import java.util.ArrayList;

public class Professor {

    private Connection conn= null;
    private ResultSet rs1, rs2;
    private int profid ;
    private int phone;
    private String name;
    private String surname;
    private String expertise;
    private String selectquery1="SELECT profid FROM professor WHERE username =? AND password =?";
    private String selectqueryABC = "SELECT* FROM COURSE WHERE profid=?";
    private  PreparedStatement stmt1;


     public static void main (String[] args) {

        try {
            Professor p1 = new Professor();
        p1.open();
        int iddd = p1.getID("VasilisPap", "frontistirio123");
        p1.getCourses(iddd);
    }catch(SQLException e) {
        System.out.println(e);
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    }
    public void  open() throws SQLException{

        try{
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://195.251.249.131:3306/ismgroup25";
            String username = "ismgroup25";
            String password = "zdsp$4";
            Class.forName(driver);
            conn = DriverManager.getConnection(url,username,password);


        } catch(Exception e) {
            throw new SQLException("No proper connection " + e);
        }
    }



    public void close() throws SQLException {
        try {
        conn.close();
        } catch(Exception e) {
            throw new SQLException("Something went wrong " + e) ;
            }
     }

    public int getID (String username, String password) throws Exception {

    stmt1 = conn.prepareStatement(selectquery1);
    stmt1.setString(1, username);
    stmt1.setString(2, password);
    rs1 = stmt1.executeQuery();
    if(rs1.next()) {
    profid = rs1.getInt("profid");
    }
    return profid;
    }

    public List<Course> getCourses(int profid) throws Exception {
        try {
        stmt1 = conn.prepareStatement(selectqueryABC);
        stmt1.setInt(1, profid);
        rs1 = stmt1.executeQuery();
        List<Course> courses = new ArrayList<Course>();
        while(rs1.next()) {
            String classN = rs1.getString("class");
            int cid = rs1.getInt("cid");
            String name = rs1.getString("name");
             profid = rs1.getInt("profid");
             System.out.println(profid);
            Course c1 = new Course(classN,cid,name,profid);
            System.out.println(c1.getName());
            courses.add(c1);


        }
        System.out.println(courses.get(3).getProfid());
        return courses;


    } catch(Exception e) {
        throw new SQLException("Couldn't create list" + e);
    }
  }
}