#!/usr/bin/perl
#
use 5.006;
use utf8;
use strict;
use warnings;
use DBI;
use DBD::mysql;
use Cwd;
#
#  ********* BEGIN Settings *********
#  1) Remote MySQL Database
#     a) Remote Database
#       my $remote_dsn = 'dbi:mysql:dbname=xxxxxxx;host=xxxxxxx';
#       my $remote_user = 'xxxxxxx';
#       my $remote_password = 'xxxxxxx';
#
#     b) Local Test-Database 
      my $remote_dsn = 'dbi:mysql:dbname=xxxxxxx;host=xxxxxxx';
      my $remote_user = 'xxxxxxx';
      my $remote_password = 'xxxxxxx';
#
#  2) Local SQLite Database (hosts all geocaches found)
      my $sqlite_file = "/home/marcus/.gcfounds2mysql/foundcaches.db";
      my $local_dsn = 'dbi:SQLite:dbname=$sqlite_file","",""';
#
#  3) Geolog.pl option file and search term for the working directory
      my $geolog_optionfile = "/home/marcus/.geolog/profile.txt";
      my $searchstring1 = 'directory: ';
#
#  4) Relative path from working directory to the found geocaches
#     Caution: leading and trailing slashes required!
      my $workdir = "/found/";
#
#  ********* END Settings *********
#
#
#
#  ********* BEGIN "Read geolog.pl working dir from its option file" *********
#
# open file; OPTIONFILE is the file handle
open (OPTIONFILE,$geolog_optionfile) or die "Error: profile.txt file cannot be opened!\n$!";
# walk line by line through the file
while (<OPTIONFILE>){            
        my $line1 = $_; # read the current line
        chomp $line1;   # remove the trailing character
        # Test: if line starts with the search string ...
        if ($line1=~/$searchstring1/) {
                    # split line into separate strings, separated by blanks
                    my @line2=split(/: /,$line1);
                    # the last value of the array is our path
                    # we add this in front of the $workdir
                    $workdir=$line2[-1].$workdir;
                    print "1: found the working directory in file $geolog_optionfile: \n$workdir\n";
                    last;
                    }
        }
# close file again
close(OPTIONFILE);
#
#  ********* END *********
#
#
# STEP 1  RECURSIVELY WALK THROUGH ALL SUB DIRECTORIES:
# STEP 1.1   change to the working directory
#
# change the directory
chdir($workdir) or die "Error: Cannot change to working directory!\n   $!";
print "2: changed to the working directory\n$workdir\n";
#
#
# STEP 1.2   open directory and read all sub directories
opendir (DIR,'.') or die "Error: Working directory cannot be opened!\n$!"; 
while(my $subdir = readdir (DIR)) {           
            print "$subdir\n";
            print "$workdir\n";
            my $subdir2 = $workdir;
            print "4: TEST\n";
            print "5: Subdir: $subdir2\n";

            $subdir2.=$subdir."\/";  # append current directory + trailing slash
            print "\n\n6: Directory: ",$subdir2,"\n";
            print "7: $subdir2\n";
            opendir (SUBDIR,'$subdir2') or die $!;
            while(my $cachefiles = readdir (SUBDIR))
                      {
                        print $cachefiles,"\n";
                      }
            # closes the sub directory after each inner loop
            closedir (SUBDIR);
          }
# closes the workdir at end of the main loop
closedir (DIR);


# --> DEBUG:  close early
exit;



# TODO 4 DATABASE LAYOUTS
# TODO 4.1  SQLite table layout (temporary)
# TODO *  Finalize Layout of local SQLite database
# Current Layout:  (temporary, testing only)
#       TABLE `main` 
#              `gcnumber` INTEGER PRIMARY KEY 
#              `gcid`     TEXT 
#              `gcstatus` TEXT 
#              `dbstatus` TEXT
#

# STEP 5 CONNECT TO LOCAL SQLite DATABASE
# # Establish connection to local SQLite database
# my $local_dbh = DBI->connect($local_dsn, { RaiseError => 1, AutoCommit => 1 }) 
              # #  DBI->connect failed:
              # or die('DBI->connect to SQLite File failed:' . DBI->errstr());
# 
# my $rv = $local_dbh->last_insert_id(undef, undef, undef, undef);
# print "last_insert_id: ", $rv, "\n";
# 
# # Disconnect from the SQLite file                    
# $local_dbh->disconnect;
#

# --> DEBUG:  close early
exit;


#
# 
# STEP 10  CONNECT TO REMOTE DATABASE
#
# Establish connection to remote database.
# Connection will be held during all transactions.
print "Establish connection to remote database...\n";

my $remote_dbh = DBI->connect($remote_dsn, $remote_user, $remote_password,
                    { RaiseError => 1, AutoCommit => 1 }) 
              #  DBI->connect fehlgeschlagen:
              or die('DBI->connect fehlgeschlagen:' . DBI->errstr());






# Danach für jeden Eintrag des Arrays einen Statement-Handle aufmachen
# in der Schleife jeweils prepare & execute & finish


## ERSTE TESTS
my $db_gcnumber = '138';

# $sth is statement handler
my $sth = $remote_dbh->prepare('SELECT * FROM jml_geocaches WHERE gcnumber >= ?') 
               or die('Kann keine Verbindung zur Datenbank aufbauen. Fehler: ' . DBI->errstr());

my $rv = $sth->execute($db_gcnumber) 
               or die('Kann Statement nicht ausführen: ' . DBI->errstr());


print "rv = $rv\n";   # returns the number of search results


                    
# Finalize the statement handler.
$sth->finish();
               
# Disconnect from the remote database                    
$remote_dbh->disconnect;


# Job done; 
exit;