create table archive
(
	id 			int not null,
	dvd_nr		int not null,
	note		text,
	username	varchar(20) not null,
	date		timestamp,
	primary key(id)
);

create table archive_backup
(
	archive_id	int not null,
	backup_id	int not null,
	status		text,
	primary key(archive_id, backup_id)
);

create table workflows(
	id 			int not null,
	step_id		int not null,
	start		timestamp,
	stop		timestamp,
	username	varchar(20),
	archive_id	int not null,
	running 	boolean default true,
	primary key(id)
);

create table workflow_step
(
	step_id		int not null,
	code		text,
	next_step	int,
	stop		boolean default false,
	primary key(step_id)
);

alter table workflow_step
	add constraint fk_workflow_next_step
	foreign key(next_step)
	references workflow_step(step_id);

alter table workflows
	add constraint fk_workflows_step_id
	foreign key(step_id)
	references workflow_step(step_id);
	
alter table workflows
	add constraint fk_workflows_archive_id
	foreign key(archive_id)
	references archive(id);	



create table workflow_log
(
	workflow_id		int not null,
	step_id			int not null,
	date			timestamp not null,
	status			text,
	primary key(workflow_id, step_id)
);

alter table workflow_log
	add constraint fk_workflow_log_workflow_id
	foreign key (workflow_id)
	references workflows(id);
	
alter table workflow_log
	add constraint fk_workflow_log_step_id
	foreign key (step_id)
	references 	workflow_step(step_id);
