#!/usr/bin/perl -w

use DBI;
use Getopt::Long;
use Term::ReadKey;
use Math::Trig;
use POSIX qw(floor);

$dbase    = "HiRISE";
$user     = "";
$pass     = "";
$host     = "127.0.0.1";
$theme    = "Polar Geology";
$pole     = "";
$outrt    = "";
$help     = 0;

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

if (!$opt || $help) {
 print "\n";
 print "stereo_summary.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 "  pole|p=s   Use n or s to limit table to the northern or southern hemispheres, omit for a global table.\n";
 print "  theme|t    Science theme to generate the repost for\n";
 print "  output|o   Output name for the HTML file, default is stereo_summary.html\n";
 print "  help|h     print this help message\n";
 print "\n";
 print "This program produces ...\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');
}


$poleselect = '';
if (($pole eq 'n') || ($pole eq 'north')) {$poleselect = 'Suggested_Observations.center_latitude > 0 AND'};
if (($pole eq 's') || ($pole eq 'south')) {$poleselect = 'Suggested_Observations.center_latitude < 0 AND'};


if ($outrt eq '') {$outrt = "stereo_summary.html"};
$fout = open PRJ, "> ".$outrt;

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

$sqlcmd1 = "
 SELECT Related_Suggestions_map.RELATION_ID
  FROM Suggested_Observations
  LEFT JOIN
  Related_Suggestions_map                         ON Suggested_Observations.ID = Related_Suggestions_map.SUGGESTED_OBSERVATIONS_ID
  LEFT JOIN
  Suggested_Observations_Planned_Observations_map ON Suggested_Observations.ID = Suggested_Observations_Planned_Observations_map.SUGGESTED_OBSERVATIONS_ID
  LEFT JOIN
  Suggestion_Relations                            ON  Related_Suggestions_map.RELATION_ID = Suggestion_Relations.ID
  LEFT JOIN
  Planned_Observations                            ON  Suggested_Observations_Planned_Observations_map.PLANNED_OBSERVATIONS_ID = Planned_Observations.ID
  LEFT JOIN
  Suggested_Observations_Science_Themes_map       ON Suggested_Observations.ID = Suggested_Observations_Science_Themes_map.SUGGESTED_OBSERVATIONS_ID
  LEFT JOIN
  Science_Themes                                  ON  Suggested_Observations_Science_Themes_map.SCIENCE_THEMES_ID = Science_Themes.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
  WHERE
  ".$poleselect."
  Suggested_Observations.STL_STEREO = 'YES' AND
  Suggested_Observations.TARGET_NAME = 'MARS' AND
  Suggestion_Relations.RELATION_TYPE = 'STEREO' AND
  ((Suggested_Observations_Science_Themes_map.PRECEDENCE = 1 AND Science_Themes.THEME = '".$theme."') OR
   (Suggested_Observations_Science_Themes_map.PRECEDENCE = 2 AND Science_Themes.THEME = '".$theme."'))
  ORDER BY
  Suggested_Observations.center_latitude;
";

$sqlcmd2 = "
 SELECT Related_Suggestions_map.RELATION_ID,Suggested_Observations.ID,Planned_Observations.Observation_ID,Planned_Observations.status,Suggested_Observations.center_latitude,Suggested_Observations.center_longitude,People.Username,Suggested_Observations.STL_STEREO,Suggested_Observations.Description,Observation_Geometry.IMAGE_CENTER_LATITUDE,Observation_Geometry.IMAGE_CENTER_LONGITUDE,Observation_Geometry.TARGET_CENTER_DISTANCE, Observation_Geometry.SUB_SPACECRAFT_LATITUDE, Observation_Geometry.SUB_SPACECRAFT_LONGITUDE
  FROM Suggested_Observations
  LEFT JOIN
  Related_Suggestions_map                         ON Suggested_Observations.ID = Related_Suggestions_map.SUGGESTED_OBSERVATIONS_ID
  LEFT JOIN
  Suggested_Observations_Planned_Observations_map ON Suggested_Observations.ID = Suggested_Observations_Planned_Observations_map.SUGGESTED_OBSERVATIONS_ID
  LEFT JOIN
  Suggestion_Relations                            ON  Related_Suggestions_map.RELATION_ID = Suggestion_Relations.ID
  LEFT JOIN
  Planned_Observations                            ON  Suggested_Observations_Planned_Observations_map.PLANNED_OBSERVATIONS_ID = Planned_Observations.ID
  LEFT JOIN
  Observation_Geometry                            ON  Planned_Observations.OBSERVATION_ID = Observation_Geometry.OBSERVATION_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
  WHERE
  Suggestion_Relations.RELATION_TYPE = 'STEREO' 
  ORDER BY
  Related_Suggestions_map.RELATION_ID,Suggested_Observations.ID;
