package models;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;

public class DBManager {

    public void deleteZooKeeper (ZooKeeper zooKeeper)
    {
        try
        {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://H:\\James hood object oriented programing\\Example project\\ZooSystem james version\\Data\\Database1.accdb");
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM ZooKeepers WHERE EmailAddress = '" + zooKeeper.getEmailAddress() + "'");
            conn.close();
        }
        catch(Exception ex)
        {
            String message = ex.getMessage();
        }
    }



    public void deleteAnimal (Animal animal)
    {
        try
        {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://H:\\James hood object oriented programing\\Example project\\ZooSystem james version\\Data\\Database1.accdb");
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM Animals WHERE AnimalID = '" + animal.getAnimalId() + "'");
            conn.close();
        }
        catch(Exception ex)
        {
            String message = ex.getMessage();
        }
    }






    public void updateZooKeeper(ZooKeeper newZooKeeper)
    {
        try
        {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://H:\\James hood object oriented programing\\Example project\\ZooSystem james version\\Data\\Database1.accdb");
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("UPDATE ZooKeepers SET PersonName = '" + newZooKeeper.getName() + "', " +
                    "Age = '" + newZooKeeper.getAge() + "', Password = '" + newZooKeeper.getPassword() + "', " +
                    "DateHired = '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(newZooKeeper.getDateHired()) + "', " +
                    "Salary = '" + newZooKeeper.getSalary() + "', TelephoneNo = '" + newZooKeeper.getTelephoneNo() + "' " +
                    "WHERE EmailAddress = '" + newZooKeeper.getEmailAddress() + "'");
            conn.close();


        }
        catch(Exception ex)
        {
            String message = ex.getMessage();
        }

    }

    public void updateAnimal(Animal newAnimal)
    {
        String waterType = "";
        String canFly = "NULL";

        if(newAnimal.getClass().getName().equals("models.Fish"))
        {
            Fish newFish = (Fish)newAnimal;
            waterType = newFish.getWaterType();
        }
        else
        {
            Bird newBird = (Bird)newAnimal;
            canFly = String.valueOf(newBird.getFlies());
        }

        try
        {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://H:\\James hood object oriented programing\\Example project\\ZooSystem james version\\Data\\Database1.accdb");
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("UPDATE Animals SET AnimalName = '" + newAnimal.getName() + "', " +
                    "Gender = '" + newAnimal.getGender() + "', Type = '" + newAnimal.getType() + "', " +
                    "Age = '" + newAnimal.getAge() + "', Location = '" + newAnimal.getLocation() + "', " +
                    "WaterType ='" + waterType + "', CanFly = " + canFly +
                    " WHERE AnimalID = '" + newAnimal.getAnimalId() + "'");
            conn.close();


        }
        catch(Exception ex)
        {
            String message = ex.getMessage();
        }
    }




    public HashMap<Integer, Animal> loadAnimals()
    {
        HashMap<Integer, Animal> animals = new HashMap();

        try
        {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://H:\\James hood object oriented programing\\Example project\\ZooSystem james version\\Data\\Database1.accdb");
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT * FROM Animals");
            while (rs.next())
            {
                int animalId = rs.getInt("animalID");
                String animalName = rs.getString("AnimalName");
                char gender = rs.getString("Gender").charAt(0);
                String type = rs.getString("Type");
                int age = rs.getInt("Age");
                String location = rs.getString("Location");
                String waterType = rs.getString("WaterType");
                boolean availability = rs.getBoolean("Availability");
                double cost = rs.getDouble("Cost");


                if (waterType != null && !waterType.isEmpty())
                {
                    Fish loadedFish = new Fish(animalName, gender, type, age, location, animalId, waterType, availability, cost);
                    animals.put(animalId, loadedFish);
                }
                else
                {
                    //String nameIn, char genderIn, String typeIn, int ageIn, String locationIn, boolean fliesIn
                    boolean canFly = rs.getBoolean("canFly");
                    Bird loadedBird = new Bird(animalName, gender, type, age, location, animalId,canFly, availability, cost);
                    animals.put(animalId, loadedBird);
                }
            }
            conn.close();


        }
        catch (Exception ex)
        {
            String message = ex.getMessage();
        }
        finally
        {
            return animals;
        }
    }




