#!/bin/sh

#  assumes both instances are running, on port 6972 and 6973

logfile1=$HOME/pg_stuff/pg_installations/pgsql.logical_replication/logfile.logical_replication
logfile2=$HOME/pg_stuff/pg_installations/pgsql.logical_replication2/logfile.logical_replication2

scale=1
if [[ ! "$1" == "" ]]
then
   scale=$1
fi

clients=1
if [[ ! "$2" == "" ]]
then
   clients=$2
fi

unset PGSERVICEFILE PGSERVICE PGPORT PGDATA PGHOST
PGDATABASE=testdb

# (this script also uses a custom pgpassfile)

## just for info:
# env | grep PG
# psql -qtAXc "select current_setting('server_version')"

port1=6972
port2=6973

function cb()
{
  #  display the 4 pgbench tables' accumulated content as md5s
  #  a,b,t,h stand for:  pgbench_accounts, -branches, -tellers, -history
  md5_total_6972='-1'
  md5_total_6973='-2'
  for port in $port1 $port2
  do
    md5_a=$(echo "select * from pgbench_accounts order by aid"|psql -qtAXp$port|md5sum|cut -b 1-9)
    md5_b=$(echo "select * from pgbench_branches order by bid"|psql -qtAXp$port|md5sum|cut -b 1-9)
    md5_t=$(echo "select * from pgbench_tellers  order by tid"|psql -qtAXp$port|md5sum|cut -b 1-9)
    md5_h=$(echo "select * from pgbench_history  order by hid"|psql -qtAXp$port|md5sum|cut -b 1-9)
    cnt_a=$(echo "select count(*) from pgbench_accounts"|psql -qtAXp $port)
    cnt_b=$(echo "select count(*) from pgbench_branches"|psql -qtAXp $port)
    cnt_t=$(echo "select count(*) from pgbench_tellers" |psql -qtAXp $port)
    cnt_h=$(echo "select count(*) from pgbench_history" |psql -qtAXp $port)
    md5_total[$port]=$( echo "${md5_a} ${md5_b} ${md5_t} ${md5_h}" | md5sum )
    printf "$port a,b,t,h: %6d %6d %6d %6d" $cnt_a  $cnt_b  $cnt_t  $cnt_h
    echo -n "   $md5_a  $md5_b  $md5_t  $md5_h"
    if   [[ $port -eq $port1 ]]; then echo    "   master"
    elif [[ $port -eq $port2 ]]; then echo -n "   replica"
    else                              echo    "             ERROR  "
    fi
  done
  if [[ "${md5_total[6972]}" == "${md5_total[6973]}" ]]
  then
    echo " ok"
  else
    echo " NOK"
  fi
}

bail=0

pub_count=$( echo "select count(*) from pg_publication" | psql -qtAXp 6972 )
if  [[ $pub_count -ne 0 ]]
then
  echo "pub_count -ne 0 - deleting pub1 & bailing out"
  echo "drop publication if exists pub1" | psql -Xp 6972
  bail=1
fi
sub_count=$( echo "select count(*) from pg_subscription" | psql -qtAXp 6973 )
if  [[ $sub_count -ne 0 ]]
then
  echo "sub_count -ne 0 - deleting sub1 & bailing out"
  echo "drop subscription if exists sub1" | psql -Xp 6973
  rc=$?
  echo "(drop subscr. ) )  rc=$rc"
  bail=1
fi

pub_count=$( echo "select count(*) from pg_publication"  | psql -qtAXp 6972 )
sub_count=$( echo "select count(*) from pg_subscription" | psql -qtAXp 6973 )

#if [[ $bail -eq 1 ]]
#then
#    if  [[ $pub_count -eq 0 ]] && [[ $sub_count -eq 0 ]]
#    then
#	    exit 0
#    else
#        exit 1
#    fi
#fi

if  [[ $pub_count -eq 1 ]] || [[ $sub_count -eq 1 ]]
then
    exit 1
