#!/usr/bin/perl

# Copyright 2012 TMA
# License: GNU GPL version 2

use strict;
use warnings;
use utf8;

my $status = 'endmail';

my ($fio,$konto,$castka,$VS,$SS,$zprava,$protiucet,$date);
my ($header, $body);

my $mul;

sub vreset() {
	($header,$body) = ('','');
	($fio,$konto,$castka,$VS,$SS,$zprava,$protiucet,$date) = undef;
}

sub process($) {
	$status = $_[0];
	no warnings;
	$date =~ s/ +\d+:\d+:\d+ +[+-]\d{4}$//o;
	$date =~ s/^..., *(\d+) (...|\d+) (\d\d\d\d).*/$3-$2-$1/o;
	$date =~ s/Jan/01/o;
	$date =~ s/Feb/02/o;
	$date =~ s/Mar/03/o;
	$date =~ s/Apr/04/o;
	$date =~ s/May/05/o;
	$date =~ s/Jun/06/o;
	$date =~ s/Jul/07/o;
	$date =~ s/Aug/08/o;
	$date =~ s/Sep/09/o;
	$date =~ s/Oct/10/o;
	$date =~ s/Nov/11/o;
	$date =~ s/Dec/12/o;
	$date =~ s/ /0/o;
	$date =~ s/-(\d)-/-0$1-/o;
	$date =~ s/-(\d)$/-0$1/o;
	print "-- $ARGV $.\n";
	print "insert into ucetni_data (konto, castka, datum, VS, SS, protiucet, zprava, comment) values
	($konto, $castka", commanullq($date),
	commanull($VS), commanull($SS), commanullq($protiucet),
	commanullq($zprava),commanullq($header."\n\n".$body),");\n";
}

sub def_or_empty($) { defined$_[0]?$_[0]:'' }

sub quot($) {
	my ($x) = @_;
	# ' needs to be doubled
	$x =~ s/'/''/go;
	# replace suspicious characters by .
	$x =~ s{[^][\na-zA-Z0-9 !@#$%^&*()_=+;:"|<>/?,.~`{}'-]}{.}go; # '
	"'$x'"
}

sub commanull_($&) {
	my ($x, $q) = (@_);
	#print STDERR "[[$x]]\n";
	no warnings;
	(length $x) ? ", ". $q->($x) : ", NULL"
}

sub commanull($) { commanull_($_[0],sub {$_[0]}) }

sub commanullq($) { commanull_($_[0],\&quot) }

sub decimal($) {
	my ($x) = @_;
	$x =~ y/,/./;
	$x =~ s/ //g;
	$x
}

my %action = (
	endmail => sub {
		#print "$status: $_\n";
		/^From / and do {{ $status = 'header'; vreset }}
	},
	header => sub {
		$header .= $_."\n";
		#print "$status: $_\n";
		/^From: automat(?:\@| at )fio\.cz/ and $fio = 1;
		/^$/ and $status = ($fio ? 'body' : 'endmail'); 
		/^Date: (.*)/ and $date = $1;
		#print "$status(",def_or_empty($fio),"): $_\n";
       	},
	body => sub {
		/^From / or $body .= $_."\n";
		#print "$status: $_\n";
		/^P..jem na kont.: (\d+)/ and ($konto, $mul) = ($1,1);
		/^V.daj na kont.: (\d+)/ and ($konto, $mul) = ($1,-1);
		/^..stka: ([0-9 ]+[,.]\d+)?/ and $castka = $mul * decimal($1);
		/^VS: (\d+)/ and $VS = $1;
		/^SS: (\d*)/ and $SS = $1;
		/^Zpr.va p..jemci: (.*)/ and $zprava = $1;
		/^Proti..et: ([0-9\/-]+)/ and $protiucet = $1;
		/_____/ and process('endmail');
		/^From / and (process('header'),vreset);
		#/^P[?ř][?í]jem na kont[?ě]: (\d+)/ and 	$konto = $1;
		#/^[?Č][?á]stka: (\d+)[,.](\d+)?/ and $castka = $1 + $2/100;
		#/^VS: (\d+)/ and $VS = $1;
		#/^SS: (\d*)/ and $SS = $1;
		#/^Zpr[?á]va p[?ř][?í]jemci: (.*)/ and $zprava = $1;
		#/^Proti..et: ([0-9\/-]+)/ and $protiucet = $1;
		#/_____/ and process;
	}
);

my ($drop,$create) = (1,1);
my $SEP = ';';
print <<EOF if $drop;
drop table if exists ucetni_data;
EOF
print <<EOF if $create;
CREATE TABLE IF NOT EXISTS ucetni_data (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `konto` decimal(30,0) NOT NULL,
  `castka` decimal(30,2) NOT NULL,
  `datum` date NOT NULL,
  `vs` int(11) DEFAULT NULL,
  `ss` int(11) DEFAULT NULL,
  `protiucet` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zprava` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comment` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=563 ;
EOF

while (<>) {
	chomp;
	$action{$status}->();
}

process('') if $status eq 'body';