#!/usr/bin/perl -w

# This program takes a list of filenames on the command line as input.
# These files should contain carefully formatted flat text tables
# containing HiRISE target suggestions. Some basic checks for sensibility are
# peformed before writing to the target to the HiCAT database.
# See the file hirise_targets.lbl for details of the table description.

# Written: 2/28/2006
# Contact: Shane Byrne - shane@lpl.arizona.edu
#
# Updates and Bug Fixes:
# ---------------------- 
# 8/15-16/2006:
# Added support for the new CRISM_COORDINATION and DESCRIPTION fields.
# Color/Stereo Justification is now Color/Stereo/CRISM Justification
# Changed script to use the Suggestion_Relations and Related_Suggestions_map for stereo and seasonal observations
# Each component of a seasonal observation now has its own Ls range
# Dis-allowed stereo and seasonally repeating properties in the same observation (too ambiguous as to when the stereo is requested)
# Moved to DBI perl modeule for MySQL interaction
# Script now properly disconnects from MySQL database after running
# Replaced $dbh->last_insert_id(); with $dbh->{'mysql_insertid'}; - Now works on all PIRL systems
#
# 8/21-22/2006:
# Upper limit on incidence angle is now 100 degrees instead of 90
# Added command line options for database, host, userrname, password
# Added validate only option
# Added option to redirect errors/success to file 
# Added some help, apperas with "-h" option 
# Added support for transactions, everything rolls back when there's an SQL error
#
# 8/28/2006: (Debugging)
# Changed password entry so that it does no echo to screen when inputted by user
# Changed order in which science themes are written to science theme map table, now writes secondary one first
# Now writes special notes field even if string is empty, previously just left as null
# Stereo/color/high-res priority is set to 1 when that property in not desired, previously just left as null
# Old method of forming ROI string might not be working properly, now use the chop operator to deal with trailing comma
#
# 9/7/2006: 
# Now allows MAX_LS to be less than MIN_LS
#
# 9/19/2006: 
# Fixed test for crism coordination (a string) old version used '=' instead of 'eq' ... doh!
# Cleaned up code for stereo and hi-res justification checks
#
# 10/15/2006: 
# Cleaned up code for justifications check... introduced bug on 9/19... duh!


# Table Entries follow the scheme...
# 0:   PRIORITY_OVERALL
# 1:   SCIENCE_RATIONALE
# 2:   DESCRIPTION
# 3:   PRIMARY_SCIENCE_THEME
# 4:   SECONDARY_SCIENCE_THEME
# 5:   ROI_VERTICES
# 6:   PRIORITY_STEREO
# 7:   PRIORITY_COLOR
# 8:   CRISM_COORDINATION
# 9:   JUSTIFICATION_COLOR_STEREO_CRISM
# 10:  PRIORITY_HIGHRES
# 11:  MAX_BINNING
# 12:  JUSTIFICATION_HIGHRES
# 13:  MAX_LSUBS
# 14:  MIN_LSUBS
# 15:  NUMBER_OF_OBSERVATIONS
# 16:  MAX_PHASE_ANGLE
# 17:  MIN_PHASE_ANGLE
# 18:  MAX_INCIDENCE_ANGLE
# 19:  MIN_INCIDENCE_ANGLE
# 20:  MAX_EMISSION_ANGLE
# 21:  MIN_EMISSION_ANGLE
# 22:  SPECIAL_NOTES
# 23:  USERNAME

# These fields are calculated from the provided information or assigned
#
# CENTER_LATITUDE
# CENTER_LONGITUDE
# MAX_LATITUDE
# MIN_LATITUDE
# MAX_LONGITUDE
# MIN_LONGITUDE
# NEED_STEREO
# NEED_COLOR

# These fields take fixed values
#
# SUGGESTION_DATE
# TARGET_NAME
# EXACT_AREA_REQUESTED


# These fields in the Suggested_Observations table are not edited
#
# ID
# VERSION
# PRIORITY
# DATE_PRIORITIZED
# SUCCESS_VALIDATOR_ID
# DATE_SUCCESS
# LAST_UPDATE

