drop table if exists project_members;
drop table if exists projects;
drop table if exists employees;

-- employees(id, first_name, last_name, address, supervisor_id)

create table employees (
    id              integer auto_increment primary key,
    first_name      varchar(255),
    last_name       varchar(255),
    address         varchar(255),
    supervisor_id   integer references employees(id)
);

insert into employees values (1, 'John', 'Doe', 'Street #215', null);
insert into employees (first_name, last_name, address, supervisor_id)
    values ('Jane', 'Doe', 'Street #711', 1);

select * from employees;

-- projects(id, name,leader_id )

create table projects (
    id          integer auto_increment primary key,
    name        varchar(255),
    leader_id   integer references employees(id)
);

insert into projects values (1, 'Firestone', 1);
insert into projects values (2, 'Blue', 2);

select * from projects;

-- project_members( project_id, member_id ) 

create table project_members (
    project_id  integer references projects(id),
    member_id   integer references employees(id)
);

insert into project_members values (1, 1);
insert into project_members values (2, 1);
insert into project_members values (2, 2);

select * from project_members;

-- Find the name and address of employee with id=1

select first_name, last_name, address from employees where id = 1;
select concat( first_name, ' ', last_name) as name, address
    from employees where id = 1;

-- Find the name of employee who leads the project Firestone

select e.first_name, e.last_name from employees e, projects p
    where p.name = 'Firestone' and p.leader_id = e.id;

select e.first_name, e.last_name from employees e inner join projects p
    on e.id = p.leader_id
    where p.name = 'Firestone';

-- Find the name of Jane Doe's supervisor

select e2.first_name, e2.last_name from employees e1, employees e2
    where e1.first_name = 'Jane' and e1.last_name = 'Doe'
    and e1.supervisor_id = e2.id;

-- Find the number of projects led by John Doe

select count(p.id) from projects p, employees e
    where p.leader_id = e.id and e.first_name = 'John'
    and e.last_name = 'Doe';

-- List the number of members of each project

select p.name, count(m.member_id) from projects p, project_members m
    where p.id = m.project_id
    group by p.name;

-- Change John Doe's address to 123 Main St.

update employees set address = '123 Main St.'
    where first_name = 'John' and last_name = 'Doe';

-- Change John Doe's name to Tom Smith 

update employees set first_name = 'Tom', last_name = 'Smith'
    where first_name = 'John' and last_name = 'Doe';

select * from employees;

-- Delete all the projects led by John Doe

delete from project_members where project_id in
    (select p.id from projects p, employees e
        where p.leader_id = e.id
        and e.first_name = 'John' and e.last_name = 'Doe');

delete from projects where leader_id = (select id from employees
        where first_name = 'John' and last_name = 'Doe');

-- Delete all the projects

delete from project_members;
delete from projects;