#!/bin/bash

# Creates analytical DB

# Usage: self [log=]

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

. "$selfDir/vegbien_dest"

if test -n "${log=1}"; then # $log defaults to 1

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

fi

(set -x; date)

sleep 5 # wait for tnrs.make to acquire the lock if being run right after import

"$selfDir/waitself" "$selfDir/../inputs/.TNRS/tnrs/tnrs.make"

mk_table()

{

    local table="$1"

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

    cat <<EOF

-- Create $table

TRUNCATE $public_table;

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

ANALYZE $public_table;

EOF

}

mk_analytical_table()

{

    local table="$1"

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

    cat <<EOF

$(mk_table "$@")

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

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

$(mk_table geoscrub_input)

$(mk_table threatened_taxonlabel)

$(mk_analytical_table taxon_trait)

$(mk_analytical_table analytical_stem)

$(mk_analytical_table provider_count)

GRANT SELECT ON TABLE analytical_db.provider_count TO public_;

SELECT "${public}_validations".remake_diff_tables();

EOF

) || exit

remake=1 "$selfDir"/../run export_ </dev/null

#"$selfDir/export_analytical_db"