#!/usr/bin/env perl
#===============================================================================
#
# Name:  update_otfr_keyword_repair.pl
#
# Description:
#   This perl script is intended to be run by a user at a command line prompt to 
#   change the contents of the ARCH_SERVER::ARCH_DB OTFR_KEYWORD_REPAIR table.  
#   Depending on the arguments supplied, it either: 
#      - inserts a new ACTIVE entry in the table OR
#      - updates the status of an existing entry to INACTIVE
#   An additional optional parameter (the literal AUTO) has been provided so 
#   the script can be exectued by the auto test runs
#
# Arguments (4 required, 1 optional, 1 additional for auto test runs only):
#   0 PR# (required):
#      PR number documenting current table change
#   1 dataSet (required):
#      9-character data set name (IPPPSSOOT)
#   2 imSetValue (required):
#      Image Set value
#   3 tlmKeyword (required):
#      keyword, as it appears in the telemetry data
#   4 newKeywordValue | new status = INACTIVE (required):
#      - if argument is the literal INACTIVE, the entry identified by the 
#         previous arguments is retrieved and updated to a status of INACTIVE
#      - otherwise, this argument is assumed to be the new value for the 
#         keyword and a table entry is inserted
#   comment (optional):
#      free-form text, up to 30 characters long, enclosed in "", describing 
#       the reason for the update
#      default, if not specified for insert: empty field (DB NULL value)
#      default, if not specified for update: Entry deactivated by PR <PR#>
#   autoTestRun (optional): AUTO
#      specified only if proc is run by the auto test program; it suppresses 
#      displays to std out
#
# Note:  the following environment variables (logicals) are required:
#   ARCH_SERVER - specifies a DADS DB server
#   ARCH_DB     - identifies the DADS database to be updated
#
# OTFR_KEYWORD_REPAIR entry description:
#     (* identify primary key fields
#      ** identify fields that uniquely ID an entry)
#   okr_instrument        char(6)     derived from the first character of the 
#                                      user-specified IPPPSSOOT
# **okr_data_set_name     varchar(39) user-specified IPPPSSOOT (dataSet)
#  *okr_imset_name        varchar(10) PRIMARY, NOTUSED; derived from value
# **okr_imset_value       varchar(30) user-specified image set value:  0, 1
# **okr_telemetry_keyword varchar(10) user-specified keyword, as it appears in 
#                                      the telemetry data
#   okr_fits_keyword      varchar(10) not used; will be left blank in new entry
#  *okr_keyword_value     varchar(50) user-specified new value to associate 
#                                      w/the keyword
#   okr_pr_number         int(4,10)   user-supplied ID of PR that documents new 
#                                      entry for insert
#   okr_entry_date        datetime    current timestamp at time of table update
#   okr_status            char(8)     ACTIVE for insert; INACTIVE for update
#  *okr_status_date       datetime    current timestamp at time of table update
#   okr_status_comment    varchar(30) user-specified; if not: NULL for insert, 
#                                      'Entry deactivated by PR #' for update
#
# History:
# 2011/10/11 69194 LThompson   initial version
#
#===============================================================================

##======================================
##   M A I N   P R O C E S S I N G
##======================================

# adjust the Perl include dir using PATH
my $PATH = $ENV{"PATH"};
@parts = split /:/, $PATH;
while( defined( $val = pop(@parts) ) ) {
   if( substr($val,0,1) eq "/" ) {
      unshift(@INC, $val)
   }
}

# get environment variables
my $ARCH_SERVER = $ENV{"ARCH_SERVER"};
my $ARCH_DB = lc($ENV{"ARCH_DB"});
 
# include necessary routines for printing and DB access
#  use PrintMsg to support auto test runs
require 'printmsg.pl';
require 'do_dbi_pkg.pl';

# define global constants
$AUTOTEST = "AUTO" ;
$COMMENTLEN = 30 ;
$DBUPDATE = "UPDATE" ;
$DBINSERT = "INSERT" ;
$DEFUPDCOMNT = "Entry deactivated by PR " ;
$IMSETPRIM = "PRIMARY" ;
$IMSETPRIMVAL = "0" ;
$IMSETOTH = "NOTUSED" ;
$IMSETOTHVAL = "1" ;
$IPPPSSOOTLEN = 9 ;
$STATACT = "ACTIVE" ;
$STATINACT = "INACTIVE" ;
 # status codes
