#!/usr/bin/perl --

use DBI;

#CONFIG

#Your gallery user id
my $g_userId = "<username>";
#
#Path to albums directory within g2data
my $g_datadir = "<path>";
#
#Path to the GPX file
my $gpxFile = "<path>";
#Path to gps2photo
$gps_photo_path = "<path>";

#
$dbh = DBI->connect("dbi:mysql:<mysql-server>:<mysql-database>", '<mysql-user>', '<mysql-password>');


my ($selected) = @_;

my $str = "";
$query = "SELECT * FROM g_AlbumItem ai LEFT JOIN g_FileSystemEntity fse ON ai.g_id = fse.g_id";
$sth = $dbh->prepare($query);
$rs = $sth->execute;

while ($r = $sth->fetchrow_hashref()) {
   #$albumId = print $r->{'g_pathComponent'}."\n";
   $albumId = $r->{'g_id'};
 
   $parentId = $albumId;
   $i=0;
   $pathStr = "";
   while (($parentId != 7) && ($i < 5)) {
      $i++;
      $pathQuery = "select g_pathComponent Path,g_parentId ParentId from g_FileSystemEntity fse".
      " LEFT JOIN g_ChildEntity ce ON fse.g_Id = ce.g_Id".
      " LEFT JOIN g_Item parent ON parent.g_Id = ce.g_parentId".
      " WHERE fse.g_id = ?";
      
      $pathHandler = $dbh->prepare($pathQuery);
      $pathHandler->execute($parentId);
      
      $path = $pathHandler->fetchrow_hashref();
      $parentId = $path->{'ParentId'};
      $pathStr = $path->{'Path'} . "/" . $pathStr;
      
   }
   
   print "** $pathStr ($albumId) ** \n";
 

      
      $childrenQuery = "select g_pathComponent Path,g_ownerId OwnerId, g_parentId ParentId FROM g_ChildEntity ce ".
      " LEFT JOIN g_FileSystemEntity fse ON fse.g_Id = ce.g_Id".
      " LEFT JOIN g_Item parent ON parent.g_Id = ce.g_parentId".
      " WHERE ce.g_parentId = ? and g_ownerId = ?";
      
      $childrenHandler = $dbh->prepare($childrenQuery);
      $childrenHandler->execute($albumId, $g_userId);
      
      $fileList = "";
      while ($child = $childrenHandler->fetchrow_hashref()) {
         $childPath = "$g_datadir/".$pathStr.$child->{'Path'};
         if ($childPath =~ /.jpg\Z/i) {
            $fileList = $childPath."\n".$fileList;
         }
      }
      if ($fileList ne "") {
         open FILE, ">filelist.txt";
         print FILE $fileList;
         close FILE;
         `$gps_photo_path --timeoffset=0 --gpsfile $gpxFile --overwrite-geotagged -I filelist.txt`;
      }
 
}