-- tables
-- Table: Appointment
CREATE TABLE Appointment (
    Appointment_Id Integer              NOT NULL,
    Department_name varchar2(50)        NOT NULL,
    Patient_complaint varchar2(15)      NOT NULL,
    CONSTRAINT Appointment_pk PRIMARY KEY (Appointment_Id)
) ;
insert into Appointment(Appointment_id, Department_name, Patient_complaint) values(1, 'otorhinolaryngology', 'Ear');
insert into Appointment(Appointment_id, Department_name, Patient_complaint) values(2, 'otorhinolaryngology', 'Pest');
insert into Appointment(Appointment_id, Department_name, Patient_complaint) values(3, 'otorhinolaryngology', 'Nose');
insert into Appointment(Appointment_id, Department_name, Patient_complaint) values(4, 'dietitian', 'Over-weight');

select Department_name, Patient_complaint from Appointment;

-- Table: Doctor
CREATE TABLE Doctor (
    Doctor_Id integer                   NOT NULL,
    eName varchar2(25)                  NOT NULL,
    Date_of_birth date                  NOT NULL,
    Profession varchar2(25)             NOT NULL,
    Start_date_job date                 NOT NULL,
    Appointment_Appointment_Id Integer      NULL,
    CONSTRAINT Doctor_pk PRIMARY KEY (Doctor_Id)
) ;

insert into Doctor(Doctor_Id, eName, Date_of_birth, Profession, Start_date_job)values(1, 'Jones',DATE '1975-03-06', 'otorhinolaryngology', DATE  '2010-06-10');
insert into Doctor(Doctor_Id, eName, Date_of_birth, Profession, Start_date_job)values(2, 'Michal',DATE '1979-03-05', 'dietitian', DATE  '2017-05-07');
insert into Doctor(Doctor_Id, eName, Date_of_birth, Profession, Start_date_job)values(3, 'Anna',DATE '1985-07-09', 'Surgeon', DATE  '2000-05-10');
insert into Doctor(Doctor_Id, eName, Date_of_birth, Profession, Start_date_job)values(4, 'Oliver',DATE '1982-03-05', 'esthesitc', DATE  '2012-10-01');

select eName, Date_of_birth,Profession from Doctor;


-- Table: Hospital
CREATE TABLE Hospital (
    Hospital_Id integer                 NOT NULL,
    Name_Hos varchar2(50)               NOT NULL,
    Address varchar2(50)                NOT NULL,
    Phone integer                       NOT NULL,
    Fax integer                         NOT NULL,
    Email varchar2(50)                  NOT NULL,
    Standart_room_number integer        NOT NULL,
    Private_room_number integer         NOT NULL,
    Visit_Visit_Id integer                  NULL,
    CONSTRAINT Hospital_pk PRIMARY KEY (Hospital_Id)
) ;

    insert into Hospital (Hospital_Id, Name_Hos, Address, Phone, Fax,  Email, Standart_room_number, Private_room_number) values (1, 'Gazi Hospital',             'xxxAddresxxx', 123123123, 123123, 'sdadada@gmail.com', 60,40);
    insert into Hospital (Hospital_Id, Name_Hos, Address, Phone, Fax,  Email, Standart_room_number, Private_room_number) values (2, 'Hacettepe Hospital',        'xxxAddressxx', 123123123, 112123, 'sdadada@gmail.com', 70,50);
    insert into Hospital (Hospital_Id, Name_Hos, Address, Phone, Fax,  Email, Standart_room_number, Private_room_number) values (3, 'Ankara university hospital','xxxAddressxx', 123123123, 122313, 'sdadada@gmail.com', 50,30);

      select Name_Hos, Address, Phone, Fax from Hospital;

-- Table: Patient
CREATE TABLE Patient (
    Patient_Id integer                  NOT NULL,
    eName varchar2(25)                  NOT NULL,
    Surname varchar2(25)                NOT NULL,
    Date_of_birth date                  NOT NULL,
    Gender varchar2(25)                 NOT NULL,
    Visit_Visit_Id integer                  NULL,
    CONSTRAINT Patient_pk PRIMARY KEY (Patient_Id)
) ;

    insert into Patient (Patient_Id, eName, Surname, Date_of_birth, Gender)values (1,'Clark', 'Monasz', DATE  '2018-05-10','Women');
    insert into Patient (Patient_Id, eName, Surname, Date_of_birth, Gender)values (2,'Liam', 'Taylor', DATE  '2018-05-10','Man');
    insert into Patient (Patient_Id, eName, Surname, Date_of_birth, Gender)values (3,'Lewis', 'Ewans', DATE  '2018-05-10','Man');
    insert into Patient (Patient_Id, eName, Surname, Date_of_birth, Gender)values (4,'Lee', 'Wilson', DATE  '2018-05-10','Man');

      select eName, Date_of_birth, Gender from Patient;


