Parcel #grcf65iq0m9jrt6

delimiter //

create procedure prescribe
in patient_name_param varchar(255),
in doctor_name_param varchar(255),
in medication_name_param varchar(255),
in ppd_param int

begin
    -- variable declarations
    declare patient_id_var int;
    declare age_var float;
    declare is_pregnant_var boolean;
    declare weight_var int;
    declare doctor_id_var int;
    declare medication_id_var int;
    declare take_under_12_var boolean;
    declare take_if_pregnant_var boolean;
    declare mg_per_pill_var double;
    declare max_mg_per_10kg_var double;
    declare message varchar(255);
    declare ddi_medication varchar(255);

    -- select relevant values into variables
    select patient_id, datediff(curdate(), dob) / 365.25, is_pregnant, weight
    into patient_id_var, age_var, is_pregnant_var, weight_var
    from patient
    where patient_name = patient_name_param;

    select doctor_id into doctor_id_var
    from doctor
    where doctor_name = doctor_name_param;

    select medication_id, take_under_12, take_if_pregnant, mg_per_pill, max_mg_per_10kg
    into medication_id_var, take_under_12_var, take_if_pregnant_var, mg_per_pill_var, max_mg_per_10kg_var
    from medication
    where medication_name = medication_name_param;

    -- check age of patient
    if (age_var < 12 and take_under_12_var = false) then
        select concat(medication_name_param, ' cannot be prescribed to children under 12.') into message;
        signal sqlstate 'HY000' set message_text = message;
    end if;

    -- check if medication ok for pregnant women (or the patient is not pregnant)
    if (is_pregnant_var = true and take_if_pregnant_var = false) then
        select concat(medication_name_param, ' cannot be prescribed to pregnant women.') into message;
        signal sqlstate 'HY000' set message_text = message;
    end if;

    -- Check for drug-drug interactions
    select m.medication_name into ddi_medication
    from prescription p
    join interaction i on p.medication_id = i.medication_1
    join medication m on i.medication_2 = m.medication_id
    where p.patient_id = patient_id_var and i.medication_2 = medication_id_var
    limit 1;

    if ddi_medication is not null then
        select concat(medication_name_param, ' interacts with ', ddi_medication, ' currently prescribed to ', patient_name_param) into message;
        signal sqlstate 'HY000' set message_text = message;
    end if;

    -- No exceptions thrown, so insert the prescription record
    insert into prescription (medication_id, patient_id, doctor_id, prescription_dt, ppd)
    values (medication_id_var, patient_id_var, doctor_id_var, now(), ppd_param);

end //

delimiter ;