#!/bin/bash

# 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)

mk_analytical_table ()

{

    local table="$1"

    cat <<EOF

-- Create $table

TRUNCATE $table;

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

-- Create analytical_db.$table view pointing to table in $public

DROP VIEW IF EXISTS analytical_db.$table;

CREATE VIEW analytical_db.$table AS

SELECT * FROM "$public".$table;

GRANT SELECT ON TABLE analytical_db.$table TO bien_read;

EOF

}

(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;

$(mk_analytical_table analytical_stem)

$(mk_analytical_table analytical_aggregate)

EOF