#!/usr/bin/perl

###############################################################################
#
#             DB deployment automation
#             must always be run from the target auto folder.
#             it will expect the sprint folders to be at ../s1 etc
#
#             always backup DB before a deploy
#             pg_dump OMG -Fc -U postgres -h localhost > omg_backup.sql
###############################################################################

use DBI;
use Data::Dumper;
use strict;

my $C_PGEXEC = 'PGPASSWORD=oliver99 psql -U postgres -d proddashboard -h localhost -1 -f ';
my $C_LOG = "pg_log.txt";
my $C_TEAM1_LOW = 1;
my $C_TEAM1_HIGH = 99;
my $C_TEAM2_LOW = 100;
my $C_TEAM2_HIGH = 199;
my $C_SPRINT_DONE = 1;
my $C_TEAM_DONE = 2;


###############################################################################
#         writeLog
###############################################################################

sub writeLog {
               my ($status, $cmd, $output) = @_;

    open(LOGFILE,">>$C_LOG") or die("Can't open log file.\n");
    print LOGFILE ("$status RUNNING $cmd\n");
    print LOGFILE ("$output\n");
    print LOGFILE ("--------------------------------------------------------------------------------------\n");
    close(LOGFILE);
}


###############################################################################
#         checkInit
#         check if deploy init had been done
###############################################################################
sub checkInit{
     my ($dbh) = @_;
     my $drecs = 0;

     #check whether deploy table exists
     my $sql = qq{SELECT count(*) FROM information_schema.tables where table_name = 'deploy' and table_schema = 'public'};
     my $sth = $dbh->prepare($sql);
     $sth->execute();

     ($drecs) = $sth->fetchrow_array;

     print "Found $drecs instances of deploy\n";
     return $drecs;

}

###############################################################################
#         updateDeploy
#         add completed deployment item to tracking db
###############################################################################
sub updateDeploy{
     my ($dbh, $sprint, $item, $file) = @_;

     my $cmd = $C_PGEXEC.$file;

     my $log = `$cmd`;
     if ( $? == -1 )
     {
          writeLog("FAILED", $cmd, $log);
          print "command $cmd failed: $!\n";
          exit(0);
     }
     else{
          writeLog("OK", $cmd, $log);
          print $log.'\n';

     eval{

          my $sql = qq{insert into public.deploy (sprint_id, script_id, status) values (?, ?, ?)};
          my $sth = $dbh->prepare($sql);
          $sth->execute( $sprint, $item, 't' );
     };

     if ($@){
          print "Error updating Deploy entry for sprint $sprint, item $item \n";
          exit(0);
          }

     }


}

###############################################################################
#         checkFailuresExist
#         check if failed item found
###############################################################################
sub checkFailuresExist{
     my ($dbh) = @_;

     my $failed;

     # check last item of last sprint
     my $sql = qq{SELECT count(*) FROM public.deploy where status != TRUE};
     my $sth = $dbh->prepare($sql);

          $sth->execute();

          ($failed) = $sth->fetchrow_array;

     return $failed;
}

###############################################################################
#         checkLastDeploy
#         check the last sprint/id deployed for this team range
###############################################################################
sub checkLastDeploy{
     my ($dbh, $high, $low) = @_;

     my %state;
     my @row;

     # check last item of last sprint
     my $sql = qq{SELECT sprint_id as sprintid, script_id as scriptid FROM public.deploy WHERE script_id <= ? and script_id >= ? order by sprint_id DESC, script_id DESC limit 1};
     my $sth = $dbh->prepare($sql);
     $sth->execute( $high, $low );

     @row = $sth->fetchrow_array;
     if (@row){
          print "values found\n";
          $state{sprint} = $row[0];
          $state{item} = $row[1];
     }
     else{
          print "values NOT found\n";
          $state{sprint} = 1;
          $state{item} = $low - 1;
     }
     #print Dumper( \@row );

     return \%state;
}

