#!/bin/bash

source /usr/local/lib/sh/include

include rc
include env
include args
require bomb

rcset c colname :date_modified  "get most recent row with col having this name"
rcset f datefmt :%Y%m%d%H%M%S   "output lastmod using this strftime() format"
rcset x exclude :               "do not consider table with this name"
rcset t table   :               "only consider the table with this name"

rcset d dbname  :               "database name for connect to pgsql"
rcset u dbuser  :${PGUSER}      "database user (role) for connect to pgsql"
rcset h dbhost  :${PGHOST}      "host name running pgsql"

rcsetusage \
        `get_invocation_name` \
        "Emit most recent of all rows from dbms tables with the given column" \
        "
        Returns time of most recently updated tuple in the specified
        database, among all relations that contain the specified
        attribute.  The PostgreSQL DBMS used to connect is given in the
        PG* variables taken from the environment, or overridden on the
        command line.

        Last update information is gleaned from the most recent value of
        any with the given attribute, selected from all relations in the
        database which have such an attribute.  Output format can be
        specified in standard strftime() format.

        The list of relations which have such an attribute is obtained
        using the 'information_schema', which is part of SQL92 and is
        theoretically portable across DBMS implementations.
        "

##############################################################################

sanity_check ()
{
        [[ $cf_dbname ]] || {
                warn "requires at least the dbname argument to run"
                rcusage_exit 1
        }

        [[ $cf_dbuser && $cf_dbhost ]] || {
                warn "must set PG envs for user and host or specify in args"
                rcusage_exit 1
        }

        [[ $cf_table && $cf_exclude ]] && {
                warn "exclude makes no sense if a table is specified"
                rcusage_exit 1
        }
}

get_interesting_tables ()
{
        # no need to find which table, the user told us
        #
        if [[ $cf_table ]]; then
                printf $cf_table
                return
        fi

        psql                                    \
                --quiet                         \
                --tuples-only                   \
                --dbname        $cf_dbname      \
                --username      $cf_dbuser      \
                --host          $cf_dbhost      \
        <<- HERE
                SELECT table_name
                FROM information_schema.columns
                WHERE column_name = '$cf_colname'
                AND table_name != '$cf_exclude'
                ;
        HERE

        (($? == 0)) ||
                bomb "psql failed with information schema"
}

build_query ()
{
        local -a query
        local -a tables

        tables=($(get_interesting_tables)) ||
                bomb "failed to generate"

        for table in ${tables[@]}
        do query+=(
                SELECT $cf_colname
                FROM $table UNION ALL
        ); done

        # the last one left a trailing UNION operator so we have to
        # join it with a final set that won't return anything useful
        #
        query+=(SELECT NULL)

        echo ${query[@]}
}

##############################################################################

main ()
{
        local query

        rcinit $@
        sanity_check $@

        query=$(build_query) ||
                bomb "query build failure"

        (set -o pipefail &>/dev/null;
         psql -qt |
         sed '/^$/d' |
         date -f - +$cf_datefmt) \
        <<- HERE
                SELECT max($cf_colname)
                FROM ($query)
                AS max_date_all_tables;
        HERE

        (($? == 0)) ||
                bomb "union select failed"
}

main $@