#!/usr/bin/env perl
#=======================================================================
#
#  NAME
#
#      	undo_dms_asn.pl
#
#  DESCRIPTION
#
#      	Restores the DADS and OPUS databases to the original 
#       pre-dms association state.
#
#  OPTIONS
#
#	S - DADS/OPUS server on which undoing the dms association
#	D - DADS database on which undoing the dms association
#	O - OPUS database on which undoing the dms association
#	o - Name of database containing the original form of the data.
#	U - User name that has delete privileges on DADS and OPUS.
#       t - Test Mode - do not run the inserts
#	h - Print Usage
#
#  HISTORY
#
#	07/11/06  56154  Lisa Gardner    Initial Release
#
#=======================================================================

use strict;

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

#-----------------------------------------------------------------------
#
# Get the DB options and input parameters.  Determine if have filename,
# association id or nothing.
#
#-----------------------------------------------------------------------
use vars ( '$opt_S', '$opt_D', '$opt_O', '$opt_U', 
           '$opt_o', '$opt_t', '$opt_h' );
&Getopts('S:D:O:U:o:th');

my $server  = uc $opt_S;
my $dadsDb  = lc $opt_D;
my $opusDb  = lc $opt_O;
my $user    = $opt_U;
my $origDb  = $opt_o || 'dms_asn_orig_data';
my $test    = $opt_t;

#-- One of these 2 values must be filled in - the other will be blank.
my $inpFileName = '';   # Name of the input file
my $inpAsnId = '';      # Association id

if ( $ARGV[0] =~ /\./ ) {
   $inpFileName = $ARGV[0];
} else {
   $inpAsnId = $ARGV[0];
}

#-- Write out the input information to the report
print "Running $0 using $server/$user/$dadsDb, $opusDb, ",
      "$origDb/$inpFileName\n\n";

#-----------------------------------------------------------------------
#
# Print the usage
#
#-----------------------------------------------------------------------
if ( $opt_h or !$server or !$dadsDb or !$opusDb or !$user or !$ARGV[0] ) {
   print "Usage: $0 -S<server> -D<dads database> -O<opus database > ",
         "-o<old data db> -U<user> [-t] <association id or filename>\n";
   print "Ex: $0 -SCATLOG -Ddadsops -Oopus -odms_asn_orig_data ",
         "-Udatamgr N3T5B1010\n    or\n",
         "Ex: $0 -SCATLOG -Ddadsops -Oopus -odms_asn_orig_data ",
         "-Udatamgr sms_123456.lis\n";
   exit;
}

#-----------------------------------------------------------------------
#
# Connect to server.  
# Note: All the databases must be on the same server for this tool to work.
#
#-----------------------------------------------------------------------
my $dbh = STScI::DBI->connect( "dbi:Sybase:server=$server" )
        or die "Can't connect to database: $DBI::errstr";

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

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

#-----------------------------------------------------------------------
#
# Get the dataset list.  
# The code will check if the input parameter is a filename.  If it is
# it will open the file, read the contents and process each association.
# If it's not a file it will assume it's an association and process it.
# The hashes are filled in by sending them in as hash references.
# Note: a filename is a string ending in ".<ext>".
#
#-----------------------------------------------------------------------
my %dsnList  = ();      # Info for unique datasets being processed

if ( $inpFileName ) {
   open ( INP, "<$inpFileName" ) or die "Couldn't open file $inpFileName $!";
   while ( my $asnId = <INP> ) {
      chop $asnId;
      $asnId =~ s/^\s+//;              # Remove leading spaces
      next if $asnId =~ /^#/g;         # Skip comment lines
      next if $asnId =~ /^\s*$/g;      # Skip blank lines
      my $error = GetDsnList ( $dbh, $asnId, \%dsnList );
      print "$error" if $error;
   }
   close( INP );
} elsif ( $inpAsnId ) {
   my $error = GetDsnList ( $dbh, $inpAsnId, \%dsnList );
   if ( $error ) {
      print "$error - exiting script.\n";
      exit;
   }
}

