#!/usr/bin/perl -w

use strict;
use DBI;
use Getopt::Std;
use POSIX qw(
    ceil
);
my %options = ();
getopts("c:",\%options);
my $configFile = $options{c};
if (!$configFile || ! -f $configFile) {
    print "Usage: $0 -c CONFIG_FILE\n";
    exit 1;
}

my $config = loadConfig($configFile);
my $dbh = makeConnection($config);
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}

$| = 1;
# Codes start...

my $zdbDir = $config->{'zRoot'} .'/'
           . $config->{'zPort'} .'/'
           . 'record' .'/'
           . $config->{'zDatabase'};

trim_spaces($dbh, $zdbDir);

# Codes end.

exit 0;
################################################################################


sub makeConnection {
    my ($config) = @_;
    if (!$config) {
        return;
    }
    my ($db_driver, $db_name, $db_host, $db_port, $db_user, $db_password);

    $db_driver   = $config->{'db_driver'} || 'mysql';
    $db_name     = $config->{'db_name'};
    $db_host     = $config->{'db_host'};
    $db_port     = $config->{'db_port'}   || '3306';
    $db_user     = $config->{'db_user'};
    $db_password = $config->{'db_password'};

    my $dsn = "dbi:$db_driver:$db_name:$db_host:$db_port";

    return DBI->connect($dsn, $db_user, $db_password);
}
############################################################


sub loadConfig {
    my ($configFile) = @_;
#    print "Enter the config filename of Opals: ";
#    $configFile = <STDIN>;
    my $config = {};

    open CONF, $configFile || die "Cannot open file $configFile";
    while (<CONF>) {
        chomp;
        s/#.*//;                # remove comments
        next if /^\s*$/;        # ignore blank lines

        if (/^\s*(\w+)\s*=\s*(.*?)\s*$/) {
            $config->{$1} = $2;
        }
    }
    close CONF;
    
    return $config;
}
############################################################
############################################################


sub trim_spaces {
    my ($dbh, $zdbDir) = @_;
    my $holdings;

    print "Trim trailing spaces in XML file...\n";
    my $updateList = get_barcode_list($dbh, undef);
    foreach my $rid (sort keys %{$updateList}) {
        $holdings = $updateList->{$rid};
        trimBarcode($zdbDir, $rid, $holdings);
    }
    print "Trim trailing spaces in XML file: done.\n";

    print "Trim trailing spaces in SQL database...\n";
    my $with_trailing_space = 1;
    $updateList = get_barcode_list($dbh, $with_trailing_space);
    foreach my $rid (sort keys %{$updateList}) {
        $holdings = $updateList->{$rid};
        trimBarcode($zdbDir, $rid, $holdings);
    }
    print "Trim trailing spaces in SQL database: done.\n";
}
############################################################


sub get_barcode_list {
    my ($dbh, $with_trailing_space) = @_;
    my $updateList;

    my $regexp_param = '[[:alnum:]]$'; # without trailing spaces
    if ($with_trailing_space) {
        $regexp_param = ' +$';
    }

    my $sth = $dbh->prepare(<<_SQL_);
select  rid, barcode
from    opl_item
where   barcode regexp '$regexp_param'
     && barcode not regexp '^___'
_SQL_

    $sth->execute() || return;
    while (my ($rid, $bc) = $sth->fetchrow_array()) {
        $updateList->{$rid}->{$bc} = 1;
    }
    $sth->finish;

    return $updateList;
}
############################################################


sub trimBarcode {
    my ($zdbDir, $rid, $holdings) = @_;

    my $file = "$zdbDir/" . ceil($rid/1000) . "/$rid.xml";
    if (! -f $file) {
        print "ERROR: $file: not found.\n";
        return;
    }
    
    my $xml = '';
    open MARCXML, "<$file";
    while (<MARCXML>) {
        $xml .= $_;
    }
    close MARCXML;

    my $updated = 0;
    my $old_bc;
    
    foreach my $bc (keys %{$holdings}) {
        $bc =~ s/ +$//;
        if ($xml =~ s/<subfield code="p">($bc +)<\/subfield>/<subfield code="p">$bc<\/subfield>/g) {
            $old_bc = $1;
            updateBarcode_inSqlDb($dbh, $bc);
            $updated = 1;
            print "$file\t=> '$old_bc'\t'$bc'\n";
        }
    }

    if ($updated == 1) {
        open FILE, ">$file";
        print FILE $xml;
        close FILE;
    }
}
############################################################


sub updateBarcode_inSqlDb {
    my ($dbh, $bc) = @_;

    foreach my $table qw(found ge852record historyItem hold inventory item itemInfo itemstatus loan sessionVar) {
        #print "Replace '$bc' by '$barcode' in opl_$table\n";
        $dbh->do(<<_SQL_);
update  opl_$table
set     barcode = '$bc'
where   barcode regexp '^$bc +\$'
_SQL_
    }
}