#!/usr/bin/env perl 
#-w 
#----------------------------------------------------------------------------
#
# Name: insert_stis_fringeflat_records
#
# This perl script verifies that the input environment variables are OK, then
# it generates a set of queries to insert stis_fringeflat records.
#
# All the records are generated using data within the replan time range of the
# SMS that is found in file_times.
#
# During processing the following volatile tables are cleared out, then filled:
#       stis_fringetmp_externals      all external stis observations except dumps
#       stis_fringetmp_all_in_sms     all stis observations in SMS, used to verify
#                                     that no changes in OPT_ELEM/CENWAVE
# They are left filled until the script runs again so that records are available 
# for verification if needed between runs.
#
# Interactive Usage:
#	insert_stis_fringeflat_records.pl  [pod_name]
#
#       if pod_name is missing the ENV variable OSF_DATASET is used.
#
#	Note that the following environment variables are also required:
#       OPUS_SERVER, SPSS_DB and OPUS_DB.
#       These queries require multiple _DBs on SAME server: 
#       keeping DSQUERY as a variable to emphasize this point.
#
#  Return status: 0 = success, 1 = failure 
#
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 06/16/08          Heller      Copied from   Baum nic_saa
# 09/12/08 60816    Hamilton    Added # in front of temporary tables.
# 07/09/09 62851    Sherbert    Rm temp tbls in favor of dbo tbls, fix a bug
# 06/30/10 64432    MSwam       Use single quotes for SQLServer
# 07/30/10 64432    MSwam       MORE single quotes (in INSERT)
# 09/18/12 72255    Sherbert    keep DSQUERY in code to emphasize the sharing 
#----------------------------------------------------------------------------
# set up external routines
unshift @INC,(split /:/, $ENV{PATH});
require 'printmsg.pl';       # prints a formatted status message
require 'do_dbi_pkg.pl';     # DoDBI package - convenience routines
require 'sogs_time_pkg.pl';  # manipulate time in SOGS format(yyy.ddd:hh:mm:ss) 

#specify exit status values
$PROCESS_FAILURE = 1; # exit status for XPOLL
$PROCESS_SUCCESS = 0; # exit status for XPOLL


#check for arguments
my $num_arg = scalar @ARGV;

if ($num_arg > 0 && substr($ARGV[0],0,1) ne "-") {
   $POD_FILE = $ARGV[0];
} else {
   $POD_FILE = $ENV{"OSF_DATASET"};
}

if ( !defined($POD_FILE)) {
   PrintMsg ("E","Missing POD name in args or environment");
   exit( $PROCESS_FAILURE);
} 
# get the external variables
$DSQUERY=           $ENV{"OPUS_SERVER"};
$OPUS_DB=           lc($ENV{"OPUS_DB"});
$SPSS_DB=           lc($ENV{"SPSS_DB"});

# begin processing
PrintMsg ("I","--- start --- Insert STIS_FRINGEFLAT Table Records ------");
PrintMsg ("I","--- POD: $POD_FILE");

my $db3 = DoDBIopen( $DSQUERY, $OPUS_DB, $PROCESS_FAILURE);
my $db2 = DoDBIopen( $DSQUERY, $OPUS_DB, $PROCESS_FAILURE);

# PR 62851
# The queries in this script are complex and nested, therefore it was 
# decided that using temporary tables is a bad idea because they disappear 
# if a connection is dropped, which has been happening.  Therefore, we will
# add empty dbo tables to serve as permanent "temporary" tables to prevent 
# the failures causes when connections are dropped.

# Clean out dbo (permanent) "temporary" (volatile) tables 

# DELETE returns a count which may make DoDBI happy and allows us to report
# what is going on
my $msg   = "DELETE stis_fringetmp_externals";
PrintMsg("D", $msg, "main");
my $clnCount1 = DoDBI($db2, "DELETE stis_fringetmp_externals");
$msg = "DELETED $clnCount1 records from stis_fringetmp_externals";
PrintMsg("I", $msg, "main");

$msg      = "DELETE stis_fringetmp_all_in_sms";
PrintMsg("D", $msg, "main");
my $clnCount2 = DoDBI($db2, "DELETE stis_fringetmp_all_in_sms");
$msg = "DELETED $clnCount2 records from stis_fringetmp_all_in_sms";
PrintMsg("I", $msg, "main");

