-- (re)create the full bodyname cache from the structure hierarchy
UPDATE body SET bodyname_cache = get_parental_bodynames(body_id);

-- create a cache field storing all relevant data concatenated for easy searching
SELECT rebuild_freetext_search_data();

-- create and update a field for freetext indexing
ALTER TABLE body ADD COLUMN ts_freetext_search tsvector;
UPDATE body SET ts_freetext_search = to_tsvector('default', freetext_search);

-- Granting access to public for the TSearch2 tables
GRANT SELECT ON TABLE pg_ts_cfg    TO PUBLIC;
GRANT SELECT ON TABLE pg_ts_cfgmap TO PUBLIC;
GRANT SELECT ON TABLE pg_ts_parser TO PUBLIC;
GRANT SELECT ON TABLE pg_ts_dict   TO PUBLIC;

-- ************************************************************
-- ***   PARIS USER INTERFACE CACHE TABLES
-- ************************************************************


CREATE TABLE ui_cache_body AS 
SELECT distinct
bodyname.bodyname, 
source_id, 
nn_fk, 
body.body_id,
address.town,
address.region, 
institution_name, 
logo_url, description,
description_url, 
notes,
collection_code ,access_restriction,
usage_restriction,
strength,
purpose ,
disclaimer ,
doc_state ,
includes_types ,
iso_country_id,
percent_database,
number_of_subunits,
number_of_species,
upper (address.country) as country, 
keyword.keyword,
body_type_fk,
address_line_1, address_line_2,
address_line_3, 
address_line_4,
address_line_5, 
telephone, 
fax, 
email
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN description ON body_id = description.body_fk 
LEFT JOIN descriptionurl ON body_id = descriptionurl.body_fk
LEFT JOIN (address 
LEFT JOIN isocountry ON (iso_country_fk = iso_country_id) ) ON (address_fk = address_id) LEFT JOIN communication ON communication_fk= communication_id
LEFT JOIN bodykeyword ON body_id = bodykeyword.body_fk 
LEFT JOIN keyword ON keyword_fk=keyword_id 
where bodyname IS NOT NULL AND (body_type_fk=1 OR body_type_fk = 2 OR body_type_fk = 3);


REVOKE ALL ON TABLE ui_cache_body FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_body TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_keyword_common_name AS 
SELECT distinct bodyname.bodyname,body.body_id,address.town, 
address.country, body_type_fk,source_id, nn_fk,keyword, qualifier, keyword_type
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN address ON (address_fk = address_id) 
LEFT  JOIN bodykeyword ON body_id = bodykeyword.body_fk 
LEFT  JOIN keyword ON keyword_fk=keyword_id  
LEFT JOIN qualifier ON qualifier_fk = qualifier_id
LEFT JOIN keywordtype ON keyword_type_fk= keyword_type_id
where keyword_type_fk = 7;


REVOKE ALL ON TABLE ui_cache_keyword_common_name FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_keyword_common_name TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_keyword_timeperiod AS 
SELECT distinct bodyname.bodyname,body.body_id,address.town, 
address.country, body_type_fk,source_id, nn_fk,keyword, qualifier, keyword_type
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN address ON (address_fk = address_id) 
LEFT  JOIN bodykeyword ON body_id = bodykeyword.body_fk 
LEFT  JOIN keyword ON keyword_fk=keyword_id  
LEFT JOIN qualifier ON qualifier_fk = qualifier_id
LEFT JOIN keywordtype ON keyword_type_fk= keyword_type_id
where keyword_type_fk = 6;


REVOKE ALL ON TABLE ui_cache_keyword_timeperiod FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_keyword_timeperiod TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_keyword_geospatial AS 
SELECT distinct bodyname.bodyname,body.body_id,address.town, 
address.country, body_type_fk,source_id, nn_fk,keyword,qualifier, keyword_type
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN address ON (address_fk = address_id) 
LEFT  JOIN bodykeyword ON body_id = bodykeyword.body_fk 
LEFT  JOIN keyword ON keyword_fk=keyword_id  
LEFT JOIN qualifier ON qualifier_fk = qualifier_id
LEFT JOIN keywordtype ON keyword_type_fk= keyword_type_id
where keyword_type_fk = 5;


