#!/usr/bin/perl -w

use strict;
use DBI;
use Data::Dump qw/dump/;
use Algorithm::CheckDigits;
use Text::Unaccent;

# sudo apt-get install libdbd-sqlite3-perl libdbd-csv-perl libtext-unaccent-perl

$|++;

my $f = DBI->connect("dbi:SQLite:dbname=ferlib-2011-06-27.sqlite","","", { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
$f->{sqlite_unicode} = 1;
my $k = DBI->connect("dbi:mysql:database=koha_fer","","", { RaiseError => 1, AutoCommit => 0, mysql_enable_utf8 => 1 }) || die $DBI::errstr;
my $u = DBI->connect("dbi:CSV:","","", { RaiseError => 1, f_ext => '.csv', csv_sep_char => '|' }) || die $DBI::errstr;

our $lookup;
sub lookup {
	my ( $dbh, $t, $k, $v, $s, $default ) = @_;
	my $hash;
	my $key = "$t $k $v";
	my $max = $v =~ s/max\((\w+)\)/$1/;
	if ( exists $lookup->{$key} ) {
		$hash = $lookup->{$key};
	} else {
		warn "# select $k,$v from $t";
		my $sth = $dbh->prepare( "select $k,$v from $t" );
		$sth->execute;
		my @non_unique;
		while (my $row = $sth->fetchrow_hashref() ) {
			if ( ! $max && exists $hash->{ $row->{$k} } ) {
				push @non_unique, $row->{$k};
				next;
			}
			$hash->{ $row->{$k} } = $row->{$v};
		}
		if ( @non_unique ) {
			warn "# remove non-unique ", dump(@non_unique);
			delete $hash->{$_} foreach @non_unique;
		}
		$lookup->{$key} = $hash;
		warn "# lookup $key = ",dump($hash);
	}
#	return unless length $s > 1;
	if ( ! exists $lookup->{$key}->{$s} ) {
		warn "ERROR: no $s in $key\n";
		return $default;
	} else {
		$lookup->{$key}->{$s};
	}
}

our $insert;
sub insert {
	my ( $table, $row ) = @_;
	$insert->{$table} ||= $k->prepare(
			"insert into $table ("
			. join(',', keys %$row)
			. ") values ("
			. join(',', map { '?' } keys %$row )
			. ")"
	);

	foreach my $c ( grep { /(date|timestamp)/ } keys %$row ) {
		$row->{$c} =~ s/^(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/;
		$row->{$c} = undef if $row->{$c} eq ''; # XXX NULL dates istead of 0000-00-00
	}

	$insert->{$table}->execute( values %$row );
	warn "# inserted $table ", dump $row;

}

sub sql {
	my ($db,$sql) = @_;
	foreach (split(/;/,$sql)) {
		next if m/^\s*$/;
		warn "# sql $_";
		$db->do($_);
	}
}


my $sif2orgjed = {
	56 => 'SRE',
	1  => 'ZPF',
	2  => 'ZPM',
	3  => 'ZOEM',
	4  => 'ZESA',
	5  => 'ZPR',
	6  => 'ZVNE',
	7  => 'ZTEL',
	8  => 'ZESOI',
	9  => 'ZARI',
	10  => 'ZEA',
	11  => 'ZEMRIS',
	12  => 'ZRK',
	15  => 'CIP'
};

sql $k => qq{
delete from subscription;
alter table subscription auto_increment = 1;
delete from subscriptionhistory;
alter table subscriptionhistory auto_increment = 1;
delete from serial;
alter table serial auto_increment = 1;
};

my $sql = qq{
select distinct
  broj.sif_casop as biblionumber,
  posj_br.sif_orgjed as branchcode,

  'v.' || broj.vol_broj || ':no.' || broj.broj as serialseq,
  broj.god_izd || '-' || broj.mjes_izd || '-' || broj.dan_izd as publisheddate,
  primj_bruk.dat_prim as planneddate,
  'ND:' || primj_bruk.ozn_nacdob || ' INV:' || primj.inv_broj || ' ; napomena: ' || broj.koment_broj || ' ; ' || primj_bruk.komentar || ' ; ' || primj.komentar as notes

from broj
left join posj_br on posj_br.sif_broj=broj.sif_broj
left join primj_bruk on primj_bruk.sif_broj=broj.sif_broj and  primj_bruk.sif_orgjed=branchcode
left join primj on primj.sif_naslov=broj.sif_broj and  primj.tip_naslov='B' and primj.sif_orgjed=branchcode and  primj_bruk.dat_prim=primj.dat_nabava
};
my $sth = $f->prepare($sql);
$sth->execute;

my $biblionumber_branchcode;

while (my $row = $sth->fetchrow_hashref ) {

	warn "## row = ",dump $row;
	my $subscriptionid;

	$row->{biblionumber} = lookup($k, qq|biblioitems where itemtype='C'|, 'collectionvolume' => 'biblionumber', $row->{biblionumber}) || die "no biblionumber";
	$row->{branchcode} = $row->{branchcode} ? $sif2orgjed->{ $row->{branchcode} } : 'SRE';

	my $bn_bc_key = $row->{biblionumber} . ' ' . $row->{branchcode};

	if ( $subscriptionid = $biblionumber_branchcode->{ $bn_bc_key } ) {
		warn "existing subscription $subscriptionid";
	} else {

		insert 'subscription' => {
			biblionumber => $row->{biblionumber},
			branchcode   => $row->{branchcode},
		};

		$biblionumber_branchcode->{ $bn_bc_key } = $subscriptionid = $k->{mysql_insertid};

		insert 'subscriptionhistory' => {
			biblionumber =>   $row->{biblionumber},
			subscriptionid => $subscriptionid,
		};

	}

	$row->{publisheddate} =~ s/-$/-01/;
	$row->{publisheddate} =~ s/--/-01-/;
	$row->{plannedddate} =~ s/(\d+)\.(\d+)\.(\d\d\d\d)/$3-$2-$1/;

	$row->{notes} =~ s/ ; $//;
	$row->{notes} =~ s/:  ; /: /;
	$row->{notes} =~ s/ ; napomena: $//;

	insert 'serial' => {
		biblionumber =>   $row->{biblionumber},
		subscriptionid => $subscriptionid,
		status => 2,
		serialseq => $row->{serialseq},
		planneddate => $row->{planneddate},
		publisheddate => $row->{publisheddate},
		notes => $row->{notes},
	};

}

$k->commit;

