#!/usr/bin/env perl
#=======================================================================
#
#  NAME
#
#      	upd_pod_2_dads.pl
#
#  DESCRIPTION
#
#      	Create a new record in opus..podnames to match the actual
#       dataset name found in DADS.  Move the original podnames
#       entry to a "old" table.
#
#  OPTIONS
#
#	S - Database server name
#	D - DADS Database name
#	O - OPUS Database name
#	U - User name with privileges to delete/insert both dbs
#       t - Test Mode - do not run the deletes/inserts
#	h - Print Usage
#
#  HISTORY
#
#	11/29/06  57066  Lisa Gardner    Initial Release
#	01/16/07  57307  Lisa Gardner    Do not delete from podnames if
#                                        rec still needed for other dads
#
#=======================================================================

use strict;

use STScI::DBI;			# Used to automatically login
require 'getopts.pl';		# contains Getopts

#-----------------------------------------------------------------------
#
# Get the DB options and input parameter.  Determine if have filename,
# association id or nothing.  A filename is defined as a word containing
# a ".".
#
#-----------------------------------------------------------------------
use vars ( '$opt_S', '$opt_D', '$opt_O', '$opt_U', '$opt_t', '$opt_h' );
&Getopts('S:D:O:U:th');

my $server   = $opt_S || 'CATLOG';
my $dadsDb   = $opt_D || 'dadsops';
my $opusDb   = $opt_O || 'dadsops_log';
my $user     = $opt_U || `whoami`; 
my $test     = $opt_t;

my $startDate = scalar(localtime);
print "Started at $startDate\n";
print "Using $server/$dadsDb/$opusDb/$user\n";

#-----------------------------------------------------------------------
#
# Print the usage
#
#-----------------------------------------------------------------------
if ( $opt_h or !$server or !$dadsDb or !$opusDb or !$user ) {
   print "Usage: $0 -S<server> -D<dads database> O<opus database> ",
                 "-U<user> [-t]\n";
   print "Ex: $0 -SCATLOG -Ddadsops -Oopus -Udatamgr\n",
         "    or\n",
         "Ex: $0 -SCATLOG -Ddadsops -Oopus -Udatamgr\n";
   exit;
}

#-----------------------------------------------------------------------
#
# Connect to database
#
#-----------------------------------------------------------------------
my $dbh = STScI::DBI->connect( "dbi:Sybase:server=$server", $user )
        or die "Can't connect to database: $DBI::errstr";

$dbh->do("use $dadsDb") or die;

$dbh->func("LONG","_date_fmt");

#-----------------------------------------------------------------------
#
# Get the exposure level dataset name.  This is what will match to
# to the podnames table in OPUS.  This information is a combination
# of the association member information in assoc_member and the 
# singleton information in science.
#
#-----------------------------------------------------------------------
my %dsnList  = ();	# Info for unique datasets being cleaned out

my $query = <<"EOQ";
select asm_member_name from assoc_member where asm_member_name like 
'O%[SQNPMTORB]'
union
select sci_data_set_name from science where sci_data_set_name like 
'O%[SQNPMTORB]'
EOQ

my $sth = $dbh->prepare($query) or die "Can't prepare query:" . $dbh->errstr;
          $sth->execute         or die "Can't execute query:" . $dbh->errstr;

while ( my ( $dsn ) = $sth->fetchrow_array ) {
   next if $dsn =~ /^O3S4/;		# SMGT data
   $dsnList{$dsn} = 1;
}

$sth->finish;


#-----------------------------------------------------------------------
#
# Process each dataset and update the podname table if needed.
# All the updates should be wrapped in a database transaction so
# they could be rolled back.
#
#-----------------------------------------------------------------------
my $podFixCnt = 0;
my $errMsg = '';
my %podCnts = ();
my $matchCnt = 0;
my $noMatchCnt = 0;
my $noUpdCnt = 0;
my %cnts = ();
my $dsnCnt = 0;

$dbh->{AutoCommit} = 0;  # enable transactions, if possible
$dbh->{RaiseError} = 1;

eval {

   foreach my $dsn ( sort keys %dsnList ) {
      $dsnCnt++;

      #-- Make adjustments to podnames
      my ( $error, $match, $noUpd, $move, $del, $ins ) 
                  = InsertPodnames ( $dbh, $dsn, \%dsnList );

      #-- Add to counts and the error message
      $podCnts{mov} += $move;
      $podCnts{del} += $del;
      $podCnts{ins} += $ins;
      $matchCnt += $match;
      $noUpdCnt += $noUpd;
      $errMsg .= $error;

      #-- Add to fix count if something was fixed.
      if ( $move > 0 ) {
         $podFixCnt++;
         #print "$dsn	$move	$del	$ins\n";
      }
   }

};
   
#-----------------------------------------------------------------------
#
# If there is a problem or your running a test case - rollback the 
# transaction otherwise commit it.  
# Note: rollback means to undo all inserts/deletes/updates.
#
#-----------------------------------------------------------------------

if ($@) {
   print "\nTransaction aborted because $@\n";
   $dbh->rollback;
} elsif ( $errMsg ) {
   print "\n*** Transaction aborted because:\n$errMsg\n";
   $dbh->rollback;
} else {
   if ( $test ) {
      print "\n*** No changes were actually made\n";
      $dbh->rollback;
   } else {
      $dbh->commit;
   }
}
$dbh->disconnect;