REVOKE ALL ON TABLE ui_cache_keyword_geospatial FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_keyword_geospatial TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_keyword_taxon_name AS 
SELECT distinct bodyname.bodyname,body.body_id,address.town, 
address.country, body_type_fk,source_id, nn_fk,keyword, qualifier, keyword_type
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN address ON (address_fk = address_id) 
LEFT JOIN bodykeyword ON body_id = bodykeyword.body_fk 
LEFT JOIN keyword ON keyword_fk=keyword_id  
LEFT JOIN qualifier ON qualifier_fk = qualifier_id
LEFT JOIN keywordtype ON keyword_type_fk= keyword_type_id
where keyword_type_fk = 4;


REVOKE ALL ON TABLE ui_cache_keyword_taxon_name FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_keyword_taxon_name TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_person AS 
SELECT distinct bodyname.bodyname,body.body_id,address.town, 
address.country,family_name, body_type_fk,source_id, nn_fk
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN address ON (address_fk = address_id) 
RIGHT  JOIN personinbody ON body_id = personinbody.body_fk 
LEFT  JOIN person ON person_fk=person_id  where family_name IS NOT NULL;


REVOKE ALL ON TABLE ui_cache_person FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_person TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_collection_class AS SELECT distinct bodyname.bodyname,
body.body_id,
address.town, 
address.country,
keyword.keyword, 
body_type_fk, source_id, 
nn_fk
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN address ON (address_fk = address_id) 
LEFT  JOIN bodykeyword ON body_id = bodykeyword.body_fk 
LEFT  JOIN keyword ON keyword_fk=keyword_id  where keyword_type_fk = 1;


REVOKE ALL ON TABLE ui_cache_collection_class FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_collection_class TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_object_class AS SELECT distinct bodyname.bodyname,body.body_id,address.town, address.country,keyword.keyword, body_type_fk, source_id, nn_fk
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN address ON (address_fk = address_id) 
LEFT  JOIN bodykeyword ON body_id = bodykeyword.body_fk 
LEFT  JOIN keyword ON keyword_fk=keyword_id  where keyword_type_fk = 2;


REVOKE ALL ON TABLE ui_cache_object_class FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_object_class TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_organisation_class AS SELECT distinct bodyname.bodyname,body.body_id,address.town, address.country,keyword.keyword,
body_type_fk, source_id, nn_fk 
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN address ON (address_fk = address_id) 
LEFT  JOIN bodykeyword ON body_id = bodykeyword.body_fk 
LEFT  JOIN keyword ON keyword_fk=keyword_id  where keyword_type_fk = 9;


REVOKE ALL ON TABLE ui_cache_organisation_class FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_organisation_class TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_keyword_collection_coverage AS 

SELECT distinct bodyname.bodyname,body.body_id,address.town, 
address.country, body_type_fk,source_id, nn_fk,keyword,qualifier, keyword_type
FROM 
(body   JOIN bodyname ON body_id = bodyname.body_fk)
LEFT JOIN address ON (address_fk = address_id) 
LEFT  JOIN bodykeyword ON body_id = bodykeyword.body_fk 
LEFT  JOIN keyword ON keyword_fk=keyword_id  
LEFT JOIN qualifier ON qualifier_fk = qualifier_id
LEFT JOIN keywordtype ON keyword_type_fk= keyword_type_id
where keyword_type_fk = 7   OR keyword_type_fk = 6 OR keyword_type_fk = 4 OR keyword_type_fk = 5;


REVOKE ALL ON TABLE ui_cache_keyword_collection_coverage FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_keyword_collection_coverage TO PUBLIC;


-- ****************************************
CREATE TABLE ui_cache_relation AS 
SELECT an.bodyname AS child_name, a.body_id AS child_id, a.body_type_fk AS child_type, 
           bn.bodyname AS parent_name, b.body_type_fk AS parent_type , b.body_id AS parent_id
