#! /usr/bin/perl

use strict;
use warnings;
use PROC::GeneralDB;
use PROC::Proc qw ($GEODDATUM);
use SOPAC::DB;
use SOPAC::DB::GeodeticCampaign::Monument;
use SOPAC::DB::GeodeticMonument;
use SOPAC::DB::GeodeticMonument::GeodeticPosition;

my $FAILURE = -1;
my $SUCCESS = 0;

my $USAGE;
$USAGE = "Usage:  $0 -f <input file> -campaign <campaign code> -db <database:schema>\n";
$USAGE .= "ABOUT:  Take spreadsheet and load monuments into database for a\n";
$USAGE .= "        given campaign.\n";
$USAGE .= "ABOUT:  information in database for that monument.\n";
$USAGE .= "OPTIONS: -v {invoke verbose mode}\n";
$USAGE .= "         -radius <meters> {search radius for monument conflicts}\n\n";

my ($verbose,$campaignID,$databaseSchema,$campaignCode,$inputFile);
my $searchRadiusMeters = 1000;
while(@ARGV) {
  my $option = shift @ARGV;
  if ($option eq "-f") {
    $inputFile = shift @ARGV;
  }
  elsif ($option eq "-db") {
    $databaseSchema = shift @ARGV;
  }
  elsif ($option eq "-radius") {
    $searchRadiusMeters = shift @ARGV;
  }
  elsif ($option eq "-v") {
    $verbose = 1;
  }
  elsif ($option eq "-campaign") {
    $campaignCode = shift @ARGV;
  }
  else {
    print STDERR "incorrect usage -> $option invalid\n\n$USAGE";
    quit($FAILURE);
  }
}

unless($inputFile) {
  print STDERR "incorrect usage -> missing a parameter\n\n$USAGE";
  quit($FAILURE);
}
unless($databaseSchema) {
  print STDERR "incorrect usage -> missing -db\n\n$USAGE";
  quit($FAILURE);
}
unless($campaignCode) {
  print STDERR "incorrect usage -> missing -code\n\n$USAGE";
  quit($FAILURE);
}



my $dbh = &SOPAC::DB::connect($databaseSchema);


my %DB_GeodeticDatums = ();
my $rar = &SOPAC::DB::query($dbh,"geodetic_datum_id,geodetic_datum_code","geodetic_datum","");
while (@$rar) {
  my $ar = shift @$rar;
  $DB_GeodeticDatums{$$ar[1]} = $$ar[0];
}

my $COORD_SOURCE_ID = &PROC::GeneralDB::getCoordinateSourceID('PGM SUBMISSION',$dbh);




# 1. Validate/verify campaign via campaign code (provided)
#    and acquire unique campaign identifier.
#
print STDERR "Verifying acknowledgement of campaign ($campaignCode) in database ($databaseSchema)...";
my $geodeticCampaigns;
$geodeticCampaigns = &SOPAC::DB::GeodeticCampaign::GetFromDB($dbh,{code => $campaignCode});
unless(defined $geodeticCampaigns) {
  print STDERR "failed!\n";
  quit(-1);
}
if (scalar @$geodeticCampaigns > 1) {
  print STDERR "multiple....ARGGGGGGGGH!\n";
  quit(-1);
} elsif (scalar @$geodeticCampaigns < 1) {
  print STDERR "not registered....sorry\n";
  quit(-1);
}
my $geodeticCampaign = shift @$geodeticCampaigns;
print STDERR $geodeticCampaign->id . " identified...ok\n";
$campaignID = $geodeticCampaign->id;











unless(open(INPUT,"<$inputFile")) {
  print STDERR "Could not open $inputFile!\n";
  quit($FAILURE);
}


