#! /usr/bin/perl -w

use strict ;
use Repocafe ;

my $TEMPL = 'lib/setup.sql' ;
my @tabs  = 
  qw(repo_mods repo_roots repos repo_rights repo_guests
     repo_groups repo_group_members repo_stats repo_xusers
    ) ;
my @drop_ok = qw(repo_mods) ;

my $prog = substr($0,rindex($0,'/')+1) ;
my $Usage = <<USAGE ;
Usage: $prog [-v] [-q] [-d] [-f] [-D] [-sql] [-c conf]
option v   : be verbose
option q   : be quiet
option d   : show debug info
option f   : action ; otherwise dry-run
option D   : force a drop of non-empty tables
option sql : just show the sql to be executed
option c   : use config-file <conf>
----------------------------------------
- $prog - setup the repocafe database tables.
- Program setup-db tries to drop all tables, before creating new ones.
  Before dropping a table, it checks that the table is empty ; setup-db will
  only drop a non-empty table if option -D is specified.
- After doing a dry-run, create the database setup with option -f. If,
  during setup, you change your config, re-run setup-db. Never, never run
  'setup-db -f' after setup is complete.
----------------------------------------
USAGE
sub Usage { die "$_[0]$Usage" ; }
sub Error { die "$prog: $_[0]\n" ; }
sub Warn  { warn "$prog: $_[0]\n" ; }

# usage: &GetOptions(ARG,ARG,..) defines $opt_ID as 1 or user spec'ed value
# usage: &GetOptions(\%opt,ARG,ARG,..) defines $opt{ID} as 1 or user value
# ARG = 'ID' | 'ID=SPC' | 'ID:SPC' for no-arg, required-arg or optional-arg
# ID  = perl identifier
# SPC = i|f|s for integer, fixedpoint real or string argument

use Getopt::Long ;
Getopt::Long::config('no_ignore_case') ;
our %opt = () ; Usage('') unless GetOptions
  ( \%opt, qw(v q d f D sql c=s help) ) ;
if ( $opt{help} ) { print $Usage ; exit 0 ; }
Usage("Arg count\n") unless @ARGV == 0 ;

my %drop_ok ;

sub trim
  { my $x = shift ;
    $x =~ s/[\s;]+$// ;
    $x =~ s/^\s+// ;
    $x ;
  }

sub get_tups
  { my $dbh = shift ;
    my $sql = shift ;
    my $tab = shift ;
    my $key = shift ;
    my @res = () ;
    my $sth = $dbh -> prepare ( $sql ) ;
    $sth -> execute ( @$tab ) ;
    while ( my $rec = $sth -> fetchrow_hashref )
      { push @res, $rec ; }
    @res ;
  }

sub find_user
  { my $dbh  = shift ;
    my $user = shift ;
    my $sql  = "SELECT * FROM pg_roles where rolname = ?" ;
    my @tups = get_tups $dbh, $sql, [ $user ] ;
    @tups ? $tups [ 0 ] -> { rolname } : '' ;
  }

sub table_cnt
  { my $dbh  = shift ;
    my $tab  = shift ;
    my $sth = $dbh -> table_info ( '', 'public', $tab, undef ) ;
    my $res = undef ;
    if ( scalar @{ $sth -> fetchall_arrayref } == 1 )
      { my $sql  = "SELECT count(*) as count FROM $tab" ;
        my @tups = get_tups $dbh, $sql, [] ;
        if ( @tups )
          { $res = $tups [ 0 ] -> { count } ; }
        else
          { Error "can't ($sql)" ; }
      }
    $res ;
  }

sub exec_sql
  { my $dbh = shift ;
    my $cmd = shift ;
    my $res = 1 ;
    if ( $cmd =~ /^DROP TABLE\s+(\w+)/ )
      { my $tab = $1 ;
        my $cnt = table_cnt $dbh, $tab ;
        printf "%s :\n", $tab ;
        if ( defined $cnt )
          { printf "  %d tuples : %s\n", $cnt, $tab ;
            if ( $cnt == 0 or $drop_ok { $tab } or $opt{D} )
              { if ( $opt{f} )
                  { # $dbh -> { RaiseError } = 0 ;
                    my $sth = $dbh -> prepare ( $cmd ) ;
                    $sth -> execute () ;
                    # $dbh -> { RaiseError } = 1 ;
                    print "  DID drop table $tab\n" ;
                  }
                else
                  { print "  WOULD drop table $tab\n" ; }
              }
            else
              { print "  not ok to drop $tab ; not empty ; use -D\n" ;
                $res = 0 ;
              }
          }
        else
          { print "  $tab not found ; ignore $cmd\n" ; }
      }
    elsif ( $opt{f} )
      { my $sth = $dbh -> prepare ( $cmd ) ; $sth -> execute () ; }
    $res ;
  }

sub exec_sqls
  { my $dbh = shift ;
    my $cmds = shift ;
    my $res = 1 ;
    for my $cmd ( @$cmds )
      { $res = 0 unless exec_sql ( $dbh, $cmd ) ; }
    $res ;
  }

my $Cafe = make_cafe ( %opt, -conn => 0 ) ;
my $conf = $Cafe -> conf ;
$Cafe -> db_connect_as ( $conf -> db_owner, $conf -> db_opswd ) ;
my $dbh = $Cafe -> _dbh ;

my $prefix  = $conf -> db_pref ;
my $db_user = $conf -> db_user ;
my $db_rupd = $conf -> db_owner ;
for my $tab ( @drop_ok ) { $drop_ok { $prefix . $tab } ++ ; }
Error "db_user ($db_user) not found" unless find_user $dbh, $db_user ;

unless ( $conf -> db_owner and $conf -> db_opswd )
  { $db_rupd = $db_user ;
    printf
      "db_owner/opswd empty ; db user '%s' can change repo_roots\n"
        , $db_user unless $opt{sql} ;
  }

open TEMPL, $TEMPL ;
my @text = grep { !/(^#)|(^\s*$)/, } <TEMPL> ;
close TEMPL ;

my @cmds = () ;
my %cmds = () ;
my $cmd  = '' ;

for my $line ( @text )
  { $line =~ s/%db_user%/$db_user/go ;
    $line =~ s/%db_rupd%/$db_rupd/go ;
    for my $tab ( @tabs )
      { my $pat = sprintf '%%%s%%', $tab ;
        $line =~ s/$pat/$prefix$tab/g ;
      }
    $cmd .= $line ;
    if ( $cmd =~ /;/ )
      { push @cmds, trim $cmd ; $cmd = '' ; }
  }
Error "last command doesn't end in ';'" if $cmd ;

for $cmd ( @cmds )
  { printf "%s ;\n", $cmd if $opt{sql} ;
    if ( $cmd =~ /^(DROP|CREATE|INSERT|GRANT)\s/ )
      { push @{ $cmds { $1 } }, $cmd ; }
    else
      { Error "unkown command type in ($cmd)\n" ; }
  }

exit if $opt{sql} ;

if ( exec_sqls $dbh, $cmds { DROP } )
  { exec_sqls $dbh, $cmds { CREATE } ;
    exec_sqls $dbh, $cmds { INSERT } ;
    exec_sqls $dbh, $cmds { GRANT  } ;
  }
else
  { printf "SKIP create's, insert's, grant's\n" ; }

print "$prog : no config errors found\n" ;