use company;
drop database company;
drop schema companytables;
create database company;
create schema companyTables;
use company;
CREATE TABLE patient (
    ID int primary Key,
    CoID varchar(6) ,
    Name varchar(50),
    telephone varchar(30),
    address varchar(150),
    birthDay date,
    enteranceDate date,
    parentStatus varchar(15),
    documents varchar(15)
);

create table provider (
    PID int primary key ,
    PName varchar(50),
    status varchar (7)
);

create table service (
    SID int primary key,
    SName varchar(50),
    status varchar (7)

);

create table providerService(
    PID int ,
    SID int,
    primary key (PID,SID)
);

create table doctor (
    DID int primary key ,
    DName varchar(50),
    DType int
) ;

create table patientServiceProvider (
    patientID int,
    serviceID int ,
    providerID int,
    Day varchar(10),
    startTime varchar(10),
    startDate date,
    primary key(patientID,serviceID,providerID,Day,startTime),
    foreign key (patientID) references patient (ID),
    foreign key (providerID) references provider (PID),
    foreign key (serviceID) references service (SID)


);


create table waitingList (
    name varchar(50),
    serviceID int ,
    providerID int,
    startDate date,
    telephone varchar(30),
    primary key(name,serviceID,providerID),
    foreign key (providerID) references provider (PID),
    foreign key (serviceID) references service (SID)


);

create table patientDoctor (
    patientID int,
    doctorID int,
    primary key(patientID,doctorID),
    foreign key (patientID) references patient (ID),
    foreign key (doctorID) references doctor (DID)

);

create table patientService (
    patientID int,
    serviceID int,
    monthReport int,
    paymentMethod varchar(15),
    paid varchar(3) default 'No',
    lastPayment date,
    primary key(patientID,serviceID),
    foreign key (patientID) references patient (ID),
    foreign key (serviceID) references service (SID)

);

create table history(
    patientID int ,
    SDate date,
    startTime varchar(10),
    serviceID int,
    providerID int,
    status int,
    primary key (patientID,SDate,startTime),
    foreign key (patientID) references patient (ID),
    foreign key (serviceID) references service (SID),
    foreign key (providerID) references provider (PID)

);
create table reason(
    PID int primary key,
    endDate date,
    reason varchar(255),
    foreign key (PID) references patient (ID)
);
CREATE TABLE help (
    ID int primary key,
    CoID varchar(6) ,
    Name varchar(50),
    telephone varchar(30),
    address varchar(150),
    birthDay date,
    enteranceDate date,
    parentStatus varchar(15),
    documents varchar(15)
);

CREATE TABLE pass (
    password varchar(60)
);

ALTER TABLE waitingList
ADD COLUMN notes VARCHAR(150) AFTER telephone;
insert into pass values("$2a$12$B8hSSrgXcbGSRKu1OvBIqe.WiGuYvPXOlUxvobhLZk7o5n78eWeQe");


drop table patientServiceProvider;

DELETE  from patient where ID = 1;

use company;
select * from waitingList ;
insert into patient values(1,"4AZ2","fady","01286493735 - 01286493735","st",'1995-11-10','2005-2-5',true,false);
select * from patient ;
select * from doctor ;
select * from service ;
select * from patientService;
select * from patientDoctor ;
select * from provider;
select * from patientServiceProvider;

select distinct patientID,serviceID,providerID from patientServiceProvider;
select * from providerService;
select * from reason;
drop table waitingList;

select * from help;
DELETE  from reason where PID =1  ;
insert into provider values(1,"fady");
insert into provider values(2,"mina");
insert into provider values(3,"peter");
insert into waitingList values("ahmed adel",1,1,'2018-12-21',"6751312");
insert into waitingList values("mohamed",1,2,'2018-12-21',"65276287");
insert into waitingList values("sayed",2,1,'2018-12-21');
insert into waitingList values(2,1,2);


select * from patientServiceProvider where patientID = 1 and serviceID = 1 and providerID =1 and Day ="Friday" ;
insert into patientServiceProvider values(1,1,1,"Friday","6:03 PM",'2018-02-01');

select min(startDate) from patientServiceProvider;
 update patientService set paymentMethod = 'month' and lastPayment='2018-12-21' where patientID = 4 and serviceID = 1;

select * from patientServiceProvider where Day = "Monday"  ;

select count(distinct(day)) from patientServiceProvider where providerID = 3  ;
select * from patient ;