#
# get time range of this SMS 
#
$pod_name = uc( $POD_FILE); #uppercased pod name needed for query

get_sms_time_range();

collect_all_stis();
collect_stis_externals();


#---------------------------------------------------------------------
#  For each external STIS
#    Find the right internal STIS by aperture preference and time
#      Convert external and internal to association id if applicable
#      Add record to stis_fringeflat
#---------------------------------------------------------------------


# Get list of all STIS externals...
my $query1 = <<"EOQ";
SELECT program_id,obset_id,ob_number,sunit_id,minwave,sp_1,pred_strt_tm
 FROM stis_fringetmp_externals
WHERE stis_fringetmp_externals.sp_1='G750L' 
   OR stis_fringetmp_externals.sp_1='G750M'
ORDER BY program_id,obset_id,ob_number desc
EOQ

my @already_added;

$verbose = 1;   # turn off after one time through loop, else will be too much
if ( $verbose ) { printQuery ($query1, "main") }
my $sth = DoDBIexecute( $db2, $query1);

while ( my ($id, $obs, $num, $su, $min, $sp, $tm) = 
       DoDBIfetch($db2, $query1, $sth)) {  # For each external record

   PrintMsg ("I","_____________________________________");


   my $ext_asn_id = get_asn_id($id,$obs,$num);
   if (!defined($ext_asn_id)) { # Not part of association
       $ext_asn_id = "O$id$obs$num"."Q";
   }

   my $done_already=0;
   if (grep {$_ eq $ext_asn_id} @already_added) {
       $done_already=1;
   }
   # Skip if this one already in stis_fringeflat table...
   if ($done_already == 0) {

       # "OVL100","OVL050","OVL200","OVL500","OVL2","OV330X090","*"
       # is the aperture preference

       my ($intid,$intobs,$intnum)=get_int_by_aper($su,$min,$sp,$tm,"SL100");
       if ($intid eq "") {
           ($intid,$intobs,$intnum) =
           get_int_by_aper($su,$min,$sp,$tm,"SL050");
       }
       if ($intid eq "") {
           ($intid,$intobs,$intnum) =
           get_int_by_aper($su,$min,$sp,$tm,"SL200");
       }
       if ($intid eq "") {
           ($intid,$intobs,$intnum) =
           get_int_by_aper($su,$min,$sp,$tm,"SL500");
       }
       if ($intid eq "") {
           ($intid,$intobs,$intnum) =
           get_int_by_aper($su,$min,$sp,$tm,"SL2");
       }
       if ($intid eq "") { 
           ($intid,$intobs,$intnum) =
           get_int_by_aper($su,$min,$sp,$tm,"S330X090");
                      }
       if ($intid eq "") {
           ($intid,$intobs,$intnum) =
           get_int_by_aper($su,$min,$sp,$tm,"*");
       }

       if ($intid ne "") {
           # Translate internal member name into association id
           #    
           my $int_asn_id = get_asn_id($intid,$intobs,$intnum);
           if (!defined($int_asn_id)) {
               $int_asn_id = "O$intid$intobs$intnum"."M";
           }
           add_stis_fringeflat($ext_asn_id,$int_asn_id)
           } else {
               PrintMsg("I","NO Internal for External $id $obs $num $su");
           } 
   }
   push @already_added, $ext_asn_id;
   $verbose = 0;
}


DoDBIclose($db2);
PrintMsg ("I","---  end  --- Insert STIS_FRINGE Table Records ----------");
exit( $PROCESS_SUCCESS);  # EXIT_SUCCESS

#--------------------------------------------------------------------
# End Main
#--------------------------------------------------------------------



