-- A stored procedure to process and validate prescriptions
-- Four things we need to check
-- a) Is patient a child and is medication suitable for children?
-- b) Is patient pregnant and is medication suitable for pregnant women?
-- c) Are there any adverse drug reactions

drop procedure if exists prescribe;

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); -- The error message
    declare ddi_medication varchar(255); -- The name of a medication involved in a drug-drug interaction

    -- select relevant values into variables

    -- check age of patient
    -- I did one for you! This shows how to throw an exception and set an error message.
    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

    -- Check for reactions involving medications already prescribed to patient

    -- No exceptions thrown, so insert the prescription record

end //
delimiter ;

-- Trigger

DROP TRIGGER IF EXISTS patient_after_update_pregnant;

DELIMITER //

CREATE TRIGGER patient_after_update_pregnant
    AFTER UPDATE ON patient
    FOR EACH ROW
BEGIN

    -- Patient became pregnant

        -- Add pre-natal recommenation
        -- Delete any prescriptions that shouldn't be taken if pregnant

    -- Patient is no longer pregnant
    -- Remove pre-natal recommendation

END //

DELIMITER ;