CREATE TYPE PersonUdt AS OBJECT(
    personId NUMBER(10),
    firstName VARCHAR(20),
    lastName VARCHAR(30),
    dob DATE,
    gender VARCHAR(10)
)INSTANTIABLE NOT FINAL;


CREATE OR REPLACE TYPE PatientUdt under PersonUdt(
    insurancePlan VARCHAR(100)
);

CREATE OR REPLACE TYPE DoctorUdt under PersonUdt(
    speciality VARCHAR(20),
    yearsExperience NUMBER(2),
  salary NUMBER(10,2)
);

CREATE TABLE Person OF PersonUdt(
    personId PRIMARY KEY
)OBJECT IDENTIFIER IS SYSTEM GENERATED;

CREATE TABLE Patient OF PatientUdt;

CREATE TABLE Doctor OF DoctorUdt;

CREATE TYPE TreatmentUdt AS object(
    tid NUMBER(10),
    duration NUMBER(3),
    medicaments VARCHAR(35),
    description VARCHAR(200),
    patientId REF PatientUdt,
    doctorId REF DoctorUdt
);

CREATE TABLE Treatment OF TreatmentUdt(
    CONSTRAINT treatmentPK PRIMARY KEY (tid),
    patientId SCOPE IS Patient,
    doctorId SCOPE IS Doctor
);

CREATE TYPE AreaUdt AS object(
    aid NUMBER(2),
    name VARCHAR(30),
    location VARCHAR(30)
);

ALTER TYPE AreaUdt add ATTRIBUTE(
    leads REF DoctorUdt
)CASCADE;


ALTER TYPE DoctorUdt ADD ATTRIBUTE(
    areaOfWork REF AreaUdt,
    leaderOf REF AreaUdt
)CASCADE;

CREATE TABLE Area OF AreaUdt(
    CONSTRAINT areaPK PRIMARY KEY(aid),
    leads SCOPE IS Doctor
)OBJECT IDENTIFIER IS SYSTEM GENERATED;


ALTER TABLE Doctor ADD SCOPE FOR(areaOfWork) IS Area;
ALTER TABLE Doctor ADD SCOPE FOR(leaderOf) IS Area;
ALTER TABLE Doctor modify (leaderof REF areaudt NULL);

----constraints

ALTER TABLE Patient ADD CONSTRAINT InsurancePlan_Constraint1 CHECK(insurancePlan IN('Unlimited','Premium','Basic'));
ALTER TABLE Doctor ADD CONSTRAINT specialityName_Constraint CHECK(speciality IN('General Med','Traumatology','Allergology','Radiology','Cardiology','Gerontology','Obstetrics','Pediatrics'));

ALTER TABLE Area ADD CONSTRAINT areaName_Constraint CHECK(name IN('General Med','Traumatology','Allergology','Radiology','Cardiology','Gerontology','Obstetrics','Pediatrics'));

ALTER TABLE Person ADD CONSTRAINT speciality_Constraint CHECK(speciality IN('General Med','Traumatology','Allergology','Radiology','Cardiology','Gerontology','Obstetrics','Pediatrics'));



----inserts

INSERT INTO Person VALUES(2, 'Adriana', 'Valenzuela', TO_DATE('1993/11/24', 'yyyy/mm/dd'), 'female');
INSERT INTO Person VALUES(3, 'Mayra', 'Ruiz', TO_DATE('1995/07/27', 'yyyy/mm/dd'), 'female');

INSERT INTO patient VALUES(6, 'Estefania', 'Guajardo', TO_DATE('1995/01/18', 'yyyy/mm/dd'), 'female', 'Basic');
INSERT INTO patient VALUES(7, 'Carlos', 'Frutos', TO_DATE('1993/12/16', 'yyyy/mm/dd'), 'male', 'Premium');

