package com;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class EmployeeManagementDao {

    static Connection con=null;
    static Statement st=null;
    static ResultSet rs=null;

//  public static void Connection()
//  {
//      String url="";
//      String username="";
//      String password="";
//      Class.forName("oracle.jdbc.OracleDriver");
//      con=DriverManager.getConnection(url,username,password);
//      st=con.createStatement();
//  }
    public int addDepartment(Department dept) throws ClassNotFoundException, SQLException
    {
        try
        {
            String url="";
            String username="";
            String password="";
            String query="Create table TBL_1534737_Department(dept_id number(4) primary key,dept_name varchar2(30) unique, dept_owner varchar2(30) unique)";
            String insertquery1="Insert into Department values(1,'IT','HARSHIT')";
            //String insertquery2="Insert into Department values(2,'CS','Prayag')";


            Class.forName("oracle.jdbc.OracleDriver");
            con=DriverManager.getConnection(url,username,password);
            st=con.createStatement();
            int i=st.executeUpdate(query);
            int ii=st.executeUpdate(insertquery1);
            //int iii=st.executeUpdate(insertquery2);
            if(ii==1)
            {
                System.out.println("Inserted");
                return 1;
            }
            else
            {
                System.out.println("Not Inserted");
                return -1;
            }


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

            if(st!=null)
            {
            st.close();
            }
            if(con!=null)
            {
            con.close();
            }
    }
        return 1;
    }



    public boolean addEmployee(Employee employee) throws ClassNotFoundException, SQLException
    {
        try
        {
            String url="";
            String username="";
            String password="";
            String query="Create table TBL_1534737_Employee"
                    + "(employee_id number(4) primary key,"
                    + "dept_id number(4) Foreign key references Department, "
                    + "employee_name varchar2(4)"
                    + "employee_address varchar2(10)"
                    + "employee_contact number(10) check (len(phone_number)=10)";
            String insertquery1="Insert into Department values(1,'IT','HARSHIT')";
            //String insertquery2="Insert into Department values(2,'CS','Prayag')";


            Class.forName("oracle.jdbc.OracleDriver");
            con=DriverManager.getConnection(url,username,password);
            st=con.createStatement();
            int i=st.executeUpdate(query);
            int ii=st.executeUpdate(insertquery1);
            //int iii=st.executeUpdate(insertquery2);
            if(ii==1)
            {
                System.out.println("Inserted");
                return true;
            }
            else
            {
                System.out.println("Not Inserted");
                return false;
            }
        }

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

                if(st!=null)
                {
                st.close();
                }
                if(con!=null)
                {
                con.close();
                }
        }
        return true;

    }

    public void displayEmployeeAndDepartment(int employeeId) throws ClassNotFoundException, SQLException
    {
        try
        {
            String url="";
            String username="";
            String password="";
            Class.forName("oracle.jdbc.OracleDriver");
            con=DriverManager.getConnection(url,username,password);
            st=con.createStatement();
            String query="select E.Emp_name,E.Emp_no,D.Dep_name,D.Dep_owner "
                    + "from TBL_1534737_Employee E Inner Join"
                    + " Department D ON E.Dep_Id=D.Dep_ID where E.Emp_id="+employeeId;
            int i=st.executeUpdate(query);
        }
        catch(ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {

            if(st!=null)
            {
            st.close();
            }
            if(con!=null)
            {
            con.close();
            }
    }
    }

    public void updateContactDetail(int employeeId, Long contactdetail) throws ClassNotFoundException, SQLException
    {
        try
        {
            String url="";
            String username="";
            String password="";
            Class.forName("oracle.jdbc.OracleDriver");
            con=DriverManager.getConnection(url,username,password);
            st=con.createStatement();
            String query="update TBL_1534737_Employee set employee_contact="+contactdetail +" where employee_id="+employeeId;
            int i=st.executeUpdate(query);
        }
        catch(ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {

            if(st!=null)
            {
            st.close();
            }
            if(con!=null)
            {
            con.close();
            }
    }
    }


    public void deleteEmployee(int employeeId) throws ClassNotFoundException, SQLException
    {
        try
        {
            String url="";
            String username="";
            String password="";
            Class.forName("oracle.jdbc.OracleDriver");
            con=DriverManager.getConnection(url,username,password);
            st=con.createStatement();
            String query="delete from TBL_1534737_Employee where employee_id="+employeeId;
            int i=st.executeUpdate(query);
        }
        catch(ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {

            if(st!=null)
            {
            st.close();
            }
            if(con!=null)
            {
            con.close();
            }
    }
    }


    public Map<Integer, Integer> getCountofEmployeeByDepartment() throws ClassNotFoundException, SQLException

    {
        Map<Integer, Integer> map = new HashMap<Integer, Integer>();
        try
        {
            String url="";
            String username="";
            String password="";
            Class.forName("oracle.jdbc.OracleDriver");
            con=DriverManager.getConnection(url,username,password);
            st=con.createStatement();
            String query="select count(Dep_id),Dep_id from Employee group by Dep_id";
            rs=st.executeQuery(query);
            while(rs.next())
            {
                int a=rs.getInt(1);
                int b=rs.getInt(2);
                map.put(a, b);
            }



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

            if(st!=null)
            {
            st.close();
            }
            if(con!=null)
            {
            con.close();
            }
    }

        return map;
    }
    }