$SUCCESS = 0;
$FAILURE = 1;
$ERROR = 2 ;

# initialize global variables
# if these are local, I have to pass them ALL to DB subroutines!
$inPr = "" ;
$inImSetVal = "" ;
$imSetName = "" ;
$inTlmKw = "" ;
$inStatus = "" ;
$comment = "" ;
$currTs = "" ;
$instName = "" ;
$newKwVal = "" ;
$clRun = 0 ;   # default to off for auto-runs

# declare local variables
my( $inDataSet, $tType );


# get and verify args
#  (#ARGV=0 when there is exactly one arg)
if( $#ARGV < 4 ) {
   PrintMsg( "F", "Missing arguments (5 are required)" );
   PrintUsage();
   exit( $FAILURE );
}
$inPr = $ARGV[0];
$inDataSet = $ARGV[1];
$inImSetVal = $ARGV[2];
$inTlmKw = $ARGV[3];

# arg 4 can be either: a new keyword value (for an insert), OR 
#                        the literal INACTIVE (for an update)
# its value affects remaining args
if( $STATINACT eq uc($ARGV[4]) ) {
   # arg 4 specifies an entry update to deactivate keyword value
   $inStatus = $STATINACT ;
   $tType = $DBUPDATE ;
   if( $#ARGV > 4 ) {
      if( $AUTOTEST eq $ARGV[5] ) {
         # auto test update run, default update comment
         $clRun = 0 ;
         $comment = $DEFUPDCOMNT . $inPr ;
      } else {
         # arg 5 contains a user-supplied comment for the update
         $comment = $ARGV[5] ;
         # validate comment length
         if( length $comment > $COMMENTLEN ) {
            PrintMsg( "E", "<comment> length must be $COMMENTLEN characters or less" );
            PrintMsg( "F", "'$comment' is too long" );
            exit( $FAILURE );
         }
         if( $#ARGV > 5 ) {
            if( $ARGV[6] eq $AUTOTEST ) {
               # auto test update run
               $clRun = 0 ;
            } else {
               # if supplied, this arg must be AUTO; it's not
               PrintMsg( "E", "If 6 arguments are supplied, the last must be AUTO" );
               PrintMsg( "F", "$ARGV[6] is an invalid last argument" );
               PrintUsage();
               exit( $FAILURE );
            }
         } else {
            # 5 args were supplied
            # interactive update run, comment already saved
            $clRun = 1 ;
         }
      } # end if value of arg 5
   } else {
      # only the 4 required args were supplied
      # interactive update run, default update comment
      $clRun = 1 ;
      $comment = $DEFUPDCOMNT . $inPr ;
   } # end if any optional args supplied for update

} else {
   # arg 4 specifies a new keyword value; this run is to insert an entry
   $newKwVal = $ARGV[4] ;
   $inStatus = $STATACT ;
   $tType = $DBINSERT ;
   if( $#ARGV > 4 ) {
      if( $ARGV[5] eq $AUTOTEST ) {
         # auto test insert run, default new entry comment
         $clRun = 0 ;
         $comment = '' ;
      } else {
         # arg 5 contains a user-supplied comment for the new entry
         $comment = $ARGV[5] ;
         if( length $comment > $COMMENTLEN ) {
            PrintMsg( "E", "<comment> length must be $COMMENTLEN characters or less" );
            PrintMsg( "F", "'$comment' is too long" );
            exit( $FAILURE );
         }
         if( $#ARGV > 5 ) {
            if( $ARGV[6] eq $AUTOTEST ) {
               # auto test insert run
               $clRun = 0 ;
            } else {
               # if 6 args supplied, last one must be AUTO; it's not
               PrintMsg( "E", "If 6 arguments are supplied, the last must be AUTO" );
               PrintMsg( "F", "$ARGV[6] is an invalid last argument" );
               PrintUsage();
               exit( $FAILURE );
            }
         } else {
            # 5 args were supplied
            # interactive insert run, comment already saved
            $clRun = 1 ;
         }
      } # end if value of arg 5
   } else {
      # only the 4 required args were supplied
      # interactive insert run, default new entry comment
      $clRun = 1 ;
      $comment = '' ;
   } # end if any optional args supplied for insert

} # end if evaluate arg #4