INSERT INTO doctor VALUES(1, 'Roberto', 'Ruiz', TO_DATE('1994/04/17', 'yyyy/mm/dd'), 'male', 'General Medicine', 5, 50000, (SELECT REF(a) FROM Area a WHERE a.aid = 1), (SELECT REF(a) FROM Area a WHERE a.aid = 1));
INSERT INTO doctor VALUES(4, 'Adriana', 'Amaro', TO_DATE('1993/04/07', 'yyyy/mm/dd'), 'female', 'Cardiology', 6, 90000, (SELECT REF(a) FROM Area a WHERE a.aid = 2), (SELECT REF(a) FROM Area a WHERE a.aid = 2));
INSERT INTO doctor VALUES(5, 'Edgar', 'Turrubiates', TO_DATE('1993/12/19', 'yyyy/mm/dd'), 'male', 'Cardiology', 4, 25000, (SELECT REF(a) FROM Area a WHERE a.aid = 2), NULL);

INSERT INTO Treatment VALUES(1, 8, 'Clopidogrel', 'Iniciar con una dosis única de 300mg de carga oral y luego continuar con 75mg una vez al dia', (SELECT REF(p) FROM Patient p WHERE p.personID = 3), NULL);
INSERT INTO Treatment VALUES(2, 15, 'Trimebutina', 'Tomar una tableta de 200mg cada 12 horas. Evitar harinas, grasas y comidas pesadas', (SELECT REF(p) FROM Patient p WHERE p.personID = 3), (SELECT REF(d) FROM Doctor d WHERE d.personID = 1));
INSERT INTO Treatment VALUES(3, 5, 'Ácido Zoledrónico', 'Suministrarse dentro de un hospital e inyectarse durante 15 minutos. Debe beber 2 vasos de agua antes de inyectarse', (SELECT REF(p) FROM Patient p WHERE p.personID = 2), (SELECT REF(d) FROM Doctor d WHERE d.personID = 1));

INSERT INTO Area VALUES(1, 'General Medicine', '2rd Floor', (SELECT REF(d) FROM Doctor d WHERE d.personID = 1));
INSERT INTO Area VALUES(2, 'Cardiology', '5th Floor', (SELECT REF(d) FROM Doctor d WHERE d.personID = 3));


CREATE OR REPLACE TRIGGER SalaryIncrement
  AFTER UPDATE OF YEARSEXPERIENCE ON DOCTOR
  FOR EACH ROW
BEGIN
    IF MOD(:NEW.YEARSEXPERIENCE,2) = 0 THEN
      UPDATE DOCTOR SET salary = (:old.salary * 0.10 + :old.salary)
      WHERE PERSONID = :old.PERSONID;
    END IF;
END;

CREATE OR REPLACE TRIGGER PatientInsurancePremiumPlan
   BEFORE INSERT
   ON TREATMENT
   FOR EACH ROW
DECLARE
   patientInsurance   EXCEPTION;
   PRAGMA EXCEPTION_INIT (patientInsurance, -20001);
   seguro VARCHAR(100);
   especialidad VARCHAR(15);
BEGIN
  SELECT DEREF(:NEW.patientid).insuranceplan
  INTO seguro
  FROM TREATMENT
  WHERE tid = :NEW.tid;

  SELECT DEREF(:NEW.doctorid).speciality
  INTO especialidad
  FROM TREATMENT
  WHERE tid = :NEW.tid;

  IF seguro = 'Premium' THEN
      IF especialidad = 'General Medicine' OR especialidad = 'Traumatology' OR especialidad = 'Allergology'
      OR especialidad = 'Cardiology' OR especialidad = 'Gerontology' OR especialidad = 'Obstetrics' OR especialidad = 'Pediatrics' THEN
        RAISE patientInsurance;
      END IF;
    END IF;
EXCEPTION
      WHEN patientInsurance THEN
            raise_application_error (-20001, 'Premium Plan does not include Radiology');
END;


CREATE OR REPLACE TRIGGER PatientInsuranceBasicPlan
   BEFORE INSERT
   ON TREATMENT
   FOR EACH ROW
