ScheduleApp.sql

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,
    positions varchar(50),
    primary key (employee_ID)
);
    
INSERT INTO Employees (first_name,last_name,isManager,email,phoneNumber,wage) VALUES ('Admin', 'Admin',true,'none','0000000000',0.0);

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

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

create table Availabilities(
	ID int not null auto_increment unique,
    employeeID int unique,
    sundayStart int default 0,
    sundayEnd int default 2359,
    mondayStart int default 0,
    mondayEnd int default 2359,
    tuesdayStart int default 0,
    tuesdayEnd int default 2359,
    wednesdayStart int default 0,
    wednesdayEnd int default 2359,
    thursdayStart int default 0,
    thursdayEnd int default 2359,
    fridayStart int default 0,
    fridayEnd int default 2359,
    saturdayStart int default 0,
    saturdayEnd int default 2359,
    foreign key (employeeID) references Employees(employee_ID)
);

create table Positions(
	ID int not null auto_increment unique,
    position varchar(20) not null unique,
    wage float not null,
    primary key (position)
);

insert into Positions (position, wage) values ("dishwasher", 12.50);

delete from Positions where position='dishwasher';

select * from Positions;

select * from Employees;


drop table Employees;

drop database ScheduleApp;

update Shifts set availability=true where shift_id=5;

insert into TimeOff (employeeID, startDate, endDate, approved, reason) values (3, "2022-03-17", "2022-03-17", true, "Vacation"); 

update TimeOff set approved=true where ID=3

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

DELETE FROM TimeOff WHERE (ID = 2);
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;