-- Parent can't be NULL
CREATE PROCEDURE addAdministrable (thename VARCHAR(40), parent INT, role INT, OUT aid INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE ancestor INT;
    DECLARE thisId INT;
    DECLARE cur CURSOR FOR SELECT ancestor_id FROM administrables_closures WHERE descendant_id = parent;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    INSERT INTO administrables (name, role_id) VALUES (thename, role);

    OPEN cur;
  
    SET thisId = LAST_INSERT_ID();

    read_loop: LOOP
        FETCH cur INTO ancestor;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO administrables_closures (ancestor_id, descendant_id) VALUES (ancestor, thisId);
    END LOOP;

    CLOSE cur;
    INSERT INTO administrables_closures (ancestor_id, descendant_id) VALUES (parent, thisId);
    SET aid = thisId;
END;

-- users will automatically get an administrable
CREATE TRIGGER useradd AFTER INSERT ON users
FOR EACH ROW BEGIN
    DECLARE theId INT;
    DECLARE allUsers INT;
    SELECT id FROM administrables WHERE name='Alla Användare' INTO allUsers;
    CALL addAdministrable('', allUsers, NULL, theId);
    UPDATE users SET administrable_id = theId WHERE id = NEW.id;
END;