package db;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import aktorer.Admansatt;
import aktorer.Ansatt;
import aktorer.Elev;
import aktorer.Fagansatt;
import aktorer.Person;
import medlemmer.Kull;

public class Database {
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/oving8";
    public static final String USER = "root";
    public static final String PW = "";

    private static java.sql.Statement setUpStatement(){
        try {
            Class.forName(DRIVER);
            Connection tilkobling = DriverManager.getConnection(URL,USER,PW);
            return tilkobling.createStatement();
        } catch (ClassNotFoundException e) {
            System.out.println("Finner ikke klassen i mysql-connector");
        } catch(SQLException e){
            System.out.println("Kan ikke koble til database4");
        }
        return null;

    }
    /**Skriver til fagansatt,elev og admansatt**/
    public static void skrivPersonTildb(Person person){
        Statement st = (Statement) setUpStatement();

        String fornavn = person.getfNavn();
        String etternavn = person.geteNavn();
        String person_ID = person.getID();

        String sql = "INSERT INTO ";

        if(person instanceof Elev){
            sql += "elev ";
        }

        else if(person instanceof Admansatt){
            sql += "admansatt ";
        }

        else{
            sql += "fagansatt ";
        }

        sql += "VALUES('" + person_ID + "','" + fornavn + "','" + etternavn + "')";

        try {
            ((java.sql.Statement) st).executeUpdate(sql);
        } catch (Exception e) {
            System.out.println("Denne personen finnes allerede");
        }
            try {
                ((java.sql.Statement) st).getConnection().close();
            } catch (SQLException e) {
                System.out.println(e);
            }
        }

    /** Skriver til kulltabell**/
    public static void skrivKullTilDB(Kull kull){
        Statement st = (Statement) setUpStatement();

        String startaar = kull.getStartaar();
        String ID = kull.getAnsvarlig().getID();

        String sql = "INSERT INTO kull VALUES('" + startaar +    "','" + ID + "')";

        try {
            st.execute();
        } catch (Exception e) {
            System.out.println("Kull finnes allerede");
        }
    try {
        ((java.sql.Statement) st).getConnection().close();
    } catch (SQLException e) {
        System.out.println(e);
    }
}


    public static void hentPersonerFraDB(){
        java.sql.Statement st = setUpStatement();

        String sqlFagansatt = "SELECT * FROM fagansatt";
        String sqlElev = "SELECT * FROM elev";
        String sqlAdmansatt = "SELECT * FROM admansatt";
        try{
        ResultSet resFagansatt = st.executeQuery(sqlFagansatt);
        while(resFagansatt.next()){
            new Fagansatt(resFagansatt.getString(sqlAdmansatt), resFagansatt.getString("fornavn"),
                    resFagansatt.getString("etternavn"), null, null);
        }

        ResultSet resElev = st.executeQuery(sqlElev);
        while(resElev.next()){
            new Elev(resElev.getString(sqlAdmansatt), resElev.getString("fornavn"),
                    resElev.getString("etternavn"), null, null);
        }

        ResultSet resAdmansatt = st.executeQuery(sqlAdmansatt);
        while(resAdmansatt.next()){
            new Admansatt(resAdmansatt.getString(sqlAdmansatt), resAdmansatt.getString("fornavn"),
                    resAdmansatt.getString("etternavn"), null);
        }



        } catch(SQLException e){
            System.out.println(e);
        }
        try {
            st.getConnection().close();
        } catch (SQLException e) {
            System.out.println(e);
        }
        }

    public static <Startaar> void hentKullFraDB() {
        java.sql.Statement st = setUpStatement();

        String sql = "SELECT * FROM kull";

        try{
        ResultSet res = ((java.sql.Statement) st).executeQuery(sql);

        while(res.next()){
           String startaar = res.getString("startaar");
            Fagansatt fagansatt_person_ID = null;

            for(Person p: Person.getPersoner()){
                if(p.getID().equals(res.getString("fagansatt_person_ID")))
                    fagansatt_person_ID = (Fagansatt) p;
                    break;
            }
            new Kull(startaar, fagansatt_person_ID);
    }

        } catch (SQLException e){
        System.out.println(e);
        }
    }
}