-- Name: v_family_history_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_family_history_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_fh.modified_when,
    c_fh.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_fh.modified_by)), (('<'::text || (c_fh.modified_by)::text) || '>'::text)) AS modified_by,
    c_fh.soap_cat,
    ((((((((((((public._(c_fhrt.description) || COALESCE((' @ '::text || c_fh.age_noted), ''::text)) || ': '::text) || c_fh.narrative) || '
'::text) || ' '::text) ||
        CASE
            WHEN (c_fh.contributed_to_death IS TRUE) THEN (public._('contributed to death'::text) || ' '::text)
            ELSE ''::text
        END) || COALESCE(('@ '::text || (justify_interval(c_fh.age_of_death))::text), ''::text)) || '
'::text) || COALESCE(((' '::text || c_fh.name_relative) || ','::text), ''::text)) || COALESCE((' '::text || to_char(c_fh.dob_relative, 'YYYY-MM-DD'::text)), ''::text)) || COALESCE(('
 '::text || c_fh.comment), ''::text)) || COALESCE(((';
'::text || array_to_string(( SELECT array_agg(((((((((r_csr.code || ' ('::text) || r_ds.name_short) || ' - '::text) || r_ds.version) || ' - '::text) || r_ds.lang) || '): '::text) || r_csr.term)) AS array_agg
           FROM ((clin.lnk_code2fhx c_lc2fhx
             JOIN ref.coding_system_root r_csr ON ((c_lc2fhx.fk_generic_code = r_csr.pk_coding_system)))
             JOIN ref.data_source r_ds ON ((r_ds.pk = r_csr.fk_data_source)))
          WHERE (c_lc2fhx.fk_item = c_fh.pk)), '; '::text)) || ';'::text), ''::text)) AS narrative,
    c_fh.fk_encounter AS pk_encounter,
    c_fh.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    c_fh.pk AS src_pk,
    'clin.family_history'::text AS src_table,
    c_fh.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM (((((clin.family_history c_fh
     JOIN clin.encounter c_enc ON ((c_fh.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_fh.fk_episode = c_epi.pk)))
     LEFT JOIN clin.fhx_relation_type c_fhrt ON ((c_fh.fk_relation_type = c_fhrt.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)));


ALTER TABLE clin.v_family_history_journal OWNER TO "gm-dbo";

--
-- Name: identity; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.identity (
    pk integer NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    gender text,
    dob timestamp with time zone,
    fk_marital_status integer,
    cob character(2),
    deceased timestamp with time zone,
    title text,
    tob time without time zone,
    emergency_contact text,
    fk_emergency_contact integer,
    comment text,
    fk_primary_provider integer,
    dob_is_estimated boolean DEFAULT false NOT NULL,
    CONSTRAINT dem_identity_sane_dob CHECK (((dob IS NULL) OR (dob <= now()))),
    CONSTRAINT dem_identity_sane_dod CHECK (((deceased IS NULL) OR (dob IS NULL) OR (deceased >= dob))),
    CONSTRAINT identity_title_check CHECK ((btrim(COALESCE(title, 'NULL'::text)) <> ''::text)),
    CONSTRAINT sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT sane_emergency_contact CHECK ((gm.is_null_or_non_empty_string(emergency_contact) IS TRUE))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.identity OWNER TO "gm-dbo";

--
-- Name: TABLE identity; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.identity IS 'represents the unique identity of a person';


--
-- Name: COLUMN identity.deleted; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.deleted IS 'whether this identity is considered deleted';


--
-- Name: COLUMN identity.gender; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.gender IS 'the gender code';


--
-- Name: COLUMN identity.dob; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.dob IS 'date/time of birth';


--
-- Name: COLUMN identity.cob; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.cob IS 'country of birth as per date of birth, coded as 2 character ISO code';


--
-- Name: COLUMN identity.deceased; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.deceased IS 'date when a person has died';


--
-- Name: COLUMN identity.title; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.title IS 'Yes, a title is an attribute of an identity, not of a name !
	 Also, there are some incredible rants of titles.';


--
-- Name: COLUMN identity.emergency_contact; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.emergency_contact IS 'Free text emergency contact information.';


--
-- Name: COLUMN identity.fk_emergency_contact; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.fk_emergency_contact IS 'Link to another dem.identity to be used as emergency contact.';


--
-- Name: COLUMN identity.comment; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.comment IS 'A free-text comment on this identity.\n
\n
Can be used to to discriminate patients which are otherwise\n
identical regarding name and date of birth.\n
Should be something non-ephemereal and unique to the person\n
itself across time, place and database instance.\n
Good: place of birth\n
Good: maiden name of mother\n
Good: mother of <name>\n
Good: hash of DNA\n
Good (?): hair color of first pet\n
Bad: current address (will change)\n
Bad: primary provider in this praxis (can change, invalid in another GNUmed instance)\n
Bad: nickname (will change, can dupe as well)\n
Bad: favourite food\n
not-quite-so-bad: occupation';


--
-- Name: COLUMN identity.fk_primary_provider; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.identity.fk_primary_provider IS 'Whether the given DOB is estimated or not. The TOB is assumed to be correct if given';


--
-- Name: v_health_issues_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_health_issues_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_hi.modified_when,
    COALESCE(( SELECT (identity.dob + c_hi.age_noted)
           FROM dem.identity
          WHERE (identity.pk = ( SELECT encounter.fk_patient
                   FROM clin.encounter
                  WHERE (encounter.pk = c_hi.fk_encounter)))), c_enc.started) AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_hi.modified_by)), (('<'::text || (c_hi.modified_by)::text) || '>'::text)) AS modified_by,
    'a'::text AS soap_cat,
    (((((((((((((public._('Health Issue'::text) || ' ('::text) ||
        CASE
            WHEN c_hi.is_active THEN public._('active'::text)
            ELSE public._('inactive'::text)
        END) || ', '::text) ||
        CASE
            WHEN c_hi.clinically_relevant THEN public._('clinically relevant'::text)
            ELSE public._('clinically not relevant'::text)
        END) || COALESCE((', '::text || c_hi.diagnostic_certainty_classification), ''::text)) || '): '::text) || c_hi.description) || COALESCE(((((';
'::text || public._('noted at age'::text)) || ': '::text) || (c_hi.age_noted)::text) || ';
'::text), ';
'::text)) || COALESCE((((public._('Laterality'::text) || ': '::text) || (c_hi.laterality)::text) || ' / '::text), ''::text)) ||
        CASE
            WHEN c_hi.is_confidential THEN (public._('confidential'::text) || ' / '::text)
            ELSE ''::text
        END) ||
        CASE
            WHEN c_hi.is_cause_of_death THEN public._('cause of death'::text)
            ELSE ''::text
        END) || COALESCE((((';
'::text || public._('Summary'::text)) || ':
'::text) || c_hi.summary), ''::text)) || COALESCE(((';
'::text || array_to_string(( SELECT array_agg(((((((((r_csr.code || ' ('::text) || r_ds.name_short) || ' - '::text) || r_ds.version) || ' - '::text) || r_ds.lang) || '): '::text) || r_csr.term)) AS array_agg
           FROM ((clin.lnk_code2h_issue c_lc2h
             JOIN ref.coding_system_root r_csr ON ((c_lc2h.fk_generic_code = r_csr.pk_coding_system)))
             JOIN ref.data_source r_ds ON ((r_ds.pk = r_csr.fk_data_source)))
          WHERE (c_lc2h.fk_item = c_hi.pk)), '; '::text)) || ';'::text), ''::text)) AS narrative,
    c_hi.fk_encounter AS pk_encounter,
    NULL::integer AS pk_episode,
    c_hi.pk AS pk_health_issue,
    c_hi.pk AS src_pk,
    'clin.health_issue'::text AS src_table,
    c_hi.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((clin.health_issue c_hi
     JOIN clin.encounter c_enc ON ((c_hi.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)));


ALTER TABLE clin.v_health_issues_journal OWNER TO "gm-dbo";

--
-- Name: v_hospital_stays_journal_multi_day_adm; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_hospital_stays_journal_multi_day_adm AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_hs.modified_when,
    c_hs.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_hs.modified_by)), (('<'::text || (c_hs.modified_by)::text) || '>'::text)) AS modified_by,
    c_hs.soap_cat,
    ((((((((((((((((((((public._('hospital stay'::text) || '
'::text) || ' '::text) || public._('admitted'::text)) || ': '::text) || to_char(c_hs.clin_when, 'YYYY-MM-DD'::text)) || '
'::text) || ' '::text) || public._('discharged'::text)) || ': '::text) || to_char(c_hs.discharge, 'YYYY-MM-DD'::text)) || '
'::text) || ' '::text) || public._('hospital'::text)) || ': "'::text) || d_ou.description) || ' @ '::text) || d_o.description) || '"'::text) || '
'::text) || COALESCE(('
 '::text || c_hs.narrative), ''::text)) AS narrative,
    c_hs.fk_encounter AS pk_encounter,
    c_hs.fk_episode AS pk_episode,
    ( SELECT episode.fk_health_issue
           FROM clin.episode
          WHERE (episode.pk = c_hs.fk_episode)) AS pk_health_issue,
    c_hs.pk AS src_pk,
    'clin.hospital_stay'::text AS src_table,
    c_hs.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((((((clin.hospital_stay c_hs
     JOIN clin.encounter c_enc ON ((c_hs.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_hs.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((d_ou.pk = c_hs.fk_org_unit)))
     LEFT JOIN dem.org d_o ON ((d_o.pk = d_ou.fk_org)))
  WHERE ((c_hs.discharge IS NOT NULL) AND (to_char(c_hs.clin_when, 'YYYYMMDD'::text) <> to_char(c_hs.discharge, 'YYYYMMDD'::text)));


ALTER TABLE clin.v_hospital_stays_journal_multi_day_adm OWNER TO "gm-dbo";

--
-- Name: v_hospital_stays_journal_multi_day_dis; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_hospital_stays_journal_multi_day_dis AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_hs.modified_when,
    c_hs.discharge AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_hs.modified_by)), (('<'::text || (c_hs.modified_by)::text) || '>'::text)) AS modified_by,
    c_hs.soap_cat,
    (((((public._('discharged from'::text) || ' "'::text) || d_ou.description) || ' @ '::text) || d_o.description) || '"'::text) AS narrative,
    c_hs.fk_encounter AS pk_encounter,
    c_hs.fk_episode AS pk_episode,
    ( SELECT episode.fk_health_issue
           FROM clin.episode
          WHERE (episode.pk = c_hs.fk_episode)) AS pk_health_issue,
    c_hs.pk AS src_pk,
    'clin.hospital_stay'::text AS src_table,
    c_hs.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((((((clin.hospital_stay c_hs
     JOIN clin.encounter c_enc ON ((c_hs.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_hs.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((d_ou.pk = c_hs.fk_org_unit)))
     LEFT JOIN dem.org d_o ON ((d_o.pk = d_ou.fk_org)))
  WHERE ((c_hs.discharge IS NOT NULL) AND (to_char(c_hs.clin_when, 'YYYYMMDD'::text) <> to_char(c_hs.discharge, 'YYYYMMDD'::text)));


ALTER TABLE clin.v_hospital_stays_journal_multi_day_dis OWNER TO "gm-dbo";

--
-- Name: v_hospital_stays_journal_no_discharge; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_hospital_stays_journal_no_discharge AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_hs.modified_when,
    c_hs.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_hs.modified_by)), (('<'::text || (c_hs.modified_by)::text) || '>'::text)) AS modified_by,
    c_hs.soap_cat,
    ((((((((((((((public._('hospital stay'::text) || '
'::text) || ' '::text) || public._('admitted'::text)) || ': '::text) || to_char(c_hs.clin_when, 'YYYY-MM-DD'::text)) || '
'::text) || ' '::text) || public._('hospital'::text)) || ': "'::text) || d_ou.description) || ' @ '::text) || d_o.description) || '"'::text) || COALESCE(('
 '::text || c_hs.narrative), ''::text)) AS narrative,
    c_hs.fk_encounter AS pk_encounter,
    c_hs.fk_episode AS pk_episode,
    ( SELECT episode.fk_health_issue
           FROM clin.episode
          WHERE (episode.pk = c_hs.fk_episode)) AS pk_health_issue,
    c_hs.pk AS src_pk,
    'clin.hospital_stay'::text AS src_table,
    c_hs.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((((((clin.hospital_stay c_hs
     JOIN clin.encounter c_enc ON ((c_hs.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_hs.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((d_ou.pk = c_hs.fk_org_unit)))
     LEFT JOIN dem.org d_o ON ((d_o.pk = d_ou.fk_org)))
  WHERE (c_hs.discharge IS NULL);


ALTER TABLE clin.v_hospital_stays_journal_no_discharge OWNER TO "gm-dbo";

--
-- Name: v_hospital_stays_journal_one_day; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_hospital_stays_journal_one_day AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_hs.modified_when,
    c_hs.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_hs.modified_by)), (('<'::text || (c_hs.modified_by)::text) || '>'::text)) AS modified_by,
    c_hs.soap_cat,
    (((((((((((((((public._('hospital stay'::text) || '
'::text) || ' '::text) || public._('admitted/discharged'::text)) || ': '::text) || to_char(c_hs.clin_when, 'YYYY-MM-DD'::text)) || '
'::text) || ' '::text) || public._('hospital'::text)) || ': "'::text) || d_ou.description) || ' @ '::text) || d_o.description) || '"'::text) || '
'::text) || COALESCE(('
 '::text || c_hs.narrative), ''::text)) AS narrative,
    c_hs.fk_encounter AS pk_encounter,
    c_hs.fk_episode AS pk_episode,
    ( SELECT episode.fk_health_issue
           FROM clin.episode
          WHERE (episode.pk = c_hs.fk_episode)) AS pk_health_issue,
    c_hs.pk AS src_pk,
    'clin.hospital_stay'::text AS src_table,
    c_hs.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((((((clin.hospital_stay c_hs
     JOIN clin.encounter c_enc ON ((c_hs.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_hs.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((d_ou.pk = c_hs.fk_org_unit)))
     LEFT JOIN dem.org d_o ON ((d_o.pk = d_ou.fk_org)))
  WHERE ((c_hs.discharge IS NOT NULL) AND (to_char(c_hs.clin_when, 'YYYYMMDD'::text) = to_char(c_hs.discharge, 'YYYYMMDD'::text)));


ALTER TABLE clin.v_hospital_stays_journal_one_day OWNER TO "gm-dbo";

--
-- Name: v_hospital_stays_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_hospital_stays_journal AS
 SELECT v_hospital_stays_journal_no_discharge.pk_patient,
    v_hospital_stays_journal_no_discharge.modified_when,
    v_hospital_stays_journal_no_discharge.clin_when,
    v_hospital_stays_journal_no_discharge.modified_by,
    v_hospital_stays_journal_no_discharge.soap_cat,
    v_hospital_stays_journal_no_discharge.narrative,
    v_hospital_stays_journal_no_discharge.pk_encounter,
    v_hospital_stays_journal_no_discharge.pk_episode,
    v_hospital_stays_journal_no_discharge.pk_health_issue,
    v_hospital_stays_journal_no_discharge.src_pk,
    v_hospital_stays_journal_no_discharge.src_table,
    v_hospital_stays_journal_no_discharge.row_version,
    v_hospital_stays_journal_no_discharge.health_issue,
    v_hospital_stays_journal_no_discharge.issue_laterality,
    v_hospital_stays_journal_no_discharge.issue_active,
    v_hospital_stays_journal_no_discharge.issue_clinically_relevant,
    v_hospital_stays_journal_no_discharge.issue_confidential,
    v_hospital_stays_journal_no_discharge.episode,
    v_hospital_stays_journal_no_discharge.episode_open,
    v_hospital_stays_journal_no_discharge.encounter_started,
    v_hospital_stays_journal_no_discharge.encounter_last_affirmed,
    v_hospital_stays_journal_no_discharge.encounter_type,
    v_hospital_stays_journal_no_discharge.encounter_l10n_type
   FROM clin.v_hospital_stays_journal_no_discharge
UNION ALL
 SELECT v_hospital_stays_journal_one_day.pk_patient,
    v_hospital_stays_journal_one_day.modified_when,
    v_hospital_stays_journal_one_day.clin_when,
    v_hospital_stays_journal_one_day.modified_by,
    v_hospital_stays_journal_one_day.soap_cat,
    v_hospital_stays_journal_one_day.narrative,
    v_hospital_stays_journal_one_day.pk_encounter,
    v_hospital_stays_journal_one_day.pk_episode,
    v_hospital_stays_journal_one_day.pk_health_issue,
    v_hospital_stays_journal_one_day.src_pk,
    v_hospital_stays_journal_one_day.src_table,
    v_hospital_stays_journal_one_day.row_version,
    v_hospital_stays_journal_one_day.health_issue,
    v_hospital_stays_journal_one_day.issue_laterality,
    v_hospital_stays_journal_one_day.issue_active,
    v_hospital_stays_journal_one_day.issue_clinically_relevant,
    v_hospital_stays_journal_one_day.issue_confidential,
    v_hospital_stays_journal_one_day.episode,
    v_hospital_stays_journal_one_day.episode_open,
    v_hospital_stays_journal_one_day.encounter_started,
    v_hospital_stays_journal_one_day.encounter_last_affirmed,
    v_hospital_stays_journal_one_day.encounter_type,
    v_hospital_stays_journal_one_day.encounter_l10n_type
   FROM clin.v_hospital_stays_journal_one_day
UNION ALL
 SELECT v_hospital_stays_journal_multi_day_adm.pk_patient,
    v_hospital_stays_journal_multi_day_adm.modified_when,
    v_hospital_stays_journal_multi_day_adm.clin_when,
    v_hospital_stays_journal_multi_day_adm.modified_by,
    v_hospital_stays_journal_multi_day_adm.soap_cat,
    v_hospital_stays_journal_multi_day_adm.narrative,
    v_hospital_stays_journal_multi_day_adm.pk_encounter,
    v_hospital_stays_journal_multi_day_adm.pk_episode,
    v_hospital_stays_journal_multi_day_adm.pk_health_issue,
    v_hospital_stays_journal_multi_day_adm.src_pk,
    v_hospital_stays_journal_multi_day_adm.src_table,
    v_hospital_stays_journal_multi_day_adm.row_version,
    v_hospital_stays_journal_multi_day_adm.health_issue,
    v_hospital_stays_journal_multi_day_adm.issue_laterality,
    v_hospital_stays_journal_multi_day_adm.issue_active,
    v_hospital_stays_journal_multi_day_adm.issue_clinically_relevant,
    v_hospital_stays_journal_multi_day_adm.issue_confidential,
    v_hospital_stays_journal_multi_day_adm.episode,
    v_hospital_stays_journal_multi_day_adm.episode_open,
    v_hospital_stays_journal_multi_day_adm.encounter_started,
    v_hospital_stays_journal_multi_day_adm.encounter_last_affirmed,
    v_hospital_stays_journal_multi_day_adm.encounter_type,
    v_hospital_stays_journal_multi_day_adm.encounter_l10n_type
   FROM clin.v_hospital_stays_journal_multi_day_adm
UNION ALL
 SELECT v_hospital_stays_journal_multi_day_dis.pk_patient,
    v_hospital_stays_journal_multi_day_dis.modified_when,
    v_hospital_stays_journal_multi_day_dis.clin_when,
    v_hospital_stays_journal_multi_day_dis.modified_by,
    v_hospital_stays_journal_multi_day_dis.soap_cat,
    v_hospital_stays_journal_multi_day_dis.narrative,
    v_hospital_stays_journal_multi_day_dis.pk_encounter,
    v_hospital_stays_journal_multi_day_dis.pk_episode,
    v_hospital_stays_journal_multi_day_dis.pk_health_issue,
    v_hospital_stays_journal_multi_day_dis.src_pk,
    v_hospital_stays_journal_multi_day_dis.src_table,
    v_hospital_stays_journal_multi_day_dis.row_version,
    v_hospital_stays_journal_multi_day_dis.health_issue,
    v_hospital_stays_journal_multi_day_dis.issue_laterality,
    v_hospital_stays_journal_multi_day_dis.issue_active,
    v_hospital_stays_journal_multi_day_dis.issue_clinically_relevant,
    v_hospital_stays_journal_multi_day_dis.issue_confidential,
    v_hospital_stays_journal_multi_day_dis.episode,
    v_hospital_stays_journal_multi_day_dis.episode_open,
    v_hospital_stays_journal_multi_day_dis.encounter_started,
    v_hospital_stays_journal_multi_day_dis.encounter_last_affirmed,
    v_hospital_stays_journal_multi_day_dis.encounter_type,
    v_hospital_stays_journal_multi_day_dis.encounter_l10n_type
   FROM clin.v_hospital_stays_journal_multi_day_dis;


ALTER TABLE clin.v_hospital_stays_journal OWNER TO "gm-dbo";

--
-- Name: v_pat_allergies_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_allergies_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_all.modified_when,
    c_all.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_all.modified_by)), (('<'::text || (c_all.modified_by)::text) || '>'::text)) AS modified_by,
    c_all.soap_cat,
    ((((((((((((public._('Allergy'::text) || ' ('::text) || public._(c_at.value)) || '): '::text) || COALESCE(c_all.narrative, ''::text)) || '
'::text) || public._('substance'::text)) || ': '::text) || c_all.substance) || '; '::text) || COALESCE((((public._('allergene'::text) || ': '::text) || c_all.allergene) || '; '::text), ''::text)) || COALESCE((((public._('generic'::text) || ': '::text) || c_all.generics) || '; '::text), ''::text)) || COALESCE((((public._('ATC code'::text) || ': '::text) || c_all.atc_code) || '; '::text), ''::text)) AS narrative,
    c_all.fk_encounter AS pk_encounter,
    c_all.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    c_all.pk AS src_pk,
    'clin.allergy'::text AS src_table,
    c_all.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM (((((clin.allergy c_all
     JOIN clin._enum_allergy_type c_at ON ((c_all.fk_type = c_at.pk)))
     JOIN clin.encounter c_enc ON ((c_all.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_all.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)));


ALTER TABLE clin.v_pat_allergies_journal OWNER TO "gm-dbo";

--
-- Name: v_pat_allergy_state_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_allergy_state_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_ast.modified_when,
    COALESCE(c_ast.last_confirmed, c_ast.modified_when) AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_ast.modified_by)), (('<'::text || (c_ast.modified_by)::text) || '>'::text)) AS modified_by,
    'o'::text AS soap_cat,
    ((((public._('Allergy state'::text) || ': '::text) ||
        CASE
            WHEN (c_ast.has_allergy IS NULL) THEN public._('unknown, unasked'::text)
            WHEN (c_ast.has_allergy = 0) THEN public._('no known allergies'::text)
            WHEN (c_ast.has_allergy = 1) THEN public._('does have allergies'::text)
            ELSE NULL::text
        END) || COALESCE((((' ('::text || public._('last confirmed'::text)) || to_char(c_ast.last_confirmed, ' YYYY-MM-DD HH24:MI'::text)) || ')'::text), ''::text)) || COALESCE(('
 '::text || c_ast.comment), ''::text)) AS narrative,
    c_ast.fk_encounter AS pk_encounter,
    NULL::integer AS pk_episode,
    NULL::integer AS pk_health_issue,
    c_ast.pk AS src_pk,
    'clin.allergy_state'::text AS src_table,
    c_ast.row_version,
    NULL::text AS health_issue,
    NULL::text AS issue_laterality,
    NULL::boolean AS issue_active,
    NULL::boolean AS issue_clinically_relevant,
    NULL::boolean AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((clin.allergy_state c_ast
     JOIN clin.encounter c_enc ON ((c_ast.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
UNION ALL
 SELECT c_enc.fk_patient AS pk_patient,
    now() AS modified_when,
    now() AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_ast.modified_by)), (('<'::text || (c_ast.modified_by)::text) || '>'::text)) AS modified_by,
    'o'::text AS soap_cat,
    (((((((((public._('Allergy state'::text) || ' ('::text) || public._('copy'::text)) || '): '::text) ||
        CASE
            WHEN (c_ast.has_allergy IS NULL) THEN public._('unknown, unasked'::text)
            WHEN (c_ast.has_allergy = 0) THEN public._('no known allergies'::text)
            WHEN (c_ast.has_allergy = 1) THEN public._('does have allergies'::text)
            ELSE NULL::text
        END) || COALESCE(('
 '::text || c_ast.comment), ''::text)) || COALESCE((('
 '::text || public._('last confirmed'::text)) || to_char(c_ast.last_confirmed, ' YYYY-MM-DD HH24:MI'::text)), ''::text)) || COALESCE((('
 '::text || public._('entry last modified'::text)) || to_char(c_ast.modified_when, ' YYYY-MM-DD HH24:MI'::text)), ''::text)) || COALESCE((('
 '::text || public._('encounter started'::text)) || to_char(c_enc.started, ' YYYY-MM-DD HH24:MI'::text)), ''::text)) || COALESCE((('
 '::text || public._('encounter last affirmed'::text)) || to_char(c_enc.last_affirmed, ' YYYY-MM-DD HH24:MI'::text)), ''::text)) AS narrative,
    c_ast.fk_encounter AS pk_encounter,
    NULL::integer AS pk_episode,
    NULL::integer AS pk_health_issue,
    c_ast.pk AS src_pk,
    'clin.allergy_state'::text AS src_table,
    c_ast.row_version,
    NULL::text AS health_issue,
    NULL::text AS issue_laterality,
    NULL::boolean AS issue_active,
    NULL::boolean AS issue_clinically_relevant,
    NULL::boolean AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    now() AS encounter_started,
    now() AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((clin.allergy_state c_ast
     JOIN clin.encounter c_enc ON ((c_ast.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)));


ALTER TABLE clin.v_pat_allergy_state_journal OWNER TO "gm-dbo";

--
-- Name: v_pat_encounters_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_encounters_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_enc.modified_when,
    c_enc.started AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_enc.modified_by)), (('<'::text || (c_enc.modified_by)::text) || '>'::text)) AS modified_by,
    NULL::text AS soap_cat,
    (((((((((public._('Encounter'::text) || ': '::text) || ( SELECT public._(encounter_type.description) AS _
           FROM clin.encounter_type
          WHERE (encounter_type.pk = c_enc.fk_type))) || to_char(c_enc.started, ' YYYY-MM-DD HH24:MI'::text)) || to_char(c_enc.last_affirmed, ' - HH24:MI'::text)) || COALESCE((((((((('
 @ '::text || public._('branch'::text)) || ' "'::text) || d_ou.description) || '" '::text) || public._('of'::text)) || ' "'::text) || d_o.description) || '"'::text), ''::text)) || COALESCE(((('
 '::text || public._('RFE'::text)) || ': '::text) || c_enc.reason_for_encounter), ''::text)) || COALESCE((('
'::text || array_to_string(( SELECT array_agg(((((((((r_csr.code || ' ('::text) || r_ds.name_short) || ' - '::text) || r_ds.version) || ' - '::text) || r_ds.lang) || '): '::text) || r_csr.term)) AS array_agg
           FROM ((clin.lnk_code2rfe c_lc2r
             JOIN ref.coding_system_root r_csr ON ((c_lc2r.fk_generic_code = r_csr.pk_coding_system)))
             JOIN ref.data_source r_ds ON ((r_ds.pk = r_csr.fk_data_source)))
          WHERE (c_lc2r.fk_item = c_enc.pk)), '; '::text)) || ';'::text), ''::text)) || COALESCE(((('
 '::text || public._('AOE'::text)) || ': '::text) || c_enc.assessment_of_encounter), ''::text)) || COALESCE((('
'::text || array_to_string(( SELECT array_agg(((((((((r_csr.code || ' ('::text) || r_ds.name_short) || ' - '::text) || r_ds.version) || ' - '::text) || r_ds.lang) || '): '::text) || r_csr.term)) AS array_agg
           FROM ((clin.lnk_code2aoe c_lc2a
             JOIN ref.coding_system_root r_csr ON ((c_lc2a.fk_generic_code = r_csr.pk_coding_system)))
             JOIN ref.data_source r_ds ON ((r_ds.pk = r_csr.fk_data_source)))
          WHERE (c_lc2a.fk_item = c_enc.pk)), '; '::text)) || ';'::text), ''::text)) AS narrative,
    c_enc.pk AS pk_encounter,
    NULL::integer AS pk_episode,
    NULL::integer AS pk_health_issue,
    c_enc.pk AS src_pk,
    'clin.encounter'::text AS src_table,
    c_enc.row_version,
    NULL::text AS health_issue,
    NULL::text AS issue_laterality,
    NULL::boolean AS issue_active,
    NULL::boolean AS issue_clinically_relevant,
    NULL::boolean AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM (((clin.encounter c_enc
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((c_enc.fk_location = d_ou.pk)))
     LEFT JOIN dem.org d_o ON ((d_ou.fk_org = d_o.pk)));


ALTER TABLE clin.v_pat_encounters_journal OWNER TO "gm-dbo";

--
-- Name: v_pat_episodes_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_episodes_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_epi.modified_when,
    c_enc.started AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_epi.modified_by)), (('<'::text || (c_epi.modified_by)::text) || '>'::text)) AS modified_by,
    'a'::text AS soap_cat,
    ((((((((public._('Episode'::text) || ' ('::text) ||
        CASE
            WHEN c_epi.is_open THEN public._('open'::text)
            ELSE public._('closed'::text)
        END) || COALESCE((', '::text || c_epi.diagnostic_certainty_classification), ''::text)) || '): '::text) || c_epi.description) || ';'::text) || COALESCE((((('
 '::text || public._('Synopsis'::text)) || ': '::text) || c_epi.summary) || ';'::text), ''::text)) || COALESCE((('
'::text || array_to_string(( SELECT array_agg(((((((((r_csr.code || ' ('::text) || r_ds.name_short) || ' - '::text) || r_ds.version) || ' - '::text) || r_ds.lang) || '): '::text) || r_csr.term)) AS array_agg
           FROM ((clin.lnk_code2episode c_lc2e
             JOIN ref.coding_system_root r_csr ON ((c_lc2e.fk_generic_code = r_csr.pk_coding_system)))
             JOIN ref.data_source r_ds ON ((r_ds.pk = r_csr.fk_data_source)))
          WHERE (c_lc2e.fk_item = c_epi.pk)), '; '::text)) || ';'::text), ''::text)) AS narrative,
    c_epi.fk_encounter AS pk_encounter,
    c_epi.pk AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    c_epi.pk AS src_pk,
    'clin.episode'::text AS src_table,
    c_epi.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM (((clin.episode c_epi
     JOIN clin.encounter c_enc ON ((c_epi.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)));


ALTER TABLE clin.v_pat_episodes_journal OWNER TO "gm-dbo";

--
-- Name: v_pat_narrative_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_narrative_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_n.modified_when,
        CASE
            WHEN (c_n.soap_cat = ANY (ARRAY['s'::text, 'o'::text, 'u'::text])) THEN c_enc.started
            WHEN (c_n.soap_cat IS NULL) THEN c_enc.last_affirmed
            WHEN (c_n.soap_cat = ANY (ARRAY['a'::text, 'p'::text])) THEN c_enc.last_affirmed
            ELSE NULL::timestamp with time zone
        END AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_n.modified_by)), (('<'::text || (c_n.modified_by)::text) || '>'::text)) AS modified_by,
    c_n.soap_cat,
    c_n.narrative,
    c_n.fk_encounter AS pk_encounter,
    c_n.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    c_n.pk AS src_pk,
    'clin.clin_narrative'::text AS src_table,
    c_n.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((((clin.clin_narrative c_n
     JOIN clin.encounter c_enc ON ((c_n.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_n.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)));


ALTER TABLE clin.v_pat_narrative_journal OWNER TO "gm-dbo";

--
-- Name: v_procedures_at_hospital_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_procedures_at_hospital_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_pr.modified_when,
    c_pr.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_pr.modified_by)), (('<'::text || (c_pr.modified_by)::text) || '>'::text)) AS modified_by,
        CASE
            WHEN (c_pr.is_ongoing IS TRUE) THEN 'p'::text
            ELSE 'o'::text
        END AS soap_cat,
    ((((((((((((public._('Procedure'::text) || ' "'::text) || c_pr.narrative) || '"'::text) || ' ('::text) || d_ou.description) || ' @ '::text) || d_o.description) || COALESCE((((', '::text || public._('until'::text)) || ' '::text) || to_char(c_pr.clin_end, 'YYYY Mon DD'::text)),
        CASE
            WHEN (c_pr.is_ongoing IS TRUE) THEN (', '::text || public._('ongoing'::text))
            ELSE ''::text
        END)) || ')'::text) || COALESCE(('
 '::text || c_pr.comment), ''::text)) || COALESCE((('
 '::text || public._('document #'::text)) || c_pr.fk_doc), ''::text)) || COALESCE((('
'::text || array_to_string(( SELECT array_agg(((((((((r_csr.code || ' ('::text) || r_ds.name_short) || ' - '::text) || r_ds.version) || ' - '::text) || r_ds.lang) || '): '::text) || r_csr.term)) AS array_agg
           FROM ((clin.lnk_code2procedure c_lc2p
             JOIN ref.coding_system_root r_csr ON ((c_lc2p.fk_generic_code = r_csr.pk_coding_system)))
             JOIN ref.data_source r_ds ON ((r_ds.pk = r_csr.fk_data_source)))
          WHERE (c_lc2p.fk_item = c_pr.pk)), '; '::text)) || ';'::text), ''::text)) AS narrative,
    c_pr.fk_encounter AS pk_encounter,
    c_pr.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    c_pr.pk AS src_pk,
    'clin.procedure'::text AS src_table,
    c_pr.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM (((((((clin.procedure c_pr
     JOIN clin.encounter c_enc ON ((c_pr.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_pr.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)))
     LEFT JOIN clin.hospital_stay c_hs ON ((c_pr.fk_hospital_stay = c_hs.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((c_hs.fk_org_unit = d_ou.pk)))
     LEFT JOIN dem.org d_o ON ((d_ou.fk_org = d_o.pk)))
  WHERE (c_pr.fk_hospital_stay IS NOT NULL);


ALTER TABLE clin.v_procedures_at_hospital_journal OWNER TO "gm-dbo";

--
-- Name: v_procedures_not_at_hospital_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_procedures_not_at_hospital_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_pr.modified_when,
    c_pr.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_pr.modified_by)), (('<'::text || (c_pr.modified_by)::text) || '>'::text)) AS modified_by,
        CASE
            WHEN (c_pr.is_ongoing IS TRUE) THEN 'p'::text
            ELSE 'o'::text
        END AS soap_cat,
    ((((((((((((public._('Procedure'::text) || ' "'::text) || c_pr.narrative) || '"'::text) || ' ('::text) || d_ou.description) || ' @ '::text) || d_o.description) || COALESCE((((', '::text || public._('until'::text)) || ' '::text) || to_char(c_pr.clin_end, 'YYYY Mon DD'::text)),
        CASE
            WHEN (c_pr.is_ongoing IS TRUE) THEN (', '::text || public._('ongoing'::text))
            ELSE ''::text
        END)) || ')'::text) || COALESCE(('
 '::text || c_pr.comment), ''::text)) || COALESCE((('
 '::text || public._('document #'::text)) || c_pr.fk_doc), ''::text)) || COALESCE((('
'::text || array_to_string(( SELECT array_agg(((((((((r_csr.code || ' ('::text) || r_ds.name_short) || ' - '::text) || r_ds.version) || ' - '::text) || r_ds.lang) || '): '::text) || r_csr.term)) AS array_agg
           FROM ((clin.lnk_code2procedure c_lc2p
             JOIN ref.coding_system_root r_csr ON ((c_lc2p.fk_generic_code = r_csr.pk_coding_system)))
             JOIN ref.data_source r_ds ON ((r_ds.pk = r_csr.fk_data_source)))
          WHERE (c_lc2p.fk_item = c_pr.pk)), '; '::text)) || ';'::text), ''::text)) AS narrative,
    c_pr.fk_encounter AS pk_encounter,
    c_pr.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    c_pr.pk AS src_pk,
    'clin.procedure'::text AS src_table,
    c_pr.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((((((clin.procedure c_pr
     JOIN clin.encounter c_enc ON ((c_pr.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_pr.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((c_pr.fk_org_unit = d_ou.pk)))
     LEFT JOIN dem.org d_o ON ((d_ou.fk_org = d_o.pk)))
  WHERE (c_pr.fk_hospital_stay IS NULL);


ALTER TABLE clin.v_procedures_not_at_hospital_journal OWNER TO "gm-dbo";

--
-- Name: v_procedures_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_procedures_journal AS
 SELECT v_procedures_at_hospital_journal.pk_patient,
    v_procedures_at_hospital_journal.modified_when,
    v_procedures_at_hospital_journal.clin_when,
    v_procedures_at_hospital_journal.modified_by,
    v_procedures_at_hospital_journal.soap_cat,
    v_procedures_at_hospital_journal.narrative,
    v_procedures_at_hospital_journal.pk_encounter,
    v_procedures_at_hospital_journal.pk_episode,
    v_procedures_at_hospital_journal.pk_health_issue,
    v_procedures_at_hospital_journal.src_pk,
    v_procedures_at_hospital_journal.src_table,
    v_procedures_at_hospital_journal.row_version,
    v_procedures_at_hospital_journal.health_issue,
    v_procedures_at_hospital_journal.issue_laterality,
    v_procedures_at_hospital_journal.issue_active,
    v_procedures_at_hospital_journal.issue_clinically_relevant,
    v_procedures_at_hospital_journal.issue_confidential,
    v_procedures_at_hospital_journal.episode,
    v_procedures_at_hospital_journal.episode_open,
    v_procedures_at_hospital_journal.encounter_started,
    v_procedures_at_hospital_journal.encounter_last_affirmed,
    v_procedures_at_hospital_journal.encounter_type,
    v_procedures_at_hospital_journal.encounter_l10n_type
   FROM clin.v_procedures_at_hospital_journal
UNION ALL
 SELECT v_procedures_not_at_hospital_journal.pk_patient,
    v_procedures_not_at_hospital_journal.modified_when,
    v_procedures_not_at_hospital_journal.clin_when,
    v_procedures_not_at_hospital_journal.modified_by,
    v_procedures_not_at_hospital_journal.soap_cat,
    v_procedures_not_at_hospital_journal.narrative,
    v_procedures_not_at_hospital_journal.pk_encounter,
    v_procedures_not_at_hospital_journal.pk_episode,
    v_procedures_not_at_hospital_journal.pk_health_issue,
    v_procedures_not_at_hospital_journal.src_pk,
    v_procedures_not_at_hospital_journal.src_table,
    v_procedures_not_at_hospital_journal.row_version,
    v_procedures_not_at_hospital_journal.health_issue,
    v_procedures_not_at_hospital_journal.issue_laterality,
    v_procedures_not_at_hospital_journal.issue_active,
    v_procedures_not_at_hospital_journal.issue_clinically_relevant,
    v_procedures_not_at_hospital_journal.issue_confidential,
    v_procedures_not_at_hospital_journal.episode,
    v_procedures_not_at_hospital_journal.episode_open,
    v_procedures_not_at_hospital_journal.encounter_started,
    v_procedures_not_at_hospital_journal.encounter_last_affirmed,
    v_procedures_not_at_hospital_journal.encounter_type,
    v_procedures_not_at_hospital_journal.encounter_l10n_type
   FROM clin.v_procedures_not_at_hospital_journal;


ALTER TABLE clin.v_procedures_journal OWNER TO "gm-dbo";

--
-- Name: message_inbox; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.message_inbox (
    pk integer NOT NULL,
    fk_staff integer,
    fk_inbox_item_type integer NOT NULL,
    comment text,
    data text,
    importance smallint DEFAULT 0 NOT NULL,
    fk_patient integer,
    ufk_context integer[],
    due_date date,
    expiry_date date,
    CONSTRAINT dem_inbox_sane_expiry_date CHECK (((expiry_date IS NULL) OR (due_date IS NULL) OR (expiry_date > due_date))),
    CONSTRAINT message_must_have_recipient CHECK ((((fk_staff IS NULL) AND (fk_patient IS NULL)) IS FALSE)),
    CONSTRAINT provider_inbox_comment_check CHECK ((btrim(COALESCE(comment, 'xxxDEFAULTxxx'::text)) <> ''::text)),
    CONSTRAINT provider_inbox_importance_check CHECK (((importance = '-1'::integer) OR (importance = 0) OR (importance = 1)))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.message_inbox OWNER TO "gm-dbo";

--
-- Name: TABLE message_inbox; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.message_inbox IS 'messages in GNUmed relating to a patient, a provider, and a context';


--
-- Name: COLUMN message_inbox.fk_staff; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.message_inbox.fk_staff IS 'the member of staff this message is addressed to';


--
-- Name: COLUMN message_inbox.fk_inbox_item_type; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.message_inbox.fk_inbox_item_type IS 'the item (message) type';


--
-- Name: COLUMN message_inbox.comment; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.message_inbox.comment IS 'a free-text comment, may be NULL but not empty';


--
-- Name: COLUMN message_inbox.data; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.message_inbox.data IS 'arbitrary data an application might wish to attach to
	 the message, like a cookie, basically';


--
-- Name: COLUMN message_inbox.importance; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.message_inbox.importance IS 'the relative importance of this message:

	 -1: lower than most things already in the inbox ("low")

	  0: same as most things ("standard")

	  1: higher than most things already there ("high")';


--
-- Name: COLUMN message_inbox.ufk_context; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.message_inbox.ufk_context IS 'a nullable array of Unchecked Foreign Keys, it is up to
	 the application to know what this points to, it will
	 have to make sense within the context of the combination
	 of staff ID, item type, and comment';


--
-- Name: COLUMN message_inbox.due_date; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.message_inbox.due_date IS 'The date this message/reminder is due. If NULL then the message is not a reminder.';


--
-- Name: COLUMN message_inbox.expiry_date; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.message_inbox.expiry_date IS 'The date this message/reminder "expires". Must be > .due_date it not NULL.';


--
-- Name: v_reminders_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_reminders_journal AS
 SELECT d_mi.fk_patient AS pk_patient,
    d_mi.modified_when,
    d_mi.due_date AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = d_mi.modified_by)), (('<'::text || (d_mi.modified_by)::text) || '>'::text)) AS modified_by,
    NULL::text AS soap_cat,
    ((((((((((((((((public._('Due today'::text) || ' ('::text) || d_vit.l10n_category) || ' - '::text) || d_vit.l10n_type) || ')'::text) || '
'::text) || COALESCE(((' '::text || d_mi.comment) || '
'::text), ''::text)) || COALESCE(((((' '::text || public._('Will expire:'::text)) || ' '::text) || to_char((d_mi.expiry_date)::timestamp with time zone, 'YYYY-MM-DD'::text)) || '
'::text), ''::text)) || ' '::text) || public._('Importance:'::text)) || ' '::text) || d_mi.importance) || '
'::text) || COALESCE(((((' '::text || public._('Context:'::text)) || ' '::text) || array_to_string(d_mi.ufk_context, ','::text, '?'::text)) || '
'::text), ''::text)) || COALESCE(((((' '::text || public._('Data:'::text)) || ' '::text) || d_mi.data) || '
'::text), ''::text)) || COALESCE((((' '::text || public._('Provider:'::text)) || ' '::text) || d_st.short_alias), ''::text)) AS narrative,
    ( SELECT c_e.pk
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = d_mi.fk_patient)
          ORDER BY c_e.started DESC
         LIMIT 1) AS pk_encounter,
    NULL::integer AS pk_episode,
    NULL::integer AS pk_health_issue,
    d_mi.pk AS src_pk,
    'dem.message_inbox'::text AS src_table,
    d_mi.row_version,
    NULL::text AS health_issue,
    NULL::text AS issue_laterality,
    NULL::boolean AS issue_active,
    NULL::boolean AS issue_clinically_relevant,
    NULL::boolean AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    ( SELECT c_e.started
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = d_mi.fk_patient)
          ORDER BY c_e.started DESC
         LIMIT 1) AS encounter_started,
    ( SELECT c_e.last_affirmed
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = d_mi.fk_patient)
          ORDER BY c_e.started DESC
         LIMIT 1) AS encounter_last_affirmed,
    NULL::text AS encounter_type,
    NULL::text AS encounter_l10n_type
   FROM ((dem.message_inbox d_mi
     JOIN dem.v_inbox_item_type d_vit ON ((d_mi.fk_inbox_item_type = d_vit.pk_type)))
     LEFT JOIN dem.staff d_st ON ((d_mi.fk_staff = d_st.pk)))
  WHERE ((d_mi.fk_patient IS NOT NULL) AND (d_vit.category = 'clinical'::text) AND (d_mi.due_date IS NOT NULL))
UNION
 SELECT d_mi.fk_patient AS pk_patient,
    d_mi.modified_when,
    d_mi.expiry_date AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = d_mi.modified_by)), (('<'::text || (d_mi.modified_by)::text) || '>'::text)) AS modified_by,
    NULL::text AS soap_cat,
    ((((((((((((((((public._('Epires today'::text) || ' ('::text) || d_vit.l10n_category) || ' - '::text) || d_vit.l10n_type) || ')'::text) || '
'::text) || COALESCE(((' '::text || d_mi.comment) || '
'::text), ''::text)) || COALESCE(((((' '::text || public._('Was due:'::text)) || ' '::text) || to_char((d_mi.due_date)::timestamp with time zone, 'YYYY-MM-DD'::text)) || '
'::text), ''::text)) || ' '::text) || public._('Importance:'::text)) || ' '::text) || d_mi.importance) || '
'::text) || COALESCE(((((' '::text || public._('Context:'::text)) || ' '::text) || array_to_string(d_mi.ufk_context, ','::text, '?'::text)) || '
'::text), ''::text)) || COALESCE(((((' '::text || public._('Data:'::text)) || ' '::text) || d_mi.data) || '
'::text), ''::text)) || COALESCE((((' '::text || public._('Provider:'::text)) || ' '::text) || d_st.short_alias), ''::text)) AS narrative,
    ( SELECT c_e.pk
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = d_mi.fk_patient)
          ORDER BY c_e.started DESC
         LIMIT 1) AS pk_encounter,
    NULL::integer AS pk_episode,
    NULL::integer AS pk_health_issue,
    d_mi.pk AS src_pk,
    'dem.message_inbox'::text AS src_table,
    d_mi.row_version,
    NULL::text AS health_issue,
    NULL::text AS issue_laterality,
    NULL::boolean AS issue_active,
    NULL::boolean AS issue_clinically_relevant,
    NULL::boolean AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    ( SELECT c_e.started
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = d_mi.fk_patient)
          ORDER BY c_e.started DESC
         LIMIT 1) AS encounter_started,
    ( SELECT c_e.last_affirmed
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = d_mi.fk_patient)
          ORDER BY c_e.started DESC
         LIMIT 1) AS encounter_last_affirmed,
    NULL::text AS encounter_type,
    NULL::text AS encounter_l10n_type
   FROM ((dem.message_inbox d_mi
     JOIN dem.v_inbox_item_type d_vit ON ((d_mi.fk_inbox_item_type = d_vit.pk_type)))
     LEFT JOIN dem.staff d_st ON ((d_mi.fk_staff = d_st.pk)))
  WHERE ((d_mi.fk_patient IS NOT NULL) AND (d_vit.category = 'clinical'::text) AND (d_mi.expiry_date IS NOT NULL))
UNION
 SELECT d_mi.fk_patient AS pk_patient,
    d_mi.modified_when,
    d_mi.modified_when AS clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = d_mi.modified_by)), (('<'::text || (d_mi.modified_by)::text) || '>'::text)) AS modified_by,
    NULL::text AS soap_cat,
    (((((((((((((((((public._('Clinical reminder'::text) || ' ('::text) || d_vit.l10n_category) || ' - '::text) || d_vit.l10n_type) || ')'::text) || '
'::text) || COALESCE(((' '::text || d_mi.comment) || '
'::text), ''::text)) || COALESCE(((((' '::text || public._('Due:'::text)) || ' '::text) || to_char((d_mi.due_date)::timestamp with time zone, 'YYYY-MM-DD'::text)) || '
'::text), ''::text)) || COALESCE(((((' '::text || public._('Expires:'::text)) || ' '::text) || to_char((d_mi.expiry_date)::timestamp with time zone, 'YYYY-MM-DD'::text)) || '
'::text), ''::text)) || ' '::text) || public._('Importance:'::text)) || ' '::text) || d_mi.importance) || '
'::text) || COALESCE(((((' '::text || public._('Context:'::text)) || ' '::text) || array_to_string(d_mi.ufk_context, ','::text, '?'::text)) || '
'::text), ''::text)) || COALESCE(((((' '::text || public._('Data:'::text)) || ' '::text) || d_mi.data) || '
'::text), ''::text)) || COALESCE((((' '::text || public._('Provider:'::text)) || ' '::text) || d_st.short_alias), ''::text)) AS narrative,
    ( SELECT c_e.pk
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = d_mi.fk_patient)
          ORDER BY c_e.started DESC
         LIMIT 1) AS pk_encounter,
    NULL::integer AS pk_episode,
    NULL::integer AS pk_health_issue,
    d_mi.pk AS src_pk,
    'dem.message_inbox'::text AS src_table,
    d_mi.row_version,
    NULL::text AS health_issue,
    NULL::text AS issue_laterality,
    NULL::boolean AS issue_active,
    NULL::boolean AS issue_clinically_relevant,
    NULL::boolean AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    ( SELECT c_e.started
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = d_mi.fk_patient)
          ORDER BY c_e.started DESC
         LIMIT 1) AS encounter_started,
    ( SELECT c_e.last_affirmed
           FROM clin.encounter c_e
          WHERE (c_e.fk_patient = d_mi.fk_patient)
          ORDER BY c_e.started DESC
         LIMIT 1) AS encounter_last_affirmed,
    NULL::text AS encounter_type,
    NULL::text AS encounter_l10n_type
   FROM ((dem.message_inbox d_mi
     JOIN dem.v_inbox_item_type d_vit ON ((d_mi.fk_inbox_item_type = d_vit.pk_type)))
     LEFT JOIN dem.staff d_st ON ((d_mi.fk_staff = d_st.pk)))
  WHERE ((d_mi.fk_patient IS NOT NULL) AND (d_vit.category = 'clinical'::text) AND ((d_mi.due_date IS NOT NULL) OR (d_mi.expiry_date IS NOT NULL)));


ALTER TABLE clin.v_reminders_journal OWNER TO "gm-dbo";

--
-- Name: dose; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.dose (
    pk integer NOT NULL,
    fk_substance integer NOT NULL,
    amount numeric NOT NULL,
    unit text NOT NULL,
    dose_unit text,
    CONSTRAINT ref_dose_sane_amount CHECK ((amount > (0)::numeric)),
    CONSTRAINT ref_dose_sane_dose_unit CHECK ((gm.is_null_or_non_empty_string(dose_unit) IS TRUE)),
    CONSTRAINT ref_dose_sane_unit CHECK ((gm.is_null_or_blank_string(unit) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE ref.dose OWNER TO "gm-dbo";

--
-- Name: TABLE dose; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.dose IS 'Links doses to consumable substances.';


--
-- Name: COLUMN dose.fk_substance; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.dose.fk_substance IS 'FK linking the substance';


--
-- Name: COLUMN dose.amount; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.dose.amount IS 'the amount of substance (the "5" in "5mg/ml")';


--
-- Name: COLUMN dose.unit; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.dose.unit IS 'unit of amount (the "mg" in "5mg/ml")';


--
-- Name: COLUMN dose.dose_unit; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.dose.dose_unit IS 'unit of reference amount,
 IOW the "ml" in "5mg/ml" (the reference amount is always assumed to be 1, as in "5mg/1ml"),
 if NULL the unit is "1 delivery unit (tablet, capsule, suppository, sachet, ...)",
 corresponds to "dose unit" in UCUM or "unit of product usage" in SNOMED';


--
-- Name: drug_product; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.drug_product (
    pk integer NOT NULL,
    description text NOT NULL,
    preparation text NOT NULL,
    atc_code text,
    is_fake boolean DEFAULT false NOT NULL,
    fk_data_source integer,
    external_code text,
    external_code_type text,
    CONSTRAINT desc_not_empty CHECK ((gm.is_null_or_blank_string(description) IS FALSE)),
    CONSTRAINT drug_sane_external_code CHECK ((gm.is_null_or_non_empty_string(external_code) IS TRUE)),
    CONSTRAINT drug_sane_external_code_type CHECK ((((external_code IS NULL) AND (external_code_type IS NULL)) OR ((external_code IS NOT NULL) AND (external_code_type IS NOT NULL)))),
    CONSTRAINT prep_not_empty CHECK ((gm.is_null_or_blank_string(preparation) IS FALSE)),
    CONSTRAINT sane_atc CHECK ((gm.is_null_or_non_empty_string(atc_code) IS TRUE))
)
INHERITS (audit.audit_fields);


ALTER TABLE ref.drug_product OWNER TO "gm-dbo";

--
-- Name: TABLE drug_product; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.drug_product IS 'The medicine chest of this praxis. Stores brands of drugs patients
 have been taking regardless of whether that brand still exists or
 in fact ever existed as such (as in lifestyle thingies).';


--
-- Name: COLUMN drug_product.description; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.drug_product.description IS 'the name of this drug it is marketed under by the manufacturer';


--
-- Name: COLUMN drug_product.preparation; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.drug_product.preparation IS 'the preparation the drug is delivered in, eg liquid, cream, tablet, etc.';


--
-- Name: COLUMN drug_product.atc_code; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.drug_product.atc_code IS 'the Anatomic Therapeutic Chemical code for this drug, used to compute possible substitutes';


--
-- Name: COLUMN drug_product.is_fake; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.drug_product.is_fake IS 'Whether this truly is an actual brand of an actual drug
 rather than a fake brand created for documenting a, say,
 lifestyle nutrient or simply a component as opposed to
 a particular actual brand.';


--
-- Name: COLUMN drug_product.fk_data_source; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.drug_product.fk_data_source IS 'the data source this entry came from';


--
-- Name: COLUMN drug_product.external_code; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.drug_product.external_code IS 'an opaque code from an external data source, such as "PZN" in Germany';


--
-- Name: COLUMN drug_product.external_code_type; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.drug_product.external_code_type IS 'an opaque code type from an external data source, such as "PZN" in Germany';


--
-- Name: lnk_dose2drug; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.lnk_dose2drug (
    pk integer NOT NULL,
    fk_drug_product integer NOT NULL,
    fk_dose integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE ref.lnk_dose2drug OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_dose2drug; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.lnk_dose2drug IS 'Links doses to drug products';


--
-- Name: COLUMN lnk_dose2drug.fk_drug_product; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.lnk_dose2drug.fk_drug_product IS 'FK linking the drug product';


--
-- Name: COLUMN lnk_dose2drug.fk_dose; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.lnk_dose2drug.fk_dose IS 'FK linking the dose';


--
-- Name: substance; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.substance (
    pk integer NOT NULL,
    description text,
    atc text,
    intake_instructions text,
    CONSTRAINT ref_substance_sane_desc CHECK ((gm.is_null_or_non_empty_string(description) IS TRUE)),
    CONSTRAINT ref_substance_sane_instructions CHECK ((gm.is_null_or_non_empty_string(intake_instructions) IS TRUE))
)
INHERITS (audit.audit_fields);


ALTER TABLE ref.substance OWNER TO "gm-dbo";

--
-- Name: TABLE substance; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.substance IS 'Holds substances that are consumed by patients for various reasons.';


--
-- Name: COLUMN substance.description; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.substance.description IS 'the name of the substance';


--
-- Name: COLUMN substance.atc; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.substance.atc IS 'the ATC of the substance';


--
-- Name: COLUMN substance.intake_instructions; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.substance.intake_instructions IS 'any intake instructions for the substance';


--
-- Name: v_substance_intake_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_substance_intake_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_si.modified_when,
    c_si.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_si.modified_by)), (('<'::text || (c_si.modified_by)::text) || '>'::text)) AS modified_by,
    c_si.soap_cat,
    (((((((((((((((((((((((
        CASE
            WHEN (c_si.is_long_term IS TRUE) THEN (public._('long-term'::text) || ' '::text)
            ELSE ''::text
        END || public._('substance intake'::text)) || ' '::text) ||
        CASE
            WHEN (c_si.intake_is_approved_of IS TRUE) THEN public._('(approved of)'::text)
            WHEN (c_si.intake_is_approved_of IS FALSE) THEN public._('(not approved of)'::text)
            ELSE public._('(of unknown approval)'::text)
        END) || ' '::text) ||
        CASE
            WHEN (c_si.harmful_use_type IS NULL) THEN ''::text
            WHEN (c_si.harmful_use_type = 0) THEN public._('no harmful use'::text)
            WHEN (c_si.harmful_use_type = 1) THEN public._('harmful use'::text)
            WHEN (c_si.harmful_use_type = 2) THEN public._('addiction'::text)
            WHEN (c_si.harmful_use_type = 3) THEN public._('previous addiction'::text)
            ELSE NULL::text
        END) || ':
'::text) || ' '::text) || r_s.description) || COALESCE(((' ['::text || r_s.atc) || '] '::text), ' '::text)) || (r_d.amount)::text) || r_d.unit) || ' '::text) || public._(r_dp.preparation)) || COALESCE((' '::text || c_si.schedule), ''::text)) || ', '::text) ||
        CASE
            WHEN (c_si.comment_on_start = '?'::text) THEN '?'::text
            WHEN (c_si.comment_on_start IS NULL) THEN to_char(c_si.clin_when, 'YYYY-MM-DD'::text)
            ELSE (((('~'::text || to_char(c_si.clin_when, 'YYYY-MM-DD'::text)) || ' ('::text) || c_si.comment_on_start) || ')'::text)
        END) || COALESCE((' -> '::text || c_si.duration), ''::text)) || '
'::text) || COALESCE(((((' '::text || public._('Discontinued'::text)) || to_char(c_si.discontinued, ': YYYY-MM-DD'::text)) || COALESCE(((' ('::text || c_si.discontinue_reason) || ')'::text), ''::text)) || '
'::text), ''::text)) || COALESCE(((('
 '::text || public._('Aim'::text)) || ': '::text) || c_si.aim), ''::text)) || COALESCE(((('
 '::text || public._('Notes'::text)) || ': '::text) || c_si.narrative), ''::text)) || COALESCE(((('
 '::text || public._('Instructions'::text)) || ': '::text) || r_s.intake_instructions), ''::text)) || COALESCE(((((' "'::text || r_dp.description) || '"'::text) || COALESCE(((' ['::text || r_dp.atc_code) || ']'::text), ''::text)) || COALESCE(((((' ('::text || r_dp.external_code_type) || ': '::text) || r_dp.external_code) || ')'::text), ''::text)), ''::text)) AS narrative,
    c_si.fk_encounter AS pk_encounter,
    c_si.fk_episode AS pk_episode,
    c_hi.pk AS pk_health_issue,
    c_si.pk AS src_pk,
    'clin.substance_intake'::text AS src_table,
    c_si.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((((((((clin.substance_intake c_si
     JOIN ref.lnk_dose2drug r_ld2d ON ((c_si.fk_drug_component = r_ld2d.pk)))
     JOIN ref.drug_product r_dp ON ((r_ld2d.fk_drug_product = r_dp.pk)))
     JOIN ref.dose r_d ON ((r_ld2d.fk_dose = r_d.pk)))
     JOIN ref.substance r_s ON ((r_d.fk_substance = r_s.pk)))
     JOIN clin.encounter c_enc ON ((c_si.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_si.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)));


ALTER TABLE clin.v_substance_intake_journal OWNER TO "gm-dbo";

--
-- Name: v_suppressed_hints_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_suppressed_hints_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_sh.modified_when,
    c_sh.suppressed_when AS clin_when,
    c_sh.modified_by,
    'p'::text AS soap_cat,
    ((((((((((((((
        CASE
            WHEN (r_vah.is_active IS TRUE) THEN public._('Active hint'::text)
            ELSE public._('Inactive hint'::text)
        END || ' #'::text) || c_sh.fk_hint) || ' '::text) || public._('suppressed by'::text)) || ' '::text) || (c_sh.suppressed_by)::text) || '
'::text) || COALESCE(((public._('Title: '::text) || r_vah.title) || '
'::text), ''::text)) || COALESCE(((public._('URL: '::text) || r_vah.url) || '
'::text), ''::text)) || COALESCE(((public._('Source: '::text) || r_vah.source) || '
'::text), ''::text)) || COALESCE(((public._('Rationale: '::text) || c_sh.rationale) || '
'::text), ''::text)) ||
        CASE
            WHEN (c_sh.md5_sum <> r_vah.md5_sum) THEN (public._('Hint definition has been modified since suppression. Rationale for suppression may no longer apply.'::text) || '
'::text)
            ELSE ''::text
        END) || COALESCE(((public._('Hint: '::text) || r_vah.hint) || '
'::text), ''::text)) || COALESCE((public._('Recommendation: '::text) || r_vah.recommendation), ''::text)) AS narrative,
    c_sh.fk_encounter,
    NULL::integer AS pk_episode,
    NULL::integer AS pk_health_issue,
    c_sh.pk AS src_pk,
    'clin.suppressed_hint'::text AS src_table,
    c_sh.row_version,
    NULL::text AS health_issue,
    NULL::text AS issue_laterality,
    NULL::boolean AS issue_active,
    NULL::boolean AS issue_clinically_relevant,
    NULL::boolean AS issue_confidential,
    NULL::text AS episode,
    NULL::boolean AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM (((clin.suppressed_hint c_sh
     JOIN clin.encounter c_enc ON ((c_sh.fk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN ref.v_auto_hints r_vah ON ((c_sh.fk_hint = r_vah.pk_auto_hint)));


ALTER TABLE clin.v_suppressed_hints_journal OWNER TO "gm-dbo";

--
-- Name: v_test_types4test_panel; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_test_types4test_panel AS
 SELECT c_tp.pk AS pk_test_panel,
    c_tt.pk AS pk_test_type,
    c_tp.description AS test_panel,
    c_tt.name AS test_type,
    c_tt.abbrev AS test_abbrev,
    c_tt.loinc,
    c_tp.comment AS comment_panel,
    c_tt.comment AS comment_test_type,
    c_tt.reference_unit,
    c_tt.fk_meta_test_type AS pk_meta_test_type,
    c_ll2tp.pk AS pk_lnk_loinc2test_panel
   FROM ((clin.test_panel c_tp
     JOIN clin.lnk_loinc2test_panel c_ll2tp ON ((c_ll2tp.fk_test_panel = c_tp.pk)))
     JOIN clin.test_type c_tt ON ((c_tt.loinc = c_ll2tp.loinc)));


ALTER TABLE clin.v_test_types4test_panel OWNER TO "gm-dbo";

--
-- Name: VIEW v_test_types4test_panel; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_test_types4test_panel IS 'Shows test types linked to test panels via their LOINC code.';


--
-- Name: v_test_types; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_test_types AS
 SELECT c_tt.pk AS pk_test_type,
    c_tt.abbrev,
    c_tt.name,
    c_tt.loinc,
    c_tt.reference_unit,
    c_tt.comment AS comment_type,
    d_ou.description AS name_org,
    c_to.comment AS comment_org,
    c_to.contact AS contact_org,
    COALESCE(c_mtt.abbrev, c_tt.abbrev) AS unified_abbrev,
    COALESCE(c_mtt.name, c_tt.name) AS unified_name,
    COALESCE(c_mtt.loinc, c_tt.loinc) AS unified_loinc,
    (c_tt.fk_meta_test_type IS NULL) AS is_fake_meta_type,
    c_mtt.abbrev AS abbrev_meta,
    c_mtt.name AS name_meta,
    c_mtt.loinc AS loinc_meta,
    c_mtt.comment AS comment_meta,
    ( SELECT array_agg(c_vtt4tp.pk_test_panel) AS array_agg
           FROM clin.v_test_types4test_panel c_vtt4tp
          WHERE (c_vtt4tp.pk_test_type = c_tt.pk)) AS pk_test_panels,
    c_tt.fk_test_org AS pk_test_org,
    c_tt.fk_meta_test_type AS pk_meta_test_type,
    c_to.fk_org_unit AS pk_org_unit,
    c_to.fk_adm_contact AS pk_adm_contact_org,
    c_to.fk_med_contact AS pk_med_contact_org,
    c_tt.xmin AS xmin_test_type
   FROM (((clin.test_type c_tt
     LEFT JOIN clin.test_org c_to ON ((c_to.pk = c_tt.fk_test_org)))
     LEFT JOIN dem.org_unit d_ou ON ((c_to.fk_org_unit = d_ou.pk)))
     LEFT JOIN clin.meta_test_type c_mtt ON ((c_tt.fk_meta_test_type = c_mtt.pk)));


ALTER TABLE clin.v_test_types OWNER TO "gm-dbo";

--
-- Name: VIEW v_test_types; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_test_types IS 'denormalizes test types with test orgs and meta types';


--
-- Name: v_test_results; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_test_results AS
 SELECT cenc.fk_patient AS pk_patient,
    tr.pk AS pk_test_result,
    tr.clin_when,
    c_vtt.unified_abbrev,
    c_vtt.unified_name,
    c_vtt.unified_loinc,
        CASE
            WHEN (COALESCE(btrim(tr.val_alpha), ''::text) = ''::text) THEN (tr.val_num)::text
            ELSE
            CASE
                WHEN (tr.val_num IS NULL) THEN tr.val_alpha
                ELSE ((((tr.val_num)::text || ' ('::text) || tr.val_alpha) || ')'::text)
            END
        END AS unified_val,
    COALESCE(tr.val_target_min, tr.val_normal_min) AS unified_target_min,
    COALESCE(tr.val_target_max, tr.val_normal_max) AS unified_target_max,
    COALESCE(tr.val_target_range, tr.val_normal_range) AS unified_target_range,
    tr.status,
    tr.soap_cat,
    tr.narrative AS comment,
    tr.val_num,
    tr.val_alpha,
    tr.val_unit,
    c_vtt.reference_unit,
    tr.val_normal_min,
    tr.val_normal_max,
    tr.val_normal_range,
    tr.val_target_min,
    tr.val_target_max,
    tr.val_target_range,
    tr.abnormality_indicator,
    tr.norm_ref_group,
    tr.note_test_org,
    tr.material,
    tr.material_detail,
    c_vtt.abbrev AS abbrev_tt,
    c_vtt.name AS name_tt,
    c_vtt.loinc AS loinc_tt,
    c_vtt.comment_type AS comment_tt,
    c_vtt.name_org AS name_test_org,
    c_vtt.contact_org AS contact_test_org,
    c_vtt.comment_org AS comment_test_org,
    c_vtt.is_fake_meta_type,
    c_vtt.abbrev_meta,
    c_vtt.name_meta,
    c_vtt.loinc_meta,
    c_vtt.comment_meta,
    epi.description AS episode,
    chi.description AS health_issue,
    (COALESCE(rtr.fk_reviewed_row, 0))::boolean AS reviewed,
    rtr.is_technically_abnormal,
    rtr.clinically_relevant AS is_clinically_relevant,
    rtr.comment AS review_comment,
    ( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.pk = rtr.fk_reviewer)) AS last_reviewer,
    rtr.modified_when AS last_reviewed,
    COALESCE((rtr.fk_reviewer = ( SELECT staff.pk
           FROM dem.staff
          WHERE (staff.db_user = "current_user"()))), false) AS review_by_you,
    COALESCE((tr.fk_intended_reviewer = rtr.fk_reviewer), false) AS review_by_responsible_reviewer,
    ( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.pk = tr.fk_intended_reviewer)) AS responsible_reviewer,
    COALESCE((tr.fk_intended_reviewer = ( SELECT staff.pk
           FROM dem.staff
          WHERE (staff.db_user = "current_user"()))), false) AS you_are_responsible,
        CASE
            WHEN (( SELECT 1
               FROM dem.staff
              WHERE (staff.db_user = tr.modified_by)) IS NULL) THEN (('<'::text || (tr.modified_by)::text) || '>'::text)
            ELSE ( SELECT staff.short_alias
               FROM dem.staff
              WHERE (staff.db_user = tr.modified_by))
        END AS modified_by,
    tr.val_grouping,
    tr.source_data,
    tr.modified_when,
    tr.row_version,
    tr.pk_item,
    tr.fk_encounter AS pk_encounter,
    tr.fk_episode AS pk_episode,
    tr.fk_type AS pk_test_type,
    tr.fk_intended_reviewer AS pk_intended_reviewer,
    tr.fk_request AS pk_request,
    tr.xmin AS xmin_test_result,
    c_vtt.pk_test_org,
    c_vtt.pk_meta_test_type,
    epi.fk_health_issue AS pk_health_issue,
    rtr.fk_reviewer AS pk_last_reviewer
   FROM ((((clin.test_result tr
     LEFT JOIN clin.encounter cenc ON ((tr.fk_encounter = cenc.pk)))
     LEFT JOIN clin.episode epi ON ((tr.fk_episode = epi.pk)))
     LEFT JOIN clin.reviewed_test_results rtr ON ((tr.pk = rtr.fk_reviewed_row)))
     LEFT JOIN clin.health_issue chi ON ((epi.fk_health_issue = chi.pk))),
    clin.v_test_types c_vtt
  WHERE (tr.fk_type = c_vtt.pk_test_type);


ALTER TABLE clin.v_test_results OWNER TO "gm-dbo";

--
-- Name: VIEW v_test_results; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_test_results IS 'denormalized view over test_results joined with (possibly unified) test
	 type and patient/episode/encounter keys';


--
-- Name: v_test_results_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_test_results_journal AS
 SELECT c_vtr.pk_patient,
    c_vtr.modified_when,
    c_vtr.clin_when,
    c_vtr.modified_by,
    c_vtr.soap_cat,
    COALESCE(((((((((((((((((((((((((((c_vtr.unified_name || ' ('::text) || c_vtr.unified_abbrev) || COALESCE(((' [#'::text || c_vtr.unified_loinc) || ']'::text), ''::text)) || '): '::text) || c_vtr.unified_val) || ' '::text) || COALESCE(c_vtr.val_unit, ''::text)) || ' '::text) || COALESCE((('('::text || c_vtr.abnormality_indicator) || ')'::text), ''::text)) || '
'::text) || public._('Range: '::text)) || COALESCE((c_vtr.unified_target_min)::text, ''::text)) || ' - '::text) || COALESCE((c_vtr.unified_target_max)::text, ''::text)) || ' / '::text) || COALESCE(c_vtr.unified_target_range, ''::text)) || COALESCE(((' ('::text || c_vtr.norm_ref_group) || ')'::text), ''::text)) || '
'::text) || COALESCE(((public._('Assessment: '::text) || c_vtr.comment) || '
'::text), ''::text)) || COALESCE(((public._('Context: '::text) || c_vtr.note_test_org) || '
'::text), ''::text)) || COALESCE(((public._('Status: '::text) || c_vtr.status) || '
'::text), ''::text)) || COALESCE(((public._('Grouping: '::text) || c_vtr.val_grouping) || '
'::text), ''::text)) || COALESCE((((((((public._('Review by '::text) || c_vtr.last_reviewer) || ' @ '::text) || to_char(c_vtr.last_reviewed, 'YYYY-MM-DD HH24:MI'::text)) || ': '::text) ||
        CASE
            WHEN c_vtr.is_technically_abnormal THEN (public._('abnormal'::text) || ', '::text)
            ELSE ''::text
        END) ||
        CASE
            WHEN c_vtr.is_clinically_relevant THEN (public._('relevant'::text) || ' '::text)
            ELSE ''::text
        END) || COALESCE((('('::text || c_vtr.review_comment) || ')
'::text), '
'::text)), ''::text)) || public._('Responsible clinician: '::text)) || c_vtr.responsible_reviewer) || COALESCE((((public._('Source data:'::text) || '
'::text) || c_vtr.source_data) || '
'::text), ''::text)), 'faulty clin.v_test_results_journal definition'::text) AS narrative,
    c_vtr.pk_encounter,
    c_vtr.pk_episode,
    c_vtr.pk_health_issue,
    c_vtr.pk_test_result AS src_pk,
    'clin.test_result'::text AS src_table,
    c_vtr.row_version,
    c_vtr.health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_vtr.episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((((clin.v_test_results c_vtr
     JOIN clin.encounter c_enc ON ((c_vtr.pk_encounter = c_enc.pk)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_vtr.pk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)));


ALTER TABLE clin.v_test_results_journal OWNER TO "gm-dbo";

--
-- Name: VIEW v_test_results_journal; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_test_results_journal IS 'formatting of v_test_results for inclusion in v_emr_journal';


--
-- Name: vaccination; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.vaccination (
    pk integer NOT NULL,
    fk_provider integer,
    fk_vaccine integer NOT NULL,
    site text,
    batch_no text NOT NULL,
    reaction text,
    CONSTRAINT vaccination_sane_narrative CHECK ((gm.is_null_or_non_empty_string(narrative) IS TRUE)),
    CONSTRAINT vaccination_sane_reaction CHECK ((gm.is_null_or_non_empty_string(reaction) IS TRUE)),
    CONSTRAINT vaccination_sane_site CHECK ((gm.is_null_or_non_empty_string(site) IS TRUE))
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.vaccination OWNER TO "gm-dbo";

--
-- Name: TABLE vaccination; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.vaccination IS 'holds vaccinations actually given';


--
-- Name: COLUMN vaccination.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination.narrative IS 'Used to record a comment on this vaccination.';


--
-- Name: COLUMN vaccination.fk_provider; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination.fk_provider IS 'Who administered this vaccination.';


--
-- Name: COLUMN vaccination.site; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination.site IS 'The site of injection used in this vaccination.';


--
-- Name: COLUMN vaccination.batch_no; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination.batch_no IS 'The batch/lot number of the vaccine given.';


--
-- Name: COLUMN vaccination.reaction; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination.reaction IS 'Used to record reactions to this vaccination.';


--
-- Name: vaccine; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.vaccine (
    pk integer NOT NULL,
    id_route integer,
    is_live boolean NOT NULL,
    min_age interval,
    max_age interval,
    comment text,
    fk_drug_product integer NOT NULL,
    CONSTRAINT vaccine_sane_max_age CHECK (((max_age IS NULL) OR (max_age < '150 years'::interval))),
    CONSTRAINT vaccine_sane_min_age CHECK (((min_age IS NULL) OR (((max_age IS NULL) AND (min_age < '150 years'::interval)) OR ((max_age IS NOT NULL) AND (min_age <= max_age)))))
)
INHERITS (audit.audit_fields);


ALTER TABLE ref.vaccine OWNER TO "gm-dbo";

--
-- Name: TABLE vaccine; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.vaccine IS 'definition of a vaccine as available on the market';


--
-- Name: COLUMN vaccine.id_route; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.vaccine.id_route IS 'route this vaccine is given';


--
-- Name: COLUMN vaccine.is_live; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.vaccine.is_live IS 'whether this is a live vaccine';


--
-- Name: COLUMN vaccine.min_age; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.vaccine.min_age IS 'minimum age this vaccine is licensed for according to the information by the manufacturer';


--
-- Name: COLUMN vaccine.max_age; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.vaccine.max_age IS 'maximum age this vaccine is licensed for according to the information
	 by the manufacturer, use "5555 years" to indicate "no maximum age"';


--
-- Name: COLUMN vaccine.fk_drug_product; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.vaccine.fk_drug_product IS 'The brand of this vaccine, can be a fake entry in ref.branded_drug.';


--
-- Name: v_vaccinations_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_vaccinations_journal AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_vacc.modified_when,
    c_vacc.clin_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_vacc.modified_by)), (('<'::text || (c_vacc.modified_by)::text) || '>'::text)) AS modified_by,
    c_vacc.soap_cat,
    ((((((((((public._('Vaccination'::text) || ': '::text) || r_dp.description) || ' '::text) || '['::text) || c_vacc.batch_no) || ']'::text) || COALESCE(((' ('::text || c_vacc.site) || ')'::text), ''::text)) || COALESCE(((('
'::text || public._('Reaction'::text)) || ': '::text) || c_vacc.reaction), ''::text)) || COALESCE(((('
'::text || public._('Comment'::text)) || ': '::text) || c_vacc.narrative), ''::text)) || COALESCE(((('
'::text || public._('Indications'::text)) || ': '::text) || array_to_string(( SELECT array_agg(public._((r_s.atc || '-target'::text))) AS array_agg
           FROM ((ref.lnk_dose2drug r_ld2d
             JOIN ref.dose r_d ON ((r_d.pk = r_ld2d.fk_dose)))
             JOIN ref.substance r_s ON ((r_d.fk_substance = r_s.pk)))
          WHERE (r_ld2d.fk_drug_product = r_dp.pk)), ' / '::text)), ''::text)) AS narrative,
    c_vacc.fk_encounter AS pk_encounter,
    c_vacc.fk_episode AS pk_episode,
    ( SELECT episode.fk_health_issue
           FROM clin.episode
          WHERE (episode.pk = c_vacc.fk_episode)) AS pk_health_issue,
    c_vacc.pk AS src_pk,
    'clin.vaccination'::text AS src_table,
    c_vacc.row_version,
    c_hi.description AS health_issue,
    c_hi.laterality AS issue_laterality,
    c_hi.is_active AS issue_active,
    c_hi.clinically_relevant AS issue_clinically_relevant,
    c_hi.is_confidential AS issue_confidential,
    c_epi.description AS episode,
    c_epi.is_open AS episode_open,
    c_enc.started AS encounter_started,
    c_enc.last_affirmed AS encounter_last_affirmed,
    c_ety.description AS encounter_type,
    public._(c_ety.description) AS encounter_l10n_type
   FROM ((((((clin.vaccination c_vacc
     JOIN clin.encounter c_enc ON ((c_enc.pk = c_vacc.fk_encounter)))
     JOIN clin.encounter_type c_ety ON ((c_enc.fk_type = c_ety.pk)))
     JOIN clin.episode c_epi ON ((c_vacc.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)))
     JOIN ref.vaccine r_vcine ON ((r_vcine.pk = c_vacc.fk_vaccine)))
     JOIN ref.drug_product r_dp ON ((r_vcine.fk_drug_product = r_dp.pk)));


ALTER TABLE clin.v_vaccinations_journal OWNER TO "gm-dbo";

--
-- Name: VIEW v_vaccinations_journal; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_vaccinations_journal IS 'Vaccination data denormalized for the EMR journal.';


--
-- Name: v_emr_journal; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_emr_journal AS
 SELECT v_pat_narrative_journal.pk_patient,
    v_pat_narrative_journal.modified_when,
    v_pat_narrative_journal.clin_when,
    v_pat_narrative_journal.modified_by,
    v_pat_narrative_journal.soap_cat,
    v_pat_narrative_journal.narrative,
    v_pat_narrative_journal.pk_encounter,
    v_pat_narrative_journal.pk_episode,
    v_pat_narrative_journal.pk_health_issue,
    v_pat_narrative_journal.src_pk,
    v_pat_narrative_journal.src_table,
    v_pat_narrative_journal.row_version,
    v_pat_narrative_journal.health_issue,
    v_pat_narrative_journal.issue_laterality,
    v_pat_narrative_journal.issue_active,
    v_pat_narrative_journal.issue_clinically_relevant,
    v_pat_narrative_journal.issue_confidential,
    v_pat_narrative_journal.episode,
    v_pat_narrative_journal.episode_open,
    v_pat_narrative_journal.encounter_started,
    v_pat_narrative_journal.encounter_last_affirmed,
    v_pat_narrative_journal.encounter_type,
    v_pat_narrative_journal.encounter_l10n_type
   FROM clin.v_pat_narrative_journal
UNION ALL
 SELECT v_health_issues_journal.pk_patient,
    v_health_issues_journal.modified_when,
    v_health_issues_journal.clin_when,
    v_health_issues_journal.modified_by,
    v_health_issues_journal.soap_cat,
    v_health_issues_journal.narrative,
    v_health_issues_journal.pk_encounter,
    v_health_issues_journal.pk_episode,
    v_health_issues_journal.pk_health_issue,
    v_health_issues_journal.src_pk,
    v_health_issues_journal.src_table,
    v_health_issues_journal.row_version,
    v_health_issues_journal.health_issue,
    v_health_issues_journal.issue_laterality,
    v_health_issues_journal.issue_active,
    v_health_issues_journal.issue_clinically_relevant,
    v_health_issues_journal.issue_confidential,
    v_health_issues_journal.episode,
    v_health_issues_journal.episode_open,
    v_health_issues_journal.encounter_started,
    v_health_issues_journal.encounter_last_affirmed,
    v_health_issues_journal.encounter_type,
    v_health_issues_journal.encounter_l10n_type
   FROM clin.v_health_issues_journal
UNION ALL
 SELECT v_pat_encounters_journal.pk_patient,
    v_pat_encounters_journal.modified_when,
    v_pat_encounters_journal.clin_when,
    v_pat_encounters_journal.modified_by,
    v_pat_encounters_journal.soap_cat,
    v_pat_encounters_journal.narrative,
    v_pat_encounters_journal.pk_encounter,
    v_pat_encounters_journal.pk_episode,
    v_pat_encounters_journal.pk_health_issue,
    v_pat_encounters_journal.src_pk,
    v_pat_encounters_journal.src_table,
    v_pat_encounters_journal.row_version,
    v_pat_encounters_journal.health_issue,
    v_pat_encounters_journal.issue_laterality,
    v_pat_encounters_journal.issue_active,
    v_pat_encounters_journal.issue_clinically_relevant,
    v_pat_encounters_journal.issue_confidential,
    v_pat_encounters_journal.episode,
    v_pat_encounters_journal.episode_open,
    v_pat_encounters_journal.encounter_started,
    v_pat_encounters_journal.encounter_last_affirmed,
    v_pat_encounters_journal.encounter_type,
    v_pat_encounters_journal.encounter_l10n_type
   FROM clin.v_pat_encounters_journal
UNION ALL
 SELECT v_pat_episodes_journal.pk_patient,
    v_pat_episodes_journal.modified_when,
    v_pat_episodes_journal.clin_when,
    v_pat_episodes_journal.modified_by,
    v_pat_episodes_journal.soap_cat,
    v_pat_episodes_journal.narrative,
    v_pat_episodes_journal.pk_encounter,
    v_pat_episodes_journal.pk_episode,
    v_pat_episodes_journal.pk_health_issue,
    v_pat_episodes_journal.src_pk,
    v_pat_episodes_journal.src_table,
    v_pat_episodes_journal.row_version,
    v_pat_episodes_journal.health_issue,
    v_pat_episodes_journal.issue_laterality,
    v_pat_episodes_journal.issue_active,
    v_pat_episodes_journal.issue_clinically_relevant,
    v_pat_episodes_journal.issue_confidential,
    v_pat_episodes_journal.episode,
    v_pat_episodes_journal.episode_open,
    v_pat_episodes_journal.encounter_started,
    v_pat_episodes_journal.encounter_last_affirmed,
    v_pat_episodes_journal.encounter_type,
    v_pat_episodes_journal.encounter_l10n_type
   FROM clin.v_pat_episodes_journal
UNION ALL
 SELECT v_family_history_journal.pk_patient,
    v_family_history_journal.modified_when,
    v_family_history_journal.clin_when,
    v_family_history_journal.modified_by,
    v_family_history_journal.soap_cat,
    v_family_history_journal.narrative,
    v_family_history_journal.pk_encounter,
    v_family_history_journal.pk_episode,
    v_family_history_journal.pk_health_issue,
    v_family_history_journal.src_pk,
    v_family_history_journal.src_table,
    v_family_history_journal.row_version,
    v_family_history_journal.health_issue,
    v_family_history_journal.issue_laterality,
    v_family_history_journal.issue_active,
    v_family_history_journal.issue_clinically_relevant,
    v_family_history_journal.issue_confidential,
    v_family_history_journal.episode,
    v_family_history_journal.episode_open,
    v_family_history_journal.encounter_started,
    v_family_history_journal.encounter_last_affirmed,
    v_family_history_journal.encounter_type,
    v_family_history_journal.encounter_l10n_type
   FROM clin.v_family_history_journal
UNION ALL
 SELECT v_pat_allergies_journal.pk_patient,
    v_pat_allergies_journal.modified_when,
    v_pat_allergies_journal.clin_when,
    v_pat_allergies_journal.modified_by,
    v_pat_allergies_journal.soap_cat,
    v_pat_allergies_journal.narrative,
    v_pat_allergies_journal.pk_encounter,
    v_pat_allergies_journal.pk_episode,
    v_pat_allergies_journal.pk_health_issue,
    v_pat_allergies_journal.src_pk,
    v_pat_allergies_journal.src_table,
    v_pat_allergies_journal.row_version,
    v_pat_allergies_journal.health_issue,
    v_pat_allergies_journal.issue_laterality,
    v_pat_allergies_journal.issue_active,
    v_pat_allergies_journal.issue_clinically_relevant,
    v_pat_allergies_journal.issue_confidential,
    v_pat_allergies_journal.episode,
    v_pat_allergies_journal.episode_open,
    v_pat_allergies_journal.encounter_started,
    v_pat_allergies_journal.encounter_last_affirmed,
    v_pat_allergies_journal.encounter_type,
    v_pat_allergies_journal.encounter_l10n_type
   FROM clin.v_pat_allergies_journal
UNION ALL
 SELECT v_pat_allergy_state_journal.pk_patient,
    v_pat_allergy_state_journal.modified_when,
    v_pat_allergy_state_journal.clin_when,
    v_pat_allergy_state_journal.modified_by,
    v_pat_allergy_state_journal.soap_cat,
    v_pat_allergy_state_journal.narrative,
    v_pat_allergy_state_journal.pk_encounter,
    v_pat_allergy_state_journal.pk_episode,
    v_pat_allergy_state_journal.pk_health_issue,
    v_pat_allergy_state_journal.src_pk,
    v_pat_allergy_state_journal.src_table,
    v_pat_allergy_state_journal.row_version,
    v_pat_allergy_state_journal.health_issue,
    v_pat_allergy_state_journal.issue_laterality,
    v_pat_allergy_state_journal.issue_active,
    v_pat_allergy_state_journal.issue_clinically_relevant,
    v_pat_allergy_state_journal.issue_confidential,
    v_pat_allergy_state_journal.episode,
    v_pat_allergy_state_journal.episode_open,
    v_pat_allergy_state_journal.encounter_started,
    v_pat_allergy_state_journal.encounter_last_affirmed,
    v_pat_allergy_state_journal.encounter_type,
    v_pat_allergy_state_journal.encounter_l10n_type
   FROM clin.v_pat_allergy_state_journal
UNION ALL
 SELECT v_test_results_journal.pk_patient,
    v_test_results_journal.modified_when,
    v_test_results_journal.clin_when,
    v_test_results_journal.modified_by,
    v_test_results_journal.soap_cat,
    v_test_results_journal.narrative,
    v_test_results_journal.pk_encounter,
    v_test_results_journal.pk_episode,
    v_test_results_journal.pk_health_issue,
    v_test_results_journal.src_pk,
    v_test_results_journal.src_table,
    v_test_results_journal.row_version,
    v_test_results_journal.health_issue,
    v_test_results_journal.issue_laterality,
    v_test_results_journal.issue_active,
    v_test_results_journal.issue_clinically_relevant,
    v_test_results_journal.issue_confidential,
    v_test_results_journal.episode,
    v_test_results_journal.episode_open,
    v_test_results_journal.encounter_started,
    v_test_results_journal.encounter_last_affirmed,
    v_test_results_journal.encounter_type,
    v_test_results_journal.encounter_l10n_type
   FROM clin.v_test_results_journal
UNION ALL
 SELECT v_hospital_stays_journal.pk_patient,
    v_hospital_stays_journal.modified_when,
    v_hospital_stays_journal.clin_when,
    v_hospital_stays_journal.modified_by,
    v_hospital_stays_journal.soap_cat,
    v_hospital_stays_journal.narrative,
    v_hospital_stays_journal.pk_encounter,
    v_hospital_stays_journal.pk_episode,
    v_hospital_stays_journal.pk_health_issue,
    v_hospital_stays_journal.src_pk,
    v_hospital_stays_journal.src_table,
    v_hospital_stays_journal.row_version,
    v_hospital_stays_journal.health_issue,
    v_hospital_stays_journal.issue_laterality,
    v_hospital_stays_journal.issue_active,
    v_hospital_stays_journal.issue_clinically_relevant,
    v_hospital_stays_journal.issue_confidential,
    v_hospital_stays_journal.episode,
    v_hospital_stays_journal.episode_open,
    v_hospital_stays_journal.encounter_started,
    v_hospital_stays_journal.encounter_last_affirmed,
    v_hospital_stays_journal.encounter_type,
    v_hospital_stays_journal.encounter_l10n_type
   FROM clin.v_hospital_stays_journal
UNION ALL
 SELECT v_doc_med_journal.pk_patient,
    v_doc_med_journal.modified_when,
    v_doc_med_journal.clin_when,
    v_doc_med_journal.modified_by,
    v_doc_med_journal.soap_cat,
    v_doc_med_journal.narrative,
    v_doc_med_journal.pk_encounter,
    v_doc_med_journal.pk_episode,
    v_doc_med_journal.pk_health_issue,
    v_doc_med_journal.src_pk,
    v_doc_med_journal.src_table,
    v_doc_med_journal.row_version,
    v_doc_med_journal.health_issue,
    v_doc_med_journal.issue_laterality,
    v_doc_med_journal.issue_active,
    v_doc_med_journal.issue_clinically_relevant,
    v_doc_med_journal.issue_confidential,
    v_doc_med_journal.episode,
    v_doc_med_journal.episode_open,
    v_doc_med_journal.encounter_started,
    v_doc_med_journal.encounter_last_affirmed,
    v_doc_med_journal.encounter_type,
    v_doc_med_journal.encounter_l10n_type
   FROM blobs.v_doc_med_journal
UNION ALL
 SELECT v_substance_intake_journal.pk_patient,
    v_substance_intake_journal.modified_when,
    v_substance_intake_journal.clin_when,
    v_substance_intake_journal.modified_by,
    v_substance_intake_journal.soap_cat,
    v_substance_intake_journal.narrative,
    v_substance_intake_journal.pk_encounter,
    v_substance_intake_journal.pk_episode,
    v_substance_intake_journal.pk_health_issue,
    v_substance_intake_journal.src_pk,
    v_substance_intake_journal.src_table,
    v_substance_intake_journal.row_version,
    v_substance_intake_journal.health_issue,
    v_substance_intake_journal.issue_laterality,
    v_substance_intake_journal.issue_active,
    v_substance_intake_journal.issue_clinically_relevant,
    v_substance_intake_journal.issue_confidential,
    v_substance_intake_journal.episode,
    v_substance_intake_journal.episode_open,
    v_substance_intake_journal.encounter_started,
    v_substance_intake_journal.encounter_last_affirmed,
    v_substance_intake_journal.encounter_type,
    v_substance_intake_journal.encounter_l10n_type
   FROM clin.v_substance_intake_journal
UNION ALL
 SELECT v_procedures_journal.pk_patient,
    v_procedures_journal.modified_when,
    v_procedures_journal.clin_when,
    v_procedures_journal.modified_by,
    v_procedures_journal.soap_cat,
    v_procedures_journal.narrative,
    v_procedures_journal.pk_encounter,
    v_procedures_journal.pk_episode,
    v_procedures_journal.pk_health_issue,
    v_procedures_journal.src_pk,
    v_procedures_journal.src_table,
    v_procedures_journal.row_version,
    v_procedures_journal.health_issue,
    v_procedures_journal.issue_laterality,
    v_procedures_journal.issue_active,
    v_procedures_journal.issue_clinically_relevant,
    v_procedures_journal.issue_confidential,
    v_procedures_journal.episode,
    v_procedures_journal.episode_open,
    v_procedures_journal.encounter_started,
    v_procedures_journal.encounter_last_affirmed,
    v_procedures_journal.encounter_type,
    v_procedures_journal.encounter_l10n_type
   FROM clin.v_procedures_journal
UNION ALL
 SELECT v_vaccinations_journal.pk_patient,
    v_vaccinations_journal.modified_when,
    v_vaccinations_journal.clin_when,
    v_vaccinations_journal.modified_by,
    v_vaccinations_journal.soap_cat,
    v_vaccinations_journal.narrative,
    v_vaccinations_journal.pk_encounter,
    v_vaccinations_journal.pk_episode,
    v_vaccinations_journal.pk_health_issue,
    v_vaccinations_journal.src_pk,
    v_vaccinations_journal.src_table,
    v_vaccinations_journal.row_version,
    v_vaccinations_journal.health_issue,
    v_vaccinations_journal.issue_laterality,
    v_vaccinations_journal.issue_active,
    v_vaccinations_journal.issue_clinically_relevant,
    v_vaccinations_journal.issue_confidential,
    v_vaccinations_journal.episode,
    v_vaccinations_journal.episode_open,
    v_vaccinations_journal.encounter_started,
    v_vaccinations_journal.encounter_last_affirmed,
    v_vaccinations_journal.encounter_type,
    v_vaccinations_journal.encounter_l10n_type
   FROM clin.v_vaccinations_journal
UNION ALL
 SELECT v_suppressed_hints_journal.pk_patient,
    v_suppressed_hints_journal.modified_when,
    v_suppressed_hints_journal.clin_when,
    v_suppressed_hints_journal.modified_by,
    v_suppressed_hints_journal.soap_cat,
    v_suppressed_hints_journal.narrative,
    v_suppressed_hints_journal.fk_encounter AS pk_encounter,
    v_suppressed_hints_journal.pk_episode,
    v_suppressed_hints_journal.pk_health_issue,
    v_suppressed_hints_journal.src_pk,
    v_suppressed_hints_journal.src_table,
    v_suppressed_hints_journal.row_version,
    v_suppressed_hints_journal.health_issue,
    v_suppressed_hints_journal.issue_laterality,
    v_suppressed_hints_journal.issue_active,
    v_suppressed_hints_journal.issue_clinically_relevant,
    v_suppressed_hints_journal.issue_confidential,
    v_suppressed_hints_journal.episode,
    v_suppressed_hints_journal.episode_open,
    v_suppressed_hints_journal.encounter_started,
    v_suppressed_hints_journal.encounter_last_affirmed,
    v_suppressed_hints_journal.encounter_type,
    v_suppressed_hints_journal.encounter_l10n_type
   FROM clin.v_suppressed_hints_journal
UNION ALL
 SELECT v_external_care_journal.pk_patient,
    v_external_care_journal.modified_when,
    v_external_care_journal.clin_when,
    v_external_care_journal.modified_by,
    v_external_care_journal.soap_cat,
    v_external_care_journal.narrative,
    v_external_care_journal.pk_encounter,
    v_external_care_journal.pk_episode,
    v_external_care_journal.pk_health_issue,
    v_external_care_journal.src_pk,
    v_external_care_journal.src_table,
    v_external_care_journal.row_version,
    v_external_care_journal.health_issue,
    v_external_care_journal.issue_laterality,
    v_external_care_journal.issue_active,
    v_external_care_journal.issue_clinically_relevant,
    v_external_care_journal.issue_confidential,
    v_external_care_journal.episode,
    v_external_care_journal.episode_open,
    v_external_care_journal.encounter_started,
    v_external_care_journal.encounter_last_affirmed,
    v_external_care_journal.encounter_type,
    v_external_care_journal.encounter_l10n_type
   FROM clin.v_external_care_journal
UNION ALL
 SELECT v_edc_journal.pk_patient,
    v_edc_journal.modified_when,
    v_edc_journal.clin_when,
    v_edc_journal.modified_by,
    v_edc_journal.soap_cat,
    v_edc_journal.narrative,
    v_edc_journal.pk_encounter,
    v_edc_journal.pk_episode,
    v_edc_journal.pk_health_issue,
    v_edc_journal.src_pk,
    v_edc_journal.src_table,
    v_edc_journal.row_version,
    v_edc_journal.health_issue,
    v_edc_journal.issue_laterality,
    v_edc_journal.issue_active,
    v_edc_journal.issue_clinically_relevant,
    v_edc_journal.issue_confidential,
    v_edc_journal.episode,
    v_edc_journal.episode_open,
    v_edc_journal.encounter_started,
    v_edc_journal.encounter_last_affirmed,
    v_edc_journal.encounter_type,
    v_edc_journal.encounter_l10n_type
   FROM clin.v_edc_journal
UNION ALL
 SELECT v_reminders_journal.pk_patient,
    v_reminders_journal.modified_when,
    v_reminders_journal.clin_when,
    v_reminders_journal.modified_by,
    v_reminders_journal.soap_cat,
    v_reminders_journal.narrative,
    v_reminders_journal.pk_encounter,
    v_reminders_journal.pk_episode,
    v_reminders_journal.pk_health_issue,
    v_reminders_journal.src_pk,
    v_reminders_journal.src_table,
    v_reminders_journal.row_version,
    v_reminders_journal.health_issue,
    v_reminders_journal.issue_laterality,
    v_reminders_journal.issue_active,
    v_reminders_journal.issue_clinically_relevant,
    v_reminders_journal.issue_confidential,
    v_reminders_journal.episode,
    v_reminders_journal.episode_open,
    v_reminders_journal.encounter_started,
    v_reminders_journal.encounter_last_affirmed,
    v_reminders_journal.encounter_type,
    v_reminders_journal.encounter_l10n_type
   FROM clin.v_reminders_journal;


ALTER TABLE clin.v_emr_journal OWNER TO "gm-dbo";

--
-- Name: VIEW v_emr_journal; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_emr_journal IS 'Clinical patient data formatted into one string per
	 clinical entity even if it constains several user-
	 visible fields. Mainly useful for display as a simple
	 EMR journal.';


--
-- Name: _view_emr_journal_without_suppressed_hints; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin._view_emr_journal_without_suppressed_hints AS
 SELECT v_emr_journal.pk_patient,
    v_emr_journal.modified_when,
    v_emr_journal.clin_when,
    v_emr_journal.modified_by,
    v_emr_journal.soap_cat,
    v_emr_journal.narrative,
    v_emr_journal.pk_encounter,
    v_emr_journal.pk_episode,
    v_emr_journal.pk_health_issue,
    v_emr_journal.src_pk,
    v_emr_journal.src_table,
    v_emr_journal.row_version,
    v_emr_journal.health_issue,
    v_emr_journal.issue_laterality,
    v_emr_journal.issue_active,
    v_emr_journal.issue_clinically_relevant,
    v_emr_journal.issue_confidential,
    v_emr_journal.episode,
    v_emr_journal.episode_open,
    v_emr_journal.encounter_started,
    v_emr_journal.encounter_last_affirmed,
    v_emr_journal.encounter_type,
    v_emr_journal.encounter_l10n_type
   FROM clin.v_emr_journal
  WHERE (v_emr_journal.src_table <> 'clin.suppressed_hint'::text);


ALTER TABLE clin._view_emr_journal_without_suppressed_hints OWNER TO "gm-dbo";

--
-- Name: allergy_id_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.allergy_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.allergy_id_seq OWNER TO "gm-dbo";

--
-- Name: allergy_id_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.allergy_id_seq OWNED BY clin.allergy.pk;


--
-- Name: allergy_state_id_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.allergy_state_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.allergy_state_id_seq OWNER TO "gm-dbo";

--
-- Name: allergy_state_id_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.allergy_state_id_seq OWNED BY clin.allergy_state.pk;


--
-- Name: clin_aux_note; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.clin_aux_note (
    pk integer NOT NULL
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.clin_aux_note OWNER TO "gm-dbo";

--
-- Name: TABLE clin_aux_note; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.clin_aux_note IS 'Other tables link to this if they need more free text fields.';


--
-- Name: clin_aux_note_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.clin_aux_note_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.clin_aux_note_pk_seq OWNER TO "gm-dbo";

--
-- Name: clin_aux_note_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.clin_aux_note_pk_seq OWNED BY clin.clin_aux_note.pk;


--
-- Name: clin_diag; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.clin_diag (
    pk integer NOT NULL,
    fk_narrative integer NOT NULL,
    laterality character(1) DEFAULT NULL::bpchar,
    is_chronic boolean DEFAULT false NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    is_definite boolean DEFAULT false NOT NULL,
    clinically_relevant boolean DEFAULT true NOT NULL,
    CONSTRAINT clin_diag_laterality_check CHECK (((laterality = ANY (ARRAY['l'::bpchar, 'r'::bpchar, 'b'::bpchar, '?'::bpchar])) OR (laterality IS NULL))),
    CONSTRAINT if_active_then_relevant CHECK (((is_active = false) OR ((is_active = true) AND (clinically_relevant = true))))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.clin_diag OWNER TO "gm-dbo";

--
-- Name: TABLE clin_diag; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.clin_diag IS 'stores additional detail on those clin.clin_narrative
	 rows where soap_cat=a that are true diagnoses,
	 true diagnoses DO have a code from one of the coding systems';


--
-- Name: COLUMN clin_diag.is_chronic; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_diag.is_chronic IS 'whether this diagnosis is chronic, eg. no complete
	 cure is to be expected, regardless of whether it is
	 *active* right now (think of active/non-active phases
	 of Multiple Sclerosis which is sure chronic)';


--
-- Name: COLUMN clin_diag.is_active; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_diag.is_active IS 'whether diagnosis is currently active or dormant';


--
-- Name: COLUMN clin_diag.clinically_relevant; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_diag.clinically_relevant IS 'whether this diagnosis is considered clinically
	 relevant, eg. significant;
	 currently active diagnoses are considered to
	 always be relevant, while inactive ones may
	 or may not be';


--
-- Name: clin_diag_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.clin_diag_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.clin_diag_pk_seq OWNER TO "gm-dbo";

--
-- Name: clin_diag_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.clin_diag_pk_seq OWNED BY clin.clin_diag.pk;


--
-- Name: clin_hx_family_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.clin_hx_family_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.clin_hx_family_pk_seq OWNER TO "gm-dbo";

--
-- Name: clin_hx_family_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.clin_hx_family_pk_seq OWNED BY clin.family_history.pk;


--
-- Name: clin_item_type; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.clin_item_type (
    pk integer NOT NULL,
    type text DEFAULT 'history'::text NOT NULL,
    code text DEFAULT 'Hx'::text NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.clin_item_type OWNER TO "gm-dbo";

--
-- Name: TABLE clin_item_type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.clin_item_type IS 'stores arbitrary types for tagging clinical items';


--
-- Name: COLUMN clin_item_type.type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_item_type.type IS 'the full name of the item type such as "family history"';


--
-- Name: COLUMN clin_item_type.code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_item_type.code IS 'shorthand for the type, eg "FHx"';


--
-- Name: clin_item_type_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.clin_item_type_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.clin_item_type_pk_seq OWNER TO "gm-dbo";

--
-- Name: clin_item_type_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.clin_item_type_pk_seq OWNED BY clin.clin_item_type.pk;


--
-- Name: clin_narrative_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.clin_narrative_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.clin_narrative_pk_seq OWNER TO "gm-dbo";

--
-- Name: clin_narrative_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.clin_narrative_pk_seq OWNED BY clin.clin_narrative.pk;


--
-- Name: clin_root_item_pk_item_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.clin_root_item_pk_item_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.clin_root_item_pk_item_seq OWNER TO "gm-dbo";

--
-- Name: clin_root_item_pk_item_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.clin_root_item_pk_item_seq OWNED BY clin.clin_root_item.pk_item;


--
-- Name: encounter_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.encounter_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.encounter_pk_seq OWNER TO "gm-dbo";

--
-- Name: encounter_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.encounter_pk_seq OWNED BY clin.encounter.pk;


--
-- Name: encounter_type_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.encounter_type_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.encounter_type_pk_seq OWNER TO "gm-dbo";

--
-- Name: encounter_type_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.encounter_type_pk_seq OWNED BY clin.encounter_type.pk;


--
-- Name: episode_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.episode_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.episode_pk_seq OWNER TO "gm-dbo";

--
-- Name: episode_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.episode_pk_seq OWNED BY clin.episode.pk;


--
-- Name: export_item; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.export_item (
    pk integer NOT NULL,
    fk_identity integer NOT NULL,
    created_by name DEFAULT "current_user"() NOT NULL,
    created_when timestamp with time zone DEFAULT statement_timestamp() NOT NULL,
    designation text,
    description text,
    fk_doc_obj integer,
    data bytea,
    filename text,
    CONSTRAINT clin_export_item_fk_obj_or_data CHECK ((((data IS NULL) AND (fk_doc_obj IS NOT NULL)) OR ((data IS NOT NULL) AND (fk_doc_obj IS NULL)))),
    CONSTRAINT clin_export_item_fk_obj_or_filename CHECK ((((filename IS NULL) AND (fk_doc_obj IS NOT NULL)) OR ((filename IS NOT NULL) AND (fk_doc_obj IS NULL)))),
    CONSTRAINT clin_export_item_sane_by CHECK ((length((created_by)::text) > 0)),
    CONSTRAINT clin_export_item_sane_data CHECK (((data IS NULL) OR (length(data) > 0))),
    CONSTRAINT clin_export_item_sane_description CHECK ((gm.is_null_or_blank_string(description) IS FALSE)),
    CONSTRAINT clin_export_item_sane_designation CHECK ((gm.is_null_or_non_empty_string(designation) IS TRUE)),
    CONSTRAINT clin_export_item_sane_filename CHECK ((gm.is_null_or_non_empty_string(filename) IS TRUE))
);


ALTER TABLE clin.export_item OWNER TO "gm-dbo";

--
-- Name: TABLE export_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.export_item IS 'A table to hold binary data for patients intended for export as print/mail/fax/removable media/...';


--
-- Name: COLUMN export_item.fk_identity; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.export_item.fk_identity IS 'the patient this item pertains to, DELETE does not cascade because we may have wanted to export data before deleting a patient ...';


--
-- Name: COLUMN export_item.created_by; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.export_item.created_by IS 'who put this item here';


--
-- Name: COLUMN export_item.created_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.export_item.created_when IS 'when was this item put here';


--
-- Name: COLUMN export_item.designation; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.export_item.designation IS 'the intended use for this item if any, say "print" for printing';


--
-- Name: COLUMN export_item.description; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.export_item.description IS 'a unique-per-patient description of the item';


--
-- Name: COLUMN export_item.fk_doc_obj; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.export_item.fk_doc_obj IS 'points to a document object';


--
-- Name: COLUMN export_item.data; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.export_item.data IS 'binary data representing the actual export item (unless fk_doc_obj points to a document object)';


--
-- Name: COLUMN export_item.filename; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.export_item.filename IS 'a filename, possibly from an import, if applicable, mainly used to please non-mime pseudo operating systems';


--
-- Name: export_item_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.export_item_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.export_item_pk_seq OWNER TO "gm-dbo";

--
-- Name: export_item_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.export_item_pk_seq OWNED BY clin.export_item.pk;


--
-- Name: external_care_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.external_care_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.external_care_pk_seq OWNER TO "gm-dbo";

--
-- Name: external_care_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.external_care_pk_seq OWNED BY clin.external_care.pk;


--
-- Name: fhx_relation_type_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.fhx_relation_type_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.fhx_relation_type_pk_seq OWNER TO "gm-dbo";

--
-- Name: fhx_relation_type_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.fhx_relation_type_pk_seq OWNED BY clin.fhx_relation_type.pk;


--
-- Name: form_data; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.form_data (
    pk integer NOT NULL,
    fk_instance integer NOT NULL,
    fk_form_field integer NOT NULL,
    value text NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.form_data OWNER TO "gm-dbo";

--
-- Name: TABLE form_data; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.form_data IS 'holds the values used in form instances, for
	 later re-use/validation';


--
-- Name: COLUMN form_data.fk_instance; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.form_data.fk_instance IS 'the form instance this value was used in';


--
-- Name: COLUMN form_data.fk_form_field; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.form_data.fk_form_field IS 'points to the definition of the field in the form
	 which in turn defines the place holder in the
	 template to replace with <value>';


--
-- Name: COLUMN form_data.value; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.form_data.value IS 'the value to replace the place holder with';


--
-- Name: form_data_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.form_data_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.form_data_pk_seq OWNER TO "gm-dbo";

--
-- Name: form_data_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.form_data_pk_seq OWNED BY clin.form_data.pk;


--
-- Name: form_instances; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.form_instances (
    pk integer NOT NULL,
    fk_form_def integer NOT NULL,
    form_name text NOT NULL,
    CONSTRAINT form_is_plan CHECK ((soap_cat = 'p'::text))
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.form_instances OWNER TO "gm-dbo";

--
-- Name: TABLE form_instances; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.form_instances IS 'instances of forms, like a log of all processed forms';


--
-- Name: COLUMN form_instances.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.form_instances.narrative IS 'can be used as a status field, eg. "printed", "faxed" etc.';


--
-- Name: COLUMN form_instances.fk_form_def; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.form_instances.fk_form_def IS 'points to the definition of this instance,
	 this FK will fail once we start separating services,
	 make it into a x_db_fk then';


--
-- Name: COLUMN form_instances.form_name; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.form_instances.form_name IS 'a string uniquely identifying the form template,
	 necessary for the audit trail';


--
-- Name: form_instances_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.form_instances_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.form_instances_pk_seq OWNER TO "gm-dbo";

--
-- Name: form_instances_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.form_instances_pk_seq OWNED BY clin.form_instances.pk;


--
-- Name: health_issue_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.health_issue_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.health_issue_pk_seq OWNER TO "gm-dbo";

--
-- Name: health_issue_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.health_issue_pk_seq OWNED BY clin.health_issue.pk;


--
-- Name: hospital_stay_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.hospital_stay_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.hospital_stay_pk_seq OWNER TO "gm-dbo";

--
-- Name: hospital_stay_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.hospital_stay_pk_seq OWNED BY clin.hospital_stay.pk;


--
-- Name: incoming_data_unmatchable; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.incoming_data_unmatchable (
    pk integer NOT NULL,
    fk_patient_candidates integer[],
    request_id text,
    firstnames text,
    lastnames text,
    dob date,
    postcode text,
    other_info text,
    type text,
    data bytea NOT NULL,
    gender text,
    requestor text,
    external_data_id text,
    fk_identity_disambiguated integer
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.incoming_data_unmatchable OWNER TO "gm-dbo";

--
-- Name: TABLE incoming_data_unmatchable; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.incoming_data_unmatchable IS 'this table holds test results that could not be matched
	 to any patient, it is intended to prevent overflow of
	 incoming_data_unmatched with unmatchable data';


--
-- Name: incoming_data_unmatchable_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.incoming_data_unmatchable_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.incoming_data_unmatchable_pk_seq OWNER TO "gm-dbo";

--
-- Name: incoming_data_unmatchable_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.incoming_data_unmatchable_pk_seq OWNED BY clin.incoming_data_unmatchable.pk;


--
-- Name: incoming_data_unmatched; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.incoming_data_unmatched (
    pk integer NOT NULL,
    fk_patient_candidates integer[],
    request_id text,
    firstnames text,
    lastnames text,
    dob date,
    postcode text,
    other_info text,
    type text,
    data bytea NOT NULL,
    gender text,
    requestor text,
    external_data_id text,
    fk_identity_disambiguated integer,
    comment text,
    fk_provider_disambiguated integer,
    CONSTRAINT unmatched_data_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.incoming_data_unmatched OWNER TO "gm-dbo";

--
-- Name: TABLE incoming_data_unmatched; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.incoming_data_unmatched IS 'this table holds incoming data (lab results, documents)
	 that could not be matched to one single patient automatically,
	 it is intended to facilitate manual matching,
	 - use "modified_when" for import time';


--
-- Name: COLUMN incoming_data_unmatched.fk_patient_candidates; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.fk_patient_candidates IS 'a matching algorithm can be applied to produce
	 a list of likely candidate patients, the question
	 remains whether this should not be done at runtime';


--
-- Name: COLUMN incoming_data_unmatched.request_id; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.request_id IS 'request ID as found in <data>';


--
-- Name: COLUMN incoming_data_unmatched.firstnames; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.firstnames IS 'first names as found in <data>';


--
-- Name: COLUMN incoming_data_unmatched.lastnames; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.lastnames IS 'last names as found in <data>';


--
-- Name: COLUMN incoming_data_unmatched.dob; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.dob IS 'date of birth as found in <data>';


--
-- Name: COLUMN incoming_data_unmatched.postcode; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.postcode IS 'postcode as found in <data>';


--
-- Name: COLUMN incoming_data_unmatched.other_info; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.other_info IS 'other identifying information as found in <data>';


--
-- Name: COLUMN incoming_data_unmatched.type; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.type IS 'the type of <data>, eg HL7, LDT, ...,
	 useful for selecting an importer';


--
-- Name: COLUMN incoming_data_unmatched.data; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.data IS 'the raw data';


--
-- Name: COLUMN incoming_data_unmatched.gender; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.gender IS 'Gender of patient in source data if available.';


--
-- Name: COLUMN incoming_data_unmatched.requestor; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.requestor IS 'Requestor of data (e.g. who ordered test results) if available in source data.';


--
-- Name: COLUMN incoming_data_unmatched.external_data_id; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.external_data_id IS 'ID of content of .data in external system (e.g. importer) where appropriate';


--
-- Name: COLUMN incoming_data_unmatched.fk_identity_disambiguated; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.fk_identity_disambiguated IS 'ID of patient the data is judged to really belong to.';


--
-- Name: COLUMN incoming_data_unmatched.comment; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.comment IS 'a free text comment on this row, eg. why is it here';


--
-- Name: COLUMN incoming_data_unmatched.fk_provider_disambiguated; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.incoming_data_unmatched.fk_provider_disambiguated IS 'The provider the data is relevant to.';


--
-- Name: incoming_data_unmatched_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.incoming_data_unmatched_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.incoming_data_unmatched_pk_seq OWNER TO "gm-dbo";

--
-- Name: incoming_data_unmatched_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.incoming_data_unmatched_pk_seq OWNED BY clin.incoming_data_unmatched.pk;


--
-- Name: lab_request; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lab_request (
    pk integer NOT NULL,
    fk_test_org integer,
    request_id text NOT NULL,
    fk_requestor integer,
    lab_request_id text,
    lab_rxd_when timestamp with time zone,
    results_reported_when timestamp with time zone,
    request_status text NOT NULL,
    is_pending boolean DEFAULT true NOT NULL,
    diagnostic_service_section text,
    ordered_service text,
    note_test_org text,
    CONSTRAINT clin_lab_req_sane_test_org_note CHECK ((gm.is_null_or_non_empty_string(note_test_org) IS TRUE)),
    CONSTRAINT lab_request_request_id_check CHECK ((btrim(request_id) <> ''::text)),
    CONSTRAINT lab_request_request_status_check CHECK ((request_status = ANY (ARRAY['pending'::text, 'preliminary'::text, 'partial'::text, 'final'::text])))
)
INHERITS (clin.clin_root_item);


ALTER TABLE clin.lab_request OWNER TO "gm-dbo";

--
-- Name: TABLE lab_request; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lab_request IS 'test request metadata';


--
-- Name: COLUMN lab_request.clin_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.clin_when IS 'the time the sample for this request was taken
	 LDT: 8432:8433
	 HL7: OBR.quantity_timing';


--
-- Name: COLUMN lab_request.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.narrative IS 'free text comment on request';


--
-- Name: COLUMN lab_request.request_id; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.request_id IS 'ID this request had when sent to the lab
	 LDT: 8310
	 HL7: OBR.filler_order_number';


--
-- Name: COLUMN lab_request.fk_requestor; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.fk_requestor IS 'who requested the test - really needed ?';


--
-- Name: COLUMN lab_request.lab_request_id; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.lab_request_id IS 'ID this request had internally at the lab
	 LDT: 8311';


--
-- Name: COLUMN lab_request.lab_rxd_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.lab_rxd_when IS 'when did the lab receive the request+sample
	 LDT: 8301
	 HL7: OBR.requested_date_time';


--
-- Name: COLUMN lab_request.results_reported_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.results_reported_when IS 'when was the report on the result generated,
	LDT: 8302
	HL7: OBR.results_report_status_change';


--
-- Name: COLUMN lab_request.request_status; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.request_status IS 'pending, final, preliminary, partial
	 LDT: 8401';


--
-- Name: COLUMN lab_request.is_pending; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.is_pending IS 'true if any (even partial) results are still pending';


--
-- Name: COLUMN lab_request.diagnostic_service_section; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.diagnostic_service_section IS 'The (section of) the diagnostic service which performed the test.
- HL7 2.3: OBR:24 Diagnostic Service Section ID
- somewhat redundant with fk_test_org, which, however,
  points to more normalized data';


--
-- Name: COLUMN lab_request.ordered_service; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.ordered_service IS 'The (battery of) test(s)/service(s) ordered.
- HL7 2.3: OBR:4 Universal Service ID';


--
-- Name: COLUMN lab_request.note_test_org; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lab_request.note_test_org IS 'A comment on this lab request by the performing organization (lab).';


--
-- Name: lab_request_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lab_request_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lab_request_pk_seq OWNER TO "gm-dbo";

--
-- Name: lab_request_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lab_request_pk_seq OWNED BY clin.lab_request.pk;


--
-- Name: lnk_code2aoe_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_code2aoe_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_code2aoe_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_code2aoe_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_code2aoe_pk_seq OWNED BY clin.lnk_code2aoe.pk;


--
-- Name: lnk_code2episode_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_code2episode_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_code2episode_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_code2episode_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_code2episode_pk_seq OWNED BY clin.lnk_code2episode.pk;


--
-- Name: lnk_code2fhx_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_code2fhx_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_code2fhx_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_code2fhx_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_code2fhx_pk_seq OWNED BY clin.lnk_code2fhx.pk;


--
-- Name: lnk_code2h_issue_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_code2h_issue_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_code2h_issue_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_code2h_issue_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_code2h_issue_pk_seq OWNED BY clin.lnk_code2h_issue.pk;


--
-- Name: lnk_code2item_root_pk_lnk_code2item_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_code2item_root_pk_lnk_code2item_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_code2item_root_pk_lnk_code2item_seq OWNER TO "gm-dbo";

--
-- Name: lnk_code2item_root_pk_lnk_code2item_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_code2item_root_pk_lnk_code2item_seq OWNED BY clin.lnk_code2item_root.pk_lnk_code2item;


--
-- Name: lnk_code2narrative; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_code2narrative (
    pk integer NOT NULL
)
INHERITS (clin.lnk_code2item_root);
ALTER TABLE ONLY clin.lnk_code2narrative ALTER COLUMN fk_generic_code SET NOT NULL;


ALTER TABLE clin.lnk_code2narrative OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_code2narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_code2narrative IS 'Links codes to SOAP narrative.';


--
-- Name: COLUMN lnk_code2narrative.fk_generic_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2narrative.fk_generic_code IS 'Custom foreign key to ref.coding_system_root.';


--
-- Name: COLUMN lnk_code2narrative.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2narrative.fk_item IS 'Foreign key to clin.clin_narrative';


--
-- Name: lnk_code2narrative_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_code2narrative_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_code2narrative_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_code2narrative_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_code2narrative_pk_seq OWNED BY clin.lnk_code2narrative.pk;


--
-- Name: lnk_code2procedure_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_code2procedure_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_code2procedure_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_code2procedure_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_code2procedure_pk_seq OWNED BY clin.lnk_code2procedure.pk;


--
-- Name: lnk_code2rfe_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_code2rfe_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_code2rfe_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_code2rfe_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_code2rfe_pk_seq OWNED BY clin.lnk_code2rfe.pk;


--
-- Name: lnk_code2tst_pnl; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_code2tst_pnl (
    pk integer NOT NULL
)
INHERITS (clin.lnk_code2item_root);
ALTER TABLE ONLY clin.lnk_code2tst_pnl ALTER COLUMN fk_generic_code SET NOT NULL;


ALTER TABLE clin.lnk_code2tst_pnl OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_code2tst_pnl; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_code2tst_pnl IS 'Links codes to test panels.';


--
-- Name: COLUMN lnk_code2tst_pnl.fk_generic_code; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2tst_pnl.fk_generic_code IS 'Custom foreign key to ref.coding_system_root.';


--
-- Name: COLUMN lnk_code2tst_pnl.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_code2tst_pnl.fk_item IS 'Foreign key to clin.test_panel';


--
-- Name: lnk_code2tst_pnl_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_code2tst_pnl_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_code2tst_pnl_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_code2tst_pnl_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_code2tst_pnl_pk_seq OWNED BY clin.lnk_code2tst_pnl.pk;


--
-- Name: lnk_constraint2vacc_course; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_constraint2vacc_course (
    pk integer NOT NULL,
    fk_vaccination_course integer NOT NULL,
    fk_constraint integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.lnk_constraint2vacc_course OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_constraint2vacc_course; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_constraint2vacc_course IS 'links constraints to courses';


--
-- Name: lnk_constraint2vacc_course_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_constraint2vacc_course_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_constraint2vacc_course_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_constraint2vacc_course_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_constraint2vacc_course_pk_seq OWNED BY clin.lnk_constraint2vacc_course.pk;


--
-- Name: lnk_loinc2test_panel_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_loinc2test_panel_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_loinc2test_panel_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_loinc2test_panel_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_loinc2test_panel_pk_seq OWNED BY clin.lnk_loinc2test_panel.pk;


--
-- Name: lnk_pat2vaccination_course; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_pat2vaccination_course (
    pk integer NOT NULL,
    fk_patient integer NOT NULL,
    fk_course integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.lnk_pat2vaccination_course OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_pat2vaccination_course; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_pat2vaccination_course IS 'links patients to vaccination courses they are actually on,
	 this allows for per-patient selection of courses to be
	 followed, eg. children at different ages may be on different
	 vaccination courses or some people are on a course due
	 to a trip abroad while most others are not';


--
-- Name: lnk_pat2vaccination_course_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_pat2vaccination_course_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_pat2vaccination_course_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_pat2vaccination_course_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_pat2vaccination_course_pk_seq OWNED BY clin.lnk_pat2vaccination_course.pk;


--
-- Name: lnk_substance2episode; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_substance2episode (
    pk integer NOT NULL,
    fk_episode integer,
    fk_substance integer
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.lnk_substance2episode OWNER TO "gm-dbo";

--
-- Name: lnk_substance2episode_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_substance2episode_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_substance2episode_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_substance2episode_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_substance2episode_pk_seq OWNED BY clin.lnk_substance2episode.pk;


--
-- Name: lnk_tst2norm; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_tst2norm (
    id integer NOT NULL,
    id_test integer NOT NULL,
    id_norm integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.lnk_tst2norm OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_tst2norm; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_tst2norm IS 'links test result evaluation norms to tests';


--
-- Name: COLUMN lnk_tst2norm.id_test; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_tst2norm.id_test IS 'which test does the linked norm apply to';


--
-- Name: COLUMN lnk_tst2norm.id_norm; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_tst2norm.id_norm IS 'the norm to apply to the linked test';


--
-- Name: lnk_tst2norm_id_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_tst2norm_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_tst2norm_id_seq OWNER TO "gm-dbo";

--
-- Name: lnk_tst2norm_id_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_tst2norm_id_seq OWNED BY clin.lnk_tst2norm.id;


--
-- Name: lnk_type2item; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_type2item (
    pk integer NOT NULL,
    fk_type integer NOT NULL,
    fk_item integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.lnk_type2item OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_type2item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_type2item IS 'allow to link many-to-many between clin.clin_root_item and clin.clin_item_type';


--
-- Name: COLUMN lnk_type2item.fk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.lnk_type2item.fk_item IS 'the item this type is linked to,
	 since PostgreSQL apparently cannot reference a value
	 inserted from a child table (?) we must simulate
	 referential integrity checks with a custom trigger,
	 this, however, does not deal with update/delete
	 cascading :-(';


--
-- Name: lnk_type2item_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_type2item_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_type2item_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_type2item_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_type2item_pk_seq OWNED BY clin.lnk_type2item.pk;


--
-- Name: lnk_vaccination_course2schedule; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.lnk_vaccination_course2schedule (
    pk integer NOT NULL,
    fk_course integer NOT NULL,
    fk_schedule integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.lnk_vaccination_course2schedule OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_vaccination_course2schedule; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.lnk_vaccination_course2schedule IS 'this table links vaccination courses for a single epitope
	 into schedules defined and recommended by a vaccination
	 council or similar entity';


--
-- Name: lnk_vaccination_course2schedule_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.lnk_vaccination_course2schedule_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.lnk_vaccination_course2schedule_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_vaccination_course2schedule_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.lnk_vaccination_course2schedule_pk_seq OWNED BY clin.lnk_vaccination_course2schedule.pk;


--
-- Name: patient_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.patient_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.patient_pk_seq OWNER TO "gm-dbo";

--
-- Name: patient_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.patient_pk_seq OWNED BY clin.patient.pk;


--
-- Name: procedure_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.procedure_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.procedure_pk_seq OWNER TO "gm-dbo";

--
-- Name: procedure_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.procedure_pk_seq OWNED BY clin.procedure.pk;


--
-- Name: review_root_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.review_root_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.review_root_pk_seq OWNER TO "gm-dbo";

--
-- Name: review_root_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.review_root_pk_seq OWNED BY clin.review_root.pk;


--
-- Name: soap_cat_ranks; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.soap_cat_ranks (
    pk integer NOT NULL,
    rank integer NOT NULL,
    soap_cat character(1),
    CONSTRAINT clin_soap_cat_ranks_sane_cats CHECK (((soap_cat IS NULL) OR (soap_cat = ANY (ARRAY['s'::bpchar, 'o'::bpchar, 'a'::bpchar, 'p'::bpchar, 'u'::bpchar])))),
    CONSTRAINT clin_soap_cat_ranks_sane_ranks CHECK ((rank = ANY (ARRAY[1, 2, 3, 4, 5, 6])))
);


ALTER TABLE clin.soap_cat_ranks OWNER TO "gm-dbo";

--
-- Name: soap_cat_ranks_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.soap_cat_ranks_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.soap_cat_ranks_pk_seq OWNER TO "gm-dbo";

--
-- Name: soap_cat_ranks_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.soap_cat_ranks_pk_seq OWNED BY clin.soap_cat_ranks.pk;


--
-- Name: substance_intake_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.substance_intake_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.substance_intake_pk_seq OWNER TO "gm-dbo";

--
-- Name: substance_intake_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.substance_intake_pk_seq OWNED BY clin.substance_intake.pk;


--
-- Name: suppressed_hint_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.suppressed_hint_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.suppressed_hint_pk_seq OWNER TO "gm-dbo";

--
-- Name: suppressed_hint_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.suppressed_hint_pk_seq OWNED BY clin.suppressed_hint.pk;


--
-- Name: test_org_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.test_org_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.test_org_pk_seq OWNER TO "gm-dbo";

--
-- Name: test_org_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.test_org_pk_seq OWNED BY clin.test_org.pk;


--
-- Name: test_panel_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.test_panel_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.test_panel_pk_seq OWNER TO "gm-dbo";

--
-- Name: test_panel_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.test_panel_pk_seq OWNED BY clin.test_panel.pk;


--
-- Name: test_result_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.test_result_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.test_result_pk_seq OWNER TO "gm-dbo";

--
-- Name: test_result_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.test_result_pk_seq OWNED BY clin.test_result.pk;


--
-- Name: test_type_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.test_type_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.test_type_pk_seq OWNER TO "gm-dbo";

--
-- Name: test_type_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.test_type_pk_seq OWNED BY clin.test_type.pk;


--
-- Name: test_type_unified_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.test_type_unified_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.test_type_unified_pk_seq OWNER TO "gm-dbo";

--
-- Name: test_type_unified_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.test_type_unified_pk_seq OWNED BY clin.meta_test_type.pk;


--
-- Name: v_candidate_diagnoses; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_candidate_diagnoses AS
 SELECT ( SELECT c_enc.fk_patient
           FROM clin.encounter c_enc
          WHERE (c_enc.pk = c_hi.fk_encounter)) AS pk_patient,
    (c_hi.description || COALESCE(((' ('::text || (c_hi.laterality)::text) || ')'::text), ''::text)) AS diagnosis,
    c_hi.is_confidential AS explicitely_confidential,
    c_hi.diagnostic_certainty_classification,
    'clin.health_issue'::text AS source
   FROM clin.health_issue c_hi
UNION ALL
 SELECT ( SELECT c_enc.fk_patient
           FROM clin.encounter c_enc
          WHERE (c_enc.pk = c_epi.fk_encounter)) AS pk_patient,
    c_epi.description AS diagnosis,
    COALESCE(( SELECT c_hi.is_confidential
           FROM clin.health_issue c_hi
          WHERE (c_hi.pk = c_epi.fk_health_issue)), false) AS explicitely_confidential,
    c_epi.diagnostic_certainty_classification,
    'clin.episode'::text AS source
   FROM clin.episode c_epi
  WHERE (c_epi.is_open IS TRUE)
UNION ALL
 SELECT c_enc.fk_patient AS pk_patient,
    c_enc.assessment_of_encounter AS diagnosis,
    false AS explicitely_confidential,
    NULL::text AS diagnostic_certainty_classification,
    'clin.encounter'::text AS source
   FROM clin.encounter c_enc
  WHERE ((c_enc.assessment_of_encounter IS NOT NULL) AND (c_enc.pk IN ( SELECT c_cri.fk_encounter
           FROM clin.clin_root_item c_cri
          WHERE (c_cri.fk_episode IN ( SELECT c_epi.pk
                   FROM clin.episode c_epi
                  WHERE (c_epi.is_open IS TRUE))))))
UNION ALL
 SELECT ( SELECT c_enc.fk_patient
           FROM clin.encounter c_enc
          WHERE (c_enc.pk = c_cn.fk_encounter)) AS pk_patient,
    c_cn.narrative AS diagnosis,
    false AS explicitely_confidential,
    NULL::text AS diagnostic_certainty_classification,
    'clin.clin_narrative'::text AS source
   FROM clin.clin_narrative c_cn
  WHERE ((c_cn.soap_cat = 'a'::text) AND (c_cn.fk_episode IN ( SELECT c_epi.pk
           FROM clin.episode c_epi
          WHERE (c_epi.is_open IS TRUE))));


ALTER TABLE clin.v_candidate_diagnoses OWNER TO "gm-dbo";

--
-- Name: VIEW v_candidate_diagnoses; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_candidate_diagnoses IS 'Candidates for diagnoses.';


--
-- Name: marital_status; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.marital_status (
    pk integer NOT NULL,
    name text NOT NULL
);


ALTER TABLE dem.marital_status OWNER TO "gm-dbo";

--
-- Name: v_all_persons; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_all_persons AS
 SELECT d_i.pk AS pk_identity,
    d_i.title,
    d_n.firstnames,
    d_n.preferred,
    d_n.lastnames,
    d_i.gender,
    public._(d_i.gender) AS l10n_gender,
    d_i.dob AS dob_only,
    (date_trunc('day'::text, d_i.dob) + (COALESCE(d_i.tob, (d_i.dob)::time without time zone))::interval) AS dob,
    d_i.tob,
    d_i.deceased,
    COALESCE(d_ms.name, 'unknown'::text) AS marital_status,
    public._(COALESCE(d_ms.name, 'unknown'::text)) AS l10n_marital_status,
    d_i.emergency_contact,
    d_i.comment,
    d_i.deleted AS is_deleted,
    d_i.fk_marital_status AS pk_marital_status,
    d_n.id AS pk_active_name,
    d_i.fk_emergency_contact AS pk_emergency_contact,
    d_i.fk_primary_provider AS pk_primary_provider,
    d_i.xmin AS xmin_identity,
    d_i.dob_is_estimated
   FROM ((dem.identity d_i
     JOIN dem.names d_n ON (((d_n.id_identity = d_i.pk) AND (d_n.active IS TRUE))))
     LEFT JOIN dem.marital_status d_ms ON ((d_i.fk_marital_status = d_ms.pk)));


ALTER TABLE dem.v_all_persons OWNER TO "gm-dbo";

--
-- Name: VIEW v_all_persons; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON VIEW dem.v_all_persons IS 'This view denormalizes persons with their active name.';


--
-- Name: v_export_items; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_export_items AS
 SELECT inner_export_items.pk_export_item,
    inner_export_items.pk_identity,
    inner_export_items.created_by,
    inner_export_items.created_when,
    inner_export_items.designation,
    inner_export_items.description,
    inner_export_items.pk_doc_obj,
    inner_export_items.md5_sum,
    inner_export_items.size,
    inner_export_items.filename,
    inner_export_items.pk_identity_raw,
    inner_export_items.xmin_export_item,
    (inner_export_items.pk_identity IS DISTINCT FROM inner_export_items.pk_identity_raw) AS pk_identity_raw_needs_update,
    d_vp.title,
    d_vp.firstnames,
    d_vp.lastnames,
    d_vp.preferred,
    d_vp.gender,
    d_vp.dob
   FROM (( SELECT c_ei.pk AS pk_export_item,
                CASE
                    WHEN (c_ei.fk_doc_obj IS NULL) THEN c_ei.fk_identity
                    ELSE ( SELECT encounter.fk_patient
                       FROM clin.encounter
                      WHERE (encounter.pk = ( SELECT doc_med.fk_encounter
                               FROM blobs.doc_med
                              WHERE (doc_med.pk = ( SELECT doc_obj.fk_doc
                                       FROM blobs.doc_obj
                                      WHERE (doc_obj.pk = c_ei.fk_doc_obj))))))
                END AS pk_identity,
            COALESCE(( SELECT staff.short_alias
                   FROM dem.staff
                  WHERE (staff.db_user = c_ei.created_by)), (c_ei.created_by)::text) AS created_by,
            c_ei.created_when,
            c_ei.designation,
            c_ei.description,
            c_ei.fk_doc_obj AS pk_doc_obj,
            md5(COALESCE(c_ei.data, COALESCE(( SELECT b_do.data
                   FROM blobs.doc_obj b_do
                  WHERE (b_do.pk = c_ei.fk_doc_obj)), '\x'::bytea))) AS md5_sum,
            octet_length(COALESCE(c_ei.data, '\x'::bytea)) AS size,
            COALESCE(c_ei.filename, ( SELECT b_do.filename
                   FROM blobs.doc_obj b_do
                  WHERE (b_do.pk = c_ei.fk_doc_obj))) AS filename,
            c_ei.fk_identity AS pk_identity_raw,
            c_ei.xmin AS xmin_export_item
           FROM clin.export_item c_ei) inner_export_items
     JOIN dem.v_all_persons d_vp ON ((d_vp.pk_identity = inner_export_items.pk_identity)));


ALTER TABLE clin.v_export_items OWNER TO "gm-dbo";

--
-- Name: v_external_care; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_external_care AS
 SELECT c_ec.pk AS pk_external_care,
    ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = c_ec.fk_encounter)) AS pk_identity,
    COALESCE(c_ec.issue, c_hi.description) AS issue,
    c_ec.provider,
    d_ou.description AS unit,
    d_o.description AS organization,
    c_ec.comment,
    c_ec.inactive,
    c_ec.fk_health_issue AS pk_health_issue,
    c_ec.fk_org_unit AS pk_org_unit,
    c_ec.fk_encounter AS pk_encounter,
    c_ec.xmin AS xmin_external_care,
    c_ec.modified_when,
    c_ec.modified_by,
    c_ec.row_version
   FROM (((clin.external_care c_ec
     LEFT JOIN clin.health_issue c_hi ON ((c_hi.pk = c_ec.fk_health_issue)))
     LEFT JOIN dem.org_unit d_ou ON ((c_ec.fk_org_unit = d_ou.pk)))
     LEFT JOIN dem.org d_o ON ((d_ou.fk_org = d_o.pk)));


ALTER TABLE clin.v_external_care OWNER TO "gm-dbo";

--
-- Name: v_family_history; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_family_history AS
 SELECT c_fh.pk AS pk_family_history,
    cenc.fk_patient AS pk_patient,
    c_fh.soap_cat,
    c_fhrt.description AS relation,
    public._(c_fhrt.description) AS l10n_relation,
    c_fh.narrative AS condition,
    c_fh.age_noted,
    c_fh.age_of_death,
    c_fh.contributed_to_death,
    c_fh.comment,
    cep.description AS episode,
    c_fh.clin_when AS when_known_to_patient,
    c_fh.name_relative,
    c_fh.dob_relative,
    c_fhrt.is_genetic AS is_genetic_relative,
    c_fh.fk_encounter AS pk_encounter,
    c_fh.fk_episode AS pk_episode,
    cep.fk_health_issue AS pk_health_issue,
    c_fhrt.pk AS pk_fhx_relation_type,
    c_fh.modified_when,
    COALESCE(( SELECT array_agg(c_lc2fhx.fk_generic_code) AS array_agg
           FROM clin.lnk_code2fhx c_lc2fhx
          WHERE (c_lc2fhx.fk_item = c_fh.pk)), ARRAY[]::integer[]) AS pk_generic_codes,
    c_fh.xmin AS xmin_family_history
   FROM (((clin.family_history c_fh
     JOIN clin.encounter cenc ON ((c_fh.fk_encounter = cenc.pk)))
     JOIN clin.episode cep ON ((c_fh.fk_episode = cep.pk)))
     LEFT JOIN clin.fhx_relation_type c_fhrt ON ((c_fh.fk_relation_type = c_fhrt.pk)));


ALTER TABLE clin.v_family_history OWNER TO "gm-dbo";

--
-- Name: VIEW v_family_history; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_family_history IS 'family history denormalized';


--
-- Name: v_health_issues; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_health_issues AS
 SELECT ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = c_hi.fk_encounter)) AS pk_patient,
    c_hi.pk AS pk_health_issue,
    c_hi.description,
    c_hi.summary,
    c_hi.laterality,
    c_hi.age_noted,
    c_hi.is_active,
    c_hi.clinically_relevant,
    c_hi.is_confidential,
    c_hi.is_cause_of_death,
    c_hi.fk_encounter AS pk_encounter,
    c_hi.modified_when,
    c_hi.modified_by,
    c_hi.row_version,
    c_hi."grouping",
    c_hi.diagnostic_certainty_classification,
    (EXISTS ( SELECT 1
           FROM clin.episode c_ep
          WHERE ((c_ep.fk_health_issue = c_hi.pk) AND (c_ep.is_open IS TRUE))
         LIMIT 1)) AS has_open_episode,
    COALESCE(( SELECT array_agg(c_lc2h.fk_generic_code) AS array_agg
           FROM clin.lnk_code2h_issue c_lc2h
          WHERE (c_lc2h.fk_item = c_hi.pk)), ARRAY[]::integer[]) AS pk_generic_codes,
    c_hi.xmin AS xmin_health_issue
   FROM clin.health_issue c_hi;


ALTER TABLE clin.v_health_issues OWNER TO "gm-dbo";

--
-- Name: v_hospital_stays; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_hospital_stays AS
 SELECT c_hs.pk AS pk_hospital_stay,
    ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = c_hs.fk_encounter)) AS pk_patient,
    d_o.description AS hospital,
    d_ou.description AS ward,
    c_hs.narrative AS comment,
    c_hs.clin_when AS admission,
    c_hs.discharge,
    c_hs.soap_cat,
    c_e.description AS episode,
    c_hi.description AS health_issue,
    c_hs.fk_encounter AS pk_encounter,
    c_hs.fk_episode AS pk_episode,
    c_hi.pk AS pk_health_issue,
    c_hs.fk_org_unit AS pk_org_unit,
    d_o.pk AS pk_org,
    COALESCE(( SELECT array_agg(b_dm.pk) AS array_agg
           FROM blobs.doc_med b_dm
          WHERE (b_dm.fk_hospital_stay = c_hs.pk)), ARRAY[]::integer[]) AS pk_documents,
    c_hs.modified_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_hs.modified_by)), (('<'::text || (c_hs.modified_by)::text) || '>'::text)) AS modified_by,
    c_hs.row_version,
    c_hs.xmin AS xmin_hospital_stay
   FROM ((((clin.hospital_stay c_hs
     LEFT JOIN clin.episode c_e ON ((c_e.pk = c_hs.fk_episode)))
     LEFT JOIN clin.health_issue c_hi ON ((c_hi.pk = c_e.fk_health_issue)))
     LEFT JOIN dem.org_unit d_ou ON ((d_ou.pk = c_hs.fk_org_unit)))
     LEFT JOIN dem.org d_o ON ((d_o.pk = d_ou.fk_org)));


ALTER TABLE clin.v_hospital_stays OWNER TO "gm-dbo";

--
-- Name: v_incoming_data_unmatched; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_incoming_data_unmatched AS
 SELECT c_idu.pk AS pk_incoming_data_unmatched,
    c_idu.fk_patient_candidates AS pk_patient_candidates,
    c_idu.firstnames,
    c_idu.lastnames,
    c_idu.dob,
    c_idu.gender,
    c_idu.postcode,
    c_idu.other_info,
    c_idu.request_id,
    c_idu.requestor,
    c_idu.external_data_id,
    c_idu.comment,
    c_idu.fk_identity_disambiguated AS pk_identity_disambiguated,
    c_idu.fk_provider_disambiguated AS pk_provider_disambiguated,
    c_idu.type AS data_type,
    md5(c_idu.data) AS md5_sum,
    octet_length(c_idu.data) AS data_size,
    c_idu.xmin AS xmin_incoming_data_unmatched
   FROM clin.incoming_data_unmatched c_idu;


ALTER TABLE clin.v_incoming_data_unmatched OWNER TO "gm-dbo";

--
-- Name: VIEW v_incoming_data_unmatched; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_incoming_data_unmatched IS 'Shows incoming data but w/o the data field.';


--
-- Name: v_linked_codes; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_linked_codes AS
 SELECT c_lc2ir.fk_item AS pk_item,
    (c_lc2ir.tableoid)::regclass AS item_table,
    (r_csr.code || COALESCE(c_lc2ir.code_modifier, ''::text)) AS code,
    r_csr.code AS base_code,
    c_lc2ir.code_modifier,
    r_csr.term,
    r_ds.name_long,
    r_ds.name_short,
    r_ds.version,
    r_ds.lang,
    (r_csr.tableoid)::regclass AS code_table,
    r_csr.pk_coding_system AS pk_generic_code,
    r_csr.fk_data_source AS pk_data_source,
    c_lc2ir.pk_lnk_code2item
   FROM ((clin.lnk_code2item_root c_lc2ir
     JOIN ref.coding_system_root r_csr ON ((r_csr.pk_coding_system = c_lc2ir.fk_generic_code)))
     JOIN ref.data_source r_ds ON ((r_ds.pk = r_csr.fk_data_source)));


ALTER TABLE clin.v_linked_codes OWNER TO "gm-dbo";

--
-- Name: VIEW v_linked_codes; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_linked_codes IS 'Denormalized codes linked to EMR structures.';


--
-- Name: v_most_recent_encounters; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_most_recent_encounters AS
 SELECT ce1.pk AS pk_encounter,
    ce1.fk_patient AS pk_patient,
    ce1.reason_for_encounter,
    ce1.assessment_of_encounter,
    et.description AS type,
    public._(et.description) AS l10n_type,
    ce1.started,
    ce1.last_affirmed,
    ce1.fk_type AS pk_type,
    ce1.fk_location AS pk_location
   FROM clin.encounter ce1,
    clin.encounter_type et
  WHERE ((ce1.fk_type = et.pk) AND (ce1.started = ( SELECT max(ce2.started) AS max
           FROM clin.encounter ce2
          WHERE (ce2.last_affirmed = ( SELECT max(ce3.last_affirmed) AS max
                   FROM clin.encounter ce3
                  WHERE (ce3.fk_patient = ce1.fk_patient)))
         LIMIT 1)));


ALTER TABLE clin.v_most_recent_encounters OWNER TO "gm-dbo";

--
-- Name: VIEW v_most_recent_encounters; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_most_recent_encounters IS 'Lists the most recent encounters per patient. Logic of "most recent" 
	is: for a patient: 
	 1) select encounters with latest "last_affirmed",
	 2) from those select encounters with latest "started"
	 3) limit those to 1 if there are duplicates (same start and end of encounter!)';


--
-- Name: v_narrative; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_narrative AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_n.clin_when AS date,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_n.modified_by)), (('<'::text || (c_n.modified_by)::text) || '>'::text)) AS modified_by,
    c_n.soap_cat,
    c_n.narrative,
    c_epi.description AS episode,
    c_hi.description AS health_issue,
    c_n.pk_item,
    c_n.pk AS pk_narrative,
    c_epi.fk_health_issue AS pk_health_issue,
    c_n.fk_episode AS pk_episode,
    c_n.fk_encounter AS pk_encounter,
    c_n.xmin AS xmin_clin_narrative,
    c_n.modified_when,
    c_n.row_version,
    c_n.pk_audit,
    c_n.modified_by AS modified_by_raw,
    COALESCE(( SELECT array_agg(c_lc2n.fk_generic_code) AS array_agg
           FROM clin.lnk_code2narrative c_lc2n
          WHERE (c_lc2n.fk_item = c_n.pk)), ARRAY[]::integer[]) AS pk_generic_codes
   FROM (((clin.clin_narrative c_n
     LEFT JOIN clin.encounter c_enc ON ((c_n.fk_encounter = c_enc.pk)))
     LEFT JOIN clin.episode c_epi ON ((c_n.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_epi.fk_health_issue = c_hi.pk)));


ALTER TABLE clin.v_narrative OWNER TO "gm-dbo";

--
-- Name: VIEW v_narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_narrative IS 'patient narrative with denormalized context added';


--
-- Name: v_pat_allergies; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_allergies AS
 SELECT a.pk AS pk_allergy,
    ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = a.fk_encounter)) AS pk_patient,
    a.soap_cat,
        CASE
            WHEN (COALESCE(btrim(a.allergene), ''::text) = ''::text) THEN a.substance
            ELSE a.allergene
        END AS descriptor,
    a.allergene,
    a.substance,
    a.substance_code,
    a.generics,
    a.generic_specific,
    a.atc_code,
    at.value AS type,
    public._(at.value) AS l10n_type,
    a.definite,
    a.narrative AS reaction,
    a.fk_type AS pk_type,
    a.pk_item,
    a.clin_when AS date,
    ( SELECT episode.fk_health_issue
           FROM clin.episode
          WHERE (episode.pk = a.fk_episode)) AS pk_health_issue,
    a.fk_episode AS pk_episode,
    a.fk_encounter AS pk_encounter,
    a.modified_when,
    a.modified_by,
    a.xmin AS xmin_allergy
   FROM clin.allergy a,
    clin._enum_allergy_type at
  WHERE (at.pk = a.fk_type);


ALTER TABLE clin.v_pat_allergies OWNER TO "gm-dbo";

--
-- Name: VIEW v_pat_allergies; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_pat_allergies IS 'denormalizes clin.allergy';


--
-- Name: v_pat_allergy_state; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_allergy_state AS
 SELECT ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = a.fk_encounter)) AS pk_patient,
    a.modified_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = a.modified_by)), (('<'::text || (a.modified_by)::text) || '>'::text)) AS modified_by,
    a.last_confirmed,
    a.has_allergy,
    a.comment,
    a.fk_encounter AS pk_encounter,
    a.pk AS pk_allergy_state,
    a.xmin AS xmin_allergy_state
   FROM clin.allergy_state a;


ALTER TABLE clin.v_pat_allergy_state OWNER TO "gm-dbo";

--
-- Name: v_pat_encounters; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_encounters AS
 SELECT c_enc.pk AS pk_encounter,
    c_enc.fk_patient AS pk_patient,
    c_enc.started,
    c_et.description AS type,
    public._(c_et.description) AS l10n_type,
    c_enc.reason_for_encounter,
    c_enc.assessment_of_encounter,
    c_enc.last_affirmed,
    c_enc.source_time_zone,
    ( SELECT timezone(( SELECT c_enc1.source_time_zone
                   FROM clin.encounter c_enc1
                  WHERE (c_enc1.pk = c_enc.pk)), c_enc.started) AS timezone) AS started_original_tz,
    ( SELECT timezone(( SELECT c_enc1.source_time_zone
                   FROM clin.encounter c_enc1
                  WHERE (c_enc1.pk = c_enc.pk)), c_enc.last_affirmed) AS timezone) AS last_affirmed_original_tz,
    COALESCE(d_ou.description, '?'::text) AS praxis_branch,
    COALESCE(d_o.description, '?'::text) AS praxis,
    c_enc.fk_location AS pk_org_unit,
    d_ou.fk_org AS pk_org,
    d_ou.fk_category AS pk_unit_type,
    d_o.fk_category AS pk_org_type,
    c_enc.fk_type AS pk_type,
    COALESCE(( SELECT array_agg(c_lc2r.fk_generic_code) AS array_agg
           FROM clin.lnk_code2rfe c_lc2r
          WHERE (c_lc2r.fk_item = c_enc.pk)), ARRAY[]::integer[]) AS pk_generic_codes_rfe,
    COALESCE(( SELECT array_agg(c_lc2a.fk_generic_code) AS array_agg
           FROM clin.lnk_code2aoe c_lc2a
          WHERE (c_lc2a.fk_item = c_enc.pk)), ARRAY[]::integer[]) AS pk_generic_codes_aoe,
    c_enc.xmin AS xmin_encounter,
    c_enc.row_version,
    c_enc.pk_audit,
    c_enc.modified_when,
    c_enc.modified_by AS modified_by_raw,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_enc.modified_by)), (('<'::text || (c_enc.modified_by)::text) || '>'::text)) AS modified_by
   FROM (((clin.encounter c_enc
     LEFT JOIN clin.encounter_type c_et ON ((c_enc.fk_type = c_et.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((c_enc.fk_location = d_ou.pk)))
     LEFT JOIN dem.org d_o ON ((d_ou.fk_org = d_o.pk)));


ALTER TABLE clin.v_pat_encounters OWNER TO "gm-dbo";

--
-- Name: VIEW v_pat_encounters; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_pat_encounters IS 'Details on encounters.';


--
-- Name: v_pat_episodes; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_episodes AS
 SELECT cenc.fk_patient AS pk_patient,
    cep.description,
    cep.summary,
    cep.is_open AS episode_open,
    NULL::text AS health_issue,
    NULL::boolean AS issue_active,
    NULL::boolean AS issue_clinically_relevant,
    ( SELECT min(cle.started) AS min
           FROM clin.encounter cle
          WHERE (cle.pk = cep.fk_encounter)
         LIMIT 1) AS started_first,
    ( SELECT max(cle.started) AS max
           FROM clin.encounter cle
          WHERE (cle.pk = cep.fk_encounter)
         LIMIT 1) AS started_last,
    ( SELECT max(cle.last_affirmed) AS max
           FROM clin.encounter cle
          WHERE (cle.pk = cep.fk_encounter)
         LIMIT 1) AS last_affirmed,
    cep.pk AS pk_episode,
    cep.fk_encounter AS pk_encounter,
    NULL::integer AS pk_health_issue,
    cep.modified_when AS episode_modified_when,
    cep.modified_by AS episode_modified_by,
    cep.diagnostic_certainty_classification,
    NULL::text AS diagnostic_certainty_classification_issue,
    COALESCE(( SELECT array_agg(c_lc2e.fk_generic_code) AS array_agg
           FROM clin.lnk_code2episode c_lc2e
          WHERE (c_lc2e.fk_item = cep.pk)), ARRAY[]::integer[]) AS pk_generic_codes,
    cep.xmin AS xmin_episode
   FROM (clin.episode cep
     JOIN clin.encounter cenc ON ((cep.fk_encounter = cenc.pk)))
  WHERE (cep.fk_health_issue IS NULL)
UNION ALL
 SELECT cenc.fk_patient AS pk_patient,
    cep.description,
    cep.summary,
    cep.is_open AS episode_open,
    chi.description AS health_issue,
    chi.is_active AS issue_active,
    chi.clinically_relevant AS issue_clinically_relevant,
    ( SELECT min(cle.started) AS min
           FROM clin.encounter cle
          WHERE (cle.pk = cep.fk_encounter)
         LIMIT 1) AS started_first,
    ( SELECT max(cle.started) AS max
           FROM clin.encounter cle
          WHERE (cle.pk = cep.fk_encounter)
         LIMIT 1) AS started_last,
    ( SELECT max(cle.last_affirmed) AS max
           FROM clin.encounter cle
          WHERE (cle.pk = cep.fk_encounter)
         LIMIT 1) AS last_affirmed,
    cep.pk AS pk_episode,
    cep.fk_encounter AS pk_encounter,
    cep.fk_health_issue AS pk_health_issue,
    cep.modified_when AS episode_modified_when,
    cep.modified_by AS episode_modified_by,
    cep.diagnostic_certainty_classification,
    chi.diagnostic_certainty_classification AS diagnostic_certainty_classification_issue,
    COALESCE(( SELECT array_agg(c_lc2e.fk_generic_code) AS array_agg
           FROM clin.lnk_code2episode c_lc2e
          WHERE (c_lc2e.fk_item = cep.pk)), ARRAY[]::integer[]) AS pk_generic_codes,
    cep.xmin AS xmin_episode
   FROM ((clin.episode cep
     JOIN clin.encounter cenc ON ((cep.fk_encounter = cenc.pk)))
     JOIN clin.health_issue chi ON ((cep.fk_health_issue = chi.pk)));


ALTER TABLE clin.v_pat_episodes OWNER TO "gm-dbo";

--
-- Name: v_pat_items; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_items AS
 SELECT cri.modified_when,
    cri.modified_by,
    cri.clin_when,
        CASE cri.row_version
            WHEN 0 THEN false
            ELSE true
        END AS is_modified,
    cenc.fk_patient AS pk_patient,
    cri.pk_item,
    cri.fk_encounter AS pk_encounter,
    cri.fk_episode AS pk_episode,
    cepi.fk_health_issue AS pk_health_issue,
    cri.soap_cat,
    cri.narrative,
    (((pgn.nspname)::text || '.'::text) || (pgc.relname)::text) AS src_table
   FROM clin.clin_root_item cri,
    clin.encounter cenc,
    clin.episode cepi,
    (pg_class pgc
     LEFT JOIN pg_namespace pgn ON ((pgc.relnamespace = pgn.oid)))
  WHERE ((cri.fk_encounter = cenc.pk) AND (cri.fk_episode = cepi.pk) AND (cri.tableoid = pgc.oid));


ALTER TABLE clin.v_pat_items OWNER TO "gm-dbo";

--
-- Name: v_pat_vaccs4indication; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_vaccs4indication AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_shot.pk AS pk_vaccination,
    c_shot.clin_when AS date_given,
    r_dp.description AS vaccine,
    public._((r_s.atc || '-target'::text), 'en'::text) AS indication,
        CASE
            WHEN (public._((r_s.atc || '-target'::text)) = (r_s.atc || '-target'::text)) THEN public._((r_s.atc || '-target'::text), 'en'::text)
            ELSE public._((r_s.atc || '-target'::text))
        END AS l10n_indication,
    c_shot.site,
    c_shot.batch_no,
    c_shot.reaction,
    c_shot.narrative AS comment,
    c_shot.soap_cat,
    c_shot.modified_when,
    c_shot.modified_by,
    c_shot.row_version,
    c_shot.fk_vaccine AS pk_vaccine,
    r_s.atc AS atc_indication,
    c_shot.fk_provider AS pk_provider,
    c_shot.fk_encounter AS pk_encounter,
    c_shot.fk_episode AS pk_episode,
    c_shot.xmin AS xmin_vaccination
   FROM ((((((clin.vaccination c_shot
     JOIN clin.encounter c_enc ON ((c_enc.pk = c_shot.fk_encounter)))
     JOIN ref.vaccine r_v ON ((r_v.pk = c_shot.fk_vaccine)))
     JOIN ref.drug_product r_dp ON ((r_dp.pk = r_v.fk_drug_product)))
     JOIN ref.lnk_dose2drug r_ld2d ON ((r_ld2d.fk_drug_product = r_dp.pk)))
     JOIN ref.dose r_d ON ((r_d.pk = r_ld2d.fk_dose)))
     JOIN ref.substance r_s ON ((r_s.pk = r_d.fk_substance)));


ALTER TABLE clin.v_pat_vaccs4indication OWNER TO "gm-dbo";

--
-- Name: VIEW v_pat_vaccs4indication; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_pat_vaccs4indication IS 'Lists all vaccinations for each indication for patients';


--
-- Name: v_pat_last_vacc4indication; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_pat_last_vacc4indication AS
 SELECT c_vpv4i.pk_patient,
    c_vpv4i.pk_vaccination,
    c_vpv4i.date_given,
    c_vpv4i.vaccine,
    c_vpv4i.indication,
    c_vpv4i.l10n_indication,
    c_vpv4i.site,
    c_vpv4i.batch_no,
    c_vpv4i.reaction,
    c_vpv4i.comment,
    c_vpv4i.soap_cat,
    c_vpv4i.modified_when,
    c_vpv4i.modified_by,
    c_vpv4i.row_version,
    c_vpv4i.pk_vaccine,
    c_vpv4i.atc_indication,
    c_vpv4i.pk_provider,
    c_vpv4i.pk_encounter,
    c_vpv4i.pk_episode,
    c_vpv4i.xmin_vaccination,
    shots_per_ind.no_of_shots
   FROM (clin.v_pat_vaccs4indication c_vpv4i
     JOIN ( SELECT count(1) AS no_of_shots,
            v_pat_vaccs4indication.pk_patient,
            v_pat_vaccs4indication.atc_indication
           FROM clin.v_pat_vaccs4indication
          GROUP BY v_pat_vaccs4indication.pk_patient, v_pat_vaccs4indication.atc_indication) shots_per_ind ON (((c_vpv4i.pk_patient = shots_per_ind.pk_patient) AND (c_vpv4i.atc_indication = shots_per_ind.atc_indication))))
  WHERE (c_vpv4i.date_given = ( SELECT max(c_vpv4i_2.date_given) AS max
           FROM clin.v_pat_vaccs4indication c_vpv4i_2
          WHERE ((c_vpv4i.pk_patient = c_vpv4i_2.pk_patient) AND (c_vpv4i.atc_indication = c_vpv4i_2.atc_indication))));


ALTER TABLE clin.v_pat_last_vacc4indication OWNER TO "gm-dbo";

--
-- Name: VIEW v_pat_last_vacc4indication; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_pat_last_vacc4indication IS 'Lists *latest* vaccinations with total count per indication.';


--
-- Name: v_potential_problem_list; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_potential_problem_list AS
 SELECT ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = cep.fk_encounter)) AS pk_patient,
    cep.description AS problem,
    cep.summary,
    'episode'::text AS type,
    public._('episode'::text) AS l10n_type,
    false AS problem_active,
    false AS clinically_relevant,
    cep.pk AS pk_episode,
    cep.fk_health_issue AS pk_health_issue,
    cep.diagnostic_certainty_classification,
    cep.fk_encounter AS pk_encounter,
    cep.modified_when
   FROM clin.episode cep
  WHERE (cep.is_open IS FALSE)
UNION
 SELECT ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = chi.fk_encounter)) AS pk_patient,
    chi.description AS problem,
    chi.summary,
    'issue'::text AS type,
    public._('health issue'::text) AS l10n_type,
    chi.is_active AS problem_active,
    false AS clinically_relevant,
    NULL::integer AS pk_episode,
    chi.pk AS pk_health_issue,
    chi.diagnostic_certainty_classification,
    chi.fk_encounter AS pk_encounter,
    chi.modified_when
   FROM clin.health_issue chi
  WHERE (chi.clinically_relevant IS FALSE);


ALTER TABLE clin.v_potential_problem_list OWNER TO "gm-dbo";

--
-- Name: v_problem_list; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_problem_list AS
 SELECT ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = cep.fk_encounter)) AS pk_patient,
    cep.description AS problem,
    cep.summary,
    'episode'::text AS type,
    public._('episode'::text) AS l10n_type,
    true AS problem_active,
    true AS clinically_relevant,
    cep.pk AS pk_episode,
    cep.fk_health_issue AS pk_health_issue,
    cep.diagnostic_certainty_classification,
    cep.fk_encounter AS pk_encounter,
    cep.modified_when
   FROM clin.episode cep
  WHERE (cep.is_open IS TRUE)
UNION
 SELECT ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = chi.fk_encounter)) AS pk_patient,
    chi.description AS problem,
    chi.summary,
    'issue'::text AS type,
    public._('health issue'::text) AS l10n_type,
    chi.is_active AS problem_active,
    true AS clinically_relevant,
    NULL::integer AS pk_episode,
    chi.pk AS pk_health_issue,
    chi.diagnostic_certainty_classification,
    chi.fk_encounter AS pk_encounter,
    chi.modified_when
   FROM clin.health_issue chi
  WHERE (chi.clinically_relevant IS TRUE);


ALTER TABLE clin.v_problem_list OWNER TO "gm-dbo";

--
-- Name: v_procedures_at_hospital; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_procedures_at_hospital AS
 SELECT c_pr.pk AS pk_procedure,
    c_enc.fk_patient AS pk_patient,
    c_pr.soap_cat,
    c_pr.clin_when,
    c_pr.clin_end,
    c_pr.is_ongoing,
    c_pr.narrative AS performed_procedure,
    c_pr.comment,
    c_vhs.ward AS unit,
    c_vhs.hospital AS organization,
    c_ep.description AS episode,
    c_hi.description AS health_issue,
    c_pr.modified_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_pr.modified_by)), (('<'::text || (c_pr.modified_by)::text) || '>'::text)) AS modified_by,
    c_pr.row_version,
    c_pr.fk_encounter AS pk_encounter,
    c_pr.fk_episode AS pk_episode,
    c_pr.fk_hospital_stay AS pk_hospital_stay,
    c_ep.fk_health_issue AS pk_health_issue,
    c_pr.fk_doc AS pk_doc,
    c_vhs.pk_org,
    c_vhs.pk_org_unit,
    COALESCE(( SELECT array_agg(c_lc2p.fk_generic_code) AS array_agg
           FROM clin.lnk_code2procedure c_lc2p
          WHERE (c_lc2p.fk_item = c_pr.pk)), ARRAY[]::integer[]) AS pk_generic_codes,
    c_pr.xmin AS xmin_procedure
   FROM ((((clin.procedure c_pr
     JOIN clin.encounter c_enc ON ((c_pr.fk_encounter = c_enc.pk)))
     JOIN clin.episode c_ep ON ((c_pr.fk_episode = c_ep.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_ep.fk_health_issue = c_hi.pk)))
     LEFT JOIN clin.v_hospital_stays c_vhs ON ((c_pr.fk_hospital_stay = c_vhs.pk_hospital_stay)))
  WHERE (c_pr.fk_hospital_stay IS NOT NULL);


ALTER TABLE clin.v_procedures_at_hospital OWNER TO "gm-dbo";

--
-- Name: v_procedures_not_at_hospital; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_procedures_not_at_hospital AS
 SELECT c_pr.pk AS pk_procedure,
    c_enc.fk_patient AS pk_patient,
    c_pr.soap_cat,
    c_pr.clin_when,
    c_pr.clin_end,
    c_pr.is_ongoing,
    c_pr.narrative AS performed_procedure,
    c_pr.comment,
    d_ou.description AS unit,
    d_o.description AS organization,
    c_ep.description AS episode,
    c_hi.description AS health_issue,
    c_pr.modified_when,
    COALESCE(( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.db_user = c_pr.modified_by)), (('<'::text || (c_pr.modified_by)::text) || '>'::text)) AS modified_by,
    c_pr.row_version,
    c_pr.fk_encounter AS pk_encounter,
    c_pr.fk_episode AS pk_episode,
    c_pr.fk_hospital_stay AS pk_hospital_stay,
    c_ep.fk_health_issue AS pk_health_issue,
    c_pr.fk_doc AS pk_doc,
    d_o.pk AS pk_org,
    d_ou.pk AS pk_org_unit,
    COALESCE(( SELECT array_agg(c_lc2p.fk_generic_code) AS array_agg
           FROM clin.lnk_code2procedure c_lc2p
          WHERE (c_lc2p.fk_item = c_pr.pk)), ARRAY[]::integer[]) AS pk_generic_codes,
    c_pr.xmin AS xmin_procedure
   FROM (((((clin.procedure c_pr
     JOIN clin.encounter c_enc ON ((c_pr.fk_encounter = c_enc.pk)))
     JOIN clin.episode c_ep ON ((c_pr.fk_episode = c_ep.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_ep.fk_health_issue = c_hi.pk)))
     LEFT JOIN dem.org_unit d_ou ON ((d_ou.pk = c_pr.fk_org_unit)))
     LEFT JOIN dem.org d_o ON ((d_o.pk = d_ou.fk_org)))
  WHERE (c_pr.fk_hospital_stay IS NULL);


ALTER TABLE clin.v_procedures_not_at_hospital OWNER TO "gm-dbo";

--
-- Name: v_procedures; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_procedures AS
 SELECT v_procedures_at_hospital.pk_procedure,
    v_procedures_at_hospital.pk_patient,
    v_procedures_at_hospital.soap_cat,
    v_procedures_at_hospital.clin_when,
    v_procedures_at_hospital.clin_end,
    v_procedures_at_hospital.is_ongoing,
    v_procedures_at_hospital.performed_procedure,
    v_procedures_at_hospital.comment,
    v_procedures_at_hospital.unit,
    v_procedures_at_hospital.organization,
    v_procedures_at_hospital.episode,
    v_procedures_at_hospital.health_issue,
    v_procedures_at_hospital.modified_when,
    v_procedures_at_hospital.modified_by,
    v_procedures_at_hospital.row_version,
    v_procedures_at_hospital.pk_encounter,
    v_procedures_at_hospital.pk_episode,
    v_procedures_at_hospital.pk_hospital_stay,
    v_procedures_at_hospital.pk_health_issue,
    v_procedures_at_hospital.pk_doc,
    v_procedures_at_hospital.pk_org,
    v_procedures_at_hospital.pk_org_unit,
    v_procedures_at_hospital.pk_generic_codes,
    v_procedures_at_hospital.xmin_procedure
   FROM clin.v_procedures_at_hospital
UNION ALL
 SELECT v_procedures_not_at_hospital.pk_procedure,
    v_procedures_not_at_hospital.pk_patient,
    v_procedures_not_at_hospital.soap_cat,
    v_procedures_not_at_hospital.clin_when,
    v_procedures_not_at_hospital.clin_end,
    v_procedures_not_at_hospital.is_ongoing,
    v_procedures_not_at_hospital.performed_procedure,
    v_procedures_not_at_hospital.comment,
    v_procedures_not_at_hospital.unit,
    v_procedures_not_at_hospital.organization,
    v_procedures_not_at_hospital.episode,
    v_procedures_not_at_hospital.health_issue,
    v_procedures_not_at_hospital.modified_when,
    v_procedures_not_at_hospital.modified_by,
    v_procedures_not_at_hospital.row_version,
    v_procedures_not_at_hospital.pk_encounter,
    v_procedures_not_at_hospital.pk_episode,
    v_procedures_not_at_hospital.pk_hospital_stay,
    v_procedures_not_at_hospital.pk_health_issue,
    v_procedures_not_at_hospital.pk_doc,
    v_procedures_not_at_hospital.pk_org,
    v_procedures_not_at_hospital.pk_org_unit,
    v_procedures_not_at_hospital.pk_generic_codes,
    v_procedures_not_at_hospital.xmin_procedure
   FROM clin.v_procedures_not_at_hospital;


ALTER TABLE clin.v_procedures OWNER TO "gm-dbo";

--
-- Name: v_reviewed_items; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_reviewed_items AS
 SELECT ( SELECT pg_class.relnamespace
           FROM pg_class
          WHERE (pg_class.oid = rr.tableoid)) AS src_schema,
    ( SELECT pg_class.relname
           FROM pg_class
          WHERE (pg_class.oid = rr.tableoid)) AS src_table,
    rr.fk_reviewed_row AS pk_reviewed_row,
    rr.is_technically_abnormal,
    rr.clinically_relevant,
    ( SELECT staff.short_alias
           FROM dem.staff
          WHERE (staff.pk = rr.fk_reviewer)) AS reviewer,
    rr.comment,
    rr.pk AS pk_review_root,
    rr.fk_reviewer AS pk_reviewer
   FROM clin.review_root rr;


ALTER TABLE clin.v_reviewed_items OWNER TO "gm-dbo";

--
-- Name: VIEW v_reviewed_items; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_reviewed_items IS 'denormalization of parent table of reviewed items';


--
-- Name: v_subst_intake4narr_search; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_subst_intake4narr_search AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_si.soap_cat,
    ((((COALESCE(c_si.narrative, ''::text) || COALESCE((' / '::text || c_si.comment_on_start), ''::text)) || COALESCE((' / '::text || c_si.schedule), ''::text)) || COALESCE((' / '::text || c_si.aim), ''::text)) || COALESCE((' / '::text || c_si.discontinue_reason), ''::text)) AS narrative,
    c_si.fk_encounter AS pk_encounter,
    c_si.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    c_si.pk AS src_pk,
    'clin.substance_intake'::text AS src_table
   FROM ((clin.substance_intake c_si
     JOIN clin.encounter c_enc ON ((c_si.fk_encounter = c_enc.pk)))
     JOIN clin.episode c_epi ON ((c_si.fk_episode = c_epi.pk)));


ALTER TABLE clin.v_subst_intake4narr_search OWNER TO "gm-dbo";

--
-- Name: lnk_loinc2substance; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.lnk_loinc2substance (
    pk integer NOT NULL,
    fk_substance integer NOT NULL,
    loinc text,
    max_age interval,
    comment text,
    CONSTRAINT ref_ll2s_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT ref_ll2s_sane_loinc CHECK ((gm.is_null_or_blank_string(loinc) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE ref.lnk_loinc2substance OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_loinc2substance; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.lnk_loinc2substance IS 'Links LOINC codes to substances (for monitoring).';


--
-- Name: COLUMN lnk_loinc2substance.fk_substance; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.lnk_loinc2substance.fk_substance IS 'FK linking the substance';


--
-- Name: COLUMN lnk_loinc2substance.loinc; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.lnk_loinc2substance.loinc IS 'LOINC to monitor for substance';


--
-- Name: COLUMN lnk_loinc2substance.max_age; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.lnk_loinc2substance.max_age IS 'maximum recommended age of value of monitored LOINC in a patient,
set this to something like "999 years" if it needs to be monitored just once,
NULL = unspecified';


--
-- Name: COLUMN lnk_loinc2substance.comment; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.lnk_loinc2substance.comment IS 'a comment on this monitoring advice';


--
-- Name: v_substance_intakes; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_substance_intakes AS
 SELECT c_si.pk AS pk_substance_intake,
    c_enc.fk_patient AS pk_patient,
    c_si.soap_cat,
    r_dp.description AS product,
    r_dp.preparation,
    public._(r_dp.preparation) AS l10n_preparation,
    r_s.description AS substance,
    r_d.amount,
    r_d.unit,
    r_d.dose_unit,
    r_s.atc AS atc_substance,
    r_dp.atc_code AS atc_drug,
    r_dp.external_code AS external_code_product,
    r_dp.external_code_type AS external_code_type_product,
    ARRAY( SELECT row_to_json(loinc_row.*) AS row_to_json
           FROM ( SELECT r_ll2s.loinc,
                    r_ll2s.comment,
                    date_part('epoch'::text, r_ll2s.max_age) AS max_age_in_secs,
                    (r_ll2s.max_age)::text AS max_age_str
                   FROM ref.lnk_loinc2substance r_ll2s
                  WHERE (r_ll2s.fk_substance = r_s.pk)) loinc_row) AS loincs,
        CASE
            WHEN (c_si.comment_on_start = '?'::text) THEN NULL::timestamp with time zone
            ELSE c_si.clin_when
        END AS started,
    c_si.comment_on_start,
        CASE
            WHEN (c_si.comment_on_start = '?'::text) THEN true
            ELSE false
        END AS start_is_unknown,
        CASE
            WHEN (c_si.comment_on_start IS NULL) THEN false
            ELSE true
        END AS start_is_approximate,
    c_si.intake_is_approved_of,
    c_si.harmful_use_type,
    c_enc.started AS last_checked_when,
    c_si.schedule,
    c_si.duration,
    c_si.discontinued,
    c_si.discontinue_reason,
    c_si.is_long_term,
    c_si.aim,
    r_s.intake_instructions,
    c_epi.description AS episode,
    c_hi.description AS health_issue,
    c_si.narrative AS notes,
    r_dp.is_fake AS is_fake_product,
        CASE
            WHEN (c_si.discontinued IS NULL) THEN true
            ELSE false
        END AS is_currently_active,
        CASE
            WHEN (c_si.discontinued IS NOT NULL) THEN true
            WHEN (c_si.clin_when IS NULL) THEN
            CASE
                WHEN (c_si.is_long_term IS TRUE) THEN false
                ELSE NULL::boolean
            END
            WHEN ((c_si.clin_when > now()) IS TRUE) THEN true
            WHEN (((c_si.clin_when + c_si.duration) < now()) IS TRUE) THEN true
            WHEN (((c_si.clin_when + c_si.duration) > now()) IS TRUE) THEN false
            ELSE NULL::boolean
        END AS seems_inactive,
    r_ld2d.fk_drug_product AS pk_drug_product,
    r_dp.fk_data_source AS pk_data_source,
    r_ld2d.fk_dose AS pk_dose,
    r_ld2d.pk AS pk_drug_component,
    r_d.fk_substance AS pk_substance,
    c_si.fk_encounter AS pk_encounter,
    c_si.fk_episode AS pk_episode,
    c_epi.fk_health_issue AS pk_health_issue,
    c_si.modified_when,
    c_si.modified_by,
    c_si.row_version,
    c_si.xmin AS xmin_substance_intake
   FROM (((((((clin.substance_intake c_si
     LEFT JOIN clin.encounter c_enc ON ((c_si.fk_encounter = c_enc.pk)))
     LEFT JOIN clin.episode c_epi ON ((c_si.fk_episode = c_epi.pk)))
     LEFT JOIN clin.health_issue c_hi ON ((c_hi.pk = c_epi.fk_health_issue)))
     JOIN ref.lnk_dose2drug r_ld2d ON ((c_si.fk_drug_component = r_ld2d.pk)))
     JOIN ref.drug_product r_dp ON ((r_ld2d.fk_drug_product = r_dp.pk)))
     JOIN ref.dose r_d ON ((r_ld2d.fk_dose = r_d.pk)))
     JOIN ref.substance r_s ON ((r_d.fk_substance = r_s.pk)));


ALTER TABLE clin.v_substance_intakes OWNER TO "gm-dbo";

--
-- Name: v_suppressed_hints; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_suppressed_hints AS
 SELECT ( SELECT encounter.fk_patient
           FROM clin.encounter
          WHERE (encounter.pk = c_sh.fk_encounter)) AS pk_identity,
    c_sh.pk AS pk_suppressed_hint,
    c_sh.fk_hint AS pk_hint,
    r_vah.title,
    r_vah.hint,
    r_vah.recommendation,
    r_vah.url,
    r_vah.is_active,
    r_vah.source,
    r_vah.query,
    r_vah.lang,
    c_sh.rationale,
    r_vah.popup_type,
    r_vah.highlight_as_priority,
    c_sh.md5_sum AS md5_suppressed,
    r_vah.md5_sum AS md5_hint,
    c_sh.suppressed_by,
    c_sh.suppressed_when,
    c_sh.fk_encounter AS pk_encounter
   FROM (clin.suppressed_hint c_sh
     JOIN ref.v_auto_hints r_vah ON ((c_sh.fk_hint = r_vah.pk_auto_hint)));


ALTER TABLE clin.v_suppressed_hints OWNER TO "gm-dbo";

--
-- Name: v_test_orgs; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_test_orgs AS
 SELECT c_to.pk AS pk_test_org,
    d_o.description AS organization,
    d_ou.description AS unit,
    c_to.comment,
    c_to.contact AS test_org_contact,
    c_to.fk_adm_contact AS pk_adm_contact,
    c_to.fk_med_contact AS pk_med_contact,
    d_o.pk AS pk_org,
    d_o.fk_category AS category_org,
    d_ou.pk AS pk_org_unit,
    d_ou.fk_category AS category_unit,
    d_ou.fk_address AS pk_address_unit,
    c_to.xmin AS xmin_test_org
   FROM ((clin.test_org c_to
     LEFT JOIN dem.org_unit d_ou ON ((c_to.fk_org_unit = d_ou.pk)))
     LEFT JOIN dem.org d_o ON ((d_ou.fk_org = d_o.pk)));


ALTER TABLE clin.v_test_orgs OWNER TO "gm-dbo";

--
-- Name: v_test_panels; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_test_panels AS
 SELECT c_tp.pk AS pk_test_panel,
    c_tp.description,
    c_tp.comment,
    c_tp.modified_when,
    c_tp.modified_by,
    COALESCE(( SELECT array_agg(c_ll2tp.loinc) AS array_agg
           FROM clin.lnk_loinc2test_panel c_ll2tp
          WHERE (c_ll2tp.fk_test_panel = c_tp.pk)), ARRAY[]::text[]) AS loincs,
    ARRAY( SELECT row_to_json(test_type_row.*) AS row_to_json
           FROM ( SELECT c_ll2tp.loinc,
                    c_tt.pk AS pk_test_type,
                    c_tt.fk_meta_test_type AS pk_meta_test_type
                   FROM (clin.lnk_loinc2test_panel c_ll2tp
                     JOIN clin.test_type c_tt ON ((c_ll2tp.loinc = c_tt.loinc)))
                  WHERE (c_ll2tp.fk_test_panel = c_tp.pk)) test_type_row) AS test_types,
    COALESCE(( SELECT array_agg(c_lc2tp.fk_generic_code) AS array_agg
           FROM clin.lnk_code2tst_pnl c_lc2tp
          WHERE (c_lc2tp.fk_item = c_tp.pk)), ARRAY[]::integer[]) AS pk_generic_codes,
    c_tp.row_version,
    c_tp.xmin AS xmin_test_panel
   FROM clin.test_panel c_tp;


ALTER TABLE clin.v_test_panels OWNER TO "gm-dbo";

--
-- Name: vaccination_course; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.vaccination_course (
    pk integer NOT NULL,
    fk_recommended_by integer,
    fk_indication integer NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    comment text
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.vaccination_course OWNER TO "gm-dbo";

--
-- Name: TABLE vaccination_course; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.vaccination_course IS 'holds vaccination courses defined at a techno-medical level for
	 a single indication and will in many cases represent a part of
	 a "recommended multi-epitope schedule",
	 note that one organization can indeed recommend several courses
	 for one and the same indication - which then only differ in their
	 constraints, PostgreSQL does not currently offer the best tools
	 to enforce such constraints';


--
-- Name: COLUMN vaccination_course.fk_recommended_by; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_course.fk_recommended_by IS 'the source/organization which defined this course, can be used to
	 differentiate several locale-dependant courses for the same indication
	 and yet tell them apart';


--
-- Name: COLUMN vaccination_course.fk_indication; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_course.fk_indication IS 'vaccination indication this course is targeted at';


--
-- Name: COLUMN vaccination_course.is_active; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_course.is_active IS 'whether this course is active or not,
	 if False: do not newly *start* patients on this course';


--
-- Name: COLUMN vaccination_course.comment; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_course.comment IS 'a free-text comment on this vaccination course';


--
-- Name: vaccination_schedule; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.vaccination_schedule (
    pk integer NOT NULL,
    name text NOT NULL,
    comment text
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.vaccination_schedule OWNER TO "gm-dbo";

--
-- Name: TABLE vaccination_schedule; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.vaccination_schedule IS 'This table holds schedules as recommended by some authority
	 such as a Vaccination Council. There will be numerous schedules
	 depending on locale, constraints, age group etc. These schedules
	 may be single or multi-epitope depending on their definition.
	 A schedule acts as a convenient handle aggregating possibly several
	 vaccination courses under a common name.';


--
-- Name: COLUMN vaccination_schedule.name; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_schedule.name IS 'name of the schedule as defined by some authority';


--
-- Name: v_vaccination_courses_in_schedule; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_vaccination_courses_in_schedule AS
 SELECT cvs.name AS vaccination_schedule,
    cvc.is_active,
    cvc.fk_recommended_by AS pk_recommended_by,
    cvc.comment AS comment_course,
    cvs.comment AS comment_schedule,
    cvc.pk AS pk_vaccination_course,
    cvc.fk_indication AS pk_indication,
    cvs.pk AS pk_vaccination_schedule
   FROM clin.vaccination_course cvc,
    clin.vaccination_schedule cvs,
    clin.lnk_vaccination_course2schedule clvc2s
  WHERE ((clvc2s.fk_course = cvc.pk) AND (clvc2s.fk_schedule = cvs.pk));


ALTER TABLE clin.v_vaccination_courses_in_schedule OWNER TO "gm-dbo";

--
-- Name: v_vaccinations; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_vaccinations AS
 SELECT c_enc.fk_patient AS pk_patient,
    c_shot.pk AS pk_vaccination,
    c_shot.clin_when AS date_given,
    (now() - c_shot.clin_when) AS interval_since_given,
    r_dp.description AS vaccine,
    ARRAY( SELECT row_to_json(indication_row.*) AS row_to_json
           FROM ( SELECT public._((r_s.atc || '-target'::text), 'en'::text) AS indication,
                        CASE
                            WHEN (public._((r_s.atc || '-target'::text)) = (r_s.atc || '-target'::text)) THEN public._((r_s.atc || '-target'::text), 'en'::text)
                            ELSE public._((r_s.atc || '-target'::text))
                        END AS l10n_indication,
                    r_s.atc AS atc_indication
                   FROM ((ref.lnk_dose2drug r_ld2d
                     JOIN ref.dose r_d ON ((r_d.pk = r_ld2d.fk_dose)))
                     JOIN ref.substance r_s ON ((r_d.fk_substance = r_s.pk)))
                  WHERE (r_ld2d.fk_drug_product = r_dp.pk)) indication_row) AS indications,
    c_shot.site,
    c_shot.batch_no,
    c_shot.reaction,
    c_shot.narrative AS comment,
    c_shot.soap_cat,
    c_shot.modified_when,
    c_shot.modified_by,
    c_shot.row_version,
    c_shot.fk_vaccine AS pk_vaccine,
    c_shot.fk_provider AS pk_provider,
    c_shot.fk_encounter AS pk_encounter,
    c_shot.fk_episode AS pk_episode,
    c_shot.xmin AS xmin_vaccination
   FROM (((clin.vaccination c_shot
     JOIN clin.encounter c_enc ON ((c_enc.pk = c_shot.fk_encounter)))
     JOIN ref.vaccine r_v ON ((r_v.pk = c_shot.fk_vaccine)))
     JOIN ref.drug_product r_dp ON ((r_v.fk_drug_product = r_dp.pk)));


ALTER TABLE clin.v_vaccinations OWNER TO "gm-dbo";

--
-- Name: VIEW v_vaccinations; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON VIEW clin.v_vaccinations IS 'Lists vaccinations for patients';


--
-- Name: waiting_list; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.waiting_list (
    pk integer NOT NULL,
    fk_patient integer NOT NULL,
    registered timestamp with time zone DEFAULT now() NOT NULL,
    urgency integer DEFAULT 0 NOT NULL,
    list_position integer NOT NULL,
    comment text,
    area text,
    CONSTRAINT non_empty_area CHECK ((btrim(area) <> ''::text)),
    CONSTRAINT waiting_list_list_position_check CHECK ((list_position > 0))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.waiting_list OWNER TO "gm-dbo";

--
-- Name: TABLE waiting_list; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.waiting_list IS 'aggregates all the patients currently waiting for an encounter';


--
-- Name: COLUMN waiting_list.fk_patient; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.waiting_list.fk_patient IS 'the waiting patient';


--
-- Name: COLUMN waiting_list.registered; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.waiting_list.registered IS 'when did the patient arrive (enter the waiting list, that is)';


--
-- Name: COLUMN waiting_list.urgency; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.waiting_list.urgency IS 'relative urgency, used by practices as they see fit,
	   0 - "standard" urgency
	 < 0 - less urgent
	 > 0 - more urgent';


--
-- Name: COLUMN waiting_list.list_position; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.waiting_list.list_position IS 'the currently assigned position of
	 this patient on the waiting list';


--
-- Name: COLUMN waiting_list.comment; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.waiting_list.comment IS 'a free comment regarding this entry,
	 NOT THE RFE !';


--
-- Name: COLUMN waiting_list.area; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.waiting_list.area IS 'an arbitrary value by which filtering waiting patients into zones becomes possible';


--
-- Name: v_waiting_list; Type: VIEW; Schema: clin; Owner: gm-dbo
--

CREATE VIEW clin.v_waiting_list AS
 SELECT c_wl.list_position,
    c_wl.area AS waiting_zone,
    c_wl.urgency,
    d_i.title,
    d_n.firstnames,
    d_n.lastnames,
    d_n.preferred AS preferred_name,
    d_i.dob,
    d_i.gender,
    public._(d_i.gender) AS l10n_gender,
    d_i.comment AS comment_identity,
    c_wl.registered,
    ( SELECT (now() - c_wl.registered)) AS waiting_time,
    ( SELECT to_char(age(now(), c_wl.registered), 'DDD HH24:MI'::text) AS to_char) AS waiting_time_formatted,
    c_wl.comment,
    d_i.pk AS pk_identity,
    d_n.id AS pk_name,
    c_wl.pk AS pk_waiting_list
   FROM clin.waiting_list c_wl,
    dem.identity d_i,
    dem.names d_n
  WHERE ((c_wl.fk_patient = d_i.pk) AND (c_wl.fk_patient = d_n.id_identity) AND (d_i.deceased IS NULL) AND (d_n.active IS TRUE));


ALTER TABLE clin.v_waiting_list OWNER TO "gm-dbo";

--
-- Name: vaccination_course_constraint; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.vaccination_course_constraint (
    pk integer NOT NULL,
    description text NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.vaccination_course_constraint OWNER TO "gm-dbo";

--
-- Name: TABLE vaccination_course_constraint; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.vaccination_course_constraint IS 'holds constraints which apply to a vaccination course';


--
-- Name: COLUMN vaccination_course_constraint.description; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_course_constraint.description IS 'description/label/name of the constraint';


--
-- Name: vaccination_course_constraint_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.vaccination_course_constraint_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.vaccination_course_constraint_pk_seq OWNER TO "gm-dbo";

--
-- Name: vaccination_course_constraint_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.vaccination_course_constraint_pk_seq OWNED BY clin.vaccination_course_constraint.pk;


--
-- Name: vaccination_course_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.vaccination_course_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.vaccination_course_pk_seq OWNER TO "gm-dbo";

--
-- Name: vaccination_course_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.vaccination_course_pk_seq OWNED BY clin.vaccination_course.pk;


--
-- Name: vaccination_definition; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.vaccination_definition (
    id integer NOT NULL,
    fk_course integer NOT NULL,
    is_booster boolean DEFAULT false NOT NULL,
    seq_no integer,
    min_age_due interval NOT NULL,
    max_age_due interval DEFAULT '01:32:35'::interval NOT NULL,
    min_interval interval,
    comment text,
    CONSTRAINT numbered_shot_xor_booster CHECK ((((is_booster IS TRUE) AND (seq_no IS NULL)) OR ((is_booster IS FALSE) AND (seq_no > 0)))),
    CONSTRAINT sensible_min_interval CHECK ((((min_interval IS NULL) AND (seq_no = 1)) OR ((min_interval IS NOT NULL) AND (min_interval > '00:00:00'::interval) AND (is_booster IS TRUE)) OR ((min_interval IS NOT NULL) AND (min_interval > '00:00:00'::interval) AND (seq_no > 1)))),
    CONSTRAINT vaccination_definition_check CHECK ((((max_age_due >= min_age_due) AND (max_age_due <= '150 years'::interval)) OR (max_age_due = '01:32:35'::interval))),
    CONSTRAINT vaccination_definition_min_age_due_check CHECK (((min_age_due >= '00:00:01'::interval) AND (min_age_due <= '150 years'::interval)))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.vaccination_definition OWNER TO "gm-dbo";

--
-- Name: TABLE vaccination_definition; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.vaccination_definition IS 'defines a given vaccination event for a particular course';


--
-- Name: COLUMN vaccination_definition.fk_course; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_definition.fk_course IS 'course to which this event belongs';


--
-- Name: COLUMN vaccination_definition.is_booster; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_definition.is_booster IS 'does this definition represent a booster,
	 also set for quasi-booster courses such as
	 Influenza';


--
-- Name: COLUMN vaccination_definition.seq_no; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_definition.seq_no IS 'sequence number for this vaccination event
	 within a particular course,
	 NULL if (is_booster == true)';


--
-- Name: COLUMN vaccination_definition.min_age_due; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_definition.min_age_due IS 'minimum age at which this shot is due';


--
-- Name: COLUMN vaccination_definition.max_age_due; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_definition.max_age_due IS 'maximum age at which this shot is due,
	 if max_age_due = "5555 years": no maximum age';


--
-- Name: COLUMN vaccination_definition.min_interval; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.vaccination_definition.min_interval IS 'if (is_booster == true):
		recommended interval for boostering
	 id (is_booster == false):
	 	minimum interval after previous vaccination,
		NULL if seq_no == 1';


--
-- Name: vaccination_definition_id_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.vaccination_definition_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.vaccination_definition_id_seq OWNER TO "gm-dbo";

--
-- Name: vaccination_definition_id_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.vaccination_definition_id_seq OWNED BY clin.vaccination_definition.id;


--
-- Name: vaccination_id_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.vaccination_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.vaccination_id_seq OWNER TO "gm-dbo";

--
-- Name: vaccination_id_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.vaccination_id_seq OWNED BY clin.vaccination.pk;


--
-- Name: vaccination_schedule_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.vaccination_schedule_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.vaccination_schedule_pk_seq OWNER TO "gm-dbo";

--
-- Name: vaccination_schedule_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.vaccination_schedule_pk_seq OWNED BY clin.vaccination_schedule.pk;


--
-- Name: waiting_list_pk_seq; Type: SEQUENCE; Schema: clin; Owner: gm-dbo
--

CREATE SEQUENCE clin.waiting_list_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE clin.waiting_list_pk_seq OWNER TO "gm-dbo";

--
-- Name: waiting_list_pk_seq; Type: SEQUENCE OWNED BY; Schema: clin; Owner: gm-dbo
--

ALTER SEQUENCE clin.waiting_list_pk_seq OWNED BY clin.waiting_list.pk;


--
-- Name: beh_fall_typ; Type: TABLE; Schema: de_de; Owner: gm-dbo
--

CREATE TABLE de_de.beh_fall_typ (
    pk integer NOT NULL,
    code text NOT NULL,
    kurzform text NOT NULL,
    name text NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE de_de.beh_fall_typ OWNER TO "gm-dbo";

--
-- Name: TABLE beh_fall_typ; Type: COMMENT; Schema: de_de; Owner: gm-dbo
--

COMMENT ON TABLE de_de.beh_fall_typ IS 'Art des Behandlungsfalls (MuVo/Impfung/...)';


--
-- Name: beh_fall_typ_pk_seq; Type: SEQUENCE; Schema: de_de; Owner: gm-dbo
--

CREATE SEQUENCE de_de.beh_fall_typ_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE de_de.beh_fall_typ_pk_seq OWNER TO "gm-dbo";

--
-- Name: beh_fall_typ_pk_seq; Type: SEQUENCE OWNED BY; Schema: de_de; Owner: gm-dbo
--

ALTER SEQUENCE de_de.beh_fall_typ_pk_seq OWNED BY de_de.beh_fall_typ.pk;


--
-- Name: kvk; Type: TABLE; Schema: de_de; Owner: gm-dbo
--

CREATE TABLE de_de.kvk (
    pk integer NOT NULL,
    fk_patient integer NOT NULL,
    kk_name character varying(28) NOT NULL,
    kk_nummer character(7) NOT NULL,
    kvk_nummer character(5),
    mitgliedsnummer character varying(12) NOT NULL,
    mitgliedsstatus character varying(4) NOT NULL,
    zusatzstatus character varying(3),
    titel character varying(15),
    vorname character varying(28),
    namenszuatz character varying(15),
    familienname character varying(28) NOT NULL,
    geburtsdatum character(8) NOT NULL,
    strasse character varying(28),
    landescode character varying(3),
    plz character varying(7) NOT NULL,
    ort character varying(23) NOT NULL,
    gueltigkeit character(4),
    crc character(1) NOT NULL,
    is_valid_address boolean DEFAULT true,
    valid_since timestamp with time zone NOT NULL,
    presented timestamp with time zone[] NOT NULL,
    invalidated timestamp with time zone
);


ALTER TABLE de_de.kvk OWNER TO "gm-dbo";

--
-- Name: TABLE kvk; Type: COMMENT; Schema: de_de; Owner: gm-dbo
--

COMMENT ON TABLE de_de.kvk IS 'Speichert die Daten einer bestimmten KVK. Wir trennen die KVK-Daten von
	 den Daten ueber Person, Wohnort, Kassenzugehoerigkeit, Mitgliedsstatus und
	 Abrechnungsfaellen. Diese Daten werden jedoch a) als Vorgaben fuer die
	 eigentlichen Personendaten und b) als gueltig fuer abrechnungstechnische
	 Belange angesehen.';


--
-- Name: COLUMN kvk.invalidated; Type: COMMENT; Schema: de_de; Owner: gm-dbo
--

COMMENT ON COLUMN de_de.kvk.invalidated IS 'Kann durchaus vor Ende von "Gueltigkeit" liegen. Zeitpunkt des
	 Austritts aus der Krankenkasse. Beim Setzen dieses Feldes muss
	 auch die Zuzahlungsbefreiung auf NULL gesetzt werden.';


--
-- Name: kvk_pk_seq; Type: SEQUENCE; Schema: de_de; Owner: gm-dbo
--

CREATE SEQUENCE de_de.kvk_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE de_de.kvk_pk_seq OWNER TO "gm-dbo";

--
-- Name: kvk_pk_seq; Type: SEQUENCE OWNED BY; Schema: de_de; Owner: gm-dbo
--

ALTER SEQUENCE de_de.kvk_pk_seq OWNED BY de_de.kvk.pk;


--
-- Name: lab_test_gnr; Type: TABLE; Schema: de_de; Owner: gm-dbo
--

CREATE TABLE de_de.lab_test_gnr (
    id integer NOT NULL,
    id_test integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE de_de.lab_test_gnr OWNER TO "gm-dbo";

--
-- Name: TABLE lab_test_gnr; Type: COMMENT; Schema: de_de; Owner: gm-dbo
--

COMMENT ON TABLE de_de.lab_test_gnr IS 'specific for Germany, GNR = GebuehrenordnungsNummeR = billing
	 item, build index before lab import and drop afterwards, check
	 against this table when importing, build table during import';


--
-- Name: COLUMN lab_test_gnr.id_test; Type: COMMENT; Schema: de_de; Owner: gm-dbo
--

COMMENT ON COLUMN de_de.lab_test_gnr.id_test IS 'link to test in our practice';


--
-- Name: lab_test_gnr_id_seq; Type: SEQUENCE; Schema: de_de; Owner: gm-dbo
--

CREATE SEQUENCE de_de.lab_test_gnr_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE de_de.lab_test_gnr_id_seq OWNER TO "gm-dbo";

--
-- Name: lab_test_gnr_id_seq; Type: SEQUENCE OWNED BY; Schema: de_de; Owner: gm-dbo
--

ALTER SEQUENCE de_de.lab_test_gnr_id_seq OWNED BY de_de.lab_test_gnr.id;


--
-- Name: payment_method; Type: TABLE; Schema: de_de; Owner: gm-dbo
--

CREATE TABLE de_de.payment_method (
    pk integer NOT NULL,
    description text NOT NULL
);


ALTER TABLE de_de.payment_method OWNER TO "gm-dbo";

--
-- Name: payment_method_pk_seq; Type: SEQUENCE; Schema: de_de; Owner: gm-dbo
--

CREATE SEQUENCE de_de.payment_method_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE de_de.payment_method_pk_seq OWNER TO "gm-dbo";

--
-- Name: payment_method_pk_seq; Type: SEQUENCE OWNED BY; Schema: de_de; Owner: gm-dbo
--

ALTER SEQUENCE de_de.payment_method_pk_seq OWNED BY de_de.payment_method.pk;


--
-- Name: prax_geb_paid; Type: TABLE; Schema: de_de; Owner: gm-dbo
--

CREATE TABLE de_de.prax_geb_paid (
    pk integer NOT NULL,
    fk_fall integer NOT NULL,
    paid_amount numeric DEFAULT 0 NOT NULL,
    paid_when date DEFAULT ('now'::text)::date NOT NULL,
    paid_with integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE de_de.prax_geb_paid OWNER TO "gm-dbo";

--
-- Name: prax_geb_paid_pk_seq; Type: SEQUENCE; Schema: de_de; Owner: gm-dbo
--

CREATE SEQUENCE de_de.prax_geb_paid_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE de_de.prax_geb_paid_pk_seq OWNER TO "gm-dbo";

--
-- Name: prax_geb_paid_pk_seq; Type: SEQUENCE OWNED BY; Schema: de_de; Owner: gm-dbo
--

ALTER SEQUENCE de_de.prax_geb_paid_pk_seq OWNED BY de_de.prax_geb_paid.pk;


--
-- Name: zuzahlungsbefreiung; Type: TABLE; Schema: de_de; Owner: gm-dbo
--

CREATE TABLE de_de.zuzahlungsbefreiung (
    id integer NOT NULL,
    id_patient integer,
    medikamente date,
    heilmittel date,
    hilfsmittel date,
    presented timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE de_de.zuzahlungsbefreiung OWNER TO "gm-dbo";

--
-- Name: zuzahlungsbefreiung_id_seq; Type: SEQUENCE; Schema: de_de; Owner: gm-dbo
--

CREATE SEQUENCE de_de.zuzahlungsbefreiung_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE de_de.zuzahlungsbefreiung_id_seq OWNER TO "gm-dbo";

--
-- Name: zuzahlungsbefreiung_id_seq; Type: SEQUENCE OWNED BY; Schema: de_de; Owner: gm-dbo
--

ALTER SEQUENCE de_de.zuzahlungsbefreiung_id_seq OWNED BY de_de.zuzahlungsbefreiung.id;


--
-- Name: address; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.address (
    id integer NOT NULL,
    id_street integer NOT NULL,
    aux_street text,
    number text NOT NULL,
    subunit text,
    addendum text,
    lat_lon point
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.address OWNER TO "gm-dbo";

--
-- Name: TABLE address; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.address IS 'an address aka a location, void of attached meaning such as type of address';


--
-- Name: COLUMN address.id_street; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.address.id_street IS 'the street this address is at from
	 whence the urb is to be found, it
	 thus indirectly references dem.urb(id)';


--
-- Name: COLUMN address.aux_street; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.address.aux_street IS 'additional street-level information which
	 formatters would usually put on lines directly
	 below the street line of an address, such as
	 postal box directions in CA';


--
-- Name: COLUMN address.number; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.address.number IS 'number of the house';


--
-- Name: COLUMN address.subunit; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.address.subunit IS 'directions *below* the unit (eg.number) level,
	 such as appartment number, room number, level,
	 entrance or even verbal directions';


--
-- Name: COLUMN address.addendum; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.address.addendum IS 'any additional information that
	 did not fit anywhere else';


--
-- Name: COLUMN address.lat_lon; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.address.lat_lon IS 'the exact location of this address in latitude-longtitude';


--
-- Name: address_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.address_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.address_id_seq OWNER TO "gm-dbo";

--
-- Name: address_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.address_id_seq OWNED BY dem.address.id;


--
-- Name: address_type; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.address_type (
    id integer NOT NULL,
    name text NOT NULL
);


ALTER TABLE dem.address_type OWNER TO "gm-dbo";

--
-- Name: address_type_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.address_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.address_type_id_seq OWNER TO "gm-dbo";

--
-- Name: address_type_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.address_type_id_seq OWNED BY dem.address_type.id;


--
-- Name: country; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.country (
    id integer NOT NULL,
    code character(2) NOT NULL,
    name text NOT NULL,
    deprecated date,
    CONSTRAINT no_linebreaks CHECK ((("position"(((COALESCE(code, ''::bpchar))::text || COALESCE(name, ''::text)), ''::text) = 0) AND ("position"(((COALESCE(code, ''::bpchar))::text || COALESCE(name, ''::text)), '
'::text) = 0) AND ("position"(((COALESCE(code, ''::bpchar))::text || COALESCE(name, ''::text)), '
'::text) = 0) AND ("position"(((COALESCE(code, ''::bpchar))::text || COALESCE(name, ''::text)), ''::text) = 0)))
);


ALTER TABLE dem.country OWNER TO "gm-dbo";

--
-- Name: TABLE country; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.country IS 'countries coded per ISO 3166-1';


--
-- Name: COLUMN country.code; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.country.code IS 'international two character country code as per ISO 3166-1';


--
-- Name: COLUMN country.deprecated; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.country.deprecated IS 'date when this country ceased officially to exist (if applicable)';


--
-- Name: country_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.country_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.country_id_seq OWNER TO "gm-dbo";

--
-- Name: country_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.country_id_seq OWNED BY dem.country.id;


--
-- Name: enum_comm_types; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.enum_comm_types (
    pk integer NOT NULL,
    description text NOT NULL
);


ALTER TABLE dem.enum_comm_types OWNER TO "gm-dbo";

--
-- Name: enum_comm_types_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.enum_comm_types_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.enum_comm_types_id_seq OWNER TO "gm-dbo";

--
-- Name: enum_comm_types_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.enum_comm_types_id_seq OWNED BY dem.enum_comm_types.pk;


--
-- Name: enum_ext_id_types; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.enum_ext_id_types (
    pk integer NOT NULL,
    name text,
    issuer text
);


ALTER TABLE dem.enum_ext_id_types OWNER TO "gm-dbo";

--
-- Name: TABLE enum_ext_id_types; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.enum_ext_id_types IS 'a list of all bureaucratic IDs/serial numbers/3rd party primary keys, etc.';


--
-- Name: COLUMN enum_ext_id_types.issuer; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.enum_ext_id_types.issuer IS 'the authority/system issuing the number';


--
-- Name: enum_ext_id_types_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.enum_ext_id_types_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.enum_ext_id_types_pk_seq OWNER TO "gm-dbo";

--
-- Name: enum_ext_id_types_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.enum_ext_id_types_pk_seq OWNED BY dem.enum_ext_id_types.pk;


--
-- Name: gender_label; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.gender_label (
    pk integer NOT NULL,
    tag text NOT NULL,
    label text NOT NULL,
    sort_weight integer NOT NULL,
    comment text NOT NULL,
    CONSTRAINT gender_label_tag_check CHECK ((tag = ANY (ARRAY['m'::text, 'f'::text, 'h'::text, 'tm'::text, 'tf'::text])))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.gender_label OWNER TO "gm-dbo";

--
-- Name: TABLE gender_label; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.gender_label IS 'This table stores the genders known to GNUmed.
	 FIXME: cross-check with CDA:administrative-gender-code';


--
-- Name: gender_label_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.gender_label_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.gender_label_pk_seq OWNER TO "gm-dbo";

--
-- Name: gender_label_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.gender_label_pk_seq OWNED BY dem.gender_label.pk;


--
-- Name: identity_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.identity_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.identity_pk_seq OWNER TO "gm-dbo";

--
-- Name: identity_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.identity_pk_seq OWNED BY dem.identity.pk;


--
-- Name: identity_tag; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.identity_tag (
    pk integer NOT NULL,
    fk_identity integer NOT NULL,
    fk_tag integer NOT NULL,
    comment text,
    CONSTRAINT dem_identity_tag_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.identity_tag OWNER TO "gm-dbo";

--
-- Name: TABLE identity_tag; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.identity_tag IS 'tags attached to this identity';


--
-- Name: identity_tag_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.identity_tag_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.identity_tag_pk_seq OWNER TO "gm-dbo";

--
-- Name: identity_tag_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.identity_tag_pk_seq OWNED BY dem.identity_tag.pk;


--
-- Name: inbox_item_category_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.inbox_item_category_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.inbox_item_category_pk_seq OWNER TO "gm-dbo";

--
-- Name: inbox_item_category_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.inbox_item_category_pk_seq OWNED BY dem.inbox_item_category.pk;


--
-- Name: inbox_item_type_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.inbox_item_type_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.inbox_item_type_pk_seq OWNER TO "gm-dbo";

--
-- Name: inbox_item_type_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.inbox_item_type_pk_seq OWNED BY dem.inbox_item_type.pk;


--
-- Name: lnk_identity2comm; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.lnk_identity2comm (
    pk integer NOT NULL,
    fk_identity integer NOT NULL,
    fk_address integer,
    url text NOT NULL,
    fk_type integer NOT NULL,
    is_confidential boolean DEFAULT false NOT NULL,
    comment text,
    CONSTRAINT d_l_i2comm_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT lnk_identity2comm_url_check CHECK ((btrim(url) <> ''::text))
);


ALTER TABLE dem.lnk_identity2comm OWNER TO "gm-dbo";

--
-- Name: COLUMN lnk_identity2comm.comment; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_identity2comm.comment IS 'a comment on this communications channel';


--
-- Name: lnk_identity2comm_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.lnk_identity2comm_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.lnk_identity2comm_id_seq OWNER TO "gm-dbo";

--
-- Name: lnk_identity2comm_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.lnk_identity2comm_id_seq OWNED BY dem.lnk_identity2comm.pk;


--
-- Name: lnk_identity2ext_id; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.lnk_identity2ext_id (
    id integer NOT NULL,
    id_identity integer NOT NULL,
    external_id text NOT NULL,
    fk_origin integer NOT NULL,
    comment text
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.lnk_identity2ext_id OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_identity2ext_id; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.lnk_identity2ext_id IS 'link external IDs to GnuMed identities';


--
-- Name: COLUMN lnk_identity2ext_id.external_id; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_identity2ext_id.external_id IS 'textual representation of external ID which
	 may be Social Security Number, patient ID of
	 another EMR system, you-name-it';


--
-- Name: COLUMN lnk_identity2ext_id.fk_origin; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_identity2ext_id.fk_origin IS 'originating system';


--
-- Name: lnk_identity2ext_id_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.lnk_identity2ext_id_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.lnk_identity2ext_id_id_seq OWNER TO "gm-dbo";

--
-- Name: lnk_identity2ext_id_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.lnk_identity2ext_id_id_seq OWNED BY dem.lnk_identity2ext_id.id;


--
-- Name: lnk_job2person; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.lnk_job2person (
    pk integer NOT NULL,
    fk_identity integer NOT NULL,
    fk_occupation integer NOT NULL,
    activities text
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.lnk_job2person OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_job2person; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.lnk_job2person IS 'linking (possibly several) jobs to a person';


--
-- Name: COLUMN lnk_job2person.activities; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_job2person.activities IS 'describes activities the person is usually
	 carrying out when working at this job';


--
-- Name: lnk_job2person_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.lnk_job2person_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.lnk_job2person_id_seq OWNER TO "gm-dbo";

--
-- Name: lnk_job2person_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.lnk_job2person_id_seq OWNED BY dem.lnk_job2person.pk;


--
-- Name: lnk_org_unit2comm; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.lnk_org_unit2comm (
    pk integer NOT NULL,
    fk_org_unit integer NOT NULL,
    url text,
    fk_type integer NOT NULL,
    is_confidential boolean DEFAULT false NOT NULL,
    comment text,
    CONSTRAINT dem_lnk_unit2comm_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT lnk_org_unit2comm_sane_url CHECK ((gm.is_null_or_blank_string(url) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.lnk_org_unit2comm OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_org_unit2comm; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.lnk_org_unit2comm IS 'Comm channels per org unit.';


--
-- Name: COLUMN lnk_org_unit2comm.comment; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_org_unit2comm.comment IS 'a comment on this comm channel';


--
-- Name: lnk_org_unit2comm_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.lnk_org_unit2comm_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.lnk_org_unit2comm_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_org_unit2comm_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.lnk_org_unit2comm_pk_seq OWNED BY dem.lnk_org_unit2comm.pk;


--
-- Name: lnk_org_unit2ext_id; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.lnk_org_unit2ext_id (
    pk integer NOT NULL,
    fk_org_unit integer NOT NULL,
    external_id text,
    fk_type integer NOT NULL,
    comment text,
    CONSTRAINT lnk_org_unit2ext_id_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)),
    CONSTRAINT lnk_org_unit2ext_id_sane_id CHECK ((gm.is_null_or_blank_string(external_id) IS FALSE))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.lnk_org_unit2ext_id OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_org_unit2ext_id; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.lnk_org_unit2ext_id IS 'External IDs per org unit.';


--
-- Name: lnk_org_unit2ext_id_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.lnk_org_unit2ext_id_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.lnk_org_unit2ext_id_pk_seq OWNER TO "gm-dbo";

--
-- Name: lnk_org_unit2ext_id_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.lnk_org_unit2ext_id_pk_seq OWNED BY dem.lnk_org_unit2ext_id.pk;


--
-- Name: lnk_person_org_address; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.lnk_person_org_address (
    id integer NOT NULL,
    id_identity integer,
    id_address integer,
    id_type integer DEFAULT 1,
    address_source text,
    id_org integer
);


ALTER TABLE dem.lnk_person_org_address OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_person_org_address; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.lnk_person_org_address IS 'a many-to-many pivot table describing the relationship
	 between an organisation, a person, their work address and
	 their occupation at that location.
	 For patients id_org is NULL';


--
-- Name: COLUMN lnk_person_org_address.id_identity; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_person_org_address.id_identity IS 'identity to which the address belongs';


--
-- Name: COLUMN lnk_person_org_address.id_address; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_person_org_address.id_address IS 'address belonging to this identity (the branch of the organisation)';


--
-- Name: COLUMN lnk_person_org_address.id_type; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_person_org_address.id_type IS 'type of this address (like home, work, parents, holidays ...)';


--
-- Name: lnk_person2address; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.lnk_person2address AS
 SELECT lnk_person_org_address.id_identity,
    lnk_person_org_address.id_address,
    lnk_person_org_address.id_type
   FROM dem.lnk_person_org_address;


ALTER TABLE dem.lnk_person2address OWNER TO "gm-dbo";

--
-- Name: lnk_person2relative; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.lnk_person2relative (
    id integer NOT NULL,
    id_identity integer NOT NULL,
    id_relative integer NOT NULL,
    id_relation_type integer NOT NULL,
    started date,
    ended date
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.lnk_person2relative OWNER TO "gm-dbo";

--
-- Name: TABLE lnk_person2relative; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.lnk_person2relative IS 'biological and social relationships between an identity and other identities';


--
-- Name: COLUMN lnk_person2relative.id_identity; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_person2relative.id_identity IS 'primary identity to whom the relationship applies';


--
-- Name: COLUMN lnk_person2relative.id_relative; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_person2relative.id_relative IS 'referred-to identity of this relationship (e.g. "child"
	 if id_identity points to the father and id_relation_type
	 points to "parent")';


--
-- Name: COLUMN lnk_person2relative.started; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_person2relative.started IS 'date when this relationship began';


--
-- Name: COLUMN lnk_person2relative.ended; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.lnk_person2relative.ended IS 'date when this relationship ended, biological
	 relationships do not end !';


--
-- Name: lnk_person2relative_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.lnk_person2relative_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.lnk_person2relative_id_seq OWNER TO "gm-dbo";

--
-- Name: lnk_person2relative_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.lnk_person2relative_id_seq OWNED BY dem.lnk_person2relative.id;


--
-- Name: lnk_person_org_address_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.lnk_person_org_address_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.lnk_person_org_address_id_seq OWNER TO "gm-dbo";

--
-- Name: lnk_person_org_address_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.lnk_person_org_address_id_seq OWNED BY dem.lnk_person_org_address.id;


--
-- Name: marital_status_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.marital_status_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.marital_status_pk_seq OWNER TO "gm-dbo";

--
-- Name: marital_status_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.marital_status_pk_seq OWNED BY dem.marital_status.pk;


--
-- Name: name_gender_map; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.name_gender_map (
    id integer NOT NULL,
    name text NOT NULL,
    gender character(1),
    CONSTRAINT name_gender_map_gender_check CHECK ((gender = ANY (ARRAY['m'::bpchar, 'f'::bpchar])))
);


ALTER TABLE dem.name_gender_map OWNER TO "gm-dbo";

--
-- Name: TABLE name_gender_map; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.name_gender_map IS 'maps (first) names to their most frequently locally assigned gender,
	 this table is updated nightly by a cron script,
	 names whose gender distribution is between 70/30 and 30/70 are
	 ignored for ambiguity reasons,
	 names with "ambigous" gender are also ignored';


--
-- Name: name_gender_map_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.name_gender_map_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.name_gender_map_id_seq OWNER TO "gm-dbo";

--
-- Name: name_gender_map_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.name_gender_map_id_seq OWNED BY dem.name_gender_map.id;


--
-- Name: names_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.names_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.names_id_seq OWNER TO "gm-dbo";

--
-- Name: names_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.names_id_seq OWNED BY dem.names.id;


--
-- Name: occupation; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.occupation (
    id integer NOT NULL,
    name text NOT NULL,
    CONSTRAINT occupation_name_check CHECK ((btrim(name) <> ''::text))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.occupation OWNER TO "gm-dbo";

--
-- Name: TABLE occupation; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.occupation IS 'collects occupation names';


--
-- Name: occupation_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.occupation_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.occupation_id_seq OWNER TO "gm-dbo";

--
-- Name: occupation_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.occupation_id_seq OWNED BY dem.occupation.id;


--
-- Name: org_category; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.org_category (
    pk integer NOT NULL,
    description text NOT NULL
);


ALTER TABLE dem.org_category OWNER TO "gm-dbo";

--
-- Name: org_category_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.org_category_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.org_category_id_seq OWNER TO "gm-dbo";

--
-- Name: org_category_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.org_category_id_seq OWNED BY dem.org_category.pk;


--
-- Name: org_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.org_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.org_pk_seq OWNER TO "gm-dbo";

--
-- Name: org_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.org_pk_seq OWNED BY dem.org.pk;


--
-- Name: org_unit_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.org_unit_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.org_unit_pk_seq OWNER TO "gm-dbo";

--
-- Name: org_unit_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.org_unit_pk_seq OWNED BY dem.org_unit.pk;


--
-- Name: praxis_branch; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.praxis_branch (
    pk integer NOT NULL,
    fk_org_unit integer NOT NULL
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.praxis_branch OWNER TO "gm-dbo";

--
-- Name: TABLE praxis_branch; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.praxis_branch IS 'Defines one branch of a praxis (which itself is a dem.org)';


--
-- Name: praxis_branch_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.praxis_branch_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.praxis_branch_pk_seq OWNER TO "gm-dbo";

--
-- Name: praxis_branch_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.praxis_branch_pk_seq OWNED BY dem.praxis_branch.pk;


--
-- Name: provider_inbox_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.provider_inbox_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.provider_inbox_pk_seq OWNER TO "gm-dbo";

--
-- Name: provider_inbox_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.provider_inbox_pk_seq OWNED BY dem.message_inbox.pk;


--
-- Name: region; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.region (
    pk integer NOT NULL,
    code text NOT NULL,
    country character(2) NOT NULL,
    name text NOT NULL,
    CONSTRAINT no_linebreaks CHECK ((("position"(((COALESCE(code, ''::text) || (COALESCE(country, ''::bpchar))::text) || COALESCE(name, ''::text)), ''::text) = 0) AND ("position"(((COALESCE(code, ''::text) || (COALESCE(country, ''::bpchar))::text) || COALESCE(name, ''::text)), '
'::text) = 0) AND ("position"(((COALESCE(code, ''::text) || (COALESCE(country, ''::bpchar))::text) || COALESCE(name, ''::text)), '
'::text) = 0) AND ("position"(((COALESCE(code, ''::text) || (COALESCE(country, ''::bpchar))::text) || COALESCE(name, ''::text)), ''::text) = 0)))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.region OWNER TO "gm-dbo";

--
-- Name: TABLE region; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.region IS 'region codes (country specific);
	 Richard agreed we should require pre-existence,
	 allow user to mail details for adding a state to developers';


--
-- Name: COLUMN region.code; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.region.code IS 'region code';


--
-- Name: COLUMN region.country; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.region.country IS '2 character ISO 3166-1 country code';


--
-- Name: relation_types; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.relation_types (
    id integer NOT NULL,
    inverse integer,
    biological boolean NOT NULL,
    biol_verified boolean DEFAULT false,
    description text
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.relation_types OWNER TO "gm-dbo";

--
-- Name: TABLE relation_types; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.relation_types IS 'types of biological/social relationships between identities';


--
-- Name: COLUMN relation_types.biological; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.relation_types.biological IS 'true if relationship is biological (proven or
	 reasonable assumption), else false';


--
-- Name: COLUMN relation_types.biol_verified; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.relation_types.biol_verified IS 'ONLY true if there is genetic proof for this relationship';


--
-- Name: COLUMN relation_types.description; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.relation_types.description IS 'plain text description of relationship';


--
-- Name: relation_types_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.relation_types_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.relation_types_id_seq OWNER TO "gm-dbo";

--
-- Name: relation_types_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.relation_types_id_seq OWNED BY dem.relation_types.id;


--
-- Name: staff_pk_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.staff_pk_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.staff_pk_seq OWNER TO "gm-dbo";

--
-- Name: staff_pk_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.staff_pk_seq OWNED BY dem.staff.pk;


--
-- Name: state_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.state_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.state_id_seq OWNER TO "gm-dbo";

--
-- Name: state_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.state_id_seq OWNED BY dem.region.pk;


--
-- Name: street; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.street (
    id integer NOT NULL,
    id_urb integer NOT NULL,
    name text NOT NULL,
    postcode text,
    suburb text,
    lat_lon point,
    CONSTRAINT no_linebreaks CHECK ((("position"(((COALESCE(postcode, ''::text) || COALESCE(suburb, ''::text)) || COALESCE(name, ''::text)), ''::text) = 0) AND ("position"(((COALESCE(postcode, ''::text) || COALESCE(suburb, ''::text)) || COALESCE(name, ''::text)), '
'::text) = 0) AND ("position"(((COALESCE(postcode, ''::text) || COALESCE(suburb, ''::text)) || COALESCE(name, ''::text)), '
'::text) = 0) AND ("position"(((COALESCE(postcode, ''::text) || COALESCE(suburb, ''::text)) || COALESCE(name, ''::text)), ''::text) = 0)))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.street OWNER TO "gm-dbo";

--
-- Name: TABLE street; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.street IS 'street names, specific for distinct "urbs"';


--
-- Name: COLUMN street.id_urb; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.street.id_urb IS 'reference to information postcode, city, country and region';


--
-- Name: COLUMN street.name; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.street.name IS 'name of this street';


--
-- Name: COLUMN street.postcode; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.street.postcode IS 'postcode for systems (such as UK Royal Mail) which specify the street';


--
-- Name: COLUMN street.suburb; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.street.suburb IS 'the suburb this street is in (if any)';


--
-- Name: COLUMN street.lat_lon; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.street.lat_lon IS 'the approximate location of the street, as lat/long co-ordinates';


--
-- Name: street_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.street_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.street_id_seq OWNER TO "gm-dbo";

--
-- Name: street_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.street_id_seq OWNED BY dem.street.id;


--
-- Name: urb; Type: TABLE; Schema: dem; Owner: gm-dbo
--

CREATE TABLE dem.urb (
    id integer NOT NULL,
    fk_region integer NOT NULL,
    postcode text NOT NULL,
    lat_lon point,
    name text NOT NULL,
    CONSTRAINT no_linebreaks CHECK ((("position"((COALESCE(postcode, ''::text) || COALESCE(name, ''::text)), ''::text) = 0) AND ("position"((COALESCE(postcode, ''::text) || COALESCE(name, ''::text)), '
'::text) = 0) AND ("position"((COALESCE(postcode, ''::text) || COALESCE(name, ''::text)), '
'::text) = 0) AND ("position"((COALESCE(postcode, ''::text) || COALESCE(name, ''::text)), ''::text) = 0)))
)
INHERITS (audit.audit_fields);


ALTER TABLE dem.urb OWNER TO "gm-dbo";

--
-- Name: TABLE urb; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON TABLE dem.urb IS 'cities, towns, dwellings ..., eg. "official" places of residence';


--
-- Name: COLUMN urb.fk_region; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.urb.fk_region IS 'reference to information about country and region';


--
-- Name: COLUMN urb.postcode; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.urb.postcode IS 'default postcode for urb.name,
	 useful for all the smaller urbs that only have one postcode,
	 also useful as a default when adding new streets to an urb';


--
-- Name: COLUMN urb.lat_lon; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.urb.lat_lon IS 'the location of the urb, as lat/long co-ordinates. Ideally this would be NOT NULL';


--
-- Name: COLUMN urb.name; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON COLUMN dem.urb.name IS 'the name of the city/town/dwelling';


--
-- Name: urb_id_seq; Type: SEQUENCE; Schema: dem; Owner: gm-dbo
--

CREATE SEQUENCE dem.urb_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE dem.urb_id_seq OWNER TO "gm-dbo";

--
-- Name: urb_id_seq; Type: SEQUENCE OWNED BY; Schema: dem; Owner: gm-dbo
--

ALTER SEQUENCE dem.urb_id_seq OWNED BY dem.urb.id;


--
-- Name: v_active_persons; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_active_persons AS
 SELECT d_vp.pk_identity,
    d_vp.title,
    d_vp.firstnames,
    d_vp.preferred,
    d_vp.lastnames,
    d_vp.gender,
    d_vp.l10n_gender,
    d_vp.dob_only,
    d_vp.dob,
    d_vp.tob,
    d_vp.deceased,
    d_vp.marital_status,
    d_vp.l10n_marital_status,
    d_vp.emergency_contact,
    d_vp.comment,
    d_vp.is_deleted,
    d_vp.pk_marital_status,
    d_vp.pk_active_name,
    d_vp.pk_emergency_contact,
    d_vp.pk_primary_provider,
    d_vp.xmin_identity,
    d_vp.dob_is_estimated
   FROM dem.v_all_persons d_vp
  WHERE (d_vp.is_deleted IS FALSE);


ALTER TABLE dem.v_active_persons OWNER TO "gm-dbo";

--
-- Name: VIEW v_active_persons; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON VIEW dem.v_active_persons IS 'This view denormalizes non-deleted persons with their active name.';


--
-- Name: v_address; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_address AS
 SELECT d_adr.id AS pk_address,
    d_str.name AS street,
    COALESCE(d_str.postcode, d_u.postcode) AS postcode,
    d_adr.aux_street AS notes_street,
    d_adr.number,
    d_adr.subunit,
    d_adr.addendum AS notes_subunit,
    d_adr.lat_lon AS lat_lon_address,
    d_str.postcode AS postcode_street,
    d_str.lat_lon AS lat_lon_street,
    d_str.suburb,
    d_u.name AS urb,
    d_u.postcode AS postcode_urb,
    d_u.lat_lon AS lat_lon_urb,
    d_r.code AS code_region,
    d_r.name AS region,
    public._(d_r.name) AS l10n_region,
    d_r.country AS code_country,
    d_c.name AS country,
    public._(d_c.name) AS l10n_country,
    d_c.deprecated AS country_deprecated,
    d_adr.id_street AS pk_street,
    d_u.id AS pk_urb,
    d_r.pk AS pk_region,
    d_adr.xmin AS xmin_address
   FROM ((((dem.address d_adr
     LEFT JOIN dem.street d_str ON ((d_adr.id_street = d_str.id)))
     LEFT JOIN dem.urb d_u ON ((d_str.id_urb = d_u.id)))
     LEFT JOIN dem.region d_r ON ((d_u.fk_region = d_r.pk)))
     LEFT JOIN dem.country d_c ON ((d_c.code = d_r.country)));


ALTER TABLE dem.v_address OWNER TO "gm-dbo";

--
-- Name: VIEW v_address; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON VIEW dem.v_address IS 'fully denormalizes data about addresses as entities in themselves';


--
-- Name: v_basic_address; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_basic_address AS
 SELECT d_adr.id,
    d_r.country AS country_code,
    d_r.code AS region_code,
    d_r.name AS region,
    d_c.name AS country,
    COALESCE(d_str.postcode, d_u.postcode) AS postcode,
    d_u.name AS urb,
    d_adr.number,
    d_str.name AS street,
    d_adr.addendum,
    COALESCE(d_adr.lat_lon, d_str.lat_lon, d_u.lat_lon) AS lat_lon
   FROM dem.address d_adr,
    dem.region d_r,
    dem.country d_c,
    dem.urb d_u,
    dem.street d_str
  WHERE ((d_r.country = d_c.code) AND (d_adr.id_street = d_str.id) AND (d_str.id_urb = d_u.id) AND (d_u.fk_region = d_r.pk));


ALTER TABLE dem.v_basic_address OWNER TO "gm-dbo";

--
-- Name: v_deleted_persons; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_deleted_persons AS
 SELECT d_vp.pk_identity,
    d_vp.title,
    d_vp.firstnames,
    d_vp.preferred,
    d_vp.lastnames,
    d_vp.gender,
    d_vp.l10n_gender,
    d_vp.dob_only,
    d_vp.dob,
    d_vp.tob,
    d_vp.deceased,
    d_vp.marital_status,
    d_vp.l10n_marital_status,
    d_vp.emergency_contact,
    d_vp.comment,
    d_vp.is_deleted,
    d_vp.pk_marital_status,
    d_vp.pk_active_name,
    d_vp.pk_emergency_contact,
    d_vp.pk_primary_provider,
    d_vp.xmin_identity,
    d_vp.dob_is_estimated
   FROM dem.v_all_persons d_vp
  WHERE (d_vp.is_deleted IS TRUE);


ALTER TABLE dem.v_deleted_persons OWNER TO "gm-dbo";

--
-- Name: VIEW v_deleted_persons; Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON VIEW dem.v_deleted_persons IS 'This view denormalizes "deleted" persons with their active name.';


--
-- Name: v_external_ids4identity; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_external_ids4identity AS
 SELECT li2ei.id_identity AS pk_identity,
    li2ei.id AS pk_id,
    eit.name,
    li2ei.external_id AS value,
    eit.issuer,
    li2ei.comment,
    li2ei.fk_origin AS pk_type
   FROM dem.lnk_identity2ext_id li2ei,
    dem.enum_ext_id_types eit
  WHERE (li2ei.fk_origin = eit.pk);


ALTER TABLE dem.v_external_ids4identity OWNER TO "gm-dbo";

--
-- Name: v_external_ids4org_unit; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_external_ids4org_unit AS
 SELECT d_lou2ei.fk_org_unit AS pk_org_unit,
    d_lou2ei.pk AS pk_id,
    d_eit.name,
    d_lou2ei.external_id AS value,
    d_eit.issuer,
    d_lou2ei.comment,
    d_lou2ei.fk_type AS pk_type
   FROM (dem.lnk_org_unit2ext_id d_lou2ei
     JOIN dem.enum_ext_id_types d_eit ON ((d_lou2ei.fk_type = d_eit.pk)));


ALTER TABLE dem.v_external_ids4org_unit OWNER TO "gm-dbo";

--
-- Name: v_gender_labels; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_gender_labels AS
 SELECT gl.tag,
    public._(gl.tag) AS l10n_tag,
    gl.label,
    public._(gl.label) AS l10n_label,
    gl.comment,
    gl.sort_weight,
    gl.pk AS pk_gender_label
   FROM dem.gender_label gl;


ALTER TABLE dem.v_gender_labels OWNER TO "gm-dbo";

--
-- Name: tag_image; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.tag_image (
    pk integer NOT NULL,
    description text,
    filename text,
    image bytea NOT NULL,
    CONSTRAINT ref_tag_image_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE)),
    CONSTRAINT ref_tag_image_sane_filename CHECK ((gm.is_null_or_non_empty_string(filename) IS TRUE))
)
INHERITS (audit.audit_fields);


ALTER TABLE ref.tag_image OWNER TO "gm-dbo";

--
-- Name: TABLE tag_image; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.tag_image IS 'Text+image tags that can be applied to a person for characterization.';


--
-- Name: COLUMN tag_image.description; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.tag_image.description IS 'A textual description of the meaning of the tag. Keep this reasonably short.';


--
-- Name: COLUMN tag_image.filename; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.tag_image.filename IS 'An example filename, mainly for preserving the file suffix. Set during import, suffix used during export.';


--
-- Name: COLUMN tag_image.image; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.tag_image.image IS 'An image showing the meaning of the tag.';


--
-- Name: v_identity_tags; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_identity_tags AS
 SELECT dit.fk_identity AS pk_identity,
    rti.description,
    public._(rti.description) AS l10n_description,
    dit.comment,
    rti.filename,
    octet_length(COALESCE(rti.image, '\x'::bytea)) AS image_size,
    dit.pk AS pk_identity_tag,
    rti.pk AS pk_tag_image,
    dit.xmin AS xmin_identity_tag
   FROM (dem.identity_tag dit
     LEFT JOIN ref.tag_image rti ON ((dit.fk_tag = rti.pk)));


ALTER TABLE dem.v_identity_tags OWNER TO "gm-dbo";

--
-- Name: v_message_inbox; Type: VIEW; Schema: dem; Owner: gm-dbo
--

CREATE VIEW dem.v_message_inbox AS
 SELECT all_msgs.received_when,
    all_msgs.modified_by,
    all_msgs.provider,
    all_msgs.importance,
    all_msgs.category,
    all_msgs.l10n_category,
    all_msgs.type,
    all_msgs.l10n_type,
    all_msgs.comment,
    all_msgs.pk_context,
    all_msgs.data,
    all_msgs.pk_inbox_message,
    all_msgs.pk_staff,
    all_msgs.pk_category,
    all_msgs.pk_type,
    all_msgs.pk_patient,
    all_msgs.is_virtual,
    all_msgs.due_date,
    all_msgs.expiry_date,
    all_msgs.is_overdue,
    all_msgs.is_expired,
    all_msgs.interval_due,
    all_msgs.xmin_message_inbox,
    d_n.lastnames,
    d_n.firstnames,
    d_n.preferred AS preferred_name,
    d_n.comment AS comment_name,
    d_i.gender,
    public._(d_i.gender) AS l10n_gender,
    (date_trunc('day'::text, d_i.dob) + (COALESCE(d_i.tob, (d_i.dob)::time without time zone))::interval) AS dob,
    d_i.deceased,
    d_i.title,
    d_i.comment AS comment_identity
   FROM ((( SELECT mi.modified_when AS received_when,
            COALESCE(( SELECT staff.short_alias
                   FROM dem.staff
                  WHERE (staff.db_user = mi.modified_by)), (('<'::text || (mi.modified_by)::text) || '>'::text)) AS modified_by,
            ( SELECT staff.short_alias
                   FROM dem.staff
                  WHERE (staff.pk = mi.fk_staff)) AS provider,
            mi.importance,
            vit.category,
            vit.l10n_category,
            vit.type,
            vit.l10n_type,
            mi.comment,
            mi.ufk_context AS pk_context,
            mi.data,
            mi.pk AS pk_inbox_message,
            mi.fk_staff AS pk_staff,
            vit.pk_category,
            mi.fk_inbox_item_type AS pk_type,
            mi.fk_patient AS pk_patient,
            false AS is_virtual,
            mi.due_date,
            mi.expiry_date,
                CASE
                    WHEN (mi.due_date IS NULL) THEN false
                    WHEN (mi.due_date > now()) THEN false
                    WHEN (mi.expiry_date IS NULL) THEN true
                    WHEN (mi.expiry_date < now()) THEN false
                    ELSE true
                END AS is_overdue,
                CASE
                    WHEN (mi.expiry_date IS NULL) THEN false
                    WHEN (mi.expiry_date > now()) THEN false
                    ELSE true
                END AS is_expired,
                CASE
                    WHEN (mi.due_date IS NULL) THEN NULL::interval
                    WHEN (mi.due_date > now()) THEN ((mi.due_date)::timestamp with time zone - now())
                    ELSE (now() - (mi.due_date)::timestamp with time zone)
                END AS interval_due,
            gm.xid2int(mi.xmin) AS xmin_message_inbox
           FROM dem.message_inbox mi,
            dem.v_inbox_item_type vit
          WHERE (mi.fk_inbox_item_type = vit.pk_type)
        UNION ALL
         SELECT v_unreviewed_docs_inbox.received_when,
            v_unreviewed_docs_inbox.modified_by,
            v_unreviewed_docs_inbox.provider,
            v_unreviewed_docs_inbox.importance,
            v_unreviewed_docs_inbox.category,
            v_unreviewed_docs_inbox.l10n_category,
            v_unreviewed_docs_inbox.type,
            v_unreviewed_docs_inbox.l10n_type,
            v_unreviewed_docs_inbox.comment,
            v_unreviewed_docs_inbox.pk_context,
            v_unreviewed_docs_inbox.data,
            v_unreviewed_docs_inbox.pk_inbox_message,
            v_unreviewed_docs_inbox.pk_staff,
            v_unreviewed_docs_inbox.pk_category,
            v_unreviewed_docs_inbox.pk_type,
            v_unreviewed_docs_inbox.pk_patient,
            v_unreviewed_docs_inbox.is_virtual,
            v_unreviewed_docs_inbox.due_date,
            v_unreviewed_docs_inbox.expiry_date,
            v_unreviewed_docs_inbox.is_overdue,
            v_unreviewed_docs_inbox.is_expired,
            v_unreviewed_docs_inbox.interval_due,
            v_unreviewed_docs_inbox.xmin_message_inbox
           FROM blobs.v_unreviewed_docs_inbox
        UNION ALL (
                 SELECT now() AS received_when,
                    vtr.modified_by,
                    ( SELECT staff.short_alias
                           FROM dem.staff
                          WHERE (staff.pk = vtr.pk_intended_reviewer)) AS provider,
                    1 AS importance,
                    'clinical'::text AS category,
                    public._('clinical'::text) AS l10n_category,
                    'review results'::text AS type,
                    public._('review results'::text) AS l10n_type,
                    ( SELECT (((((public._('unreviewed (abnormal) results for patient'::text) || ' ['::text) || dn.lastnames) || ', '::text) || dn.firstnames) || ']'::text)
                           FROM dem.names dn
                          WHERE ((dn.id_identity = vtr.pk_patient) AND (dn.active IS TRUE))) AS comment,
                    NULL::integer[] AS pk_context,
                    NULL::text AS data,
                    NULL::integer AS pk_inbox_message,
                    vtr.pk_intended_reviewer AS pk_staff,
                    ( SELECT v_inbox_item_type.pk_category
                           FROM dem.v_inbox_item_type
                          WHERE (v_inbox_item_type.type = 'review results'::text)) AS pk_category,
                    ( SELECT v_inbox_item_type.pk_type
                           FROM dem.v_inbox_item_type
                          WHERE (v_inbox_item_type.type = 'review results'::text)) AS pk_type,
                    vtr.pk_patient,
                    true AS is_virtual,
                    (now() - '01:00:00'::interval) AS due_date,
                    NULL::timestamp with time zone AS expiry_date,
                    true AS is_overdue,
                    false AS is_expired,
                    '01:00:00'::interval AS interval_due,
                    NULL::integer AS xmin_message_inbox
                   FROM clin.v_test_results vtr
                  WHERE ((vtr.reviewed IS FALSE) AND ((vtr.is_technically_abnormal IS TRUE) OR ((vtr.is_technically_abnormal IS NULL) AND (vtr.abnormality_indicator IS NOT NULL))))
                UNION
                 SELECT now() AS received_when,
                    vtr.modified_by,
                    ( SELECT staff.short_alias
                           FROM dem.staff
                          WHERE (staff.pk = vtr.pk_intended_reviewer)) AS provider,
                    0 AS importance,
                    'clinical'::text AS category,
                    public._('clinical'::text) AS l10n_category,
                    'review results'::text AS type,
                    public._('review results'::text) AS l10n_type,
                    ( SELECT (((((public._('unreviewed (normal) results for patient'::text) || ' ['::text) || dn.lastnames) || ', '::text) || dn.firstnames) || ']'::text)
                           FROM dem.names dn
                          WHERE ((dn.id_identity = vtr.pk_patient) AND (dn.active IS TRUE))) AS comment,
                    NULL::integer[] AS pk_context,
                    NULL::text AS data,
                    NULL::integer AS pk_inbox_message,
                    vtr.pk_intended_reviewer AS pk_staff,
                    ( SELECT v_inbox_item_type.pk_category
                           FROM dem.v_inbox_item_type
                          WHERE (v_inbox_item_type.type = 'review results'::text)) AS pk_category,
                    ( SELECT v_inbox_item_type.pk_type
                           FROM dem.v_inbox_item_type
                          WHERE (v_inbox_item_type.type = 'review results'::text)) AS pk_type,
                    vtr.pk_patient,
                    true AS is_virtual,
                    (now() - '01:00:00'::interval) AS due_date,
                    NULL::timestamp with time zone AS expiry_date,
                    true AS is_overdue,
                    false AS is_expired,
                    '01:00:00'::interval AS interval_due,
                    NULL::integer AS xmin_message_inbox
                   FROM clin.v_test_results vtr
                  WHERE ((vtr.reviewed IS FALSE) AND ((vtr.is_technically_abnormal IS FALSE) OR ((vtr.is_technically_abnormal IS NULL) AND (vtr.abnormality_indicator IS NULL))))
        )) all_msgs
     LEFT JOIN dem.identity d_i ON ((all_msgs.pk_patient = d_i.pk)))
     LEFT JOIN dem.names d_n ON ((d_i.pk = d_n.id_identity)))
  WHERE (d_n.active IS DISTINCT FROM false);


ALTER TABLE dem.v_message_inbox OWNER TO "gm-dbo";

--
