DROP FUNCTION IF EXISTS bd_hospital.getCountIllnes;
DELIMITER //
CREATE FUNCTION bd_hospital.getCountIllness (illnes_name_p VARCHAR(50), startDate DATE, endDate DATE)
RETURNS INT
BEGIN
    DECLARE current_d_id INT;
    DECLARE current_i_id VARCHAR(50);
    DECLARE COUNT INT;
    DECLARE LENGTH INT;
    DECLARE cur_close_date DATE;
    DECLARE illnes_name_u VARCHAR(50);
    DECLARE cur cursor FOR (SELECT illness_name AS current_i_id, id_diagnosis AS current_d_id  FROM bd_hospital.map_diagnosis_illness);
    SET illnes_name_u = UPPER(illnes_name_p);
    SET LENGTH = (SELECT COUNT(*) FROM bd_hospital.map_diagnosis_illness);
    SET COUNT = 0;
    OPEN cur;
    loop_label:  LOOP
    fetch cur INTO current_i_id ,current_d_id ;
    IF current_i_id = illnes_name_u THEN
        SET cur_close_date = (SELECT close_date FROM bd_hospital.diagnosis  WHERE current_d_id = diagnosis.id);
        IF
        startDate < (SELECT open_date FROM bd_hospital.diagnosis WHERE current_d_id = diagnosis.id)
        AND
        (cur_close_date IS NULL OR endDate > cur_close_date)
        THEN
            SET COUNT =  COUNT + 1;
        END IF;
    END IF;
    SET LENGTH = LENGTH - 1;
    IF LENGTH = 0 THEN close cur; RETURN COUNT; END IF;
    END LOOP;
    RETURN 0;
END//

DROP PROCEDURE IF EXISTS bd_hospital.add_patientByName;
DELIMITER //
CREATE PROCEDURE bd_hospital.add_patientByName (IN _name VARCHAR(256))
BEGIN
DECLARE last_id INT;
INSERT person (name) VALUES (_name);
SET last_id = (SELECT LAST_INSERT_ID());
INSERT patient(id_person) VALUES(last_id);
END//

CREATE PROCEDURE bd_hospital.add_doctorByName (IN _name VARCHAR(256))
BEGIN
DECLARE last_id INT;
INSERT person (name) VALUES (_name);
SET last_id = (SELECT LAST_INSERT_ID() );
INSERT bd_hospital.doctor(id_person) VALUES(last_id);
END//



DROP FUNCTION IF EXISTS bd_hospital.checkValidPatient;
DELIMITER //
CREATE FUNCTION bd_hospital.checkValidPatient(id_person_p INT)
RETURNS bool
BEGIN
DECLARE c INT;
SET c = (SELECT COUNT(*) FROM bd_hospital.doctor WHERE doctor.id_person = id_person_p);
IF c = 0 THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END//

DROP FUNCTION IF EXISTS bd_hospital.checkValidDoctor;
DELIMITER //
CREATE FUNCTION bd_hospital.checkValidDoctor(id_person_doc INT)
RETURNS bool
BEGIN
DECLARE c INT;
SET c = (SELECT COUNT(*) FROM bd_hospital.patient WHERE .patient.id_person = id_person_doc);
IF c = 0 THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END//


DROP TRIGGER IF EXISTS bd_hospital.onInsertDoctor;
DELIMITER //

CREATE TRIGGER bd_hospital.onInsertDoctor BEFORE INSERT ON bd_hospital.doctor FOR EACH ROW
BEGIN
DECLARE new_id INT;
SET new_id = NEW.id_person;
IF NOT(checkValidDoctor(new_id)) THEN
   SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'You can not add this doctor becouse it is a pacient';
END IF;
END//

DROP TRIGGER IF EXISTS bd_hospital.onUpdateDoctor;
DELIMITER //

CREATE TRIGGER bd_hospital.onUpdateDoctor BEFORE UPDATE ON bd_hospital.doctor FOR EACH ROW
BEGIN
DECLARE new_id INT;
SET new_id = NEW.id_person;
IF NOT(checkValidDoctor(new_id)) THEN
   SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'You can not add this doctor becouse it is a pacient';
END IF;
END//

DROP TRIGGER IF EXISTS bd_hospital.onInsertPatient;
DELIMITER //

CREATE TRIGGER bd_hospital.onInsertPatient BEFORE INSERT ON bd_hospital.patient FOR EACH ROW
BEGIN
DECLARE new_id INT;
SET new_id = NEW.id_person;
IF NOT(checkValidPatient(new_id)) THEN
   SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'You can not add this patient becouse it is a doctor';
END IF;
END//


DROP TRIGGER IF EXISTS bd_hospital.onUpdatePatient;
DELIMITER //
CREATE TRIGGER bd_hospital.onUpdatePatient BEFORE UPDATE ON bd_hospital.patient FOR EACH ROW
BEGIN
DECLARE new_id INT;
SET new_id = NEW.id_person;
IF NOT(checkValidPatient(new_id)) THEN
   SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'You can not set this patient becouse it is a doctor';
END IF;
END//

DROP PROCEDURE IF EXISTS bd_hospital.get_doctor_congestion;
DELIMITER //
CREATE PROCEDURE bd_hospital.get_doctor_congestion()
BEGIN
DECLARE LENGTH INT;
DECLARE doc_id INT;
DECLARE doc_c INT;
DECLARE doc_n VARCHAR(50);
DECLARE cur cursor FOR (SELECT id AS doc_id  FROM bd_hospital.doctor);
CREATE TEMPORARY TABLE IF NOT EXISTS bd_hospital.doctor_congestion (
    doctor_name VARCHAR(256),
    COUNT INT
);
TRUNCATE TABLE bd_hospital.doctor_congestion;
SET LENGTH = (SELECT COUNT(*) FROM bd_hospital.doctor);
OPEN cur;
loop_label:  LOOP
    fetch cur INTO doc_id;
    SET doc_c = 0;
    SET doc_c = doc_c + (SELECT COUNT(*) FROM bd_hospital.patient WHERE patient.id_doctor = doc_id);
    SET doc_n = (SELECT name FROM person JOIN doctor ON(doctor.id_person = person.id AND doctor.id = doc_id));
    INSERT bd_hospital.doctor_congestion(doctor_name, COUNT) VALUES (doc_n,doc_c);
    SET LENGTH = LENGTH - 1;
    IF LENGTH = 0 THEN close cur; leave loop_label;  END IF;
    END LOOP;
SELECT * FROM bd_hospital.doctor_congestion ORDER BY bd_hospital.doctor_congestion.COUNT DESC;
END//


DROP TRIGGER IF EXISTS bd_hospital.onAddIll;
DELIMITER //
CREATE TRIGGER bd_hospital.onAddIll BEFORE UPDATE ON bd_hospital.illness FOR EACH ROW
BEGIN
    SET NEW.name = UPPER(NEW.name);
END//

DROP TRIGGER IF EXISTS bd_hospital.onUpdateIll;
DELIMITER //
CREATE TRIGGER bd_hospital.onUpdateIll BEFORE INSERT ON bd_hospital.illness FOR EACH ROW
BEGIN
    SET NEW.name = UPPER(NEW.name);
END//