=-- --------------------------------------------
=-- general FH storage
create table hx_family_item (
	pk serial primary key,
	fk_narrative_condition integer
		default null
		references clin_narrative(pk)
		on update cascade
		on delete restrict,
	fk_relative integer
		default null
		references xlnk_identity(xfk_identity)
		on update cascade
		on delete set null,
	name_relative text
		default null
		check (coalesce(trim(name_relative), 'dummy') != ''),
	dob_relative timestamp with time zone
		default null,
	condition text
		default null
		check (coalesce(trim(condition), 'dummy') != ''),
	age_noted text,
	age_of_death interval
		default null,
	is_cause_of_death boolean
		not null
		default false,
	unique (name_relative, dob_relative, condition),
	unique (fk_relative, condition)
) inherits (audit_fields);

select add_table_for_audit('hx_family_item');

alter table hx_family_item add constraint link_or_know_condition
	check (
		(fk_narrative_condition is not null and condition is null)
			or
		(fk_narrative_condition is null and condition is not null)
	);

alter table hx_family_item add constraint link_or_know_relative
	check (
		=-- from linked narrative
		(fk_narrative_condition is not null and fk_relative is null and name_relative is null and dob_relative is null)
			or
		=-- from linked relative
		(fk_narrative_condition is null and fk_relative is not null and name_relative is null and dob_relative is null)
			or
		=-- from name
		(fk_narrative_condition is null and fk_relative is null and name_relative is not null)
	);

comment on table hx_family_item is
	'stores family history items independant of the patient,
	 this is out-of-EMR so that several patients can link to it';
comment on column hx_family_item.fk_narrative_condition is
	'can point to a narrative item of a relative if in database';
comment on column hx_family_item.fk_relative is
	'foreign key to relative if in database';
comment on column hx_family_item.name_relative is
	'name of the relative if not in database';
comment on column hx_family_item.dob_relative is
	'DOB of relative if not in database';
comment on column hx_family_item.condition is
	'narrative holding the condition the relative suffered from,
	 must be NULL if fk_narrative_condition is not';
comment on column hx_family_item.age_noted is
	'at what age the relative acquired the condition';
comment on column hx_family_item.age_of_death is
	'at what age the relative died';
comment on column hx_family_item.is_cause_of_death is
	'whether relative died of this problem, Richard
	 suggested to allow that several times per relative';


=-- patient linked FH
create table clin_hx_family (
	pk serial primary key,
	fk_hx_family_item integer
		not null
		references hx_family_item(pk)
		on update cascade
		on delete restrict
) inherits (clin_root_item);

alter table clin_hx_family add constraint narrative_not_empty
	check (coalesce(trim(narrative), '') != '');
=-- FIXME: constraint trigger has_type(fHx)

select add_table_for_audit('clin_hx_family');

comment on table clin_hx_family is
	'stores family history for a given patient';
comment on column clin_hx_family.clin_when is
	'when the family history item became known';
comment on column clin_hx_family.fk_encounter is
	'encounter during which family history item became known';
comment on column clin_hx_family.fk_episode is
	'episode to which family history item is of importance';
comment on column clin_hx_family.narrative is
	'how is the afflicted person related to the patient';
comment on column clin_hx_family.soap_cat is
	'as usual, must be NULL if fk_narrative_condition is not but
	 this is not enforced and only done in the view';


create view v_hx_family as
=-- those not linked to another patient as relative
select
	vpi.pk_patient as pk_patient,
	vpi.pk_health_issue as pk_health_issue,

	chxf.clin_when as clin_when,
	chxf.fk_encounter as pk_encounter,
	chxf.fk_episode as pk_episode,
	chxf.narrative as relationship,
	chxf.soap_cat as soap_cat,
	chxf.pk as pk_clin_hx_family,
	chxf.fk_hx_family_item as pk_hx_family_item,

	null::integer as pk_narrative_condition,
	null::integer as pk_relative_identity,
	hxfi.name_relative as name_relative,
	hxfi.dob_relative as dob_relative,
	hxfi.condition as condition,
	hxfi.age_noted as age_noted,
	hxfi.age_of_death as age_of_death,
	hxfi.is_cause_of_death as is_cause_of_death
from
	v_pat_items vpi,
	clin_hx_family chxf,
	hx_family_item hxfi,
	v_basic_person vbp
where
	vpi.pk_item = chxf.pk_item
		and
	hxfi.pk = chxf.fk_hx_family_item
		and
	hxfi.fk_narrative_condition is null
		and
	hxfi.fk_relative is null

UNION

=-- those linked to another patient as relative
select
	vpi.pk_patient as pk_patient,
	vpi.pk_health_issue as pk_health_issue,

	chxf.clin_when as clin_when,
	chxf.fk_encounter as pk_encounter,
	chxf.fk_episode as pk_episode,
	chxf.narrative as relationship,
	chxf.soap_cat as soap_cat,
	chxf.pk as pk_clin_hx_family,
	chxf.fk_hx_family_item as pk_hx_family_item,

	null::integer as pk_narrative_condition,
	hxfi.fk_relative as pk_relative_identity,
	vbp.firstnames || ' ' || vbp.lastnames as name_relative,
	vbp.dob as dob_relative,
	hxfi.condition as condition,
	hxfi.age_noted as age_noted,
	hxfi.age_of_death as age_of_death,
	hxfi.is_cause_of_death as is_cause_of_death
from
	v_pat_items vpi,
	clin_hx_family chxf,
	hx_family_item hxfi,
	v_basic_person vbp
where
	vpi.pk_item = chxf.pk_item
		and
	hxfi.pk = chxf.fk_hx_family_item
		and
	hxfi.fk_narrative_condition is null
		and
	hxfi.fk_relative = v_basic_person.pk_identity

UNION

=-- those linked to a condition of another patient being a relative
select
	vpn.pk_patient as pk_patient,
	vpn.pk_health_issue as pk_health_issue,

	chxf.clin_when as clin_when,
	chxf.fk_encounter as pk_encounter,
	chxf.fk_episode as pk_episode,
	chxf.narrative as relationship,
	chxf.soap_cat as soap_cat,
	chxf.pk as pk_clin_hx_family,
	chxf.fk_hx_family_item as pk_hx_family_item,

	hxfi.fk_narrative_condition as pk_narrative_condition,
	vpn.pk_patient as pk_relative_identity,
	vbp.firstnames || ' ' || vbp.lastnames as name_relative,
	vbp.dob as dob_relative,
	vpn.narrative as condition,
	hxfi.age_noted as age_noted,
	hxfi.age_of_death as age_of_death,
	hxfi.is_cause_of_death as is_cause_of_death
from
	clin_hx_family chxf,
	hx_family_item hxfi,
	v_basic_person vbp,
	v_pat_narrative vpn
where
	hxfi.pk = chxf.fk_hx_family_item
		and
	hxfi.fk_narrative_condition = vpn.pk_narrative
		and
	hxfi.fk_relative is null
		and
	v_basic_person.pk_identity = vpn.pk_patient
;