#-----------------------------------------------------------------------
#
# Print totals
#
#-----------------------------------------------------------------------
print "\nNumber of datasets processed                 : $dsnCnt\n";
print "Number of podnames entries matched DADS      : $matchCnt\n";
print "Number of podnames entries better than DADS  : $noUpdCnt\n";
print "Number of dataset fixes made to podnames     : $podFixCnt\n";
print "Number of podnames entries moved             : $podCnts{mov}\n";
print "Number of podnames entries deleted           : $podCnts{del}\n";
print "Number of podnames entries inserted          : $podCnts{ins}\n";
my $endDate = scalar(localtime);
print "Completed at $endDate\n";
exit;

#=======================================================================
#
# Subroutines
#
#=======================================================================
#-----------------------------------------------------------------------
#
# Subroutine InsertPodname:
#    Determine if need to insert a podnames record for the 
#    dataset name passed in.
#
#-----------------------------------------------------------------------
sub InsertPodnames
{
   my ( $dbh, $dsn, $dsnList ) = @_;

   my %podInfo = ();
   my $match = 0;
   my $noUpdate = 0;
   my $moveCnt = 0;
   my $delCnt = 0;
   my $insCnt = 0;
   my $error = '';

   #-- Get all records from podnames that match the ipppssoo 
   #-- and save in hash

   my $ipso = substr($dsn,0,8) . '%';
   my $query = "select ipppssoot, podname, orphan_name from " .
               "$opusDb..podnames where ipppssoot like '$ipso'";
   my $sth = $dbh->prepare($query) 
                            or die "Can't prepare query:" . $dbh->errstr;
      $sth->execute         or die "Can't execute query:" . $dbh->errstr;

   while ( my ( $ipppssoot, $podName, $orphanName ) = $sth->fetchrow_array ) {
      $podInfo{$ipppssoot}{$podName} = $orphanName;
   }

   #-- If there is not a record in podnames that matches the dataset name
   #-- then move the old records to a cleanup table and create new entries
   #-- for podnames.

   if ( exists $podInfo{$dsn} ) {
      $match = 1;
   } else {
      foreach my $ipppssoot ( sort keys %podInfo ) {

         #-- If the trans source in DADS is worse than the 
         #-- trans source (last letter) in podnames - do nothing

         my $dadsTs = substr($dsn,8,1);
         my $opusTs = substr($ipppssoot,8,1);
         #my $i1 = index("SQNPMTORB", $ts1);
         #my $i2 = index("SQNPMTORB", $ts2);
         #print "ts=$dsn/$ipppssoot/$ts1/$ts2/$i1/$i2\n";
         if ( index("SQNPMTORB", $dadsTs) > index("SQNPMTORB", $opusTs) ) {
            print "$dsn has a worse trans source than $ipppssoot ",
                  "- no changes are being made\n";
            $noUpdate++;
            next;
         }

         foreach my $podName ( sort keys %{$podInfo{$ipppssoot}} ) {
            my $orphanName = $podInfo{$ipppssoot}{$podName};


            #-- Move the record to the new table.  Add useful information.
            my $line = "insert into $opusDb..podnames_old_ipppssoot " .
                       "values ('$ipppssoot','$podName','$orphanName', " .
                       "'$dsn',getdate(), 'PR 57066')";
            my $rowsAffected = SetDbValues( $dbh, $line );
            $moveCnt += $rowsAffected;

            #-- Delete the record from podnames if there is no match
            #-- for other entries in the DADS database.
            if ( $dsnList->{$ipppssoot} ) {
               $line = "delete from $opusDb..podnames where ipppssoot = " .
                       "'$ipppssoot' and " . "podname = '$podName'";
               $rowsAffected = SetDbValues( $dbh, $line );
               $delCnt += $rowsAffected;
            }

            #-- Insert a new record into podnames with the pr number
            #-- in the orphan_name column

            $line =  "insert into $opusDb..podnames " .
                     "values ('$dsn','$podName','PR 57066')\n";
            $rowsAffected = SetDbValues( $dbh, $line );
            $insCnt += $rowsAffected;

            #-- If any of the counts are off then write out an error message
            #-- and rollback all transactions.

            if ( $moveCnt == 0 or $insCnt == 0 ) {
               $error = "Invalid counts for $dsn; move=$moveCnt, " .
                        "delete=$delCnt, insert=$insCnt\n";
            }  
         }
      }
   }

   return ( $error, $match, $noUpdate, $moveCnt, $delCnt, $insCnt );
}

#-----------------------------------------------------------------------
#
# Subroutine SetDbValues:
#   Delete, Insert or Update a record to the database
#   DBI returns 0E0 when the number of rows returned is 0.  I want
#   the original 0 so I'm translating it back.
#
#-----------------------------------------------------------------------
sub SetDbValues
{
   my ( $dbh, $query ) = @_;

   my $rowsAffected = '';

   eval {
      $rowsAffected = $dbh->do($query) or die $dbh->errstr;
      $rowsAffected = 0 if $rowsAffected eq "0E0";
   };

   die "Error encountered running query:$query\n$@" if $@;

   return $rowsAffected;
}