#-----------------------------------------------------------------------
#
# Tables that need to be undone.
#
#-----------------------------------------------------------------------
my %general     = ( 'science','sci', 'sci_inst_db_join','sij_sdb',
                    'target_keyword','tak','target_synonym','tsy',
                    'fixed_target','fit','moving_target_position','mtp',
                    'scan_parameters','scp','oms_summary','oss',
                    'oms_data','oms', 'archive_data_set_all','ads',
                    'archive_files','afi', 'assoc_member','asm',
                    'assoc_status','ast', 'assoc_orphan','aso',
                    'archive_data_set_log_del','adl',
                    'archive_files_log_del','afl', 'pdq_summary','pdq' );

my %acs         = ( 'acs_science','acs','acs_ref_data','acr' );
my %acsInstr    = ( 'acs_a_data','aca', 'shp_data','shp',
                    'acs_chip','acc' );

my %nicmos      = ( 'nicmos_science','nss','nicmos_ref_data','nsr' );
my %nicmosInstr = ( 'nicmos_a_data','nsa','nicmos_b_data','nsb', 
                    'nicmos_c_data','nsc', 'shp_data','shp',
                    'nicmos_times','nst' );

my %stis        = ( 'stis_science','sss','stis_ref_data','ssr' );
my %stisInstr   = ( 'stis_a_data','ssa','stis_b_data','ssb',
                    'stis_c_data','ssc','shp_data','shp' );

#-- The OPUS tables do not have acronyms associated with them.
#-- I use them here only to print out the total in the total line.

my %opus        = ( "$opusDb..podnames",'pod',
                    "$opusDb..dms_asn_id", 'dai',
                    "$opusDb..asn_association", 'aas',
                    "$opusDb..asn_members", 'ame',
                    "$opusDb..asn_product_link", 'apl',
                    "$opusDb..stis_go_wavecal", 'sgw');

my %acsList    = ( %general, %opus, %acs, %acsInstr );
my %nicmosList = ( %general, %opus, %nicmos, %nicmosInstr );
my %stisList   = ( %general, %opus, %stis, %stisInstr );

#-- Set up the instrument specific tables.  This is used to print the
#-- totals in the correct column for the DADS instrument tables.

my %instrS = ( J => 'acs', N => 'nss', O => 'sss' );
my %instrR = ( J => 'acr', N => 'nsr', O => 'ssr' );
my %instrA = ( J => 'aca', N => 'nsa', O => 'ssa' );
my %instrB = (             N => 'nsb', O => 'ssb' );
my %instrC = (             N => 'nsc', O => 'ssc' );
my %instrX = ( J => 'acc', N => 'nst'             );

#-----------------------------------------------------------------------
#
# Write out the headers and define the print line
#
#-----------------------------------------------------------------------
print "                                      ***   UNDO DMS ASN IN DADS AND OPUS DATABASES   ***\n\n";
print " ASN  ID  Dataset   Typ sci INs INr tak tsy scp fit mtp sij shp INa INb INc INx oss oms pdq ads afi adl afl pod dai aas ame apl sgw dau\n";
print "--------- --------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---\n";

my $printFmt = "%-9s %-9s %3s %3s %3s %3s %3s %3s %3s %3s %3s %3s %3s " .
                         "%3s %3s %3s %3s %3s %3s %3s %3s %3s %3s %3s " .
                         "%3s %3s %3s %3s %3s %3s %3s\n";

#-----------------------------------------------------------------------
#
# Begin processing the asnId/dataset list in a transaction.  Any
# database error will cause the whole transaction to roll back.
#
#-----------------------------------------------------------------------
my $errMsg = '';                # Lists any errors found while processing
my $prevAsnId = $inpAsnId;      # Used to print a space between associations
my $dsnCnt = 0;                 # Number of datasets undone
my $asnCnt = 0;                 # Number of association ids undone

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