#############################################################################
#############################################################################
# Parse options and connect to HiRISE database.
use DBI;
use Getopt::Long;
use Term::ReadKey;

$dbase    = "HiRISE_Test";
$user     = "";
$pass     = "";
$host     = "pirldb.lpl.arizona.edu";
$help     = 0;
$validate = 0;
$output   = '';

$opt = GetOptions ("dbase|d=s"         => \$dbase,
                   "host=s"            => \$host,
                   "user|u=s"          => \$user,
                   "pass|p=s"          => \$pass,
                   "help|h"            => \$help,
                   "validate|v"        => \$validate,
                   "output|o=s"        => \$output);

if (!$opt || $help || !@ARGV) {
 print "\n";
 print "hirise_targets_updated2.pl -dbase -host -user -pass -help -validate -output  XXXXXX.txt YYYYY.txt\n";
 print "\n";
 print "  dbase|d    database to connect to (default is HiRISE_Test)\n";
 print "  host       hostname where the database is stored (default is pirldb.lpl.arizona.edu)\n";
 print "  user|u     username to connect to database (not username associated with suggestions)\n";
 print "  pass|p     password to connect to database (program will prompt you if not provided here)\n";
 print "  help|h     print this help message\n";
 print "  validate|v verify that the records are error free, but do not write to database\n";
 print "  output|o   redirect error or success messages to filename provided\n";
 print "\n";
 print "In addition, this program takes a list of filenames on the command line as input e.g.XXXXXX.txt YYYYY.txt. 
These files should contain carefully formatted flat text tables containing HiRISE target suggestions. Some
basic checks for sensibility are peformed before writing to the target to the HiCAT database. See the file
hirise_targets.lbl for details of the table description. As complete as possible a list of errors is generated
where the table enteries look suspicious.\n";
 print "Questions? Contact: Shane Byrne - shane\@lpl.arizona.edu\n";
 if (!$opt)  {die("\nYou have an error in the options you just tried...\n") };
 if (!@ARGV) {die("\nNo input files were specified...\n") };
 die("\n");
}

foreach $i (@ARGV) {
 if (!(-e $i)){ 
  die("I cannot find the file $i...\n");
 }
 if (!(-T $i)){ 
  die("File $i is not a textfile...\n");
 }
}

if ($user eq '') {
 print "MySQL username: ";
 $user = <STDIN>;
 chomp $user;
}

if ($pass eq '') {
 print "MySQL password: ";
 ReadMode('noecho');
 $pass = ReadLine(0);
 chomp $pass;
 ReadMode('normal');
}

$dbh = DBI->connect("dbi:mysql:database=".$dbase.";host=".$host, $user, $pass);
$dbh->{RaiseError} = 1;
$dbh->{AutoCommit} = 0;

$fout = 0;
if ($output) {
 $fout = open LOG, "> $output";
 if (!$fout) {die("I tried to create $output for output results, but could not!")};
}

# First we do some idiot-proofing, we need some files as inputs, 
# which both must exist and be text files.


# Read in all the targets within the specified textfiles.
print "Reading files...\n";
@targets = <>;

# Loop through targets performing reality checks
print "File(s) read in... performing reality checks on input... \n";

