#!/bin/bash

# Creates analytical DB

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

. "$selfDir/vegbien_dest"

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

(set -x; date)

mk_analytical_table ()

{

    local table="$1"

    local public_table="\"$public\".$table" analyt_table="analytical_db.$table"

    cat <<EOF

-- Create $table

TRUNCATE $public_table;

INSERT INTO $public_table SELECT * FROM ${public_table}_view;

-- Create $analyt_table view pointing to table in $public

DROP VIEW IF EXISTS $analyt_table;

CREATE VIEW $analyt_table AS

SELECT * FROM $public_table;

GRANT SELECT ON TABLE $analyt_table TO bien_read;

GRANT SELECT ON TABLE $analyt_table TO public_;

    -- actual access depends on underlying table's permissions

EOF

}

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

GRANT USAGE ON SCHEMA analytical_db TO bien_read;

GRANT USAGE ON SCHEMA analytical_db TO public_;

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;

$(mk_analytical_table taxon_trait)

$(mk_analytical_table analytical_stem)

$(mk_analytical_table provider_count)

$(mk_analytical_table analytical_aggregate)

EOF

"$selfDir/export_analytical_db"