eval {

   #-----------------------------------------------------------------------
   #
   # For each member of each association:
   # - Delete out current data from DADS and OPUS databases
   # - Reload original data for DADS database
   # - Print out totals for that member
   #
   #-----------------------------------------------------------------------
   foreach my $dsn ( sort ByAsnId keys %dsnList ) {

      my %dsnTot = ();

      my $asnId = $dsnList{$dsn};
      my $instr = substr($dsn,0,1);

      #-- Log the association you will be undoing for the association only.

      if ( $dsn =~ /0$/ ) {
         my $insLine = "insert into $opusDb..dms_asn_undone " .
                       "select *, getdate() from $opusDb..dms_asn_id " .
                       "where association_id = '$dsn'";
         $dsnTot{ins}{dau} = SetDbValues( $dbh, $insLine );
         $asnCnt++;
      }

      #-- Do not reload this dataset if it is in more than one 
      #-- association.

      my $mult = InMultipleAsns ( $dbh, $dsn );
      if ( $mult ) {
         print "'$dsn' will not be undone due to it's being a member ",
               "of a different association.\n\n";
         next;
      }

      #-- Determine which table hash based on the instrument (first letter 
      #-- of dataset name) to use in the cleanup for this dataset.
      #-- If not a valid instrument, write out a message and do no further 
      #-- processing for that dataset.

      if ( $instr eq 'J' ) {
         RestoreData ( $dbh, $asnId, $dsn, \%dsnTot, %acsList );
      } elsif ( $instr eq 'N' ) {
         RestoreData ( $dbh, $asnId, $dsn, \%dsnTot, %nicmosList );
      } elsif ( $instr eq 'O' ) {
         RestoreData ( $dbh, $asnId, $dsn, \%dsnTot, %stisList );
      } else {
         print "$dsn is an unknown instrument - it will not be processed.\n";
         next;
      }

      #-----------------------------------------------------------------------
      #
      # Print one line for all the deletes for that member and another
      # line listing the inserts for that member.
      # Put 2 blank lines between asssocation for ease of reading.
      #
      #-----------------------------------------------------------------------
      if ( $prevAsnId and $prevAsnId ne $asnId ) {
         print "\n";
      }
      $prevAsnId = $asnId;
      $dsnCnt++;

      foreach my $type ( 'del','ins' ) {

         my $printLine = sprintf($printFmt, $asnId, $dsn, $type, 
                              $dsnTot{$type}{sci},
                              $dsnTot{$type}{$instrS{$instr}},
                              $dsnTot{$type}{$instrR{$instr}},
                              $dsnTot{$type}{tak}, $dsnTot{$type}{tsy}, 
                              $dsnTot{$type}{scp}, $dsnTot{$type}{fit}, 
                              $dsnTot{$type}{mtp}, $dsnTot{$type}{sij_sdb},
                              $dsnTot{$type}{shp}, 
                              $dsnTot{$type}{$instrA{$instr}},
                              $dsnTot{$type}{$instrB{$instr}}, 
                              $dsnTot{$type}{$instrC{$instr}},
                              $dsnTot{$type}{$instrX{$instr}},
                              $dsnTot{$type}{oss}, $dsnTot{$type}{oms}, 
                              $dsnTot{$type}{pdq}, $dsnTot{$type}{ads}, 
                              $dsnTot{$type}{afi}, $dsnTot{$type}{adl}, 
                              $dsnTot{$type}{afl}, $dsnTot{$type}{pod}, 
                              $dsnTot{$type}{dai}, $dsnTot{$type}{aas}, 
                              $dsnTot{$type}{ame}, $dsnTot{$type}{apl}, 
                              $dsnTot{$type}{sgw}, $dsnTot{$type}{dau});
         print $printLine;
      }
      print "\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.
#
#-----------------------------------------------------------------------

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 that were undone   : $dsnCnt\n";
print "Number of asn_ids  that were undone   : $asnCnt\n";

exit;
#=======================================================================
#
# Subroutines
#
#=======================================================================
#-----------------------------------------------------------------------
#
# GetDsnList:
#    Get the list of members in the dms association and save it
#    into the hash dsnList passed in.  Also determine if the
#    association exists so can write out error message. 
#    In the query we join between tables in the DADS database, 
#    OPUS database and the database containing the original data.
#    If the association information is not in all 3 don't bother
#    processing the data.
#
#-----------------------------------------------------------------------
sub GetDsnList
{
   my ( $dbh, $asnId, $dsnList ) = @_;

   my $errMsg = '';			# Error message
   my $asnExists = 0;			# Flag indicating asn exists
   my $dontUndo = 0;			# Flag indicating missing members
   my @memList = ();			# List of members in this asn

   #-- In order to verify that all the pieces are there we need to be sure
   #-- that the data is in DADS, OPUS and the dms_orig data databases.
   #-- Since the asn_id's in dms_asn_exp are guesses we can not join on
   #-- that field.  Instead we use assoc_member as the main table and get
   #-- the members from there and then see if the association is in OPUS
   #-- and see if the members are in the dms_asn_exp.  The query says give
   #-- me all the members in assoc_member and let me know if there is a 
   #-- matching record in OPUS for the association id and matching record in
   #-- dms_asn_exp for the members.  If the obsnum field is empty there
   #-- is no match and we don't want to undo the assocation.

   my $query = <<"   EOQ";
   select distinct a.asm_member_name, b.obsnum
   from assoc_member a, $origDb..dms_asn_exp b,
          $opusDb..dms_asn_id c
   where a.asm_asn_id = '$asnId' and a.asm_program_id *= b.program_id and
         a.asm_obset_id *= b.obset_id and a.asm_obsnum *= b.obsnum and
         a.asm_asn_id = c.association_id and a.asm_member_type not like 'PROD%'
   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 ( $memName, $obsnum ) = $sth->fetchrow_array ) {
      $dsnList->{$memName} = $asnId;

      #-- Save the list of members in case we don't want to undo the asn.
      push( @memList, $memName );

      $asnExists = 1;

      #-- Save the association id too only if members are found.
      $dsnList->{$asnId} = $asnId;

      #-- No obsnum means there was no record in dms_asn_exp for the record.
      if ( $obsnum eq '' ) {
         $dontUndo = 1;
      }
   }

   #-- Generate the error messages for any errors encountered.
   if ( !$asnExists ) {
      $errMsg = "'$asnId' does not exist in $server/$dadsDb..assoc_member " .
                  "or $opusDb..dms_asn_id - This will not be undone\n\n";
   } elsif ( $dontUndo ) {
      $errMsg = "'$asnId' is missing members from " .
                  "$server/$origDb..dms_asn_exp - This will not be undone\n\n";

      #-- Remove all members from the dsnList to be undone.
      foreach my $memName ( @memList ) {
         delete $dsnList->{$memName};
      }
   }

   return $errMsg;
}

#-----------------------------------------------------------------------
#
# InMultipleAsns:
#    Determine if this member belongs to more than one association.
#
#-----------------------------------------------------------------------
sub InMultipleAsns
{
   my ( $dbh, $dsn ) = @_;

   my $inMultAsns = 0;

   my $ipso = $dsn;
   $ipso =~ s/[SQNPMTOR]$/[SQNPMTOR]/;

   my $query = <<"   EOQ";
   select count(*) from assoc_member where asm_member_name like '$ipso'
   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 ( $cnt ) = $sth->fetchrow_array ) {
      $inMultAsns = 1 if $cnt > 1;
   }

   return $inMultAsns;
}