fi


# clear logs
echo > $logfile1
echo > $logfile2

   echo "drop table if exists pgbench_accounts;
         drop table if exists pgbench_branches;
         drop table if exists pgbench_tellers;
         drop table if exists pgbench_history;" | psql -X -p $port1 \
&& echo "drop table if exists pgbench_accounts;
         drop table if exists pgbench_branches;
         drop table if exists pgbench_tellers;
         drop table if exists pgbench_history;" | psql -X -p $port2

pgbench -p $port1 -qis $scale && echo "
        alter table pgbench_history add column hid serial primary key;
     -- alter table pgbench_history replica identity full;
     -- delete from pgbench_accounts where aid > 40;
" | psql -1p $port1 \
  && pg_dump -F c  -p $port1 \
           -t pgbench_accounts \
           -t pgbench_branches \
           -t pgbench_tellers  \
           -t pgbench_history  \
  | pg_restore -p $port2 -d testdb

echo "-- (no diffs expected... )" 
echo  "$(cb)"
#pgbench -p $port2 -qis 1 && echo "
#        alter table pgbench_history add column hid serial primary key;
#     -- alter table pgbench_history replica identity full;" | psql -1p $port2
#

currval=1
# currval=$( echo "
# select nextval('pgbench_history_hid_seq'::regclass);
# select currval('pgbench_history_hid_seq'::regclass);" | psql -qtAXp $port1 | tail -n 1 )
# echo "-- currval [$currval]"

# empty tables at replica:
echo "
delete from pgbench_accounts; 
delete from pgbench_branches; 
delete from pgbench_tellers; 
delete from pgbench_history;
-- select setval('pgbench_history_hid_seq', $currval);
" | psql -q -X -p $port2 

echo "-- (pre-replication,  diffs expected... )" 
echo  "$(cb)"

echo "create publication pub1 for all tables;" | psql -p $port1 -aqtAX
echo "create subscription sub1 connection 'port=${port1}' publication pub1 with (disabled);
alter subscription sub1 enable; 
" | psql -p $port2 -aqtAX
#------------------------------------

if [[ 1 -eq 1 ]]
then
  echo "-- pgbench -c $clients -T 20 -P 5 -n "
           pgbench -c $clients -T 20 -P 5 -n
fi
sleep 5
echo "-- "
echo  "$(cb)"

echo "select * from pgbench_accounts order by aid" | psql -Xp 6972 > acc.6972.md5
echo "select * from pgbench_accounts order by aid" | psql -Xp 6973 > acc.6973.md5
echo "select * from pgbench_history order by hid"  | psql -Xp 6972 > his.6972.md5
echo "select * from pgbench_history order by hid"  | psql -Xp 6973 > his.6973.md5
wc_acc=$( diff acc.6972.md5 acc.6973.md5 | wc -l )
wc_his=$( diff his.6972.md5 his.6973.md5 | wc -l )

echo "-- wc -l accounts  $wc_acc"
echo "-- wc -l history   $wc_his"

echo -n "-- logfile1 "; wc -l $logfile1 | cut '-d ' -f 1 
echo -n "-- logfile2 "; wc -l $logfile2 | cut '-d ' -f 1

if [[ $wc_acc -eq 0 ]]
then
  echo "-- wc lines [$wc_acc] - ok"
  exit 0
fi

echo "-- waiting 30s..."
sleep 30
echo "-- "
echo  "$(cb)"

echo "select * from pgbench_accounts order by aid" | psql -Xp 6972 > accounts.6972.md5
echo "select * from pgbench_accounts order by aid" | psql -Xp 6973 > accounts.6973.md5
diff accounts.6972.md5  accounts.6973.md5 | wc -l

echo -n "-- logfile1 "; wc -l $logfile1 | cut '-d ' -f 1 
echo -n "-- logfile2 "; wc -l $logfile2 | cut '-d ' -f 1