DROP TABLE Patient;
DROP TABLE Proffesion;
DROP TABLE Doctor;
DROP TABLE Schedule;
DROP TABLE Service;
DROP TABLE Service_provided;
DROP TABLE Visit;
CREATE TABLE Patient(
patientPesel VARCHAR2(11) PRIMARY KEY,
name VARCHAR2(30),
surname VARCHAR2(30),
address VARCHAR2(100),
sex CHAR(1) CONSTRAINT PatientSex CHECK (sex IN ('M', 'F')));

CREATE TABLE Proffesion(
proffesionId NUMBER PRIMARY KEY,
name VARCHAR2(30));

CREATE TABLE Doctor(
pwz VARCHAR2(7) PRIMARY KEY,
name VARCHAR2(30),
surname VARCHAR2(30),
proffesion NUMBER REFERENCES Proffesion (proffesionId));

CREATE TABLE Schedule(
periodId NUMBER PRIMARY KEY,
scheduleFrom TIMESTAMP,
scheduleTo TIMESTAMP,
pwz VARCHAR2(7) REFERENCES Doctor(pwz));

CREATE TABLE Service(
serviceId NUMBER PRIMARY KEY,
serviceName VARCHAR2(50),
serviceDuration NUMBER,
price DECIMAL);

CREATE TABLE Visit(
visitId NUMBER PRIMARY KEY,
time_created TIMESTAMP,
patientPesel VARCHAR2(11) REFERENCES Patient(patientPesel),
pwz VARCHAR(7) REFERENCES Doctor(pwz),
start_time TIMESTAMP,
end_time_expected TIMESTAMP,
total_price DECIMAL,
discount DECIMAL,
final_price DECIMAL,
canceled CHAR(1) CONSTRAINT IsCanceled CHECK (canceled IN ('Y', 'N')),
canceled_reason VARCHAR2(250));

CREATE TABLE Service_provided(
visitId NUMBER NOT NULL,
serviceId NUMBER NOT NULL,
CONSTRAINT Service_provided_pk PRIMARY KEY (visitId, serviceId)
);