-- Table: Ambulance
CREATE TABLE Ambulance
  (
    Ambulance_Id   integer      NOT NULL,
    Plaque_No      varchar2(10) NOT NULL,
    CONSTRAINT Ambulance_pk PRIMARY KEY (Ambulance_Id)
  );

    insert into Ambulance(Ambulance_Id, Plaque_No) values(1, '13AY755');
    insert into Ambulance(Ambulance_Id, Plaque_No) values(2, '06SAD23');
    insert into Ambulance(Ambulance_Id, Plaque_No) values(3, '06AY645');

    select * from Ambulance;

-- Table: Appointment
CREATE TABLE Appointment
  (
    Appointment_id      integer       NOT NULL,
    Department_name     varchar2(50)  NOT NULL,
    Doctor_Identify     integer       NOT NULL,
    Doctor_Gender       varchar2(1)   NOT NULL,
    Patient_number      integer       NOT NULL,
    Date_of_birth       date          NOT NULL,
    date_of_appointment date          NOT NULL,
    Patient_complaint   varchar(15)   NOT NULL,
    CONSTRAINT Appointment_pk PRIMARY KEY (Appointment_id)
  );

    insert into Appointment(Appointment_id, Department_name, Doctor_Identify, Doctor_Gender, Patient_number,Date_of_birth, date_of_appointment,Patient_complaint) values(1, 'otorhinolaryngology', 123456, 'M', 123123, DATE '1955-01-03', DATE '2019-05-07', 'Ear'        );
    insert into Appointment(Appointment_id, Department_name, Doctor_Identify, Doctor_Gender, Patient_number,Date_of_birth, date_of_appointment,Patient_complaint) values(2, 'dietitian',           123426, 'W', 112123, DATE '1979-02-05', DATE '2019-12-06', 'Over-weight');
    insert into Appointment(Appointment_id, Department_name, Doctor_Identify, Doctor_Gender, Patient_number,Date_of_birth, date_of_appointment,Patient_complaint) values(3, 'otorhinolaryngology', 123154, 'M', 122312, DATE '1965-12-06', DATE '2019-12-10', 'Nose'       );

    select * from Appointment;
     --It will be show only man if I change m it will be show women
    select * from Appointment where Doctor_Gender = 'M';

-- Table: Hospital
CREATE TABLE Hospital
  (
    Hospital_Id                 integer        NOT NULL,
    eName                       varchar2(150)  NOT NULL,
    Address                     varchar2(200)  NOT NULL,
    Phone                       integer        NOT NULL,
    Fax                         integer        NOT NULL,
    Email                       varchar2(100)  NOT NULL,
    Standart_room_number        integer        NOT NULL,
    Private_room_number         integer        NOT NULL,
    Ambulance_Ambulance_Id      integer            NULL,
    Room_info_id                integer            NULL,
    Personel_Info_Personal_Id   integer            NULL,
    Patient_info_id             integer            NULL,
    Appointment_Appointment_id  integer            NULL,
    CONSTRAINT Hospital_pk PRIMARY KEY (Hospital_Id)
  );

    insert into Hospital (Hospital_Id, eName, 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, eName, 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, eName, Address, Phone, Fax,  Email, Standart_room_number, Private_room_number) values (3, 'Ankara university hospital','xxxAddressxx', 123123123, 122313, 'sdadada@gmail.com', 50,30);

      select * from Hospital;

-- Table: Information_about_patient
CREATE TABLE Information_about_patient
  (
    Patient_Id                  integer        NOT NULL,
    Name_Surname                varchar2(100)  NOT NULL,
    Gender                      varchar2(15)   NOT NULL,
    Hospital_Hospital_Id        integer            NULL,
    Ambulance_Ambulance_Id      integer            NULL,
    movement_information_Id     integer            NULL,
    Room_information_Id         integer            NULL,
    CONSTRAINT Information_about_patient_pk PRIMARY KEY (Patient_Id)
  );

    insert into Information_about_patient (Patient_Id, Name_Surname, Gender) values(1, 'Clark', 'Man');
    insert into Information_about_patient (Patient_Id, Name_Surname, Gender) values(2, 'Emma',  'Women');
    insert into Information_about_patient (Patient_Id, Name_Surname, Gender) values(3, 'Emily', 'Women');

      select * from Information_about_patient where Gender = 'Women';

