#!/bin/sh

# Creates analytical DB

selfDir="$(dirname -- "$0")"

prefix=

. "$selfDir/vegbien_dest"

exec 2>>"$selfDir/../inputs/analytical_db/logs/make_analytical_db.log.sql" >&2

(set -x; date)

(set -x; "$selfDir/psql_verbose_vegbien" <<EOF

TRUNCATE geoscrub_input;

INSERT INTO geoscrub_input SELECT * FROM geoscrub_input_view;

SELECT make_family_higher_plant_group();

TRUNCATE threatened_taxonlabel;

INSERT INTO threatened_taxonlabel SELECT * FROM threatened_taxonlabel_view;

TRUNCATE analytical_stem;

INSERT INTO analytical_stem SELECT * FROM analytical_stem_view;

TRUNCATE analytical_aggregate;

INSERT INTO analytical_aggregate SELECT * FROM analytical_aggregate_view;

-- Create analytical_db views pointing to the analytical_* versions in $public

DROP VIEW IF EXISTS analytical_db.analytical_stem;

CREATE VIEW analytical_db.analytical_stem AS

SELECT * FROM "$public".analytical_stem;

GRANT SELECT ON TABLE analytical_db.analytical_stem TO bien_read;

DROP VIEW IF EXISTS analytical_db.analytical_aggregate;

CREATE VIEW analytical_db.analytical_aggregate AS

SELECT * FROM "$public".analytical_aggregate;

GRANT SELECT ON TABLE analytical_db.analytical_aggregate TO bien_read;

EOF

)