###############################################################################
#         deploy
#         deploy all incomplete items from specified item + 1
#         it will only deploy items in this team range
###############################################################################
sub deploy{
     my ($dbh, $sprint, $item, $low) = @_;
     print "Beginning deployment at sprint $sprint item $item \n";

     my $cont = 1;
     do {

          # check for next sprint item to deploy
          $item++;

          my $spath = "../s".$sprint."/deploy/".$item."_*.sql";
          print "checking $spath \n";

          my @files = glob($spath);
          my $numfiles = @files;

          if ( $numfiles ){
               print "found deploy file for sprint $sprint item $item as $files[0] \n";
               updateDeploy( $dbh, $sprint, $item, $files[0] );
          }
          else{
               print "no more items in current sprint\n";

               # check next sprint
               $sprint++;
               $item = $low-1;
               $spath = "../s".$sprint."/deploy/";

               if (-e $spath){
                    print "checking next sprint $sprint\n";
               }
               else{
                    print "Deploy complete\n";
                    $cont = 0;
               }
          }

     } while ($cont);
}

###############################################################################
#         deploy_next
#         deploy all incomplete items from specified item + 1 within this
#         sprint only.
#         it will only deploy items in this team range
###############################################################################
sub deploy_next{
     my ($dbh, $sprint, $item, $low) = @_;
     print "Beginning deployment at sprint $sprint item $item low $low \n";

     my $cont = 1;
     do {

          # check for next sprint item to deploy
          $item++;

          my $spath = "../s".$sprint."/deploy/".$item."_*.sql";
          print "checking $spath \n";

          my @files = glob($spath);
          my $numfiles = @files;

          if ( $numfiles ){
               print "found deploy file for sprint $sprint item $item as $files[0] \n";
               updateDeploy( $dbh, $sprint, $item, $files[0] );
          }
          else{
               print "no more items in current sprint\n";

               # check next sprint
               $sprint++;
               $item = $low-1;
               $spath = "../s".$sprint."/deploy/";

               if (-e $spath){
                   print "next sprint available\n";
                   return $C_SPRINT_DONE;
               }
               else{
                    print "Deploy complete\n";
                    return $C_TEAM_DONE;
               }
          }

     } while ($cont);
}

sub main{
     print "Begin Deploy\n";
     my $sync = 0;

     my $dbh = DBI->connect('dbi:Pg:dbname=proddashboard;host=localhost','pgdev','pgdev',{AutoCommit=>1,RaiseError=>1,PrintError=>0});

     # check if init done
     my $init = checkInit( $dbh );

     if ($init){
          print "init done\n";
     }
     else{
          print "init has not been run - please run init_deploy.sh from the command line\n";
          exit(0);
     }

     # now get the last deployment entry on this system
     #my ($sprint, $item) = checkLastDeploy( $dbh );
     if ( checkFailuresExist( $dbh ) ){
          print "Failures exist - please resolve first\n";
          exit(0);
     }

     print "no failures found - checking last deploy point for TEAM1\n";

     # get current deploy state for both teams
     my ($team1state, $team2state, $first, $second, $cont, $sprint, $res1, $res2);
     $team1state = checkLastDeploy( $dbh, $C_TEAM1_HIGH, $C_TEAM1_LOW );
     $team2state = checkLastDeploy( $dbh, $C_TEAM2_HIGH, $C_TEAM2_LOW );
     $team2state->{low} = $C_TEAM2_LOW;
     $team2state->{high} = $C_TEAM2_HIGH;
     $team1state->{low} = $C_TEAM1_LOW;
     $team1state->{high} = $C_TEAM1_HIGH;

     # choose lowest sprint
     if ($team2state->{sprint} < $team1state->{sprint}){
          $first = $team2state;
          $second = $team1state;
     }
     else{
          $first = $team1state;
          $second = $team2state;          
     }

     # now start on first team and sync sprint by sprint
     $cont = 1;
     $sprint = $first->{sprint};
     do{
          # check if sprints are in sync and make sure team1 is first
          if ($second->{sprint} == $first->{sprint}){
               $first = $team1state;
               $second = $team2state;                         
               $sync = 1;
          }

          # run 
          $res1 = deploy_next( $dbh, $first->{sprint}, $first->{item}, $first->{low} );
          # move to next sprint
          $first->{sprint} = $first->{sprint} + 1;
          $first->{item} = $first->{low} - 1;


          # check if second team sprint caught up
          $res2 = 0;
          if ($sync){
               # run this sprint
               $res2 = deploy_next( $dbh, $second->{sprint}, $second->{item}, $second->{low} );
               $second->{sprint} = $second->{sprint} + 1;
               $second->{item} = $second->{low} - 1;
          }

          # now check if this completed the deploy or more sprints left
          if ($res1 == $C_TEAM_DONE){
               $cont = 0;
          }
     } while ($cont);

     print "End Deploy\n";
}


#
# run main
#
main();

# end