#!/bin/sh
#----------------------------------------------------------------------------
# /var/install/bin/phppgadmin-tools-report-db
#
# Creation:     2017-04-07 hbfl
# Last Update:  $Id$
#
# Copyright (c) 2017-2022 Holger Bruenjes, holgerbruenjes(at)gmx(dot)net
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#----------------------------------------------------------------------------

# include eislib
. /var/install/include/eislib

#debug=true

if ${debug:-false}
then
    exec 2>/tmp/$(basename ${0})-trace$$.log
    set -x
    ask_debug=true
    export ask_debug
fi

# ---------------------------------------------------------------------------
# Create report db
# ---------------------------------------------------------------------------
#CREATE DATABASE phppgadmin;
#
#\connect phppgadmin
#
#CREATE TABLE ppa_reports (
#    report_id SERIAL,
#    report_name varchar(255) NOT NULL,
#    db_name varchar(255) NOT NULL,
#    date_created date DEFAULT NOW() NOT NULL,
#    created_by varchar(255) NOT NULL,
#    descr text,
#    report_sql text NOT NULL,
#    paginate boolean NOT NULL,
#    PRIMARY KEY (report_id)
#);
#
#-- Allow everyone to do everything with reports.  This may
#-- or may not be what you want.
#GRANT SELECT,INSERT,UPDATE,DELETE ON ppa_reports TO PUBLIC;
#GRANT SELECT,UPDATE ON ppa_reports_report_id_seq TO PUBLIC;


# ---------------------------------------------------------------------------
# create the sql script and execute it
# ---------------------------------------------------------------------------
db_operation()
{
    # check if entered server is active
    eval active='${PHPPGADMIN_SERVER_'${server}'_ACTIVE}'

    eval host='${PHPPGADMIN_SERVER_'${server}'_HOST}'
    eval port='${PHPPGADMIN_SERVER_'${server}'_PORT}'
    eval name='${PHPPGADMIN_SERVER_'${server}'_NAME}'

    if [ "${active}" != "yes" ]
    then
        echo
        mecho -n --warn "PostgreSQL Server on '"
        mecho -n --std "${host} ${name} ${port}"
        mecho --warn "' is not active"
        echo
        anykey
        select_server
    fi

    check_status

    admin_user=''
    # check if the admin user exists in pgpass file
    if [ -f /root/.pgpass ]
    then
        if grep -q ":${port}:" /root/.pgpass
        then
            admin_user=$(${AWK} -F: '/'^${host}'/ {print $4}' /root/.pgpass)
        fi
    fi

    # if not exists get him
    if [ ! -f /root/.pgpass ] || [ -z "${admin_user}" ]
    then
        get_admin_and_passwd
    fi

    # check if db exists
    check_report_db
    db_chk_ret=${?}

    if [ ${db_chk_ret} -eq 0 ]
    then
        create_report_db
    else
        clrhome

        echo
        mecho -n --info "Report DB on '"
        mecho -n --std "${host} ${name} ${port}"
        mecho --info "' exists"
        echo

        techo --begin '3 3 2 39'
        techo --row '' --info 'No' '' --info 'Action'
        techo --row '' '1.' '-' 'Remove Report database and create New'
        techo --row '' '2.' '-' 'Drop Report database'
        techo --end
        echo

        _ask_tmpfile=$(${MKTEMP} -t .XXXXXXXXXXXXX)
        ${ASK} "Select" "" "1-2" "^$=Return" "0=Exit" >${_ask_tmpfile}
        rc=${?}
        read action < ${_ask_tmpfile}
        ${RM} -f ${_ask_tmpfile}

        # if ask break, ask returned 255
        if [ ${rc} = 255 ]
        then
            select_server
        fi

        case ${action} in
        '') select_server ;;
        0)
            check_executable
            exit 127
            ;;
        1)
            remove_report_db
            db_re_ret=${?}

            # only for .... to wait
            sleep 3

            if [ ${db_re_ret} -eq 0 ]
            then
                create_report_db
            fi
            ;;
        2)
            remove_report_db
            ;;
        esac
    fi
}

# ---------------------------------------------------------------------------
# remove report db
# ---------------------------------------------------------------------------
remove_report_db()
{
    # remove db
    echo "DROP DATABASE phppgadmin ;" |
         ${database_exec} \
         -h${host} \
         -p${port} \
         -U${admin_user} \
         -dpostgres >/dev/null 2>&1

    db_remove_ret=${?}
}

# ---------------------------------------------------------------------------
# check report db
# ---------------------------------------------------------------------------
check_report_db()
{
    local db_exists

    # output in tmpfile is require to catch all output from psql too :-(
    tmpfile=$(${MKTEMP} -t .XXXXXXXXXXXXX)
    echo "SELECT d.datname FROM pg_catalog.pg_database d ;" |
                ${database_exec} \
                -h${host} \
                -p${port} \
                -U${admin_user} \
                -dpostgres \
                -t > ${tmpfile} 2>&1

    if ${GREP} -q 'phppgadmin' ${tmpfile}
    then
        db_exists=1
    fi

    ${RM} -f ${tmpfile}

    return ${db_exists:-0}
}

# ---------------------------------------------------------------------------
# create report db
# ---------------------------------------------------------------------------
create_report_db()
{
    # create db
    echo "CREATE DATABASE phppgadmin ;" |
         ${database_exec} \
         -h${host} \
         -p${port} \
         -U${admin_user} \
         -dpostgres >/dev/null 2>&1

    db_ret=${?}

    if [ ${db_ret} -eq 0 ]
    then
        # create table
        echo "CREATE TABLE ppa_reports (
              report_id SERIAL,
              report_name varchar(255) NOT NULL,
              db_name varchar(255) NOT NULL,
              date_created date DEFAULT NOW() NOT NULL,
              created_by varchar(255) NOT NULL,
              descr text,
              report_sql text NOT NULL,
              paginate boolean NOT NULL,
              PRIMARY KEY (report_id) );" |
              ${database_exec} \
              -h${host} \
              -U${admin_user} \
              -dphppgadmin >/dev/null 2>&1

        echo "GRANT SELECT,INSERT,UPDATE,DELETE ON ppa_reports TO PUBLIC;" |
               ${database_exec} \
              -h${host} \
              -p${port} \
              -U${admin_user} \
              -dphppgadmin >/dev/null 2>&1


        echo "GRANT SELECT,UPDATE ON ppa_reports_report_id_seq TO PUBLIC;" |
               ${database_exec} \
              -h${host} \
              -p${port} \
              -U${admin_user} \
              -dphppgadmin >/dev/null 2>&1
    fi
}

# ---------------------------------------------------------------------------
# select server
# ---------------------------------------------------------------------------
select_server()
{
    clrhome

    mecho
    mecho 'This script will create or delete the report database. To do'
    mecho 'this you have to choose one of the available servers and enter name'
    mecho 'and password of a user with admin rights on the choosen server.'
    mecho

    list_configured_servers

    case ${server} in
    '')
        check_executable
        exit 0
        ;;
    0)
        check_executable
        exit 127
        ;;
    *)
        db_operation ${server}
        select_server
        ;;
    esac
}

# ---------------------------------------------------------------------------
# main
# ---------------------------------------------------------------------------
main()
{
    # include config
    . /etc/config.d/phppgadmin
    # include helper function
    . /var/install/bin/apache2-config-modules-phppgadmin-tools-helper

    select_server
    check_executable

    exit 0
}

# ---------------------------------------------------------------------------
# call function main
# ---------------------------------------------------------------------------
main "${@}"

# ---------------------------------------------------------------------------
# end
# ---------------------------------------------------------------------------