# set imset_name based on $inImSetVal
#  (it doesn't really matter what it is; it is an unused field;
if( $inImSetVal eq $IMSETPRIMVAL ) {
   $imSetName = $IMSETPRIM ;
} elsif( $inImSetVal eq $IMSETOTHVAL ) {
   $imSetName = $IMSETOTH ;
} else {
   PrintMsg( "E", 
             "<imSet value> must be $IMSETPRIMVAL for $IMSETPRIM or $IMSETOTHVAL for $IMSETOTH" ) ;
   PrintMsg( "F", "$inImSetVal is invalid" ) ;
   exit( $FAILURE );
}


# Validate input data set name, ID instrument, have user verify
#  (subroutine will exit program if there are problems)
my $dataSet = VerifyDataSet( $inDataSet ) ;


# get current timestamp
use POSIX qw(strftime);
$currTs = strftime "%d-%b-%Y %T", localtime; 

# display values in debug mode
PrintMsg( "D", "Transaction Type is: $tType" ) ;
PrintMsg( "D", "Received PR: $inPr" ) ;
PrintMsg( "D", "         imSetValue: $inImSetVal" ) ;
PrintMsg( "D", "         tlmKeyword: $inTlmKw" ) ;
if( $tType eq $DBINSERT ) {
   PrintMsg( "D", "         newValue: $newKwVal" ) ;
}
PrintMsg( "D", "dataSet: $dataSet" ) ;
PrintMsg( "D", "comment: $comment" ) ;
PrintMsg( "D", "status: $inStatus" ) ;
PrintMsg( "D", "imSetName: $imSetName" ) ;
PrintMsg( "D", "instrument: $instName" ) ;
PrintMsg( "D", "current timestamp: $currTs" ) ;
if( $clRun ) {
   PrintMsg( "D", "clRun: $clRun (interactive run)" ) ;
} else {
   PrintMsg( "D", "clRun: $clRun ($AUTOTEST run)" ) ;
}


# open a connection to the DB
$db = DoDBIopen( $ARCH_SERVER, $ARCH_DB, $FAILURE);
my $errMsg = "Failed to establish connection to $ARCH_SERVER::$ARCH_DB - Very rare!" ;
die( $errMsg ) if $dbConn == $FAILURE;   # very rarely dies - catch error message
PrintMsg( "I", "connected to ${ARCH_SERVER}::${ARCH_DB}\n" ) ;

my $dbStatus = $SUCCESS ;

# user-specified transaction type determines necessary queries
if( $tType eq $DBINSERT ) {
   # verify an entry does not already exist
   # insert the entry
   # using global vars so I don't have to pass EVERYTHING
   $dbStatus = InsertEntry( $dataSet ) ;
} else {
   # ( $tType eq $DBUPDATE )
   # verify an entry does exist w/a status of ACTIVE
   # update status to INACTIVE
   # using global vars so I don't have to pass EVERYTHING
   $dbStatus = UpdateEntry( $dataSet ) ;
}

# close DB connection and exit
DoDBIclose($db);

if( $dbStatus == $SUCCESS ) {
   PrintMsg( "I", "Script complete; table contents changed\n" ) ;
   exit( $SUCCESS );
} else {
   PrintMsg( "F", "Table contents NOT changed\n" ) ;
   exit( $FAILURE );
}
#   end main processing


