import java.util.*;
import java.sql.*;
import static java.lang.System.out;

class StudentMenu {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        Connection conn = null;
        ResultSet rs = null;

        try {
            Class.forName("org.postgresql.Driver");
            conn = DriverManager.getConnection("jdbc:postgresql://localhost/jdbcarjun", "postgres", "");

            if(conn == null) {
                out.println("Failed to establish conncection");
            } else {
                int choice;
                boolean userCont = true;
                out.println("Operations on Student Table");
                do {
                    out.println("Menu:\n1. Insert\n2. Modify\n3. Delete\n4. Search\n5. View All\n6. Exit");
                    choice = sc.nextInt();
                    if(choice == 1) {
                        int newRoll, newPerc;
                        String newName;
                        out.println("Enter the roll number: ");
                        newRoll = sc.nextInt();
                        out.println("Enter the name: ");
                        newName = sc.next();
                        out.println("Enter percentage: ");
                        newPerc = sc.nextInt();
                        if(newPerc < 0 || newPerc > 100) {
                            out.println("Not a valid value");
                        } else {
                            PreparedStatement ps = conn.prepareStatement("INSERT INTO students VALUES(?,?,?)");
                            ps.setInt(1, newRoll);
                            ps.setString(2, newName);
                            ps.setInt(3, newPerc);
                            try {
                                if(ps.executeUpdate() == 1)
                                    out.println("Database insertion successful!");
                            } catch (Exception e) {
                                out.println(e);
                            }
                            ps.close();
                        }
                    } else if(choice == 2) {
                        out.println("WIP");
                    } else if(choice == 3) {
                        out.println("WIP");
                    } else if(choice == 4) {
                        out.println("Search by:\n1: Roll No\n2: Name\n3: Percentage Less Than\n4: Percentage More Than\n5: Percentage Equals");
                        int usrSearchChoice = sc.nextInt();
                        PreparedStatement ps = null;
                        if(usrSearchChoice == 1) {
                            ps = conn.prepareStatement("SELECT * FROM students WHERE rollno=?");
                            out.println("Enter roll number to search for: ");
                            ps.setInt(1, sc.nextInt());
                            rs = ps.executeQuery();
                            while(rs.next()) {
                                out.println("Name: " + rs.getString(2) + "\t" + "Percentage: " + rs.getString(3));
                            }
                        } else if(usrSearchChoice == 2) {
                        } else if(usrSearchChoice == 3) {
                        } else if(usrSearchChoice == 4) {
                        } else if(usrSearchChoice == 5) {
                        } else {
                            out.println("Invalid choice");
                        }
                    } else if(choice == 5) {
                        Statement stmt = conn.createStatement();
                        ResultSet rset = stmt.executeQuery("SELECT * FROM students");
                        out.println("-----------");
                        while(rset.next()) {
                            out.println("Roll No: " + rset.getInt(1));
                            out.println("Name: " + rset.getString(2));
                            out.println("Percentage: " + rset.getInt(3));
                        }
                        out.println("------------");
                    } else if(choice == 6) {
                        userCont = false;
                    } else {
                        out.println("Invalid choice. Try again.");
                    }
                } while(userCont);

            }
        } catch(Exception e) {
            out.println(e);
        }
    }
}