$ln  = 1;
$err = 0;
if ($fout) {select LOG};
foreach $rec (@targets) 
{
 chop($rec);
 (@n1) = split(/,/,$rec);

 # Check that there are enough columns in the table line
 # Skip the line if the number of columns is not correct
 
 if ($#n1 != 23){
  print "Line $ln: Table must have 24 columns, line $ln is unusable!!! \n";
  $err = $err + 1;
 } else {

 # 0:  NAME = PRIORITY_OVERALL
 if (($n1[0] > 5) || ($n1[0] < 1) || ($n1[0] != int($n1[0]))) {
  print "Line $ln: Overall priority must be an integer in the range of 1-5. \n";
  $err = $err + 1;
 }

 # 1:  NAME = SCIENCE_RATIONALE
 $_ = $n1[1]; (s/^\s+//g); (s/\s+$//g); 
 if ((substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"') || ($_ eq '""')) {
  print "Line $ln: Science Rationale must be set and enclosed in double quotes. \n";
  $err = $err + 1;
 }

 # 2:  NAME = DESCRIPTION
 $_ = $n1[2]; (s/^\s+//g); (s/\s+$//g); 
 if ((substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"') || ($_ eq '""') || (length($_) > 75)) {
  print "Line $ln: Description must be set, enclosed in double quotes and be shorter than 75 characters. \n";
  $err = $err + 1;
 }

 # 3:  NAME = PRIMARY_SCIENCE_THEME
 # 4:  NAME = SECONDARY_SCIENCE_THEME
 $_ = $n1[3]; (s/^\s+//g); (s/\s+$//g); 
 if ((substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"') || ($_ eq '""')) {
  print "Line $ln: Primary Science must exist and be enclosed in double quotes. \n";
  $err = $err + 1;
 } else {
  $sqlcmd = "select ID from Science_Themes where THEME = ".$n1[3];
  @thm1 = $dbh->selectrow_array($sqlcmd);
  if ($#thm1 == -1) {
   print "Line $ln: The Primary science theme must exactly match one of those in HiWEB.\n";
   $err = $err + 1;
  }
 }
 
 $_ = $n1[4]; (s/^\s+//g); (s/\s+$//g); 
 if ((substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"') || ($_ eq '""')) {
  print "Line $ln: Secondary Science must exist and be enclosed in double quotes. \n";
  $err = $err + 1;
 } else {
  $sqlcmd = "select ID from Science_Themes where THEME = ".$n1[4];
  @thm2 = $dbh->selectrow_array($sqlcmd);
  if ($#thm2 == -1) {
   print "Line $ln: The Secondary science theme must exactly match one of those in HiWEB.\n";
   $err = $err + 1;
  }
 }

 # 5:  NAME = ROI_VERTICES

 $_    = $n1[5]; (s/^\s+//g); (s/\s+$//g);
 if ((substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"') || ($_ eq '""')) {
  print "Line $ln: ROI vertices must exist and be enclosed in double quotes. \n";
  $err = $err + 1;
 } else {
  (s/"//g);
  @nroi = split(/ /,$_);
  $ele  = ($#nroi + 1)/2.0 ;

  if (($ele < 3) || ($ele > 16)) {
   print "Line $ln: You must have at least 3 and at most 16 vertices for each ROI polygon. \n";
   $err = $err + 1;
  } else {

   for ($i = 1; $i <= $ele; $i++) {
    $lat[$i-1] = $nroi[2.0*($i-1) + 1];
    $lon[$i-1] = $nroi[2.0*($i-1)    ];
   }
   $#lon = $ele-1;
   $#lat = $ele-1;
   $minlat = (sort {$a <=> $b} @lat)[0];
   $maxlat = (sort {$b <=> $a} @lat)[0];
   $minlon = (sort {$a <=> $b} @lon)[0];
   $maxlon = (sort {$b <=> $a} @lon)[0];

   if ($maxlon-$minlon > 180) {
    for ($i = 1; $i <= $ele; $i++) {
     if ($lon[$i-1] > 180) { 
      $lon[$i-1] = $lon[$i-1] - 360.0;
     }
    }
    $minlon = (sort {$a <=> $b} @lon)[0];
    $maxlon = (sort {$b <=> $a} @lon)[0];
   }

   $area   = 3513.489766 * ($maxlat-$minlat) * ($maxlon-$minlon) * cos(0.5*($maxlat+$minlat) / 57.296);
   
   if ($area > 10000.0) {
    print "Line $ln: ROI bounding box has an area of $area square km!!! This looks like an error (the cutoff is 10,000 square km). \n";
    $err = $err + 1;
   }
   
   if (($maxlat > 90.0) || ($minlat < -90.0) || ($maxlon > 360.0) || ($minlon < -180.0)) {
    print "Line $ln: ROI verticies out of range, did you mix up longitude and latitude? \n";
    $err = $err + 1;
   }
  }
 }
 
 # 6:  NAME = PRIORITY_STEREO
 # 7:  NAME = PRIORITY_COLOR
 # 8:  NAME = CRISM_COORDINATION
 # 9:  NAME = JUSTIFICATION_COLOR_STEREO
 if (($n1[6] > 5) || ($n1[6] < 0) || ($n1[6] != int($n1[6]))) {
  print "Line $ln: Stereo priority must be either 0 (No stereo) or an integer in the range of 1-5. \n";
  $err = $err + 1;
 }
 if (($n1[7] > 5) || ($n1[7] < 0) || ($n1[7] != int($n1[7]))) {
  print "Line $ln: Color priority must be either 0 (No color) or an integer in the range of 1-5. \n";
  $err = $err + 1;
 }

 $_ = $n1[8]; (s/^\s+//g); (s/\s+$//g); 
 if ( (substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"') || (($_ ne '"YES"') && ($_ ne '"NO"')) ) {
  print "Line $ln: CRISM coordination field must be set to YES or NO and be enclosed in double quotes \n";
  $err = $err + 1;
 }

 $_ = $n1[9]; (s/^\s+//g); (s/\s+$//g); 
 if ((substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"')) {
  print "Line $ln: Color/Stereo/CRISM justification must be enclosed in double quotes. (Even if empty)  \n";
  $err = $err + 1;
 }
 if ((($n1[6] > 0) || ($n1[7] > 0) || ($n1[8] eq '"YES"')) && ($_ eq '""')) {
  print "Line $ln: Color/Stereo/CRISM justification must be set if any of these are requested. \n";
  $err = $err + 1;
 }

 # 10:  NAME = PRIORITY_HIGHRES
 # 11:  NAME = MAX_BINNING
 # 12: NAME = JUSTIFICATION_HIGHRES
 if (($n1[11] > 4) || ($n1[11] < 1) || ($n1[11] != int($n1[11]))) {
  print "Line $ln: Maximum binning allowed must be an integer in the range of 1-4. \n";
  $err = $err + 1;
 }
 if (($n1[11] == 1) && (($n1[10] > 5) || ($n1[10] < 1))) {
  print "Line $ln: Hi-res priority must be in the range of 1-5 when a maximum binning of 1 is requested. \n";
  $err = $err + 1;
 }

 $_ = $n1[12]; (s/^\s+//g); (s/\s+$//g); 
 if ((substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"')) {
  print "Line $ln: Hi-Res justification must be enclosed in double quotes. (Even if empty) \n";
  $err = $err + 1;
 }
 if (($n1[11] == 1) && ($_ eq '""')) {
  print "Line $ln: Hi-res justification must be set if binning of 1 is requested. \n";
  $err = $err + 1;
 }
 

 # 13: NAME = MAX_LSUBS
 # 14: NAME = MIN_LSUBS
 if (($n1[13] > 360) || ($n1[13] < 0)) {
  print "Line $ln: Maximum Ls must be in the range 0-360. \n";
  $err = $err + 1;
 }
 if (($n1[14] > 360) || ($n1[14] < 0)) {
  print "Line $ln: Minimum Ls must be in the range 0-360. \n";
  $err = $err + 1;
 }
 if ($n1[14] == $n1[13]) {
  print "Line $ln: Minimum and maximum Ls must not be equal. \n";
  $err = $err + 1;
 }


 # 15: NAME = NUMBER_OF_OBSERVATIONS
 if (($n1[15] > 20) || ($n1[15] < 1) || ($n1[15] != int($n1[15]))) {
  print "Line $ln: Number of observations must be an integer in the range 1-20. \n";
  $err = $err + 1;
 }

 # 15: NAME = NUMBER_OF_OBSERVATIONS
 # 6:  NAME = PRIORITY_STEREO
 if (($n1[15] > 1) && ($n1[6] > 0)) {
  print "Line $ln: You cannot have seasonally repeating stereo observations in this script. Separate into 2 records or use HiWEB! \n";
  $err = $err + 1;
 }

 # 16: NAME = MAX_PHASE_ANGLE
 # 17: NAME = MIN_PHASE_ANGLE
 if (($n1[16] > 180) || ($n1[16] < 0)) {
  print "Line $ln: Maximum phase angle must be in the range 0-180. \n";
  $err = $err + 1;
 }
 if (($n1[16] > 180) || ($n1[16] < 0)) {
  print "Line $ln: Minimum phase angle must be in the range 0-180. \n";
  $err = $err + 1;
 }
 if ($n1[17] > $n1[16]) {
  print "Line $ln: Minimum phase angle must less than the maximum phase angle. \n";
  $err = $err + 1;
 }


 # 18: NAME = MAX_INCIDENCE_ANGLE
 # 19: NAME = MIN_INCIDENCE_ANGLE
 if (($n1[18] > 100) || ($n1[18] < 0)) {
  print "Line $ln: Maximum incidence angle must be in the range 0-90. \n";
  $err = $err + 1;
 }
 if (($n1[19] > 100) || ($n1[19] < 0)) {
  print "Line $ln: Minimum incidence angle must be in the range 0-90. \n";
  $err = $err + 1;
 }
 if ($n1[19] > $n1[18]) {
  print "Line $ln: Minimum incidence angle must less than the maximum incidence angle. \n";
  $err = $err + 1;
 }


 # 20: NAME = MAX_EMISSION_ANGLE
 # 21: NAME = MIN_EMISSION_ANGLE
 if (($n1[20] > 90) || ($n1[20] < 0)) {
  print "Line $ln: Maximum emission angle must be in the range 0-90. \n";
  $err = $err + 1;
 }
 if (($n1[21] > 90) || ($n1[21] < 0)) {
  print "Line $ln: Minimum emission angle must be in the range 0-90. \n";
  $err = $err + 1;
 }
 if ($n1[21] > $n1[20]) {
  print "Line $ln: Minimum emission angle must less than the maximum emission angle. \n";
  $err = $err + 1;
 }

 # 22: NAME = SPECIAL_NOTES
 $_ = $n1[22]; (s/^\s+//g); (s/\s+$//g); 
 if ((substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"')) {
  print "Line $ln: Special Notes must be enclosed in double quotes. \n";
  $err = $err + 1;
 }

 
 # 23: NAME = USERNAME
 $_ = $n1[23]; (s/^\s+//g); (s/\s+$//g); 
 if ((substr($_,0,1) ne '"') || (substr($_,-1,1) ne '"') || ($_ eq '""')) {
  print "Line $ln: Username must be set and enclosed in double quotes. \n";
  $err = $err + 1;
 } else {
  $sqlcmd = "select ID from People where USERNAME = ".$n1[23];
  @usr = $dbh->selectrow_array($sqlcmd);
  if ($#usr == -1) {
   print "Line $ln: The username must exactly match one of those in HiWEB.\n";
   $err = $err + 1;
  }
}

 }
 $ln = $ln + 1;
}
select STDOUT;

# Only proceed if there were no errors in the table. If errors exist return the file
# and a dump of this program's output to the user.

if ($err != 0) {
 $dbh->disconnect;
 if ($fout) {
  close LOG;
  die("\n$err errors encountered... See $output for details.\n");
 }
 die("\n$err errors encountered... See previous output for details.\n");
}


 # If the validate flag is set then just stop here
 # REMEMBER TO ADD CODE TO THAT EFFECT...

if ($validate) {
 $dbh->disconnect;
 if ($fout) {close LOG};
 die("Reality checks OK... validation complete...\n");
}



 # If we get this far then we (hopefully) have no errors in the user input files.
 # Proceed to MySQL writing section....
 print "Reality checks OK... writing to HiCAT...\n\n";
 if ($fout) {select LOG};

$ln = 1;
$dberror = 0;

foreach $rec (@targets) 
{
 (@n1) = split(/,/,$rec);

 $scirat = $n1[1];
 
 $flds = 'PRIORITY_OVERALL';
 $vals = $n1[0];

 $flds = $flds.', MAX_BINNING';
 $vals = $vals.','.$n1[11];
 if ($n1[11] == 1) {
  $flds = $flds.', PRIORITY_HIGHRES';
  $vals = $vals.','.$n1[10];

  $_    = $scirat;
  (s/"//g);
  (s/^\s+//g);
  (s/\s+$//g);
  $scirat = $_;

  $_    = $n1[12];
  (s/"//g);
  (s/^\s+//g);
  (s/\s+$//g);
  $resrat = $_;

  $scirat = '"'.$scirat.' Resolution Justification: '.$resrat.'"';
 } else {
  $flds = $flds.', PRIORITY_HIGHRES';
  $vals = $vals.',1';
 }

 if ($n1[6] != 0) {
  $flds = $flds.', PRIORITY_STEREO, NEED_STEREO';
  $vals = $vals.','.$n1[6].',1';
 } else {
  $flds = $flds.', PRIORITY_STEREO, NEED_STEREO';
  $vals = $vals.',1,0';
 }


 if ($n1[7] != 0) {
  $flds = $flds.', PRIORITY_COLOR, NEED_COLOR';
  $vals = $vals.','.$n1[7].',1';
 } else {
  $flds = $flds.', PRIORITY_COLOR, NEED_COLOR';
  $vals = $vals.',1,0';
 }


  $flds = $flds.', CRISM_COORDINATION';
  $vals = $vals.','.$n1[8];
  $flds = $flds.', DESCRIPTION';
  $vals = $vals.','.$n1[2];


 if (($n1[6] != 0) || ($n1[7] != 0) || ($n1[8] eq '"YES"')) {
  $_    = $scirat;
  (s/"//g);
  (s/^\s+//g);
  (s/\s+$//g);
  $scirat = $_;

  $_    = $n1[9];
  (s/"//g);
  (s/^\s+//g);
  (s/\s+$//g);
  $colrat = $_;
 
  $scirat = '"'.$scirat.' Color/Stereo/CRISM Justification: '.$colrat.'"';
 }


 $flds = $flds.', SPECIAL_NOTES';
 $vals = $vals.','.$n1[22];

 $flds = $flds.', SCIENCE_RATIONALE, NUMBER_OF_OBSERVATIONS, MAX_PHASE_ANGLE, MIN_PHASE_ANGLE, MAX_INCIDENCE_ANGLE, MIN_INCIDENCE_ANGLE, MAX_EMISSION_ANGLE, MIN_EMISSION_ANGLE, SUGGESTION_DATE, TARGET_NAME, EXACT_AREA_REQUESTED';
 $vals = $vals.','.$scirat.','.'1'.','.$n1[16].','.$n1[17].','.$n1[18].','.$n1[19].','.$n1[20].','.$n1[21].', NOW(), "MARS", 0';

 $_    = $n1[5];
 (s/"//g); (s/^\s+//g); (s/\s+$//g);
 @nroi = split(/ /,$_);
 $ele  = ($#nroi + 1)/2.0 ;

 $alat = 0.0;
 $alon = 0.0;
 for ($i = 1; $i <= $ele; $i++) {
  $lat[$i-1] = $nroi[2.0*($i-1) + 1];
  $lon[$i-1] = $nroi[2.0*($i-1)    ];
 }
 $#lon = $ele-1;
 $#lat = $ele-1;
 $minlat = (sort {$a <=> $b} @lat)[0];
 $maxlat = (sort {$b <=> $a} @lat)[0];
 $minlon = (sort {$a <=> $b} @lon)[0];
 $maxlon = (sort {$b <=> $a} @lon)[0];

 if ($maxlon-$minlon > 180) {
  for ($i = 1; $i <= $ele; $i++) {
   if ($lon[$i-1] > 180) { $lon[$i-1] = $lon[$i-1] - 360 }
  }
  $minlon = (sort {$a <=> $b} @lon)[0];
  $maxlon = (sort {$b <=> $a} @lon)[0];
 }

 $roi = '"';
 for ($i = 1; $i <= $ele; $i++) { 
#  $roi = $roi.$lon[$i-1].','.$lat[$i-1].',';
  $roi = $roi.substr($lon[$i-1],0,9).','.substr($lat[$i-1],0,9).',';
  $alat = $alat + $lat[$i-1]/$ele;
  $alon = $alon + $lon[$i-1]/$ele;
 }
 chop $roi;
 $roi = $roi.'"';


 $flds = $flds.', ROI_VERTICES, CENTER_LATITUDE, CENTER_LONGITUDE, MAX_LATITUDE, MIN_LATITUDE, MAX_LONGITUDE, MIN_LONGITUDE';
 $vals = $vals.','.$roi.','.$alat.','.$alon.','.$maxlat.','.$minlat.','.$maxlon.','.$minlon;

 eval{

 # Query the People table for the user ID
 $sqlcmd = "select ID from People where USERNAME = ".$n1[23];
 @usr = $dbh->selectrow_array($sqlcmd);

 # Query the Science_Themes table for the theme ID of the primary science theme
 $sqlcmd  = "select ID from Science_Themes where THEME = ".$n1[3];
 @thm1 = $dbh->selectrow_array($sqlcmd);

 # Query the Science_Themes table for the theme ID of the secondary science theme
 $sqlcmd  = "select ID from Science_Themes where THEME = ".$n1[4];
 @thm2 = $dbh->selectrow_array($sqlcmd);

 #Three situations supported:
 #1. Regular non-stereo, non seasonally-repeating observation
 #2. Stereo but non seasonally-repeating observation
 #3. Non-stereo but seasonally-repeating observation


 #1. Regular non-stereo, non seasonally-repeating observation
 if (($n1[15] == 1) && ($n1[6] == 0)) {
  $numobs = 1;
  $flds   = $flds.', MAX_LSUBS, MIN_LSUBS';
  $vals   = $vals.','.$n1[13].','.$n1[14];
  $addobs = 'INSERT INTO Suggested_Observations ('.$flds.') VALUES ('.$vals.')';

  for ($i = 1; $i <= $numobs; $i++) { 
   # Construct the MySQL command string from the fields and values above
   # Insert the observation request in the Suggested_Observations table
   # Insert the observation id into the relationship map
   $query     = $dbh->do($addobs);
#   $obsid[$i] = $dbh->last_insert_id(undef,undef,"Suggested_Observations",undef);
   $obsid[$i]  = $dbh->{'mysql_insertid'};
  }
 }
 
 
 #2. Stereo but non seasonally-repeating observation
 if ($n1[6] > 0) {
  $numobs = 2;
  $flds   = $flds.', MAX_LSUBS, MIN_LSUBS';
  $vals   = $vals.','.$n1[13].','.$n1[14];
  $addobs = 'INSERT INTO Suggested_Observations ('.$flds.') VALUES ('.$vals.')';

  $query  = $dbh->do('INSERT INTO Suggestion_Relations (RELATION_TYPE) VALUES ("STEREO")');
#  $relid  = $dbh->last_insert_id(undef,undef,"Suggestion_Relations",undef);
  $relid  = $dbh->{'mysql_insertid'};

  for ($i = 1; $i <= $numobs; $i++) { 
   # Construct the MySQL command string from the fields and values above
   # Insert the observation request in the Suggested_Observations table
   # Insert the observation id into the relationship map
   $query      = $dbh->do($addobs);
#   $obsid[$i]  = $dbh->last_insert_id(undef,undef,"Suggested_Observations",undef);
   $obsid[$i]  = $dbh->{'mysql_insertid'};
   $query      = $dbh->do('INSERT INTO Related_Suggestions_map (RELATION_ID,SUGGESTED_OBSERVATIONS_ID) VALUES ('.$relid.','.$obsid[$i].')');
  }
 }


 #3. Non-stereo but seasonally-repeating observation
 if ($n1[15] > 1) {
  $numobs = $n1[15];
  $flds = $flds.', MAX_LSUBS, MIN_LSUBS';
# $vals = $vals.','.$n1[13].','.$n1[14]

  if ($n1[13] < $n1[14]) {$n1[13] = $n1[13] + 360}; # This line allows maxls to be < minls

  $dls = 0.1 * (($n1[13]-$n1[14]) / ($n1[15] - 1));
  if ($dls <  1) {$dls =  1};
  if ($dls > 45) {$dls = 45};
  $query  = $dbh->do('INSERT INTO Suggestion_Relations (RELATION_TYPE) VALUES ("SEASONAL")');
# $relid  = $dbh->last_insert_id(undef,undef,"Suggestion_Relations",undef);
  $relid  = $dbh->{'mysql_insertid'};

  for ($i = 1; $i <= $numobs; $i++) { 
   $cls   = ($i-1) * (($n1[13]-$n1[14]) / ($n1[15] - 1)) + $n1[14];
   $maxls = ($cls+$dls +720) % 360;
   $minls = ($cls-$dls +720) % 360;
#   if ($maxls > 360) {$maxls = $maxls - 360};
#   if ($minls < 0) {$minls = $minls + 360};
   $addobs = 'INSERT INTO Suggested_Observations ('.$flds.') VALUES ('.$vals.','.$maxls.','.$minls.')';

   # Construct the MySQL command string from the fields and values above
   # Insert the observation request in the Suggested_Observations table
   # Insert the observation id into the relationship map
   $query      = $dbh->do($addobs);
#   $obsid[$i]  = $dbh->last_insert_id(undef,undef,"Suggested_Observations",undef);
   $obsid[$i]  = $dbh->{'mysql_insertid'};
   $query      = $dbh->do('INSERT INTO Related_Suggestions_map (RELATION_ID,SUGGESTED_OBSERVATIONS_ID) VALUES ('.$relid.','.$obsid[$i].')');
  }
   
 }


 # Insert the observation id into the people map
 # Insert the observation id into the science_theme map

 for ($i = 1; $i <= $numobs; $i++) { 
  # Update the Suggested_Observations_People_map table with the relevant info
  $addusr = 'INSERT INTO Suggested_Observations_People_map (SUGGESTED_OBSERVATIONS_ID, PEOPLE_ID) VALUES ('.$obsid[$i].','.$usr[0].')';
  $query  = $dbh->do($addusr);
  
  # Update the Suggested_Observations_Science_Themes_map table with the relevant info
  $addthm2 = 'INSERT INTO Suggested_Observations_Science_Themes_map (SUGGESTED_OBSERVATIONS_ID, SCIENCE_THEMES_ID, PRECEDENCE) VALUES ('.$obsid[$i].','.$thm2[0].',2)';
  $query   = $dbh->do($addthm2);

  # Update the Suggested_Observations_Science_Themes_map table with the relevant info
  $addthm1 = 'INSERT INTO Suggested_Observations_Science_Themes_map (SUGGESTED_OBSERVATIONS_ID, SCIENCE_THEMES_ID, PRECEDENCE) VALUES ('.$obsid[$i].','.$thm1[0].',1)';
  $query   = $dbh->do($addthm1);
 }

 }; # End of evaluation section

 if ($@) {
  print STDOUT "Error interfacing with database for traget $ln\n";
  $dberror = 1;
 } else {
  $_  = $n1[2]; (s/"//g); (s/^\s+//g); (s/\s+$//g); $desc = $_;
  for ($i = 1; $i <= $numobs; $i++) {print "Successfully inserted observation $obsid[$i], $desc\n"};
 }

$ln = $ln +1;
}

 select STDOUT;
 if ($fout) {close LOG};
 if ($dberror) {$dbh->rollback} else {$dbh->commit};
 $dbh->disconnect;
 if ($dberror) {print "\nMission failed!\n"} else {print "\nMission accomplished!\n"};