";

print "Performing MySQL queries...\n";
@rel = @{  $dbh->selectall_arrayref($sqlcmd1) };
@res = @{  $dbh->selectall_arrayref($sqlcmd2) };

print "Arranging Data...\n";

# Get a list of unique relationship ID's in @uniq_rel_ids
%seen = ();
foreach $j (@rel) { push @rel_ids,@{$j} };
foreach $j (@rel_ids) { push(@uniq_rel_ids, $j) unless $seen{$j}++ };

$c0 = 0;
$c1 = 0;
$c2 = 0;

$dpi  = 3.141592653589793116; # Double prec. PI
$d2r  = $dpi/180.0;           # Degrees to radians
$r2d  = 180.0/$dpi;           # Radians to degrees
$ra   = 3396.1900;            # Mars equatorial radius
$rc   = 3376.2000;            # Mars polar radius

@bigtab = ();
# Table of column titles (seperate from results table)
push @bigtab, "<table border=1> \n";
push @bigtab, "<tr align='center' bgcolor='#808080'><td width=100>Relation ID</td>\n";
push @bigtab, "<td><table border=1>\n";
push @bigtab, "<tr align='center'> \n";
push @bigtab, "<td width=80>HiCAT ID</td><td width=150>Planned ID</td><td width=150>Status</td>\n";
push @bigtab, "</tr> \n";
push @bigtab, "</table></td>\n";
push @bigtab, "<td width=80>Stereo Angle</td><td width=80>Latitude</td><td width=80>Longitude</td><td width=80>User</td><td width=200>Description</td>\n";
push @bigtab, "</tr> \n";
push @bigtab, "</table> \n";
push @bigtab, "<br> \n";

push @bigtab, "<table border=1 rowspacing=15> \n";
foreach $k (@uniq_rel_ids) {
 @cells = ();
 push @cells,"<td><table border=1> \n";
 $cnt = 0;
 $gcnt= 0;

 $stereo_sep = '--';
 foreach $j (@res) { 
  @res2 = @{$j}; 
  if ($res2[0] eq $k) {
	  
   foreach $i (@res2) { 
    if ((defined $i) eq "") { $i  = '--' }; 
    if ($i eq "") { $i  = '--' }; 
   }
   
   push @cells, "<tr align='center'> \n";
   push @cells, "<td width=80>$res2[1]</td><td width=150>$res2[2]</td><td width=150>$res2[3]</td>\n";
   push @cells, "</tr> \n";
   if ($res2[2] ne '--') {$cnt = $cnt+1};
   
   if (($res2[9] ne '--') && ($gcnt == 2)) {
    $stereo_sep = "Many combinations";
    $gcnt = 3;
   }

   if (($res2[9] ne '--') && ($gcnt == 1)) {
    @geo2 = @res2[9..13];
 
    $clat   = 0.5*($geo1[0]+$geo2[0])*$d2r; #center latitude  of target in radians
    $clon   = 0.5*($geo1[1]+$geo2[1])*$d2r; #center longitude of target in radians
    $rmars  = ($ra*$rc) / sqrt( $rc**2*cos($clat)**2 + $ra**2*sin($clat)**2 ); # Mars radius at center latitude

    $trg_x = $rmars*cos($clat)*sin($clon); # Cartesian coordinates of the target
    $trg_y = $rmars*cos($clat)*cos($clon);
    $trg_z = $rmars*sin($clat);

    $sp1_x = $geo1[2]*cos($geo1[3]*$d2r)*sin($geo1[4]*$d2r); # Cartesian coordinates of the spacecraft at 1st image
    $sp1_y = $geo1[2]*cos($geo1[3]*$d2r)*cos($geo1[4]*$d2r);
    $sp1_z = $geo1[2]*sin($geo1[3]*$d2r);

    $sp2_x = $geo2[2]*cos($geo2[3]*$d2r)*sin($geo2[4]*$d2r); # Cartesian coordinates of the spacecraft at 2nd image
    $sp2_y = $geo2[2]*cos($geo2[3]*$d2r)*cos($geo2[4]*$d2r);
    $sp2_z = $geo2[2]*sin($geo2[3]*$d2r);

    $t2sp1_x = $sp1_x - $trg_x;
    $t2sp1_y = $sp1_y - $trg_y;
    $t2sp1_z = $sp1_z - $trg_z;
    $t2sp1_mag = sqrt($t2sp1_x**2 + $t2sp1_y**2 + $t2sp1_z**2);
    $t2sp1_x = $t2sp1_x / $t2sp1_mag;
    $t2sp1_y = $t2sp1_y / $t2sp1_mag;
    $t2sp1_z = $t2sp1_z / $t2sp1_mag;

    $t2sp2_x = $sp2_x - $trg_x;
    $t2sp2_y = $sp2_y - $trg_y;
    $t2sp2_z = $sp2_z - $trg_z;
    $t2sp2_mag = sqrt($t2sp2_x**2 + $t2sp2_y**2 + $t2sp2_z**2);
    $t2sp2_x = $t2sp2_x / $t2sp2_mag;
    $t2sp2_y = $t2sp2_y / $t2sp2_mag;
    $t2sp2_z = $t2sp2_z / $t2sp2_mag;

    $stereo_sep = acos($t2sp1_x*$t2sp2_x + $t2sp1_y*$t2sp2_y + $t2sp1_z*$t2sp2_z)*$r2d;
    $stereo_sep = floor($stereo_sep*1000.0) / 1000.0;
    $gcnt = 2;
   }
   
   if (($res2[9] ne '--') && ($gcnt == 0)) {
    @geo1 = @res2[9..13];
    $gcnt = 1;
   }

  }
 }
 
 push @cells, "</table></td> \n";
 push @cells, "<td width=80>$stereo_sep</td>";
 
 if ($cnt == 0) { unshift @cells,"<tr align='center' bgcolor='#8080ff'><b><td width=100>$k</td> \n"; }
 if ($cnt == 1) { unshift @cells,"<tr align='center' bgcolor='#ff8080'><b><td width=100>$k</td> \n"; }
 if ($cnt >= 2) { unshift @cells,"<tr align='center' bgcolor='#80ff80'><b><td width=100>$k</td> \n"; }
 if ($cnt == 0) { $c0 = $c0+1; }
 if ($cnt == 1) { $c1 = $c1+1; }
 if ($cnt >= 2) { $c2 = $c2+1; }

 LINE: foreach $j (@res) { 
  @res2 = @{$j}; 
  if ($res2[0] eq $k) {
  push @cells,"<td width=80>$res2[4]</td><td width=80>$res2[5]</td><td width=80>$res2[6]</td><td width=200>$res2[8]</td>\n";
   last LINE;
  }
 }
  
 push @cells,"</tr></b> \n\n";
 push @bigtab,@cells
}
push @bigtab, "</table> \n";