-- Table: Patient_move_information
CREATE TABLE Patient_move_information (
    Patient_move_information_Id integer  NOT NULL,
    Made_operation varchar2(25)          NOT NULL,
    Patient_Patient_Id integer              NULL,
    CONSTRAINT Patient_move_information_pk PRIMARY KEY (Patient_move_information_Id)
) ;

    insert into Patient_move_information(Patient_move_information_Id, Made_operation) values(1, 'Test');
    insert into Patient_move_information(Patient_move_information_Id, Made_operation) values(2, 'Surgery');
    insert into Patient_move_information(Patient_move_information_Id, Made_operation) values(3, 'Take blood');

      select Made_operation from Patient_move_information;


-- Table: Room_information
CREATE TABLE Room_information (
    Room_information_Id integer         NOT NULL,
    Bed_number integer                  NOT NULL,
    Private_room varchar2(25)           NOT NULL,
    Standart_room varchar2(25)          NOT NULL,
    Price integer                       NOT NULL,
    Patient_move_info_Id integer            NULL,
    CONSTRAINT Room_information_pk PRIMARY KEY (Room_information_Id)
) ;

    insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Price) values(1, 2, 'No',  'Yes', 500);
    insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Price) values(2, 1, 'Yes', 'No', 1000);
    insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Price) values(3, 2, 'No',  'Yes', 700);

      select Bed_number, Private_room, Standart_room, Price from Room_Information;

      select Bed_number, Private_room, Standart_room, Price from Room_Information;
      --when you want see max value from patient you have to use select max
      Select max(price) from Room_Information;
      --when you want see mix value from patient you have to use select min
      Select min(price) from Room_Information;

-- Table: Visit
CREATE TABLE Visit (
    Visit_Id integer                    NOT NULL,
    Which_hospital varchar2(50)         NOT NULL,
    Date_of_appointment date            NOT NULL,
    Appointment_Appointment_Id Integer      NULL,
    Patient_move_info_Id integer            NULL,
    CONSTRAINT Visit_pk PRIMARY KEY (Visit_Id)
) ;
    insert into Visit(Visit_Id, Which_hospital, Date_of_appointment) values(1, 'Gazi Hospital',DATE '2019-10-06');
    insert into Visit(Visit_Id, Which_hospital, Date_of_appointment) values(2, 'Hacettepe Hospital',DATE '2019-07-20');
    insert into Visit(Visit_Id, Which_hospital, Date_of_appointment) values(3, 'Ankara University Hospital',DATE '2019-11-12');

    Select Which_hospital, Date_of_appointment from Visit;

-- foreign keys
-- Reference: Doctor_Appointment (table: Doctor)
ALTER TABLE Doctor ADD CONSTRAINT Doctor_Appointment
    FOREIGN KEY (Appointment_Appointment_Id)
    REFERENCES Appointment (Appointment_Id);

-- Reference: Hospital_Visit (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Visit
    FOREIGN KEY (Visit_Visit_Id)
    REFERENCES Visit (Visit_Id);

-- Reference: Patient_Visit (table: Patient)
ALTER TABLE Patient ADD CONSTRAINT Patient_Visit
    FOREIGN KEY (Visit_Visit_Id)
    REFERENCES Visit (Visit_Id);

-- Reference: Patient_move_info_Patient (table: Patient_move_information)
ALTER TABLE Patient_move_information ADD CONSTRAINT Patient_move_info_Patient
    FOREIGN KEY (Patient_Patient_Id)
    REFERENCES Patient (Patient_Id);

-- Reference: Room_info_Patient_move_info (table: Room_information)
ALTER TABLE Room_information ADD CONSTRAINT Room_info_Patient_move_info
    FOREIGN KEY (Patient_move_info_Id)
    REFERENCES Patient_move_information (Patient_move_information_Id);

-- Reference: Visit_Appointment (table: Visit)
ALTER TABLE Visit ADD CONSTRAINT Visit_Appointment
    FOREIGN KEY (Appointment_Appointment_Id)
    REFERENCES Appointment (Appointment_Id);

-- Reference: Visit_Patient_move_information (table: Visit)
ALTER TABLE Visit ADD CONSTRAINT Visit_Patient_move_information
    FOREIGN KEY (Patient_move_info_Id)
    REFERENCES Patient_move_information (Patient_move_information_Id);