use employees;

delimiter //
drop function if exists emp_dept_id //
drop function if exists emp_dept_name //
drop function if exists emp_name //
drop function if exists current_manager //
drop procedure if exists show_departments //

--
-- returns the department id of a given employee
--
create function emp_dept_id( employee_id int )
returns char(4)
reads sql data
begin
 declare max_date date;
 set max_date = (
 select
 max(from_date)
 from
 dept_emp
 where
 emp_no = employee_id
 );
 set @max_date=max_date;
 return (
 select
 dept_no
 from
 dept_emp
 where
 emp_no = employee_id
 and
 from_date = max_date
 limit 1
 );
end //

--
-- returns the department name of a given employee
--

create function emp_dept_name( employee_id int )
returns varchar(40)
reads sql data
begin
 return (
 select
 dept_name
 from
 departments
 where
 dept_no = emp_dept_id(employee_id)
 );
end//

--
-- returns the employee name of a given employee id
--
create function emp_name (employee_id int)
returns varchar(32)
reads SQL data
begin
 return (
 select
 concat(first_name, ' ', last_name) as name
 from
 employees
 where
 emp_no = employee_id
 );
end//

--
-- returns the manager of a department
-- choosing the most recent one
-- from the manager list
--
create function current_manager( dept_id char(4) )
returns varchar(32)
reads sql data
begin
 declare max_date date;
 set max_date = (
 select
 max(from_date)
 from
 dept_manager
 where
 dept_no = dept_id
 );
 set @max_date=max_date;
 return (
 select
 emp_name(emp_no)
 from
 dept_manager
 where
 dept_no = dept_id
 and
 from_date = max_date
 limit 1
 );
end //

delimiter ;

--
-- selects the employee records with the
-- latest department
--

CREATE OR REPLACE VIEW v_full_employees
AS
SELECT
 emp_no,
 first_name , last_name ,
 birth_date , gender,
 hire_date,
 emp_dept_name(emp_no) as department
from
 employees;

--
-- selects the department list with manager names
--

CREATE OR REPLACE VIEW v_full_departments
AS
SELECT
 dept_no, dept_name, current_manager(dept_no) as manager
FROM
 departments;

delimiter //

--
-- shows the departments with the number of employees
-- per department
--
create procedure show_departments()
modifies sql data
begin
 DROP TABLE IF EXISTS department_max_date;
 DROP TABLE IF EXISTS department_people;
 CREATE TEMPORARY TABLE department_max_date
 (
 emp_no int not null primary key,
 dept_from_date date not null,
 dept_to_date date not null, # bug#320513
 KEY (dept_from_date, dept_to_date)
 );
 INSERT INTO department_max_date
 SELECT
 emp_no, max(from_date), max(to_date)
 FROM
 dept_emp
 GROUP BY
 emp_no;

 CREATE TEMPORARY TABLE department_people
 (
 emp_no int not null,
 dept_no char(4) not null,
 primary key (emp_no, dept_no)
 );

 insert into department_people
 select dmd.emp_no, dept_no
 from
 department_max_date dmd
 inner join dept_emp de
 on dmd.dept_from_date=de.from_date
 and dmd.dept_to_date=de.to_date
 and dmd.emp_no=de.emp_no;
 SELECT
 dept_no,dept_name,manager, count(*)
 from v_full_departments
 inner join department_people using (dept_no)
 group by dept_no;
 # with rollup;
 DROP TABLE department_max_date;
 DROP TABLE department_people;
end //

drop function if exists employees_usage //
drop procedure if exists employees_help //

CREATE FUNCTION employees_usage ()
RETURNS TEXT
DETERMINISTIC
BEGIN
 RETURN
'
 == USAGE ==
 ====================

 PROCEDURE show_departments()

 shows the departments with the manager and
 number of employees per department

 FUNCTION current_manager (dept_id)

 Shows who is the manager of a given departmennt

 FUNCTION emp_name (emp_id)

 Shows name and surname of a given employee

 FUNCTION emp_dept_id (emp_id)

 Shows the current department of given employee
';
END //

create procedure employees_help()
deterministic
begin
 select employees_usage() as info;
end//

delimiter ;