#-----------------------------------------------------------------------
#
# Subroutine RestoreData:
#    Each dataset needs to be deleted from the DADS and OPUS databases.
#    After that insert the data from the DADS tables from the original
#    data database into the real DADS tables.  
#    Note: OPUS doesn't need to have their records reinserted since
#    the records created for the dms associations are all new records.
#
#-----------------------------------------------------------------------
sub RestoreData
{
   my ( $dbh, $asnId, $dsn, $dsnTot, %tblList ) = @_;

   #-- Replace any transmission source with all possible transmission
   #-- sources (including J for OMS).

   my $ipso = $dsn;
   $ipso =~ s/[SQNPMTOR]$/[SQNPMTORJ]/;

   #-- Loop through DADS and OPUS tables deleting and inserting as needed.

   foreach my $tbl ( sort keys %tblList ) {

      #-- Don't bother deleting from OPUS tables for asn members 
      #-- except for the podnames record which may have been inserted
      #-- by the cleanup_dms_asn.pl tool.

      next if $tbl =~ /\.\./ and $dsn =~ /[SQNPMTOR]$/ and $tbl !~ /podnames/;

      #-- Generate the where clause.  
      my $whereClause = GenWhereClause ( $asnId, $dsn, $tbl, $tblList{$tbl} );

      #-- For STIS there are certain tables which have to have the dataset
      #-- name added to the query so as not to accidentally delete a wavecal 
      #-- from another association.  The tables affected are listed below.
      #-- The reason we match on either dataset name or association id is
      #-- that we canundo an association either before or after it was 
      #-- cleaned out.  This takes care of both cases.

      if ( $dsn =~ /^O/ and
           ( $tbl eq 'stis_a_data' or $tbl eq 'stis_b_data' or
             $tbl eq 'stis_c_data' or $tbl eq 'shp_data' or
             $tbl eq 'oms_data' ) ) {
         $whereClause .= " and ( $tblList{$tbl}_data_set_name like '$ipso'" .
                         " or $tblList{$tbl}_data_set_name = '$asnId')";
      }

      #-- Create the delete query.
      my $delLine = "delete from $tbl\n$whereClause";
      #print "$delLine\ngo\n";

      #-- Run the delete.  Keep track of number of rows deleted per table.
      my $rowsAffected = SetDbValues( $dbh, $delLine );
      $dsnTot->{del}{$tblList{$tbl}} = $rowsAffected;

      #-- If the table has a database name prefix that means it's an OPUS
      #-- table and no inserts need be done.

      next if $tbl =~ /\.\./ ;

      #-- Create the insert query.  
      my $insLine = "insert into $tbl select * from " .
                    "$origDb..$tbl\n$whereClause";

      #-- Run the insert.  Keep track of number of rows inserted per table.
      $dsnTot->{ins}{$tblList{$tbl}} = SetDbValues( $dbh, $insLine );
   }
}

