--Email notification support
create table pgagent.pga_jobmailnottype (
	jmntypeid text,
	jmntypename text,
	primary key (jmntypeid)
);
comment on table pgagent.pga_jobmailnottype is 'Mail notification events';

insert into pgagent.pga_jobmailnottype values ('','None');
insert into pgagent.pga_jobmailnottype values ('f','Failure');
insert into pgagent.pga_jobmailnottype values ('s','Success');
insert into pgagent.pga_jobmailnottype values ('b','Failure + Success');

ALTER TABLE pgagent.pga_job ADD COLUMN jobmailnottype text;
ALTER TABLE pgagent.pga_job ADD COLUMN jobmailnotrecp text;

ALTER TABLE pgagent.pga_job ADD CONSTRAINT pga_job_jobjmntypeid_fkey FOREIGN KEY (jobmailnottype)
      REFERENCES pgagent.pga_jobmailnottype (jmntypeid) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
	  
ALTER TABLE pgagent.pga_jobagent ADD COLUMN jagemailconfigured boolean default false;

--Jobstep SQL-File support
ALTER TABLE pgagent.pga_jobstep DROP CONSTRAINT pga_jobstep_check;	
ALTER TABLE pgagent.pga_jobstep DROP CONSTRAINT pga_jobstep_check1;	
ALTER TABLE pgagent.pga_jobstep DROP CONSTRAINT pga_jobstep_jstkind_check;	
ALTER TABLE pgagent.pga_jobstep ADD CONSTRAINT pga_jobstep_check CHECK (jstconnstr <> ''::text AND (jstkind = ANY (ARRAY['s'::bpchar, 'f'::bpchar])) OR jstconnstr = ''::text AND (jstkind = 'b'::bpchar OR jstdbname <> ''::name)),
ALTER TABLE pgagent.pga_jobstep ADD CONSTRAINT pga_jobstep_check1 CHECK (jstdbname <> ''::name AND (jstkind = ANY (ARRAY['s'::bpchar, 'f'::bpchar])) OR jstdbname = ''::name AND (jstkind = 'b'::bpchar OR jstconnstr <> ''::text)),
ALTER TABLE pgagent.pga_jobstep ADD CONSTRAINT pga_jobstep_jstkind_check CHECK (jstkind = ANY (ARRAY['b'::bpchar, 's'::bpchar, 'f'::bpchar])),

--Update schema version
CREATE OR REPLACE FUNCTION pgagent.pgagent_schema_version()
  RETURNS smallint AS
$BODY$
BEGIN
    -- RETURNS PGAGENT MAJOR VERSION
    -- WE WILL CHANGE THE MAJOR VERSION, ONLY IF THERE IS A SCHEMA CHANGE
    RETURN 4;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  