/opt/firebird/bin/isql -q -u sysdba -p phoenix << EOF

connect /opt/firebird/DeskShows.fdb;

SET TERM !! ;

EXECUTE BLOCK AS
BEGIN

  -- *********************************************
  -- Remove the last loaded show delete cascade constraint
  -- *********************************************

  IF( EXISTS ( SELECT RDB\$CONSTRAINT_NAME FROM RDB\$RELATION_CONSTRAINTS WHERE RDB\$CONSTRAINT_NAME = 'FK_LASTLOADEDSHOW_SHOW')) THEN 
  EXECUTE STATEMENT 'ALTER TABLE last_loaded_show DROP CONSTRAINT fk_lastloadedshow_show;';

  -- *********************************************
  -- Insert a null value, then update with the uuid of the last saved non default show if it exists
  -- *********************************************

  IF( NOT EXISTS ( SELECT * FROM LAST_LOADED_SHOW) ) THEN
  BEGIN
    EXECUTE STATEMENT 'INSERT INTO last_loaded_show values (null);';
    EXECUTE STATEMENT 'UPDATE last_loaded_show SET show_uuid = 
      (SELECT uuid from shows WHERE last_saved = (SELECT MAX(last_saved) FROM shows WHERE default_show = 0)) WHERE show_uuid IS NULL;';
  END
  
END!!

-- *********************************************
-- add a trigger which sets the last loaded show uuid using the last saved date
-- *********************************************

CREATE OR ALTER TRIGGER tr_last_loaded_show_deleted FOR shows
ACTIVE AFTER DELETE AS
BEGIN
  UPDATE last_loaded_show set show_uuid = (SELECT uuid from shows WHERE last_saved = 
    (SELECT MAX(last_saved) FROM shows WHERE default_show = 0)) WHERE show_uuid = old.uuid;
END!!

SET TERM ; !!

COMMIT;

EOF