##======================================
##   S U B R O U T I N E S
##      (in called order)
##======================================

 ##-----------------------------------------------------------------------------
 ##
 ## display script usage information
 ##
 ##-----------------------------------------------------------------------------

 sub PrintUsage {

    use File::Basename;
    my( $name, $unixpath, $ext ) = fileparse($0, '.pl' );
    $name = $name . '.pl';

    # display the following to the user, exactly as it appears here
print STDERR<<EOF; 

This script changes the contents of the OTFR_KEYWORD_REPAIR table.  
Depending on the value of the 5th parameter, it either: 
  - inserts a new table entry to activate a keyword value, OR 
  - updates an existing table entry to deactivate a keyword value

USAGE: $name <PR #> <dataSet> <imSet value> <tlm keyword> 
                                     [<keyword value> | INACTIVE]
                                     ?<comment>?
  where *REQUIRED* arguments are:
    <PR #>  :  ID of PR documenting the table change
    <dataSet>  :  9-character IPPPSSOOT
    <imSet value>  :  image set value (0 or 1)
    <tlm keyword>  :  keyword that appears in the telemetry data
    <keyword value>  -or-  INACTIVE  :  
        specify either:
          - the keyword's new value for an insert, OR
          - INACTIVE (new status value for an existing entry)
  and an OPTIONAL argument is:
    <comment>  :  <= $COMMENTLEN characters, enclosed in quotes, 
                   describes reason for table change
        if not supplied the following defaults will be set:
          - for insert: blank (DB NULL)
          - for update: Entry deactivated by PR <PR #>

EXAMPLES:
  insert new entry, no comment (comment will be DB NULL value):
    $name 12345 a6h01hczq 0 TLMKEY1 1.234567890

  insert new entry, specifying a comment: 
    $name 23456 a2he020en 1 TLMKEY2 2 "reason entry being added"

  update existing entry to INACTIVE, accepting default comment:
    $name 34567 a8c2kecjq 0 TLMKEY3 inactive 

  update existing entry to INACTIVE, specifying a comment:
    $name 45678 a49wa2010 1 TLMKEY4 INACTIVE "no longer valid"

EOF

 }   # end subroutine PrintUsage


 ##-----------------------------------------------------------------------------
 ##
 ## validate user-provided data set name and identify instrument
 ##
 ## Note:  No database connection has been made yet, so no need to call closeDB
 ##        if there is a failure
 ##
 ## Returns:  validated data set name ready for database
 ##
 ##-----------------------------------------------------------------------------

 sub VerifyDataSet {

    my $userDataSet = $_[0];
    my $dbDataSet = "";

    # check for unacceptable characters (spaces, non-word chars && _)
    my $cleanDataSet = $userDataSet ;
    $cleanDataSet =~ s/\s+//g;
    $cleanDataSet =~ s/[\W_]+//g;
    if( $cleanDataSet ne $userDataSet ) {
       PrintMsg( "E", "<dataSet> must contain only letters and numbers" ) ;
       PrintMsg( "F", "$userDataSet contains unacceptable characters" ) ;
       exit( $FAILURE ) ;
    }

    if( length($userDataSet) < $IPPPSSOOTLEN ) {
       PrintMsg( "E", "<dataSet> must be a $IPPPSSOOTLEN-character IPPPSSOOT" ) ;
       PrintMsg( "F", "$userDataSet is too short" ) ;
       exit( $FAILURE ) ;
    }

    # DB field is varchar(39), but no existing entry is longer than 9
    if( length($userDataSet) > $IPPPSSOOTLEN ) {
       # truncate to $IPPPSSOOTLEN chars
       $dbDataSet = substr( $userDataSet, 0, $IPPPSSOOTLEN ) ;
       PrintMsg( "E", "<dataSet> must be a $IPPPSSOOTLEN-character IPPPSSOOT" ) ;
       PrintMsg( "F", "$userDataSet is too long" ) ;
       exit( $FAILURE ) ;
    }

    # uppercase user-entered data set
    $dbDataSet = uc($userDataSet);

    # derive instrument from 1st character of data set
    if    ($dbDataSet =~ /^O/) { $instName = "STIS";   }
    elsif ($dbDataSet =~ /^U/) { $instName = "WFPC2";  }
    elsif ($dbDataSet =~ /^N/) { $instName = "NICMOS"; }
    elsif ($dbDataSet =~ /^J/) { $instName = "ACS";    }
    elsif ($dbDataSet =~ /^L/) { $instName = "COS";    }
    elsif ($dbDataSet =~ /^X/) { $instName = "FOC";    }
    elsif ($dbDataSet =~ /^I/) { $instName = "WFC3";   }
    else {
       PrintMsg( "E", "First character of <dataSet> must identify instrument" );
       PrintMsg( "F", "$dbDataSet does not begin with a valid letter" );
       exit( $FAILURE );
    }

    # dataset is in correct DB format and verified by user
    return( $dbDataSet );

 }   # end subroutine CleanupDataSet


 ##-----------------------------------------------------------------------------
 ##
 ## called if user-supplied parameters indicate a new entry to insert
 ##
 ## verify the specified entry does not already exist in the table
 ## if it exits 
 ##   if the run was submitted at the command line
 ##     display the retrieved entry and an error msg to the user
 ## else
 ##   insert entry
 ##
 ## Possible outcomes:
 ##   insert not attempted because entry already exists : return $FAILURE
 ##   insert failed                                     : return $SUCCESS
 ##   insert successful                                 : return $SUCCESS
 ##
 ##-----------------------------------------------------------------------------

 sub InsertEntry {

    my $dbDataSet = $_[0];
    my $query = "" ;   # used for select and insert


    # determine if the record already exists in OTFR_KEYWORD_REPAIR
    #  (data_set_name,imset_value,telemtry_keyword uniquely ID each entry)
    # query first so if existing entries are found they can be displayed to user

    $query = <<"EOQ" ;
SELECT *
 FROM otfr_keyword_repair
 WHERE okr_data_set_name = '$dataSet'
   AND okr_imset_value = '$inImSetVal'
   AND okr_telemetry_keyword = '$inTlmKw'
EOQ
    PrintMsg( "I", "executing: \n$query" );

    # execute query
    my $cursor = DoDBIexecute( $db, $query ) ;

    my $entryFound = 0 ;
    while( my @retrRow = DoDBIfetch($db, $query, $cursor) ) {
       PrintMsg( "E", "Found existing matching entry:\n  @retrRow" ) ;
       $entryFound = 1 ;
    }

    if( $entryFound ) {
       PrintMsg( "E", "Cannot insert duplicate entry" ) ;
       return( $FAILURE ) ;
    }

    # no entries were found, entry can be inserted
    if( $clRun ) {
       # interactive run; ask user to verify entry
       print "The following entry does not exist and will be inserted:\n" ;
       print " '$instName', '$dataSet', '$imSetName', '$inImSetVal', '$inTlmKw', ' ', '$newKwVal', 
   '$inPr', '$currTs', 
   '$inStatus', '$currTs', 
   '$comment'\n" ;
       print "OK to continue? [y|n]\n";
       my $answer = <STDIN>;
       chomp($answer);
       if( $answer !~ /^[Yy]/ ) {
          PrintMsg( "I", "Terminating script at user's request" );
          return( $FAILURE ) ;
       }
    }


    # insert new entry
    $query = <<"EOQ";
INSERT INTO otfr_keyword_repair
 ( okr_instrument,
   okr_data_set_name,
   okr_imset_name,
   okr_imset_value,
   okr_telemetry_keyword,
   okr_fits_keyword,
   okr_keyword_value,
   okr_pr_number,
   okr_entry_date,
   okr_status,
   okr_status_date,
   okr_status_comment )
VALUES
 ( '$instName',
   '$dataSet',
   '$imSetName',
   '$inImSetVal',
   '$inTlmKw',
   ' ',
   '$newKwVal',
   '$inPr',
   '$currTs',
   '$inStatus',
   '$currTs',
   '$comment' )
EOQ
    PrintMsg( "I", "executing: \n$query" );

    # execute insert
    my $insCount = DoDBI( $db, $query );

    if( 1 == $insCount ) {
       # insert successful
       PrintMsg( "I", "Inserted $insCount new entry into OTFR_KEYWORD_REPAIR" );
       return( $SUCCESS ) ;
    } else {
       # insert failed
       PrintMsg( "E", "Failed to insert new entry into OTFR_KEYWORD_REPAIR" );
       return( $FAILURE ) ;
    }

 }   # end subroutine InsertEntry


 ##-----------------------------------------------------------------------------
 ##
 ## called if user-supplied parameters indicate an existing entry is to be 
 ##    deactivated
 ##
 ## verify the specified entry exists in the table
 ## if it does NOT exist
 ##   if the run was submitted at the command line
 ##     display an error msg to the user
 ## else
 ##   update status of entry to INACTIVE
 ##
 ## Possible outcomes:
 ##   update not attempted because:            return $FAILURE
 ##    entry not found OR
 ##    multiple entries found (should not happen)
 ##    status of existing entry is already INACTIVE; no update needed
 ##   entry successfully updated to INACTIVE : return $SUCCESS
 ##   update failed                          : return $FAILURE
 ##
 ##-----------------------------------------------------------------------------

 sub UpdateEntry {

    my $dbDataSet = $_[0];
    my $query = "" ;   # used for select and update

    # verify the record already exists in OTFR_KEYWORD_REPAIR as ACTIVE
    #  (data_set_name,imset_value,telemtry_keyword uniquely ID each entry)
    # query first so if existing entries are found they can be displayed to user

    $query = <<"EOQ" ;
SELECT * FROM otfr_keyword_repair
 WHERE okr_data_set_name = '$dataSet'
   AND okr_imset_value = '$inImSetVal'
   AND okr_telemetry_keyword = '$inTlmKw'
EOQ
    PrintMsg( "I", "executing: \n$query" );

    # execute query
    my $cursor = DoDBIexecute( $db, $query ) ;

    my $entryFound = 0 ;
    my $entryStatus = "" ;
    while( ($in, $ds, $is, $iv, $tk, $fk, $kv, $pr, $id, $st, $ud, $cm) = 
           DoDBIfetch($db, $query, $cursor) ) {
       PrintMsg( "I", 
                 "found matching entry:\n $in, $ds, $is, $iv, $tk, $fk, $kv, $pr, $id, $st, $ud, $cm" ) ;
       # save the status for evaluation
       $entryStatus = $st ;
       $entryFound += 1 ;
    }

    if( $entryFound == 0 ) {
       PrintMsg( "E", "Found no matching entry to update" ) ;
       return( $FAILURE ) ;
    } elsif( $entryFound > 1 ) {
       # since retrieval is not using a unique key; multiple entries could be selected
       PrintMsg( "E", "Found multiple matching entries:" ) ;
       return( $FAILURE ) ;
    } else {
       # exactly one entry retrieved, as expected; verify status
       if( uc($entryStatus) eq $STATINACT ) {
          PrintMsg( "E", "This entry is already $STATINACT" ) ;
          return( $FAILURE ) ;
       }
    }

    # found one ACTIVE entry
    if( $clRun ) {
       # interactive run; ask user to verify update
       print "\nThe entry listed above will be updated as follows:\n" ;
       print   " OKR_STATUS to:  $STATINACT\n";
       print   " OKR_STATUS_COMMENT to: $comment\n";
       print "OK to continue? [y|n]\n";
       my $answer = <STDIN>;
       chomp($answer);
       if( $answer !~ /^[Yy]/ ) {
          PrintMsg( "I", "Terminating script at user's request" );
          return( $FAILURE ) ;
       }
    }


    # update entry
    $query = <<"EOQ";
UPDATE otfr_keyword_repair
 SET okr_status = '$STATINACT',
     okr_status_comment = '$comment'
 WHERE okr_data_set_name = '$dataSet'
   AND okr_telemetry_keyword = '$inTlmKw'
   AND okr_imset_value = '$inImSetVal'
EOQ
    PrintMsg( "I", "executing: \n$query" );

    # execute update
    my $updCount = DoDBI( $db, $query );

    if( 1 == $updCount ) {
       # update successful
       PrintMsg( "I", "Updated $updCount OTFR_KEYWORD_REPAIR entry to $STATINACT" );
       return( $SUCCESS ) ;
    } else {
       # update failed
       PrintMsg( "E", "Failed to update status of OTFR_KEYWORD_REPAIR entry" );
       return( $FAILURE ) ;
    }

 }   # end subroutine UpdateEntry

# end script update_otfr_keyword_repair.pl