FROM body a INNER JOIN bodyname  an ON body_id = body_fk, body b 
INNER JOIN bodyname  bn ON body_id = body_fk, ispartof 
where a.body_id = child_body_fk and b.body_id = parent_body_fk;


REVOKE ALL ON TABLE ui_cache_relation FROM PUBLIC;
GRANT SELECT ON TABLE ui_cache_relation TO PUBLIC;




-- ************************************************************
-- ***   POSTGRES INDICES
-- ************************************************************
-- not used in db "corm" to speed up writing...



--
-- TOC entry 93 (OID 615054340)
-- Name: personrole_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX personrole_idx_00 ON personrole USING btree (person_role_id);


--
-- TOC entry 95 (OID 615054341)
-- Name: isocountry_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX isocountry_idx_00 ON isocountry USING btree (iso_country_id);


--
-- TOC entry 97 (OID 615054342)
-- Name: relatedmaterialtype_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX relatedmaterialtype_idx_00 ON relatedmaterialtype USING btree (related_material_type_id);


--
-- TOC entry 109 (OID 615054350)
-- Name: person_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX person_idx_00 ON person USING btree (person_id);


--
-- TOC entry 99 (OID 615054343)
-- Name: qualifier_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX qualifier_idx ON qualifier USING btree (qualifier_id);


--
-- TOC entry 101 (OID 615054344)
-- Name: bodytype_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX bodytype_idx_00 ON bodytype USING btree (body_type_id);


--
-- TOC entry 103 (OID 615054345)
-- Name: personinbody_idx_01; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX personinbody_idx_01 ON personinbody USING btree (person_fk);


--
-- TOC entry 104 (OID 615054346)
-- Name: personinbody_idx_02; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX personinbody_idx_02 ON personinbody USING btree (body_fk);


--
-- TOC entry 105 (OID 615054347)
-- Name: personinbody_idx_03; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX personinbody_idx_03 ON personinbody USING btree (person_role_fk);


--
-- TOC entry 110 (OID 615054351)
-- Name: person_idx_01; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX person_idx_01 ON person USING btree (address_fk);


--
-- TOC entry 111 (OID 615054352)
-- Name: person_idx_02; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX person_idx_02 ON person USING btree (communication_fk);


--
-- TOC entry 113 (OID 615054353)
-- Name: communication_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX communication_idx_00 ON communication USING btree (communication_id);


--
-- TOC entry 115 (OID 615054354)
-- Name: bodykeyword_idx_01; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX bodykeyword_idx_01 ON bodykeyword USING btree (body_fk);


--
-- TOC entry 116 (OID 615054355)
-- Name: bodykeyword_idx_02; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX bodykeyword_idx_02 ON bodykeyword USING btree (keyword_fk);


--
-- TOC entry 118 (OID 615054356)
-- Name: kfbstatus_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX kfbstatus_idx_00 ON kfbstatus USING btree (kfb_status_id);


--
-- TOC entry 123 (OID 615054357)
-- Name: isolanguage_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX isolanguage_idx_00 ON isolanguage USING btree (iso_language_id);


--
-- TOC entry 133 (OID 615054358)
-- Name: idx_body_bodynamefk; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_body_bodynamefk ON body USING btree (bodyname_pref_fk);


--
-- TOC entry 132 (OID 615054359)
-- Name: idx_body_address; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_body_address ON body USING btree (address_fk);


--
-- TOC entry 135 (OID 615054360)
-- Name: idx_body_communication; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_body_communication ON body USING btree (communication_fk);


--
-- TOC entry 134 (OID 615054361)
-- Name: idx_body_bodytype; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_body_bodytype ON body USING btree (body_type_fk);


--
-- TOC entry 126 (OID 615054363)
-- Name: idx_description_body; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_description_body ON description USING btree (body_fk);


--
-- TOC entry 127 (OID 615054364)
-- Name: idx_description_language; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_description_language ON description USING btree (language_fk);


--
-- TOC entry 129 (OID 615054365)
-- Name: idx_descriptionurl_body; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_descriptionurl_body ON descriptionurl USING btree (body_fk);


