--Script to regenerate all caches and tables in PostGIS

--We have to load the functions that we will need later!!!!
-- They should be present befoe this file is executed
--get_c_square_polygon
--get_csquare
--concat aggregate

--We add indexes to GBIF tables (this might change in the future!)

--CREATE INDEX highertaxon_index1 ON "higherTaxon" USING btree ("taxonKey");
--CREATE INDEX highertaxon_index2 ON "higherTaxon" USING btree ("higherTaxonName");
--VACUUM ANALYZE "higherTaxon";
--CREATE INDEX image_biorecord_fk ON "imageRecord" USING btree (imagerecordbiorecordkey);
--VACUUM ANALYZE "imageRecord";
--CREATE INDEX country_codekey ON "isoCountry" USING btree ("isoCountryCode");
--VACUUM ANALYZE "isoCountry";
--CREATE INDEX resource_pk ON "resource" USING btree ("resourceKey");
--VACUUM ANALYZE "resource";
--CREATE INDEX taxon_pk ON taxon USING btree("taxonKey");
--CREATE INDEX taxonnameindex_1 ON taxon USING btree ("taxonName");
--VACUUM ANALYZE "taxon";

----------------------------------------------

--Modify the biorecord table:
ALTER TABLE "biorecord" ADD COLUMN c_square_code_1 varchar(12);
ALTER TABLE "biorecord" ADD COLUMN c_square_code_05 varchar(12);
ALTER TABLE "biorecord" ADD COLUMN c_square_code_01 varchar(12);
--TODO!
update "biorecord" set c_square_code_1 = get_csquare("geoLatitude","geoLongitude",int4(1)), c_square_code_05 = get_csquare("geoLatitude","geoLongitude",int4(0.5)), c_square_code_01 = get_csquare("geoLatitude","geoLongitude",int4(0.1));
--the the_geom column is already there from the transfer
--SELECT AddGeometryColumn('biorecord', 'the_geom', 4326, 'POINT', 2 );
--update biorecord SET the_geom=PointFromWKB(Point("geoLongitude", "geoLatitude"));
    
CREATE INDEX biorecord_sp_index ON "biorecord" USING gist (the_geom);
CREATE INDEX biorecord_taxon_fk ON "biorecord" USING btree ("bioRecordTaxonKey");
CREATE INDEX country_code ON "biorecord" USING btree ("bioRecordCountryCode");
CREATE INDEX provider_index ON "biorecord" USING btree ("bioRecordResourceKey");
CREATE INDEX c_square_1_index ON "biorecord" USING btree (c_square_code_1);
CREATE INDEX c_square_05_index ON "biorecord" USING btree (c_square_code_05);
CREATE INDEX c_square_01_index ON "biorecord" USING btree (c_square_code_01);
VACUUM ANALYZE "biorecord";



-----------------------------------
-- Generate the point_stats table

CREATE TABLE point_stats
(
  num_records int4 NOT NULL,
  num_taxon int4,
  num_providers int4,
  point_id serial NOT NULL
) 
WITHOUT OIDS;
SELECT AddGeometryColumn('point_stats','the_geom', 4326, 'POINT',2);


insert into point_stats
    select count("bioRecordKey") as num_records, 
	    count( distinct "bioRecordTaxonKey") as num_taxon, 
	    count(distinct "bioRecordResourceKey") as num_providers, 
	    nextval('point_stats_point_id_seq') as point_id,
	    the_geom
    from "biorecord" group by the_geom;
    
CREATE INDEX point_stats_sp_index ON point_stats USING gist (the_geom);

VACUUM ANALYZE point_stats;

-----------------------------------
-- Generate the grid_1_stats table

CREATE TABLE grid_1_stats
(
  c_square_code varchar(12) NOT NULL,
  num_records int8,
  num_taxon int8,
  num_providers int8,
  CONSTRAINT grid_1_stats_pk PRIMARY KEY (c_square_code)
)
WITHOUT OIDS;
SELECT AddGeometryColumn('grid_1_stats', 'the_geom', 4326, 'POLYGON', 2 );

insert into grid_1_stats 
    select  c_square_code_1 as c_square_code,
        count(distinct "bioRecordKey") as num_records,
        count(distinct "bioRecordTaxonKey") as num_taxon,
        count(distinct "bioRecordResourceKey") as num_provider,
        get_c_square_polygon(c_square_code_1,1) as the_geom
    from "biorecord" group by c_square_code_1;

CREATE INDEX grid_1_stats_sp_index ON grid_1_stats USING gist (the_geom);
VACUUM ANALYZE grid_1_stats;

-----------------------------------
-- Generate the grid_05_stats table

CREATE TABLE grid_05_stats
(
  c_square_code varchar(12) NOT NULL,
  num_records int8,
  num_taxon int8,
  num_providers int8,
  CONSTRAINT grid_05_stats_pk PRIMARY KEY (c_square_code)
)
WITHOUT OIDS;
SELECT AddGeometryColumn('grid_05_stats', 'the_geom', 4326, 'POLYGON', 2 );

insert into grid_05_stats 
    select  c_square_code_05 as c_square_code,
        count(distinct "bioRecordKey") as num_records,
        count(distinct "bioRecordTaxonKey") as num_taxon,
        count(distinct "bioRecordResourceKey") as num_provider,
        get_c_square_polygon(c_square_code_05,0.5) as the_geom
    from "biorecord" group by c_square_code_05;
    
CREATE INDEX grid_05_stats_sp_index ON grid_05_stats USING gist (the_geom);
VACUUM ANALYZE grid_05_stats;

-----------------------------------
-- Generate the grid_01_stats table

CREATE TABLE grid_01_stats
(
  c_square_code varchar(12) NOT NULL,
  num_records int8,
  num_taxon int8,
  num_providers int8,
  CONSTRAINT grid_01_stats_pk PRIMARY KEY (c_square_code)
)
WITHOUT OIDS;
SELECT AddGeometryColumn('grid_01_stats', 'the_geom', 4326, 'POLYGON', 2 );

insert into grid_01_stats 
    select  c_square_code_01 as c_square_code,
        count(distinct "bioRecordKey") as num_records,
        count(distinct "bioRecordTaxonKey") as num_taxon,
        count(distinct "bioRecordResourceKey") as num_provider,
        get_c_square_polygon(c_square_code_01,0.1) as the_geom
    from "biorecord" group by c_square_code_01;
    
CREATE INDEX grid_01_stats_sp_index ON grid_01_stats USING gist (the_geom);
VACUUM ANALYZE grid_01_stats;


-----------------------------------
-- Generate the taxon_occurrence table
CREATE TABLE taxon_occurrence
(
  taxon_key int4,
  taxon_name varchar(127)
) 
WITH OIDS;
SELECT AddGeometryColumn('taxon_occurrence', 'the_geom', 4326, 'MULTIPOINT', 2 );

insert into taxon_occurrence
select 
    "bioRecordTaxonKey" as taxon_key, 
    "taxonName" as taxon_name, 
    GeomFromText('MULTIPOINT('|| substr(CONCAT(distinct x(the_geom)::varchar ||' '::varchar  || y(the_geom)::varchar || ','),1,length(CONCAT(distinct x(the_geom)::varchar ||' '::varchar  || y(the_geom)::varchar || ','))-1) || ')', 4326)  as the_geom 
from "biorecord" inner join taxon on  "biorecord"."bioRecordTaxonKey" = taxon."taxonKey" 
group by "bioRecordTaxonKey","taxonName";

CREATE INDEX taxon_occurrence_sp_index ON taxon_occurrence USING gist (the_geom);
VACUUM ANALYZE taxon_occurrence;