-- Table: Patient_movement_information
CREATE TABLE Patient_movement_information
  (
    Patient_move_info_Id integer        NOT NULL,
    Made_operation       varchar2(100)  NOT NULL,
    Price                integer        NOT NULL,
    CONSTRAINT Patient_movement_informatio_pk PRIMARY KEY (Patient_move_info_Id)
  );

    insert into Patient_movement_information(Patient_move_info_Id, Made_operation, Price) values(1, 'Test',       500);
    insert into Patient_movement_information(Patient_move_info_Id, Made_operation, Price) values(2, 'Surgery',   1000);
    insert into Patient_movement_information(Patient_move_info_Id, Made_operation, Price) values(3, 'Take blood', 300);

      select * from Patient_movement_information;
      --when you want see max value from patient you have to use select max
      Select max(price) from Patient_movement_information;
      --when you want see mix value from patient you have to use select min
      Select min(price) from Patient_movement_information;

-- Table: Personel_Information
CREATE TABLE Personel_Information
  (
    Personal_Id       integer        NOT NULL,
    Idenity_sumber    integer        NOT NULL,
    Name_surname      varchar2(150)  NOT NULL,
    Profession        varchar2(150)  NOT NULL,
    Start_date_to_job date           NOT NULL,
    info_Patient_Id   integer            NULL,
    CONSTRAINT Personel_Information_pk PRIMARY KEY (Personal_Id)
  );

    insert into Personel_Information(Personal_Id, Idenity_sumber, Name_surname, Profession, Start_date_to_job) values(1, 123456, 'Clark surname', 'Ear',       DATE  '2018-05-10');
    insert into Personel_Information(Personal_Id, Idenity_sumber, Name_surname, Profession, Start_date_to_job) values(2, 234562, 'Michal qwrewrx', 'Surgeon',  DATE  '2018-10-05');
    insert into Personel_Information(Personal_Id, Idenity_sumber, Name_surname, Profession, Start_date_to_job) values(3, 345614, 'Emma ewrrwx',   'Throat',    DATE  '2018-10-05');

      select * from Personel_Information;

-- Table: Room_Information
CREATE TABLE Room_Information
  (
    Room_information_Id  integer        NOT NULL,
    Bed_number           integer        NOT NULL,
    Private_room         varchar2(10)   NOT NULL,
    Standart_room        varchar2(10)   NOT NULL,
    Hospital_name        varchar2(150)  NOT NULL,
    CONSTRAINT Room_Information_pk PRIMARY KEY (Room_information_Id)
  );

    insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Hospital_name) values(1, 2, 'No',  'Yes', 'Gazi Hospital');
    insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Hospital_name) values(2, 1, 'Yes', 'No',  'Hacettepe Hospital');
    insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Hospital_name) values(3, 2, 'No',  'Yes', 'Ankara university hospital');

      select * from Room_Information;

-- foreign keys
-- Reference: Hospital_Ambulance (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Ambulance
    FOREIGN KEY (Ambulance_Ambulance_Id)
    REFERENCES Ambulance (Ambulance_Id);

-- Reference: Hospital_Appointment (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Appointment
    FOREIGN KEY (Appointment_Appointment_id)
    REFERENCES Appointment (Appointment_id);

-- Reference: Hospital_Patient (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Patient
    FOREIGN KEY (Patient_info_id)
    REFERENCES Patient_movement_information (Patient_move_info_Id);

-- Reference: Hospital_Personel (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Personel
    FOREIGN KEY (Personel_Info_Personal_Id)
    REFERENCES Personel_Information (Personal_Id);

-- Reference: Hospital_Room (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Room
    FOREIGN KEY (Room_info_id)
    REFERENCES Room_Information (Room_information_Id);

-- Reference: Info_patient_Ambulance (table: Information_about_patient)
ALTER TABLE Information_about_patient ADD CONSTRAINT Info_patient_Ambulance
    FOREIGN KEY (Ambulance_Ambulance_Id)
    REFERENCES Ambulance (Ambulance_Id);

-- Reference: Info_patient_Hospital (table: Information_about_patient)
ALTER TABLE Information_about_patient ADD CONSTRAINT Info_patient_Hospital
    FOREIGN KEY (Hospital_Hospital_Id)
    REFERENCES Hospital (Hospital_Id);

-- Reference: Personel_Information (table: Personel_Information)
ALTER TABLE Personel_Information ADD CONSTRAINT Personel_Information
    FOREIGN KEY (info_Patient_Id)
    REFERENCES Information_about_patient (Patient_Id);

-- Reference: patient_Room_info (table: Information_about_patient)
ALTER TABLE Information_about_patient ADD CONSTRAINT patient_Room_info
    FOREIGN KEY (Room_information_Id)
    REFERENCES Room_Information (Room_information_Id);

-- Reference: patient_move_info (table: Information_about_patient)
ALTER TABLE Information_about_patient ADD CONSTRAINT patient_move_info
    FOREIGN KEY (movement_information_Id)
    REFERENCES Patient_movement_information (Patient_move_info_Id);