#!/usr/bin/perl -w

use DBI;
use Getopt::Long;
use Term::ReadKey;
use Geo::Shapelib qw/:all/;
use Math::Trig;

$dbase    = "HiRISE";
$user     = "";
$pass     = "";
$host     = "pirldb.lpl.arizona.edu";
$ogrph    = 0;
$outrt    = "";
$help     = 0;

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

if (!$opt || $help) {
 print "\n";
 print "dump_hicat.pl -dbase -host -user -pass -help -output \n";
 print "\n";
 print "  dbase|d    database to connect to (default is HiRISE)\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 "  graphic|g  Export shapefile in 'ographic latitudes\n";
 print "  output|o   Root name for output e.g. 'xx' will produce files xx_yyyymmdd.shp etc...\n";
 print "  help|h     print this help message\n";
 print "\n";
 print "This program produces an ESRI shapefile containing HiRISE target suggestions.\n";
 print "It does this by reading the HiCAT database.";
 print "Questions? Contact: Shane Byrne - shane\@lpl.arizona.edu\n";
 die("\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');
}

if ($outrt eq '') {$outrt = "hicat_dump"};

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


$selection = "WHERE Suggested_Observations.target_name = 'MARS' ";#AND Suggested_Observations.PRIORITY_OVERALL > 0  ";#AND Suggested_Observations.ID > 10 ";#AND Suggested_Observations.ROI_VERTICES != '-1000.0,-1000.0' "; 

$sqlcmd1 = " select  
 Suggested_Observations.PRIORITY_OVERALL
,Suggested_Observations.SCIENCE_RATIONALE
,Suggested_Observations.DESCRIPTION
,Science_Themes.THEME 
,'UNKNOWN'
,Suggested_Observations.ROI_VERTICES
,Suggested_Observations.PRIORITY_STEREO
,Suggested_Observations.PRIORITY_COLOR
,Suggested_Observations.CRISM_COORDINATION
,''
,Suggested_Observations.PRIORITY_HIGHRES
,Suggested_Observations.MAX_BINNING
,''
,Suggested_Observations.MAX_LSUBS
,Suggested_Observations.MIN_LSUBS
,Suggested_Observations.NUMBER_OF_OBSERVATIONS
,Suggested_Observations.MAX_PHASE_ANGLE
,Suggested_Observations.MIN_PHASE_ANGLE
,Suggested_Observations.MAX_INCIDENCE_ANGLE
,Suggested_Observations.MIN_INCIDENCE_ANGLE
,Suggested_Observations.MAX_EMISSION_ANGLE
,Suggested_Observations.MIN_EMISSION_ANGLE
,Suggested_Observations.SPECIAL_NOTES
,People.Username
,Suggested_Observations.ID
  FROM Suggested_Observations 
  LEFT JOIN 
  Suggested_Observations_Science_Themes_map ON Suggested_Observations.ID = Suggested_Observations_Science_Themes_map.SUGGESTED_OBSERVATIONS_ID 
  LEFT JOIN 
  Suggested_Observations_People_map         ON Suggested_Observations.ID = Suggested_Observations_People_map.SUGGESTED_OBSERVATIONS_ID
  LEFT JOIN 
  People                                    ON  Suggested_Observations_People_map.PEOPLE_ID = People.ID
  LEFT JOIN 
  Science_Themes                            ON  Suggested_Observations_Science_Themes_map.SCIENCE_THEMES_ID = Science_Themes.ID ";

$sqlcmd2 = " select  
Science_Themes.THEME,Suggested_Observations.ID
  FROM Suggested_Observations 
  LEFT JOIN 
  Suggested_Observations_Science_Themes_map ON Suggested_Observations.ID = Suggested_Observations_Science_Themes_map.SUGGESTED_OBSERVATIONS_ID 
  LEFT JOIN 
  Suggested_Observations_People_map         ON Suggested_Observations.ID = Suggested_Observations_People_map.SUGGESTED_OBSERVATIONS_ID
  LEFT JOIN 
  People                                    ON  Suggested_Observations_People_map.PEOPLE_ID = People.ID
  LEFT JOIN 
  Science_Themes                            ON  Suggested_Observations_Science_Themes_map.SCIENCE_THEMES_ID = Science_Themes.ID ";

@fldnames = qw/PRIORITY RATIONALE DESCRIBE THEME_1 THEME_2 VERTICES STEREO_P COLOR_P CRISM SCC_JUS BIN1_P BINNING BIN1_JUS MAX_LS MIN_LS NUM_OBS MAX_PHA MIN_PHA MAX_INC MIN_INC MAX_EMI MIN_EMI NOTES USER HICAT_ID/;
@fldtypes = qw/Integer  String    String   String  String  String   Integer  Integer String String Integer Integer String  Double Double Integer Double  Double  Double  Double  Double  Double  String String Integer/;

$ff  = ((3396190.0/3376200.0)*(3396190.0/3376200.0));
$r2d = 57.295779513082323;


($tm1, $tm2, $tm3) = (localtime)[3,4,5];
$tm2 = $tm2 + 1;
if (length($tm1) == 1) { $tm1 = "0".$tm1 };
if (length($tm2) == 1) { $tm2 = "0".$tm2 };
$nm = $outrt."_".($tm3+1900).$tm2.$tm1;

$shpfile = new Geo::Shapelib {
   Name => $nm,
   Shapetype => POLYGON,
   FieldNames => [@fldnames],
   FieldTypes => [@fldtypes]
};


print "Performing MySQL queries...\n";

@res = @{  $dbh->selectall_arrayref($sqlcmd1.$selection."AND  Suggested_Observations_Science_Themes_map.PRECEDENCE = 1;") };
@sci = @{  $dbh->selectall_arrayref($sqlcmd2.$selection."AND  Suggested_Observations_Science_Themes_map.PRECEDENCE = 2;") };


print "Processing records...\n";
if ($ogrph) {print "Converting latitudes to planetographic\n"};

foreach $j (@res) { 
 @res2 = @{$j};

 foreach $k (@sci) { 
  @sci2 = @{$k};
  if ($res2[24] eq $sci2[1]) { 
   $res2[4] = $sci2[0];
  }
 }

 if (index($res2[1],'Color/Stereo/CRISM Justification:') != -1) {
  $res2[9] = substr($res2[1],index($res2[1],'Color/Stereo/CRISM Justification:')+33,length($res2[1])-index($res2[1],'Color/Stereo/CRISM Justification:')-33);
  $res2[1] = substr($res2[1],0,index($res2[1],'Color/Stereo/CRISM Justification:')-1);
 }

 if (index($res2[1],'Color/Stereo Justification:') != -1) {
  $res2[9] = substr($res2[1],index($res2[1],'Color/Stereo Justification:')+27,length($res2[1])-index($res2[1],'Color/Stereo Justification:')-27);
  $res2[1] = substr($res2[1],0,index($res2[1],'Color/Stereo Justification:')-1);
 }
 
 if (index($res2[1],'Resolution Justification:') != -1) {
  $res2[12] = substr($res2[1],index($res2[1],'Resolution Justification:')+25,length($res2[1])-index($res2[1],'Resolution Justification:')-25);
  $res2[1] = substr($res2[1],0,index($res2[1],'Resolution Justification:')-1);
 }


 @nroi = split(/,/,$res2[5]);
 $ele  = ($#nroi + 1)/2.0;

 if ($ele >= 3) { 
  if ($ogrph) {
   for ($i = 1; $i <= $ele; $i++) { $nroi[2.0*($i-1)+1] = atan2(($ff*tan($nroi[2.0*($i-1)+1]/$r2d)),1.0)*$r2d };
  }
  
  for ($i = 1; $i <= $ele; $i++) { 
   if (($nroi[2.0*($i-1)] - 180.0) > 0) { 
    $nroi[2.0*($i-1)] = $nroi[2.0*($i-1)]-360.0;
   }
  }

  @vert=[( $nroi[0], $nroi[1], 0.0, 0.0)];
  for ($i = 2; $i <= $ele; $i++) { push @vert,[( $nroi[2.0*($i-1)], $nroi[2.0*($i-1)+1], 0.0, 0.0)] };
  push @vert,[( $nroi[0], $nroi[1], 0.0, 0.0)];
 
  foreach $i (@res2) { 
   if ((defined $i) eq "") { $i  = 0 };
   $i =~ s/^\s+//g; 
   $i =~ s/\s+$//g;
   $i =~ s/\n//g;
   if (length($i) > 254) { $i  = substr($i,0,254) };
  }  

  push @{$shpfile->{ShapeRecords}}, [@res2];
  push @{$shpfile->{Shapes}}, {Vertices=>[@vert]};
 }
 
}

 $fout = open PRJ, "> $nm.prj";
 print PRJ 'GEOGCS["GCS_Mars_2000",DATUM["D_Mars_2000",SPHEROID["Mars_2000_IAU_IAG",3396190.0,169.8944472236118]],PRIMEM["Reference_Meridian",0.0],UNIT["Degree",0.0174532925199433]]\n';

 print "Closing files and database connections...\n";
 if ($fout) {close PRJ};
 $dbh->disconnect;
 $shpfile->save();