create database ScheduleApp;
use ScheduleApp;
create table Employees(
	employee_ID INT NOT NULL auto_increment,
	first_name varchar(25),
    last_name varchar(25),
    isManager boolean,
    email varchar(50),
    phoneNumber varchar(10),
    wage float,
    primary key (employee_ID)
    );

create table Shifts(
	shift_id INT NOT NULL auto_increment UNIQUE,
    full_date DATE,
    start_time INT,
    end_time INT,
    employee_ID INT,
    availability boolean,
    PRIMARY KEY (shift_id),
    FOREIGN KEY (employee_ID) REFERENCES Employees(employee_ID)
);

create table TimeOff(
	timeOffID int not null auto_increment unique,
	startDay date,
    endDay date,
    employeeID int,
    approved boolean,
    reason varchar(10),
    primary key (timeOffID),
    foreign key (employeeID) references Employees(employee_ID)
    
);

select * from Employees;

drop table Employees;

drop table Shifts;

drop database ScheduleApp;

INSERT INTO Employees (first_name,last_name,isManager,email,phoneNumber,wage) VALUES ('Rafi', 'Zereselasie',true,'rafi@gmail.com','3062414201',0.1);

DELETE FROM shifts WHERE (shift_id = 3);
DELETE FROM employees WHERE (employee_ID = 3);

#INSERT INTO posts (tp,dt) VALUES ('CMPT353', 'NOV 5');
#select * from posts order by tp desc;
#show tables;
#drop table posts;
#
-- alter user 'root'@'localhost' identified with mysql_native_password by 'password';
-- flush privileges;