Triggers for Artem

-- 1 employees
CREATE OR REPLACE FUNCTION check_is_joined_before_born()
    RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.joined_at < NEW.date_of_birth
    THEN
        RAISE EXCEPTION 'Employee must be born before joining the airport';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER verify_joined_date
    BEFORE INSERT OR UPDATE
    ON employees
    FOR EACH ROW
EXECUTE FUNCTION check_is_joined_before_born();

-- 2 employees
CREATE OR REPLACE FUNCTION check_is_brigade_specialization_correct()
    RETURNS TRIGGER AS
$$
BEGIN
    IF NOT (SELECT specializations.name FROM specializations WHERE id = NEW.specialization_id) =
           (SELECT s.name
            FROM brigades
                     INNER JOIN specializations s ON s.id = brigades.specialization_id
            WHERE brigades.id = NEW.brigade_id)
    THEN
        RAISE EXCEPTION 'Brigade is not associated with the given specialization';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER verify_specialization
    BEFORE INSERT OR UPDATE
    ON employees
    FOR EACH ROW
EXECUTE FUNCTION check_is_brigade_specialization_correct();

-- 1 departments

CREATE OR REPLACE FUNCTION check_is_department_unique()
    RETURNS TRIGGER AS
$$
BEGIN
    IF EXISTS (SELECT 1 FROM departments WHERE departments.name = NEW.name)
    THEN
        RAISE EXCEPTION 'Department name must be unique';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER verify_department_name
    BEFORE INSERT OR UPDATE
    ON departments
    FOR EACH ROW
EXECUTE FUNCTION check_is_department_unique();

-- 2 departments

CREATE OR REPLACE FUNCTION check_is_chef_from_same_department()
    RETURNS TRIGGER AS
$$
BEGIN
    IF NOT (SELECT departments.name
            FROM departments
                     INNER JOIN departments_chiefs dc ON departments.id = dc.department_id
            WHERE departments.name = NEW.name) = (SELECT d.name
                                                  FROM employees
                                                           INNER JOIN departments d ON d.id = employees.department_id
                                                  WHERE employees.id = NEW.chef_id)
    THEN
        RAISE EXCEPTION 'Employee must belong to the given department';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER verify_department_chef
    BEFORE INSERT OR UPDATE
    ON departments
    FOR EACH ROW
EXECUTE FUNCTION check_is_chef_from_same_department();

-- 1 brigades

CREATE OR REPLACE FUNCTION check_is_brigade_unique()
    RETURNS TRIGGER AS
$$
BEGIN
    IF EXISTS (SELECT 1 FROM brigades WHERE brigades.name = NEW.name)
    THEN
        RAISE EXCEPTION 'Brigade name must be unique';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER verify_brigade_name
    BEFORE INSERT OR UPDATE
    ON brigades
    FOR EACH ROW
EXECUTE FUNCTION check_is_brigade_unique();

-- 2 brigades

CREATE OR REPLACE FUNCTION check_is_brigade_has_airplanes_assigned()
    RETURNS TRIGGER AS
$$
BEGIN
    IF EXISTS (SELECT 1 from airplanes inner join brigades b on OLD.id = airplanes.pilots_brigade_id)
    THEN
        RAISE EXCEPTION 'There should be no airplanes serviced by the brigade to delete it';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER verify_brigade_has_no_airplanes_assigned
    BEFORE DELETE
    ON brigades
    FOR EACH ROW
EXECUTE FUNCTION check_is_brigade_has_airplanes_assigned();

-- 1 pilotsmedicalexaminations

CREATE OR REPLACE FUNCTION check_is_exists_by_done_at()
    RETURNS TRIGGER AS
$$
BEGIN
    IF EXISTS (SELECT 1 from airplane_maintenance_operations WHERE airplane_maintenance_operations.done_at = NEW.done_at)
    THEN
        RAISE EXCEPTION 'Airplane maintenance operation for this date is already registered';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER verify_examination_by_done_at_date
    BEFORE INSERT OR UPDATE
    ON brigades
    FOR EACH ROW
EXECUTE FUNCTION check_is_exists_by_done_at();


-- 2 pilotsmedicalexaminations

-- в классе больше нет исключений, если что утром доделаю, если скажешь, какой сделать