--
-- TOC entry 120 (OID 615054366)
-- Name: idx_keywordfeedback_keywordtype; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_keywordfeedback_keywordtype ON keywordfeedback USING btree (keyword_type_fk);


--
-- TOC entry 121 (OID 615054367)
-- Name: idx_keywordfeedback_source; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_keywordfeedback_source ON keywordfeedback USING btree (node_fk, source_id_fk);


--
-- TOC entry 107 (OID 615054368)
-- Name: idx_person_address; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_person_address ON person USING btree (address_fk);


--
-- TOC entry 108 (OID 615054369)
-- Name: idx_person_communication; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_person_communication ON person USING btree (communication_fk);


--
-- TOC entry 136 (OID 615054371)
-- Name: idx_body_source; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_body_source ON body USING btree (nn_fk, source_id);


--
-- TOC entry 138 (OID 615054372)
-- Name: idx_address_country; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_address_country ON address USING btree (iso_country_fk);


--
-- TOC entry 140 (OID 615054373)
-- Name: idx_keyword_keywordtype; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_keyword_keywordtype ON keyword USING btree (keyword_type_fk);


--
-- TOC entry 139 (OID 615054374)
-- Name: idx_keyword_keyword; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_keyword_keyword ON keyword USING btree (keyword);


--
-- TOC entry 143 (OID 615054375)
-- Name: idx_relatedmaterial_relatedmaterialtype; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_relatedmaterial_relatedmaterialtype ON relatedmaterial USING btree (related_material_type_fk);


--
-- TOC entry 142 (OID 615054376)
-- Name: idx_relatedmaterial_body; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_relatedmaterial_body ON relatedmaterial USING btree (body_fk);


--
-- TOC entry 152 (OID 615054377)
-- Name: idx_concept_schema; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_concept_schema ON concept USING btree (schema_fk);


--
-- TOC entry 156 (OID 615054378)
-- Name: keywordtype_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX keywordtype_idx_00 ON keywordtype USING btree (keyword_type_id);


--
-- TOC entry 147 (OID 615054379)
-- Name: datasource_fk; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX datasource_fk ON unitcacheterm USING btree (datasource_fk);


--
-- TOC entry 146 (OID 615054380)
-- Name: concept_fk; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX concept_fk ON unitcacheterm USING btree (concept_fk);


--
-- TOC entry 160 (OID 615054381)
-- Name: webspy_idx_00; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX webspy_idx_00 ON webspy USING btree (web_spy_id);


--
-- TOC entry 164 (OID 702335177)
-- Name: idx_bodyname_body; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_bodyname_body ON bodyname USING btree (body_fk);


--
-- TOC entry 165 (OID 702335179)
-- Name: idx_bodyname_bodyname; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_bodyname_bodyname ON bodyname USING btree (bodyname);


--
-- TOC entry 166 (OID 702386997)
-- Name: ispartof_idx_01; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX ispartof_idx_01 ON ispartof USING btree (child_body_fk);


--
-- TOC entry 167 (OID 702386999)
-- Name: ispartof_idx_02; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX ispartof_idx_02 ON ispartof USING btree (parent_body_fk);


--
-- TOC entry 148 (OID 929734122)
-- Name: idx_term_lower; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_term_lower ON unitcacheterm USING btree (term_lower);

-- Indexes for cache tables ------------------
CREATE INDEX idx_cache_body_source 	         ON ui_cache_body   (nn_fk, source_id);
CREATE INDEX idx_cache_body_body_id   	     ON ui_cache_body   (body_id);
CREATE INDEX idx_cache_person_body_id        ON ui_cache_person (body_id);

-- GiST index for full text searcg
CREATE INDEX idx_body_freetext_search        ON body USING gist(ts_freetext_search);


-- ************************************************************
-- ***   POSTGRES CLUSTERS
-- ************************************************************

CLUSTER idx_term_lower ON unitcacheterm;
CLUSTER idx_keyword_keyword ON keyword;

CLUSTER idx_cache_body_source           ON ui_cache_body;
CLUSTER idx_cache_person_body_id        ON ui_cache_person;
CLUSTER idx_body_bodytype   	        ON body;