#-----------------------------------------------------------------------
#
# GenWhereClause:
#    Generate the where clause based on the table name, acronym
#    and database from which it comes.
#
#-----------------------------------------------------------------------
sub GenWhereClause
{
   my ( $asnId, $dsn, $tbl, $acronym ) = @_;

   my $whereClause = '';

   #-- Get the pso key
   my $ppp = substr($dsn,1,3);
   my $ss = substr($dsn,4,2);
   my $ooo = substr($dsn,6,3);
   $ooo =~ s/[SQNPMTOR ]$//;

   #-- Need the ipso for DADS housekeeping tables.
   my $ipso = $dsn;
   $ipso =~ s/[SQNPMTOR]$/[SQNPMTORJ]/g;

   #-- Start the where clause
   my $whereClause = "where ";

   #-- Generate the where clause if it's an OPUS table.
   if ( $opus{$tbl} ) {
      if ( $tbl =~ /dms_asn_id/ or $tbl =~ /asn_association/ or
           $tbl =~ /asn_member/ ) {
         $whereClause .= "association_id = '$asnId'";
      } elsif ( $tbl =~ /podnames/ ) {
         $whereClause .= "ipppssoot like '$dsn' and orphan_name = 'NACT'";
      } else {
         $whereClause .= "program_id = '$ppp' " .
                         "and obset_id = '$ss' " .
                         "and member_num = '$ooo'";
      }

   #-- Generate the where clause if it's an DADS table.
   } else {
      if ( $tbl =~ /^archive_data_set/ or $tbl =~ /^archive_files/ ) {
         $whereClause .= "${acronym}_data_set_name like '$ipso' and " .
                         "${acronym}_archive_class in ('CAL','ASN','OMS','PDQ')";
      } elsif ( $tbl =~ /^assoc_/ ) {
         $whereClause .= "${acronym}_asn_id = '$dsn'";
      } else {
         $whereClause .= "${acronym}_program_id = '$ppp' " .
                         "and ${acronym}_obset_id = '$ss' " .
                         "and ${acronym}_obsnum = '$ooo'";
      }
   }
   return $whereClause;
}

#-----------------------------------------------------------------------
#
# ByAsnId:
#   This is a perl sorting routine.  Want to group all datasets
#   belonging to a dms association together.  Within the association
#   sort by the dataset names.
#
#-----------------------------------------------------------------------
sub ByAsnId {
   if ( $dsnList{$a} eq $dsnList{$b} ) {
      $a cmp $b;
   } else {
      $dsnList{$a} cmp $dsnList{$b};
   }
}

#-----------------------------------------------------------------------
#
# 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;
}