DECLARE
   patientInsurance   EXCEPTION;
   PRAGMA EXCEPTION_INIT (patientInsurance, -20001);
   seguro VARCHAR(100);
   especialidad VARCHAR(30);
BEGIN
  SELECT DEREF(:NEW.patientid).insuranceplan
  INTO seguro
  FROM TREATMENT
  WHERE tid = :NEW.tid;

  SELECT DEREF(:NEW.doctorid).speciality
  INTO especialidad
  FROM TREATMENT
  WHERE tid = :NEW.tid;

  IF seguro =  'Basic' THEN
      IF especialidad  = 'General Medicine' OR especialidad = 'Obstetrics' OR especialidad = 'Pediatrics' THEN
        RAISE patientInsurance;
      END IF;
    END IF;
EXCEPTION
      WHEN patientInsurance THEN
            raise_application_error (-20001, 'Basic Plan can only include General Medicine, Obstetrics and Pediatrics');
END;

CREATE OR REPLACE TRIGGER OnChangeAreaUpdateLeader
   After UPDATE
   ON DOCTOR
   FOR EACH ROW
DECLARE
   varLeader VARCHAR(30);
   maxDoctor NUMBER(3);
BEGIN

  SELECT DEREF(:old.leaderof).name
  INTO varLeader
  FROM Doctor
  WHERE personid = :old.personid;

  SELECT PERSONID
  INTO maxDoctor
  FROM DOCTOR
  WHERE YEARSEXPERIENCE >= (SELECT MAX(YEARSEXPERIENCE) FROM doctor);

  IF varLeader IS NOT NULL THEN
    UPDATE AREA SET LEADS = (SELECT REF(doc) FROM Doctor doc WHERE doc.Personid = maxDoctor) WHERE aid = DEREF(:old.areaofwork).aid;
  END IF;

END;

CREATE OR REPLACE TRIGGER DoctorAreaWork
   BEFORE INSERT
   ON DOCTOR
   FOR EACH ROW
DECLARE
   doctorAreaWorkEx   EXCEPTION;
   PRAGMA EXCEPTION_INIT (doctorAreaWorkEx, -20001);
   areaName VARCHAR(30);
   doctorSpeciality VARCHAR(30);
BEGIN

  IF areaName IS NULL THEN
    RAISE doctorAreaWorkEx;
  END IF;

  SELECT DEREF(:NEW.areaofwork).name
  INTO areaName
  FROM DOCTOR
  WHERE personid = :NEW.personid;

  SELECT :NEW.speciality
  INTO doctorSpeciality
  FROM DOCTOR
  WHERE personid = :NEW.personid;

      IF areaName = doctorSpeciality THEN
        RAISE doctorAreaWorkEx;
    END IF;

EXCEPTION
      WHEN doctorAreaWorkEx THEN
            raise_application_error (-20001, 'A doctor only works on his area of speciality');
END;

CREATE OR REPLACE TRIGGER DoctorLeader
   BEFORE INSERT
   ON DOCTOR
   FOR EACH ROW
DECLARE
   doctorLeaderEx   EXCEPTION;
   PRAGMA EXCEPTION_INIT (doctorLeaderEx, -20001);
   areaName VARCHAR(30);
   doctorLeads VARCHAR(30);
BEGIN
  SELECT DEREF(:NEW.areaofwork).name
  INTO areaName
  FROM DOCTOR
  WHERE personid = :NEW.personid;

  SELECT DEREF(:NEW.leaderof).name
  INTO doctorLeads
  FROM DOCTOR
  WHERE personid = :NEW.personid;

  IF doctorLeads IS NULL THEN
    RAISE doctorLeaderEx;
  END IF;

  IF doctorLeads IS NOT NULL THEN
    IF areaName = doctorLeads THEN
        RAISE doctorLeaderEx;
    END IF;
  END IF;
EXCEPTION
      WHEN doctorLeaderEx THEN
            raise_application_error (-20001, 'A doctor only can lead the area he works on');
END;