-- Oef3
-- S1: add_photo foto w aan gegeven set toegevoegt
-- Nieuwe foto volgt op de laatste foto in de set

DELIMITER $$
DROP PROCEDURE IF EXISTS add_Photo $$

CREATE PROCEDURE add_Photo(photoId INT, setId INT) -- photoId en set
BEGIN
  DECLARE photoCnt, setCnt, maxSetId INT;
  DECLARE prevId INT;

  -- Controle op photoId
  SELECT count(*) FROM photos WHERE photo_id = photoId INTO photoCnt;
  IF photoCnt = 0 THEN
    LEAVE proc;
  END IF;

  -- controle op setId
  SELECT count(*) FROM sets WHERE setphoto_set_id = setId INTO setCnt;
  IF setCnt = 0 THEN
    LEAVE proc;
  END IF;

  -- previous photo
  SELECT count(*) FROM setphotos WHERE setphoto_set_id = setId INTO maxSetId;

  -- toevoegen aan setphotos
  INSERT INTO setphotos (setphoto_photo_id, setphoto_set_id, setphoto_prevphoto_id)
  VALUES (photoId, setId, prevId);

END $$

DELIMITER ;

CALL add_Photo(17,6);

select * from setphotos order by setphoto_set_id, setphoto_photo_id;