#---------------------------------------------------------------------
#  Use file_times table to get true time range of SMS. Always use the
#  replan time and report an info message if the replan time is not
#  equal to the start time.
#---------------------------------------------------------------------
sub get_sms_time_range {  # Use global pod_name

    my $query = <<"EOQ";
SELECT window_start, window_stop, replan_time 
 FROM file_times
WHERE dataset_name = '$pod_name' 
  AND archclass = 'MSC' OR  archclass = 'SMS' 
EOQ

    if ( $verbose ) { printQuery ( $query, "gstr") }
    my $sth = DoDBIexecute( $db2, $query);
    my ($sms_st, $sms_sp, $old_st) = DoDBIfetch($db2, $query, $sth);
    PrintMsg("I", "Got: start: $sms_st stop: $sms_sp replan: $old_st ", "gstr");
    $sms_start = DecompressTime($sms_st);
    $sms_stop  = DecompressTime($sms_sp);
    $old_start = DecompressTime($old_st);

    if ($sms_start ne $old_start and $old_start ne "N/A") {
        PrintMsg ("I","Replan SMS found. Original start is $old_start.");
        $sms_start = $old_start;
    }
    PrintMsg ("I","Start: $sms_start  End: $sms_stop.");
    #

}

#---------------------------------------------------------------------
# Fill volatile table on OPUS_DB for STIS external observations in time 
# range of SMS that need FRINGEFLAT keyword populated.  
# Exclude dumps having blank control_id.
#---------------------------------------------------------------------
sub collect_stis_externals {

    my $query = <<"EOQ";
INSERT INTO stis_fringetmp_externals
(program_id,  obset_id,    ob_number, proposal_id, alignment_id, 
 exposure_id, version_num, sp_1,      minwave,     sunit_id,  pred_strt_tm)
SELECT o.program_id, o.obset_id, o.ob_number, l.proposal_id, l.alignment_id, 
       l.exposure_id,l.version_num,s.sp_1,s.minwave,b.sunit_id, o.pred_strt_tm
FROM  $SPSS_DB..qobservation o, $SPSS_DB..qolink l,  $SPSS_DB..qesiparm e, 
$SPSS_DB..qelogsheet s, $SPSS_DB..qsbranching b
WHERE o.pred_strt_tm>'$sms_start'
  AND o.pred_strt_tm<'$sms_stop'
  AND o.si_id = 'STIS'
  AND o.control_id!=' '
  AND l.program_id = o.program_id
  AND l.obset_id = o.obset_id 
  AND l.ob_number = o.ob_number
  AND e.si_par_name = 'TARGTYPE' 
  AND e.si_par_value = 'EXTERNAL'
  AND l.proposal_id=e.proposal_id 
  AND l.obset_id=e.obset_id
  AND l.alignment_id=e.alignment_id 
  AND l.exposure_id=e.exposure_id 
  AND l.version_num=e.version_num 
  AND l.proposal_id=s.proposal_id 
  AND l.obset_id=s.obset_id
  AND l.alignment_id=s.alignment_id 
  AND l.exposure_id=s.exposure_id 
  AND l.version_num=s.version_num 
  AND l.proposal_id=b.proposal_id 
  AND l.obset_id=b.obset_id
  AND l.version_num=b.version_num   
  AND (s.sp_1='G750L' or s.sp_1='G750M')
EOQ

    if ( $verbose ) { printQuery ($query, "cext") }
    my $count = DoDBI( $db2, $query );
    PrintMsg ("I","$count STIS observations put into tempdb..stis_ext.");

}