    public void addAnimal(Animal newAnimal)
    {
        String waterType = "";
        String canFly = "NULL";

        if(newAnimal.getClass().getName().equals("models.Fish"))
        {
            Fish newFish = (Fish)newAnimal;
            waterType = newFish.getWaterType();
        }
        else
        {
            Bird newBird = (Bird)newAnimal;
            canFly = String.valueOf(newBird.getFlies());
        }

        try
        {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://H:\\James hood object oriented programing\\Example project\\ZooSystem james version\\Data\\Database1.accdb");
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("INSERT INTO Animals (AnimalName, Gender, Type, Age, Location, WaterType, CanFly) VALUES " +
                    "('" + newAnimal.getName() + "','" + newAnimal.getGender() + "','" + newAnimal.getType() + "','" + newAnimal.getAge() +
                    "','" + newAnimal.getLocation() + "','" + waterType + "'," + canFly + ")");
            conn.close();


        }
        catch(Exception ex)
        {
            String message = ex.getMessage();
        }
    }




    public boolean registerZooKeeper(ZooKeeper newKeeper)
    {
        try
        {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://H:\\James hood object oriented programing\\Example project\\ZooSystem james version\\Data\\Database1.accdb");
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("INSERT INTO ZooKeepers (EmailAddress, PersonName, Age, Password, DateHired, Salary, TelephoneNo) VALUES " +
                    "('" + newKeeper.getEmailAddress() + "','" + newKeeper.getName() + "','" + newKeeper.getAge() + "','" + newKeeper.getPassword() +
                    "','" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(newKeeper.getDateHired()) + "','" + newKeeper.getSalary() + "','" + newKeeper.getTelephoneNo() + "')"  );
            conn.close();
            return true;

        }
        catch(Exception ex)
        {
            String message = ex.getMessage();
            return false;

        }
    }



     public Admin adminLogin(String emailAddress, String password){

        HashMap<String, Admin> admins = loadAdmins();

        if(admins.containsKey(emailAddress)){

            Admin foundAdmin = admins.get(emailAddress);

            if (foundAdmin.getPassword().equals(password)){

                return foundAdmin;
            }
        }
        //if not found return null
        return null;
    }

    public ZooKeeper zooKeeperLogin(String emailAddress, String password){

        HashMap<String, ZooKeeper> zooKeepers = loadZooKeepers();

        if(zooKeepers.containsKey(emailAddress)){

            ZooKeeper foundZooKeeper = zooKeepers.get(emailAddress);

            if (foundZooKeeper.getPassword().equals(password)){

                return foundZooKeeper;
            }
        }
        //if not found return null
        return null;
    }




    public HashMap<String, ZooKeeper> loadZooKeepers() {

        HashMap<String, ZooKeeper> zooKeepers = new HashMap<>();

        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://H:\\James hood object oriented programing\\Example project\\ZooSystem james version\\Data\\Database1.accdb");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM ZooKeepers");

            while (rs.next()) {

                String emailAddress = rs.getString("EmailAddress");
                String personName = rs.getString("PersonName");
                int age = rs.getInt("age");
                String password = rs.getString("Password");
                Date dateHired = rs.getDate("DateHired");
                double salary = rs.getDouble("Salary");
                String telephoneNo = rs.getString("TelephoneNo");

                ZooKeeper loadedZooKeeper = new ZooKeeper(personName, emailAddress, age, password, dateHired, salary, telephoneNo);

                zooKeepers.put(emailAddress, loadedZooKeeper);

            }
            conn.close();
        }
        catch (Exception ex)
        {
            String message = ex.getMessage();

        }

        finally
        {
            return zooKeepers;
        }
    }

    public HashMap<String, Admin> loadAdmins() {

        HashMap<String, Admin> admins = new HashMap<>();

        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://H:\\James hood object oriented programing\\Example project\\ZooSystem james version\\Data\\Database1.accdb");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM Admins");

            while (rs.next()) {

                String emailAddress = rs.getString("EmailAddress");
                String personName = rs.getString("PersonName");
                int age = rs.getInt("age");
                String password = rs.getString("Password");
                Date dateHired = rs.getDate("DateHired");
                double salary = rs.getDouble("Salary");
                boolean superUser = rs.getBoolean("SuperUser");

                Admin loadedAdmin = new Admin(personName, emailAddress, age, password, dateHired, salary, superUser);

                admins.put(emailAddress, loadedAdmin);

            }
            conn.close();
        }
        catch (Exception ex)
        {
            String message = ex.getMessage();

        }

        finally
        {
            return admins;
        }
    }

}