while(<INPUT>) {
  my $line = $_;
  chomp($line);
  my (@pars) = split(/\t/,$line);

  my $geodeticMonument = SOPAC::DB::GeodeticMonument->new();

  unless($pars[0] =~ /\d\d\d/) {
    print STDERR "bad format : SSN...skipping\n";
    next;
  }
  my $File_SSN = $pars[0];


  unless($pars[1] =~ /\w{4}/) {
    print STDERR "bad format : 4CID...skipping\n";
    next;
  }
  my $File_SHORT_ID = lc($pars[1]);


  unless($pars[2] =~ /.*/) {
    print STDERR "bad format : DISTRICT...skipping\n";
    next;
  }
  my $File_DISTRICT = $pars[2];


  unless($pars[3] =~ /\w+/) {
    print STDERR "bad format : NETWORK...skipping\n";
    next;
  }
  my $File_NETWORK = $pars[3];


  unless($pars[4] =~ /\w+/) {
    print STDERR "bad format : DESIGNATION...skipping\n";
    next;
  }
  my $File_SITE_NAME = $pars[4];


  unless($pars[5] =~ /\d+\.\d+/) {
    print STDERR "bad format : LATITUDE...skipping\n";
    next;
  }
  my $File_LATITUDE = $pars[5];


  unless($pars[6] =~ /\-?\d+\.\d+/) {
    print STDERR "bad format : LONGITUDE...skipping\n";
    next;
  }
  my $File_LONGITUDE = $pars[6];


  unless((length($pars[7]) == 0) || ($pars[7] =~ /^\w{6}$/)) {
    print STDERR "bad format : NGSPID...skipping\n";
    next;
  }
  my $File_NGS_PID = undef;
  $File_NGS_PID = uc($pars[7]) if ($pars[7] =~ /^\w{6}$/);


  unless($pars[8] =~ /.*/) {
    print STDERR "bad format : COUNTY...skipping\n";
    next;
  }
  my $File_COUNTY = $pars[8];


  
  # a. Search for match in provided database:
  #     1. by NGS PID in database
  #     2. by spatial proximity query in database
  #
  print STDERR "Looking up ($File_SHORT_ID)..." if ($verbose);

  #
  # a1. Search by NGS PID
  #
  if ($File_NGS_PID) {
    my $dbMonuments = &SOPAC::DB::GeodeticMonument::GetFromDB($dbh,{ngs_pid => $File_NGS_PID});
    unless(defined $dbMonuments) {
      print STDERR "failed!...bailing\n" if ($verbose);
      next;
    }
    if (scalar @$dbMonuments > 1) {
      print STDERR" multiple matches located by NGS PID!!!!!...bailing\n" if ($verbose);
      next;
    }
    if (scalar @$dbMonuments == 1) {
      $geodeticMonument = shift @$dbMonuments;
      print STDERR "1 located by NGS_PID (" . $geodeticMonument->ngs_pid . ")...";
    } else {
      print STDERR "0 located by NGS_PID...";
    }
  } else {
    print STDERR "0 located by NGS_PID...";
  }



  #
  # a2. Spatial proximity query in database.
  #
  unless ($geodeticMonument->site_id()) {
    my $_m = $searchRadiusMeters;
    my $_x = $File_LONGITUDE;
    my $_y = $File_LATITUDE;

    my $r = "sdo_within_distance(s.shape,MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE($_x,$_y,0),null,null)";
    $r .= ",'distance=$_m,unit=METER') = 'TRUE'";


    my $rar = &SOPAC::DB::query($dbh,"s.site_id","site s",$r);
    unless(defined $rar) {
      print "failed!...bailing\n" if ($verbose);
      next;
    }
    if (scalar @$rar == 0) {
      print STDERR "0 found within $_m M...search exhausted\n" if ($verbose);
    } elsif (scalar @$rar > 1) {
      print STDERR (scalar @$rar) . " located within $_m M\n\tskipping, update your database first please!\n" if ($verbose);
      next;
    } else {
      my $ar = shift @$rar;
      print STDERR "1 found within $_m M...($$ar[0])..." if ($verbose);
      my $dbMonuments = &SOPAC::DB::GeodeticMonument::GetFromDB($dbh,{site_id => $$ar[0]});
      unless(defined $dbMonuments) {
	print STDERR "failed!...bailing\n" if ($verbose);
	next;
      }
      if (scalar @$dbMonuments > 1) {
	print STDERR" multiple matches located by SITE_ID!!!!!...bailing\n" if ($verbose);
	next;
      }
      $geodeticMonument = shift @$dbMonuments;
    }
  }



  # b. If a single match was found, transfer
  #    monument information, if necessary.
  #
  if ($geodeticMonument->site_id()) {

    # b-a.0 Why not?  Let's see, first, what 4-char id was used for this site,
    #       originally (when it first entered the database), and compare that to
    #       the one being used for this monument in this campaign.
    if ($geodeticMonument->site_code ne $File_SHORT_ID) {
      print STDERR "original site_code (" . $geodeticMonument->site_code . ") differs..." if ($verbose);
    }


    # b-a.1 Find out if this monument is already associated with the
    #       mentioned campaign or not.
    #
    my $monumentAssociations = &SOPAC::DB::GeodeticCampaign::Monument::GetFromDB($dbh,
                                                                                 {geodetic_campaign_id => $campaignID,
                                                                                  monument_id => $geodeticMonument->site_id});
    if (scalar @$monumentAssociations) {
      unless(scalar @$monumentAssociations == 1) {
        print STDERR "but has multiple roles in campaign ($campaignID)....skipping\n";
        next;
      }
      # Ok.  Monument already known in database AND associated
      # with this campaign.
      #
      # That's ok.  Just check to be certain the same 4-char-id
      # matches as well......otherwise bail out because someone
      # probably screwed up in associating monuments with 4-char
      # identifiers.
      #
      #
      my $monumentAssociation = shift @$monumentAssociations;
      unless($monumentAssociation->{short_char_id} eq $File_SHORT_ID) {
        print STDERR "but referenced by ($File_SHORT_ID) in ($inputFile) is already\n";
        print STDERR "   referenced in campaign ($campaignCode) as (" . $monumentAssociation->{short_char_id} . ").\n";
        print STDERR "   Please investigate this problem and deal with separately.\n";
        next;
      }
    }

    #
    # b-a.2 Set "working" monument to be one we just
    #       located.
    #
    print STDERR "OK\n" if ($verbose);

    # b-a.3 Transfer information (where applicable) to working monument object.
    #
    $geodeticMonument->state("California") unless(defined $geodeticMonument->state);
    $geodeticMonument->country("USA") unless(defined $geodeticMonument->country);
    $geodeticMonument->county($File_COUNTY) unless(defined $geodeticMonument->county);
    $geodeticMonument->site_name($File_SITE_NAME) unless(defined $geodeticMonument->site_name);

    if (scalar @{$geodeticMonument->GeodeticPositions} == 0) {
      push(@{$geodeticMonument->GeodeticPositions},
	   &SOPAC::DB::GeodeticMonument::GeodeticPosition::new({lat => $File_LATITUDE, lon => $File_LONGITUDE, ellip_ht => "0.0",
								source_id => $COORD_SOURCE_ID,
								geodetic_datum_id => $DB_GeodeticDatums{lc($GEODDATUM)}}));
    }


  } else {
    #
    # b-b.1 New addition to the database....create an entirely new geodeticMonument
    #       object and start from there.
    #
    print STDERR "    A. Creating and populating a new geodeticMonument object..." if ($verbose);
    $geodeticMonument = SOPAC::DB::GeodeticMonument->new({state => "California",country => "USA",site_type_code => "CAMGPS"});

    # b-b.2 Populate monument properties from scratch.
    #
    $geodeticMonument->site_code($File_SHORT_ID);
    $geodeticMonument->site_id($geodeticMonument->site_code . "02");
    $geodeticMonument->county($File_COUNTY) if (length($File_COUNTY));
    $geodeticMonument->site_name($File_SITE_NAME) if (length($File_SITE_NAME));
    $geodeticMonument->ngs_pid($File_NGS_PID) if ($File_NGS_PID);
    push(@{$geodeticMonument->GeodeticPositions},
	 &SOPAC::DB::GeodeticMonument::GeodeticPosition::new({lat => $File_LATITUDE, lon => $File_LONGITUDE, ellip_ht => "0.0",
							      source_id => $COORD_SOURCE_ID,
							      geodetic_datum_id => $DB_GeodeticDatums{lc($GEODDATUM)}}));
    print STDERR "ok\n";
  }


  #next;

  # c. Synchronize monument with database.
  #
  print STDERR "     B. Synchronizing monument ($File_SHORT_ID) with..." if ($verbose);
  if (defined($geodeticMonument->SetInDB($dbh))) {
    print STDERR "SOPAC database..." if ($verbose);
    unless(&SOPAC::DB::GeodeticCampaign::Monument::SetInDB($dbh,$campaignID,$geodeticMonument->site_id,$File_SHORT_ID)) {
      print STDERR "\n\tfailed association with campaign...skipping and removing from catalog\n";
      next;
    }
    print STDERR "this campaign...ok...id=" . $geodeticMonument->site_id . "\n" if ($verbose);
    $dbh->commit;
    next;
  } else {
    print STDERR "failed!...bailing\n" if ($verbose);
    next;
  }

}

close(INPUT);





quit(0);



sub quit {
  $dbh->disconnect() if (defined $dbh);
  exit shift;
}