create schema rokofo;
set search_path to 'rokofo';
create table files_base (
	id		serial	constraint files_pkey primary key,
	parentId	integer constraint files_parentRef
					references files_base,
	targetId	integer	constraint files_targetRef
					references files_base,
	directory	varchar(250),
	name		varchar(50) constraint filess_nnn not null,
	type		varchar(20),
	size		integer,
		constraint files_size check (size > 0),
		constraint files_relpath check (directory = '' or
						directory similar to '[^/]%/'),
	      constraint files_realpath check (directory not like '%/../%'
						and directory not like '%/./%'
						and directory not like '../%'
						and directory not like './%'),
		constraint files_name check (name not like '%/%'),
		constraint files_type check (type not like '%/%' and
						type not like '%.%'),
		constraint files_link check (targetId is null or
						parentId is not null),
		constraint files_hierarchy check ((parentId is null) =
						(directory is not null)),
		constraint files_path	unique (directory, name),
		constraint files_path_hier	unique (parentId, name),
		constraint files_dirNsize check ((directory is null) =
						(size is null)),
		constraint files_file_type check (case when
			directory is not null and name is not null and
				name != ''
			then type is not null end)
	) without oids;

create table persons (
	id		serial	constraint persons_pkey primary key,
	sex		char(1) constraint persons_sex
				check (sex = 'm' or sex = 'f'),	
	title		varchar(20),
	forenames	varchar(50),
	surnames	varchar(50),
	nick		varchar(20),
	visiblename	varchar(100)
);

create table filePersonRelations(
	fileId		integer	constraint filePersonRelations_ref_files
					references files_base,
	personId	integer constraint filePersonRelations_ref_persons
					references persons,
	sequence	integer constraint filePersonRelations_sequence
					check (sequence < 100 and sequence >0),
	rating		integer constraint filePersonRelations_rating
					check (rating > 0 and rating < 10),
		constraint filePersonRelations_pkey
			primary key (fileId, personId)
);

create or replace view filePerson as
	select f.directory || f.name || '.' || f.type as path,
		case when p.visiblename is not null then p.visiblename
		when p.title is not null then p.title || ' ' ||
			case when p.surnames is not null then p.surnames
			when p.nick is not null then p.nick
			when p.forenames is not null then p.forenames end
		else case when p.forenames is not null then p.forenames
				else '' end ||
			case when p.nick is not null then ' (' || p.nick || ')'
				else '' end ||
			case when p.surnames is not null then ' '||p.surnames
				else '' end
		end
		as name
	from files_base f, filePersonRelations fp, persons p
	where f.id = fp.fileId and fp.personId = p.id
	order by sequence, rating;
		

-- create or replace view pictures as
--	select *, directory || name || '.' || type as path from pictures_base;
--