#---------------------------------------------------------------------
# Fill volatile table on OPUS_DB for STIS internal observations in time 
# range of SMS that are possible FRINGEFLAT keyword values
# Exclude dumps having blank control_id.
#---------------------------------------------------------------------
sub collect_all_stis {

    my $query = <<"EOQ";
INSERT INTO stis_fringetmp_all_in_sms
( program_id, obset_id, ob_number, sp_1, minwave, sunit_id, 
       pred_strt_tm, ap_1, si_par_name, si_par_value )
SELECT o.program_id, o.obset_id, o.ob_number, s.sp_1, s.minwave, b.sunit_id, 
       o.pred_strt_tm, s.ap_1, e.si_par_name, e.si_par_value
 FROM  $SPSS_DB..qobservation o, $SPSS_DB..qolink l,  $SPSS_DB..qesiparm e, 
       $SPSS_DB..qelogsheet s,   $SPSS_DB..qsbranching b
WHERE o.pred_strt_tm>'$sms_start'
  AND o.pred_strt_tm<'$sms_stop'
  AND o.si_id = 'STIS'
  AND o.control_id!=' '
  AND l.program_id = o.program_id
  AND l.obset_id = o.obset_id 
  AND l.ob_number = o.ob_number
  AND l.proposal_id=e.proposal_id 
  AND l.obset_id=e.obset_id
  AND l.alignment_id=e.alignment_id 
  AND l.exposure_id=e.exposure_id 
  AND l.version_num=e.version_num 
  AND l.proposal_id=s.proposal_id 
  AND l.obset_id=s.obset_id
  AND l.alignment_id=s.alignment_id 
  AND l.exposure_id=s.exposure_id 
  AND l.version_num=s.version_num 
  AND l.proposal_id=b.proposal_id 
  AND l.obset_id=b.obset_id
  AND l.version_num=b.version_num 
  AND e.si_par_name = 'TARGTYPE' 
EOQ

    if ( $verbose ) { printQuery ($query, "cAll") }
    my $count = DoDBI( $db2, $query );
    PrintMsg ("I","$count STIS observations found for this sms.");

}



#--------------------------------------------------------------------
# Get association_id for exposure
#--------------------------------------------------------------------
sub get_asn_id {

    my ($id,$obs,$num) = @_;
    my $query = <<"EOQ";
SELECT a.association_id
 FROM asn_members a
WHERE a.program_id = '$id'
  AND a.obset_id   = '$obs'
  AND a.member_num = '$num'
EOQ

    if ( $verbose ) { printQuery ($query, "gASN") }
    my $sth = DoDBIexecute( $db2, $query);
    my ($asn_id) = DoDBIfetch($db2, $query, $sth);
    return ($asn_id)
}


#--------------------------------------------------------------------
# Find internal exposure by aperture name
#
# 09/06/18 62851 Sherbert  Change the logic for the times
#--------------------------------------------------------------------
sub get_int_by_aper {
   ($sunit,$minwave,$sp_1,$ext_time, $aper_prefer,) = @_;

   my $best_time=99999999999999;
   my $diff_time=0;
   my $return_id="";
   my $return_obs="";
   my $return_num="";
   my $last_minwave=$minwave;
   my $last_sp_1=$sp_1;

   my $query3 = <<"EOQ";
SELECT program_id,obset_id,ob_number,pred_strt_tm,
       si_par_name,si_par_value,minwave,sp_1,ap_1
 FROM stis_fringetmp_all_in_sms 
WHERE sunit_id = '$sunit'
  AND si_par_name='TARGTYPE'
ORDER BY pred_strt_tm 
EOQ

    if ( $verbose ) { printQuery ($query3, "giba") }
   my $sth2 = DoDBIexecute( $db3, $query3);
   while (my ($id,$obs,$num,$tm,$parname,$parvalue,$min,$sp,$aper) = 
          DoDBIfetch($db3, $query3, $sth2)) {

       PrintMsg ("I","For entry: $id$obs$num MSM: $min, $sp");

       if ($parname eq "TARGTYPE" and 
           ($parvalue eq "CCDFLAT" or $parvalue eq "TUNGSTEN") and
           ($min == $minwave and $sp eq $sp_1) and 
           ($aper eq $aper_prefer or $aper_prefer eq "*")){
            PrintMsg ("I","This ($id$obs$num) internal matches MSM combo");

           # Right MSM combo before this internal,is it closest in time

           my $internal_time = convert_time_to_int($tm);      
           my $external_time = convert_time_to_int($ext_time);
           #PrintMsg ("I", " $internal_time $external_time ");

           my $diff_time = abs($internal_time - $external_time);
           if ($diff_time<$best_time) {
               #Check now that no change in sp_1 or minwave between this
               # internal obs and external obs
               my $number_msm_changes=0;
               if ($internal_time < $external_time) {
                   # tm (internal) is earlier than ext_time (external)
                   $number_msm_changes=any_MSM_changes($tm,$ext_time);
               } else {
                   # tm (internal) is later than ext_time (external)
                   $number_msm_changes=any_MSM_changes($ext_time,$tm);
               }
               if ($number_msm_changes==0) {
                   $return_id=$id;
                   $return_obs=$obs;
                   $return_num=$num;
                   $best_time = $diff_time;
                   PrintMsg ("I",
                    "Time difference: $diff_time from $id $obs $num $tm");
               } else {
                   PrintMsg ("I",
                    "MSM changes between $tm $ext_time for $id $obs $num $tm");
               }
           }          
       } 
   }        

   return  ($return_id,$return_obs,$return_num);    
}

