package terminkalender;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class DBHandler {
    boolean abfrage = true;
    private static Connection conuser;
    private static Connection conevent;
    private static Connection conuser_event;
    private static boolean hasData = false;

    public ResultSet displayUsers() throws ClassNotFoundException, SQLException{
        if(conuser == null && conevent == null && conuser_event == null){
            getConnection();
        }
    }

    private void getConnection() throws ClassNotFoundException, SQLException {
       Class.forName("org.sqlite.JDBC");
       conuser = DriverManager.getConnection("jdbc:sqlite:USER.db");
       conevent = DriverManager.getConnection("jdbc:sqlite:EVENT.db");
       conuser_event = DriverManager.getConnection("jdbc:sqlite:USER_EVENT.db");
       initialise();
    }

    private void initialise() throws SQLException{
        if(!hasData){
            hasData = true;
            Statement state1 = conuser.createStatement();
            Statement state2 = conevent.createStatement();
            Statement state3 = conuser_event.createStatement();

            ResultSet res1 = state1.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='user'");
            ResultSet res2 = state2.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='event'");
            ResultSet res3 = state3.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='user_event");

            if(!res1.next() && !res2.next() && !res3.next()){
                System.out.println("Building the User table with prepopulated values.");
                Statement stateuser = conuser.createStatement();
                stateuser.execute("CREATE TABLE user(userID integer,"
                        + "fName varchar(60),"
                        + "lName varchar(60),"
                        + "pw varchar(60),"
                        + "primary key(userID))");

                System.out.println("Building the Event table with prepopulated values.");
                Statement stateevent = conevent.createStatement();
                stateevent.execute("CREATE TABLE event(eventID integer,"
                        + "name varchar(60),"
                        + "day intger,"
                        + "month integer,"
                        + "year integer,"
                        + "hours integer,"
                        + "minutes integer,"
                        + "primary key(eventID))");

                System.out.println("Building the User_Event table with prepopulated values.");
                Statement stateuser_event = conuser_event.createStatement();
                stateuser_event.execute("CREATE TABLE user_event(user_eventID integer,"
                        + "userID integer,"
                        + "eventID integer,"
                        + "foreign key(userID),"
                        + "references suppliers(userID),"
                        + "foreign key(eventID),"
                        + "references suppliers(eventID),"
                        + "primary key(user_eventID))");
                }
            }
        while(abfrage == true){
            dialog();
            System.out.println("--------------------------------");
        }
    }

    public void dialog() throws SQLException{
        System.out.print("(1) Neuen Benutzer anlegen\n(2) Neuen Termin anlegen\n(3) Beenden und Ausgeben \nGewuenschte Operation angeben: ");
        Scanner sc = new Scanner(System.in);
        int operation = sc.nextInt();

        switch(operation){
            case 1:
                addUser();
                break;

            case 2:
                addEvent();
                break;

            case 3:
                abfrage = false;
                break;

            default:
                System.out.println("Gewuenschte Operation gibt es nicht");
        }
    }

    public void addUser(){
        PreparedStatement prep = conuser.prepareStatement("INSERT INTO user values(?,?,?);");
        System.out.print("Bitte Vorname eingeben: ");
        Scanner sc = new Scanner(System.in);
        String fName = sc.next();
        System.out.print("Bitte Nachnamen ngeben: ");
        String lName = sc.next();
        prep.setString(2, fName);
        prep.setString(3, lName);
        prep.execute();
    }

    public void addEvent(){

    }
}