package learn.studentsDb;

import com.sun.org.apache.xpath.internal.SourceTree;

import java.lang.invoke.SwitchPoint;
import java.sql.*;
import java.util.Date;
import java.util.Scanner;

public class Main {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/students";
    static final String USER = "root";
    static final String PASS = "";

    public static void main(String[] args) {
        Scanner in = new Scanner(System.in);
        Connection conn = null;
        Statement stmt = null;
        String sql;

        //showing information for input
        info();
        int response = in.nextInt();

        try {

            Class.forName(JDBC_DRIVER);
            System.out.println("Connecting to database...");

            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();

            switch (response){
                case 1: // list all students info

                    sql = "SELECT id, name, lesson, grade, time FROM class";
                    ResultSet rs = stmt.executeQuery(sql);

                    while (rs.next()){
                        //getting values
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        String lesson = rs.getString("lesson");
                        int grade = rs.getInt("grade");
                        Date time = rs.getDate("time");


                        //Displaying
                        System.out.print("student ID: " + id +
                                        "   Name: " + name +
                                        "   Lesson: " + lesson +
                                        "   Grade: " + grade +
                                        "   Date: " + time
                                        );
                        System.out.println();
                    }
                    //disconnect from database
                    disconncetDb(conn, stmt);
                    break;

                case 2: // Insert new info
                    System.out.println("Please Enter student's name: ");
                        String newName = in.next();
                    System.out.println("Lesson's name: ");
                        String newLesson = in.next();
                    System.out.println("Student's grade: ");
                        int newGrade = in.nextInt();
                        sql = "INSERT INTO class(name, lesson, grade) VALUES('"+ newName +"','"+ newLesson+"',"+ newGrade +")";
                        stmt.execute(sql);
                    System.out.println("new information added successfully!");
                        disconncetDb(conn,stmt);
                    break;

                case 3: // Delete info
                    System.out.println("Please enter the Student's ID which you want to DELETE: ");
                    int studentId = in.nextInt();
                    sql = "DELETE FROM class WHERE id="+ studentId;
                    stmt.execute(sql);
                    disconncetDb(conn,stmt);
                    break;

                case 4: // update info
                    System.out.println("What do you want to update? (name, lesson or grade") ;
                    String answer = in.next();
                    switch (answer){
                        case "name":
                            System.out.println("Please enter Student's ID which you want to update: ");
                            int updateID = in.nextInt();

                            System.out.println("Enter the name: ");
                            String updateName = in.next();

                            sql = "UPDATE class SET name='"+ updateName +"' WHERE id="+ updateID;
                            stmt.execute(sql);
                            System.out.println("Student's name updated by ID of " + updateID);
                            break;
                        case "lesson":
                            System.out.println("Please enter Student's ID which you want to update: ");
                                updateID = in.nextInt();

                            System.out.println("Enter the name: ");
                            String updateLesson = in.next();

                            sql = "UPDATE class SET lesson='"+ updateLesson +"' WHERE id="+ updateID;
                            stmt.execute(sql);
                            System.out.println("Student's lesson updated by ID of " + updateID);
                            break;
                        case "grade":
                            System.out.println("Please enter Student's ID which you want to update: ");
                                updateID = in.nextInt();

                            System.out.println("Enter the name: ");
                            int updateGrade = in.nextInt();

                            sql = "UPDATE class SET grade="+ updateGrade +" WHERE id="+ updateID;
                            stmt.execute(sql);
                            System.out.println("Student's grade updated by ID of " + updateID);
                            break;
                        default:
                            System.out.println("Incorrect input, you can choose only between name, lesson or grade.");
                            break;
                    }
                    disconncetDb(conn, stmt);
                    break;
                default: // exit mode
                    System.out.println("OK");
                    break;
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void info(){
        System.out.println(
                "Enter:" +
                        "\n 1: To list all students info" +
                        "\n 2: To add new student info" +
                        "\n 3: To delete student info" +
                        "\n 4: To update student info" +
                        "\n 5: EXIT"
        );
        System.out.println("Your input is: ");
    }

    public static void disconncetDb(Connection conn, Statement stmt){
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("Disconnected from database.");
    }
}