#---------------------------------------------------------------------
# Check for Mode Select Mechanism changes since last exposure
# 
# From PR 55219:
# STIS GO specified wavecal exposures and STIS IR fringeflat exposures
# should be associated with the corresponding science exposures, so
# that these internal lamp exposures can be delivered to archive users
# along with the external science data. The GO wavecals will also be
# used during calibration in OTFR.
# 
# STIS GO WAVECAL ASSOCIATION REQUIREMENTS
# 
# Preferred requirements:  For any STIS spectroscopic science
# observation, all wavecal exposures (defined as internal exposures
# with SCLAMP=LINE, HITM1, or HITM2) taken during the same visit which
# use the same grating (OPT_ELEM) and central wavelength (CENWAVE)
# combination as the science observation, should be associated with
#                      * * *
# that science observation if and only if there was no movement of the
# mode select mechanism (MSM) between the two exposures (i.e., no
# intervening STIS exposures with anything other than that same
#                      * * *
# OPT_ELEM/CENWAVE).  
# 
#---------------------------------------------------------------------
sub any_MSM_changes {

    my ($first_tm,$second_tm) = @_;

    my $query = <<"EOQ";
SELECT program_id,obset_id,ob_number,pred_strt_tm,
       si_par_name,si_par_value,minwave,sp_1,ap_1
 FROM stis_fringetmp_all_in_sms 
WHERE minwave != $minwave
  AND sp_1 != '$sp_1'
  AND pred_strt_tm > '$first_tm'
  AND pred_strt_tm < '$second_tm'
EOQ

    if ( $verbose ) { printQuery ($query, "aMSM") }
    my $count = DoDBI( $db2, $query);
    return ($count)

}

#---------------------------------------------------------------------
# Add records to stis_fringeflat relation
#---------------------------------------------------------------------
sub add_stis_fringeflat {

    my ($this_sci_exposure,$fringeflat) = @_;

    my $queryDel = <<"EOQ";
DELETE FROM stis_fringeflat
WHERE sci_exposure = '$this_sci_exposure'
EOQ

    if ( $verbose ) { printQuery ($queryDel, "asfr") }
    my $countDel = DoDBI( $db2, $queryDel );
    if ($countDel > 0) {
        PrintMsg ("I",
         "$countDel deleted in stis_fringeflat for sci_exp: $this_sci_exposure");
    }

    my $query = <<"EOQ";
INSERT INTO stis_fringeflat
VALUES ('$this_sci_exposure', '$fringeflat')
EOQ

    if ( $verbose ) { printQuery ($query, "asfr") }
    my $count15 = DoDBI( $db2, $query );
    PrintMsg ("I",
        "$count15 in stis_fringeflat sci_exp: $this_sci_exposure frflat: $fringeflat.");

}


#---------------------------------------------------------------------
# Convert time to integer so it can be used in numeric comparisons
#---------------------------------------------------------------------
sub convert_time_to_int {
   my ( $date_time ) = @_;    
   my $int_time =0;
   if (!defined($date_time)) {
       PrintMsg ("E","Empty string date_time");
       return($int_time);
   }

   if (length($date_time)<17) {
       PrintMsg ("E","String $date_time, not valid format");
       return($int_time);
   }
   my $dstr= substr($date_time,0,4) . substr($date_time,5,3) .
       substr($date_time,9,2) .substr($date_time,12,2) .
       substr($date_time,15,2);

   $int_time = $dstr;

   #PrintMsg ("I","String $date_time, Int $int_time");
   return ($int_time);
}


#---------------------------------------------------------------------
# Debugging: print query string and short id of from whence it came
#---------------------------------------------------------------------
sub printQuery {

    my ( $inQuery, $id ) = @_ ;
    PrintMsg ("D", "query is \n$inQuery", $id );

}