# -- Migratie EekBoek database van versie 1.0.8 naar 1.0.9 (EB 0.92).

package main;

our $cfg;
our $dbh;

package EB::DatabaseMigrator;

use strict;
use warnings;
use EB;
use EB::Tools::SQLEngine;

my $en = EB::Tools::SQLEngine->new(dbh => $dbh->dbh,
				   trace => $cfg->val(qw(internal trace_migration), 0));

$en->process(<<EOS);
BEGIN WORK;

-- Add new column.

ALTER TABLE Boekstukken
  ADD COLUMN bsk_isaldo int8;
EOS

$dbh->trace($cfg->val(qw(internal trace_migration), 0));

my $sth1 = $dbh->sql_exec("SELECT dbk_id".
			  " FROM Dagboeken".
			  " WHERE dbk_type = ? OR dbk_type = ?",
			  DBKTYPE_BANK, DBKTYPE_KAS);
while ( my $rr1 = $sth1->fetchrow_arrayref ) {
    my ($dbk_id) = @$rr1;
  my $sth3 = $dbh->sql_exec("SELECT bky_code FROM Boekjaren");
  while ( my $rb = $sth3->fetchrow_arrayref ) {
    my $bky = $rb->[0];

    my %saldi;
    my %amt;
    my $sth2 = $dbh->sql_exec("SELECT bsk_nr,bsk_amount,bsk_saldo".
			      " FROM Boekstukken".
			      " WHERE bsk_dbk_id = ?".
			      " AND bsk_bky = ?",
			      $dbk_id, $bky);
    while ( my $rr2 = $sth2->fetchrow_arrayref ) {
	$amt{$rr2->[0]} = $rr2->[1];
	$saldi{$rr2->[0]} = $rr2->[2];
    }
    $sth2->finish;
    foreach my $bsk_nr ( keys(%saldi) ) {
	if ( exists $saldi{$bsk_nr-1} ) {
	    warn("SALDO MISMATCH: dbk=$dbk_id nr=$bsk_nr -- PLEASE REBUILD DATABASE\n")
	      unless $saldi{$bsk_nr-1} == $saldi{$bsk_nr} - $amt{$bsk_nr};
	    $dbh->sql_exec("UPDATE Boekstukken".
			   " SET bsk_isaldo = ?".
			   " WHERE bsk_nr = ?".
			   " AND bsk_dbk_id = ?".
			   " AND bsk_bky = ?",
			   $saldi{$bsk_nr-1}, $bsk_nr, $dbk_id, $bky)->finish;
	}
	else {
	    $dbh->sql_exec("UPDATE Boekstukken".
			   " SET bsk_isaldo = bsk_saldo - bsk_amount".
			   " WHERE bsk_nr = ?".
			   " AND bsk_dbk_id = ?".
			   " AND bsk_bky = ?",
			   $bsk_nr, $dbk_id, $bky)->finish;
	}
    }
  }
}

$en->process(<<EOS);
-- Bump version.

UPDATE Constants
  SET value = 9
  WHERE name = 'SCM_REVISION';
UPDATE Metadata
  SET adm_scm_revision =
    (SELECT int4(value) FROM Constants WHERE name = 'SCM_REVISION');

COMMIT WORK;
EOS

1;