package zad3;

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;


public class MySqlDAOImpl implements DAO{

	static long positionInc;
	static long employeeInc;

	private static ObjectOutputStream employeeFileOut  = null;

	private static ObjectOutputStream positionFileOut  = null;

        private static Connection con;

        MySqlDAOImpl() throws SQLException, ClassNotFoundException{
            String db = "db";
            String url = "jdbc:mysql://localhost:3306/" + db;
            String userName = "root";
            String password = "mysql";
            Class.forName(com.mysql.jdbc.Driver.class.getName());
            con = DriverManager.getConnection(url, userName, password);
        }

	ObjectOutputStream getStreamToPositions() throws FileNotFoundException, IOException{
		if(positionFileOut == null)
			positionFileOut = new ObjectOutputStream(new FileOutputStream("Positions.dat"));
		return positionFileOut;
	}

	static long getNextPositionID(){
		return ++positionInc;
	}

	static long getNextEmployeeID(){
		return ++employeeInc;
	}




	public void savePosition(Position p)throws SQLException{
                Statement stmt = con.createStatement();

                boolean succeed = stmt.execute("INSERT INTO stanowiska(nazwa) VALUES ('"+p.getPosition()+"')");

	}


	public List getAllPositions() throws SQLException {
                List array = new ArrayList();

                Object o;

                String query = "select * from stanowiska";

                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(query);

                while (rs.next()){
                        Position position = new Position(rs.getString("nazwa"));
                        array.add(position);
                }

                return array;
	}


	public Position getPosition(long id) throws SQLException {
                Position p =new Position();
		String sql ="SELECT * FROM stanowiska WHERE id = "+id;
                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                if(rs.next())
                {
                    Integer i = rs.getInt("id");

                    String name = rs.getString("nazwa");
                    p.setPosition(name);
                }
                return p;
	}




	public List getAllEmployees() throws SQLException {
            List array = new ArrayList();

            Object o;

            String query = "select p.id, p.imie, p.nazwisko, s.nazwa from stanowiska as s, pracownicy as p where p.id_stanowiska = s.id;";

            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()){
                    Employee employee = new Employee(rs.getString("imie"), rs.getString("nazwisko"), new Position(rs.getString("nazwa")));
                    employee.setId(rs.getInt("id"));
                    array.add(employee);
            }

            return array;
	}


	public Employee getEmployee(long id) throws SQLException {
                Employee e =new Employee();
		String sql ="select p.id, p.imie, p.nazwisko, s.nazwa from stanowiska as s, pracownicy as p where p.id_stanowiska = s.id and p.id = "+id;

                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                if(rs.next())
                {
                    Integer i = rs.getInt("id");

                    String imie = rs.getString("imie");
                    String nazwisko = rs.getString("nazwisko");
                    String stanowisko = rs.getString("nazwa");

                    e.setId(i);
                    e.setName(imie);
                    e.setSurname(nazwisko);
                    e.setPosition(new Position(stanowisko));

                    return e;
                }
                return null;

	}


	public void saveEmployee(Employee emp) throws SQLException {
                Position p =new Position();
		String sql ="SELECT * FROM stanowiska WHERE nazwa = '"+emp.getPosition().getPosition() + "'";
                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(sql);

                int id = 0;
                if(rs.next())
                {
                    id = rs.getInt("id");
                }


		//Statement stmt = con.createStatement();
                System.out.println(id);
                boolean succeed = stmt.execute("INSERT INTO pracownicy(imie, nazwisko, id_stanowiska) VALUES ('"+emp.getName()+"', '"+emp.getSurname()+"', '"+id+"')");

	}

	public void deleteEmployee(Employee emp) throws SQLException {
		Statement stmt = con.createStatement();

                int modifiedRows = stmt.executeUpdate("DELETE from pracownicy WHERE id=" + emp.getId());


	}


}