# This trigger will delete any orphaned tags that no longer have any
# photos associated with them after a photo file deletion

DELIMITER $$
DROP TRIGGER IF EXISTS after_file_delete;
CREATE TRIGGER after_file_delete
AFTER DELETE ON `myersjac`.`photo_files` 
FOR EACH ROW
BEGIN
   DECLARE var_tag_id INT;
   DECLARE more_orphans INTEGER DEFAULT 1; -- always begin assuming they exit

   -- cursor for orphaned tags
   DECLARE tag_cursor CURSOR FOR
      SELECT tag_id FROM `myersjac`.`photo_tags`
      WHERE tag_id NOT IN (
               SELECT DISTINCT tag_id FROM `myersjac`.`photo_files_tags`) AND
            user_tag = 1;

   -- "not found" handler
   DECLARE CONTINUE HANDLER
      FOR NOT FOUND SET more_orphans = 0;

   OPEN tag_cursor;

   get_orphans: LOOP
      FETCH tag_cursor INTO var_tag_id;

      IF more_orphans = 0 THEN
         LEAVE get_orphans;
      END IF;

      DELETE FROM photo_tags WHERE tag_id = var_tag_id;

   END LOOP get_orphans;

   CLOSE tag_cursor;

END;$$
DELIMITER ;