print "Outputing html code... \n";
# HTML header stuff
print PRJ "<html> \n";
print PRJ "<head><title>".$theme." - HiRISE STEREO ACQUISITION REPORT (HiSTARE) - ".(localtime)."</title>\n";
print PRJ "<style type='text/css'>\n";
print PRJ "<!--\n";
print PRJ "a	{ text-decoration: none }\n";
print PRJ "a:hover	{ font-size: 115%; font-weight: bolder }\n";
print PRJ "body	{ background-attachment: fixed}\n";
print PRJ "-->\n";
print PRJ "</style>\n";
print PRJ "</head>\n";
print PRJ "<body background='/~shane/images/pheobe.jpg' bgcolor='#ffffff' text='#000000' link='#0000ff' vlink='#0000ff' alink='#0000ff' bgproperties='fixed'> \n";
print PRJ "<b><center> \n";
print PRJ "<h1>".$theme."</h1> \n";
print PRJ "<hr width=75%> \n";
print PRJ "<h2> <u>Hi</u>RISE <u>ST</u>EREO <u>A</u>CQUISITION <u>RE</u>PORT (HiSTARE) <br> Generated on ".(localtime)."</h2> \n";
print PRJ "<hr width=75%> \n";

if (($pole eq 'n') || ($pole eq 'north')) {print PRJ "<h2>Northern Targets Only</h2> \n" };
if (($pole eq 's') || ($pole eq 'south')) {print PRJ "<h2>Southern Targets Only</h2> \n" };

print PRJ "<table border=1> \n";
print PRJ "<tr bgcolor='#8080ff' align='center'><td width=300>Unstarted Pairs</td><td width=50>".$c0."</td></tr> \n";
print PRJ "<tr bgcolor='#ff8080' align='center'><td width=300>Incomplete Pairs</td><td width=50>".$c1."</td></tr> \n";
print PRJ "<tr bgcolor='#80ff80' align='center'><td width=300>Complete Pairs</td><td width=50>".$c2."</td></tr> \n";
print PRJ "</table> \n";
print PRJ "<hr width=75%><br> \n";

foreach $i (@bigtab) { print PRJ $i };

print PRJ "<br><hr width=75%> \n";
print PRJ "This is an autogenerated list from the HiCAT database.<br><a href='mailto:shane\@lpl.arizona.edu'>shane\@lpl.arizona.edu</a><br>\n";
print PRJ "</center></b> \n";
print PRJ "</body></html> \n";

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