#!/pro/bin/perl

# Dit deel verzorgt het opzetten en/of verwijderen van de tijdelijke tyabellen
# die worden gebruikt bij het afleiden van de gebeurteniscode voor de BAG
#
# De code is database-ignorant opgezet en zou moeten werken bij gebruik van
# Oracle, Postgres, en SQLite

package BG::tmp;

use v5.16.2;
use warnings;

# Table definitions
my %tmp_def = (
    # Tijdelijke kolom met alle WORDT-objecten o.a. om datumbegingeldigheid om
    # te kunnen zetten van date naar timestamp en om gebeurtenissen aan te maken

    # Omzetting - met diff - van BG naar entiteit
    x_entit => [
	# type = WPL, PND, ... (entiteittype)
	[ type                      => "varchar (9)",	"not null",		],
	# id van het actuele voorkomen
	[ id                        => "number (11)",	"not null",		],
	# id in historie
	[ rid                       => "number (11)",	"not null",		],
	[ hid                       => "number (11)",				],
	# volgcode binnen entiteit: 0 = actueel
	[ versie                    => "number  (4)",	"not null",		],
	[ versie_max                => "number  (4)",	"not null",		],
	[ identificatie             => "number (16)",	"not null",		],

	[ begingeldigheid           => "number (17)",				],
	[ eindgeldigheid            => "number (17)",				],
	[ tijdstipregistratie       => "number (17)",				],

	[ c_gem                     => "number (4)",				],
	[ doc_datum                 => "number (8)",				],
	[ doc                       => "varchar (20)",				],

	# Gebeurtenis
	[ geb_fk                    => "number (11)",				],

	# Indicaties gewijzigd: T, V, of W
	[ geconstateerd             => "char (1)",	"default ' '"		],
	[ inonderzoek               => "char (1)",	"default ' '"		],
	[ status                    => "char (1)",	"default ' '"		],
	[ gebruiksdoel              => "char (1)",	"default ' '"		],
	[ oppervlakte               => "char (1)",	"default ' '"		],
	[ bouwjaar                  => "char (1)",	"default ' '"		],
	[ geometrie                 => "char (1)",	"default ' '"		],
	[ adres                     => "char (1)",	"default ' '"		],
	[ postcode                  => "char (1)",	"default ' '"		],
	[ woonplaats                => "char (1)",	"default ' '"		],

	[ n_status                  => "number (2)",	"default 0",		],
	[ o_status                  => "number (2)",				],

	[ actie                     => "char (1)",	"default ' '"		],
	],

    x_tgopnd => [
	[ tgo_fk                    => "number (11)",				],
	[ tgo_identificatie         => "number (16)",				],
	[ pnd_fk                    => "number (11)",				],
	[ pnd_identificatie         => "number (16)",				],
	[ beginrelatie              => "number (17)",				],
	[ eindrelatie               => "number (17)",				],
	],

    x_aoatgo => [
	[ aoa_fk                    => "number (11)",				],
	[ aoa_identificatie         => "number (16)",				],
	[ tgo_identificatie         => "number (16)",				],
	[ beginrelatie              => "number (17)",				],
	[ eindrelatie               => "number (17)",				],
	[ hoofdnevenadres           => "char (1)",	"default 'H'"		],
	],

    x_aoaopr => [
	[ aoa_fk                    => "number (11)",				],
	[ aoa_identificatie         => "number (16)",				],
	[ opr_identificatie         => "number (16)",				],
	[ wpl_identificatie         => "number (16)",				],
	[ aoa_begingeldigheid       => "number (17)",				],
	[ aoa_eindgeldigheid        => "number (17)",				],
	],

    x_oprwpl => [
	[ opr_fk                    => "number (11)",				],
	[ opr_identificatie         => "number (16)",				],
	[ wpl_identificatie         => "number (16)",				],
	[ opr_begingeldigheid       => "number (17)",				],
	[ opr_eindgeldigheid        => "number (17)",				],
	],

    x_geb => [
	[ id                        => "number (11)",	"not null primary key"	],
	[ volgnr                    => "number (11)",				],
	[ c_gem                     => "number (4)",				],
	[ datum                     => "number (8)",				],
	[ geb_bag                   => "varchar (20)",				],
	[ geb_oms                   => "varchar (100)",				],
	[ geb_0310                  => "varchar (20)",				],
	[ const_in                  => "number (6)",	"default 0",		],
	[ const_uit                 => "number (6)",	"default 0",		],
	[ ond_in                    => "number (6)",	"default 0",		],
	[ ond_uit                   => "number (6)",	"default 0",		],
	[ wpl                       => "number (6)",	"default 0",		],
	[ opr                       => "number (6)",	"default 0",		],
	[ pnd                       => "number (6)",	"default 0",		],
	[ vbo                       => "number (6)",	"default 0",		],
	[ sta                       => "number (6)",	"default 0",		],
	[ lig                       => "number (6)",	"default 0",		],
	[ aoa                       => "number (6)",	"default 0",		],
	[ objecten                  => "number (6)",	"default 0",		],
	],
    );

my %def_def = (
    bg_gc	=> [
	[ id                        => "bigserial",	"not null primary key"	],
	[ indSync                   => "varchar (5)",	"not null",		],
	[ type                      => "varchar (20)",	"not null",		],
	[ type_n                    => "char (1)",	"not null default '2'",	], # ignored
	[ type_id                   => "number (11)",	"not null",		],
	[ type_id_n                 => "char (1)",	"not null default '2'",	], # ignored
	[ type_id_h                 => "number (11)",				],
	[ type_id_h_n               => "char (1)",	"not null default '2'",	], # ignored
	[ ind_h                     => "varchar (1)",	"not null",		],
	[ ind_h_n                   => "char (1)",	"not null default '2'",	], # ignored
	[ identificatie             => "number (16)",	"not null",		],
	[ identificatie_n           => "char (1)",	"not null default '2'",	], # ignored
	[ c_gem                     => "number (4)",				],
	[ c_gem_n                   => "char (1)",	"not null default '2'",	], # ignored
	[ datum                     => "number (8)",				],
	[ datum_n                   => "char (1)",	"not null default '2'",	], # ignored
	[ gebeurtenis               => "number (11)",	"not null",		],
	[ gebeurtenis_n             => "char (1)",	"not null default '2'",	], # ignored
	[ volgnummer                => "number (4)",	"not null",		],
	[ volgnummer_n              => "char (1)",	"not null default '2'",	], # ignored
	[ bag                       => "varchar (20)",				],
	[ bag_n                     => "char (1)",	"not null default '2'",	], # ignored
	[ bg                        => "varchar (20)",				],
	[ bg_n                      => "char (1)",	"not null default '2'",	], # ignored
	[ omschrijving              => "varchar (100)",				],
	[ omschrijving_n            => "char (1)",	"not null default '2'",	], # ignored
	[ actie                     => "varchar (1)",				],
	[ actie_n                   => "char (1)",	"not null default '2'",	], # ignored
	],
    );

# The indexes I want
my @tmp_idx = (
    [ x_entit  => qw(u type id versie				)],
    [ x_entit  => qw(  rid type					)],
    [ x_entit  => qw(  id begingeldigheid type			)],
    [ x_entit  => qw(  identificatie versie type		)],
    [ x_entit  => qw(  doc_datum doc type			)],
    [ x_entit  => qw(  geb_fk type begingeldigheid		)],
    [ x_entit  => qw(  geb_fk type identificatie versie		)],
    [ x_entit  => qw(  geb_fk begingeldigheid			)],
    [ x_entit  => qw(  begingeldigheid versie			)],
    [ x_entit  => qw(  n_status o_status versie versie_max	)],
    [ x_entit  => qw(  n_status type versie_max versie		)],
    [ x_tgopnd => qw(  tgo_fk beginrelatie eindrelatie		)],
    [ x_aoatgo => qw(  aoa_fk					)],
    [ x_aoaopr => qw(  aoa_fk					)],
    [ x_oprwpl => qw(  opr_fk opr_begingeldigheid		)],
    [ x_geb    => qw(  volgnr id				)],
    [ x_geb    => qw(  geb_oms id				)],
    );
my @def_idx = (
    [ bg_gc    => qw(  c_gem gebeurtenis volgnummer		)],
    [ bg_gc    => qw(  gebeurtenis volgnummer type		)],
    );

my %tbl;

my %range = (
    x_entit      => "tijdstipregistratie",
    x_geb        => "datum",
    bg_gc        => "datum",
    bg_gc_status => "tijdstempel",
    );

sub count
{
    my ($dbh, $tbl, $r) = @_;
    $::opt_v or return;
    local $" = ", ";
    %tbl or all_tables ($dbh);
    unless (exists $tbl{$tbl}) {
	printf STDERR "%-12s:  missing\n", $tbl;
	return;
	}
    my @fld = "count (*)";
    $r = $range{$tbl} and push @fld, "min ($r)", "max ($r)";
    my $sth = $dbh->prepare ("select @fld from $tbl");
    $sth->execute;
    if (my @row = $sth->fetchrow_array) {
	my $cnt = shift @row;
	printf STDERR "%-12s:%9d %s\n", $tbl, $cnt, join " .. " => map { sprintf "%8.8s", $_ // "-" } @row;
	}
    } # count

sub count_all
{
    my $dbh = shift;
    $::opt_v or return;
    warn "\n";
    count ($dbh, "x_$_") for qw( entit tgopnd aoatgo aoaopr oprwpl geb );
    } # count_all

sub all_tables
{
    my $dbh = shift;
    %tbl = ();
    foreach my $tbl ($dbh->tables (undef, undef, undef, undef)) {
	$tbl =~ tr/'"`//d;
	$tbl =~ s/.*\.//;
	$tbl{lc $tbl}++;
	}
    } # all_tables

sub clear_tmp
{
    my $dbh = shift;
    my %done;
    $::opt_v and warn "Clearing temp tables ...\n";
    # Find me all tables
    all_tables ($dbh);
    foreach my $tbl (sort keys %tbl) {
	# Drop only the tables I replace and drop them only once
	(exists $tmp_def{$tbl} || $tbl =~ m/^(x|tmp)_/i) && !$done{$tbl}++ or next;
	$::opt_v and warn "Drop table $tbl\n";
	$dbh->do ("drop table $tbl");
	}
    $dbh->{AutoCommit} or $dbh->commit;
    } # clear_tmp

my %fields;
sub fields
{
    return @{$fields{$_[0]}};
    } # fields

sub create_table
{
    my ($dbh, $tbl, @def) = @_;

    if ($dbh->{Driver}{Name} eq "Oracle") {
	$_->[1] =~ s/varchar/varchar2/ for @def;
	}
    if ($dbh->{Driver}{Name} eq "Pg") {
	$_->[1] =~ s/number/numeric/   for @def;
	}
    else {
	# CRASH on bigserial
	}

    my $def = join ", " => map { join " " => @$_ } @def;
    $fields{$tbl} = [ grep !/_n$/, map { $_->[0] } @def ];

    exists $tbl{$tbl} and return 0;

    $::opt_v and warn "Create table $tbl\n";
    $dbh->do ("create table $tbl ($def)");
    return 1;
    } # create_table

sub create_index
{
    my ($dbh, $tbl, @fld) = @_;

    my $u = $fld[0] eq "u" ? do { shift @fld; " unique" } : "";
    my $idx = join "_", "idx", $tbl, @fld;
    $idx =~ s/_[a-z]{5}\K[a-z]+_/_/g;
    local $" = ", ";
    length ($idx) > 30 and $idx = substr $idx, 0, 29;
    $::opt_v and warn "Create$u index $idx\n";
    $dbh->do ("create$u index $idx on $tbl (@fld)");
    } # create index

sub create_bg
{
    my $dbh = shift;

    all_tables ($dbh);

    my $n = 0;
    $n +=  create_table ($dbh, $_, @{$def_def{$_}}) for keys %def_def;
    $n and create_index ($dbh, @$_)                 for @def_idx;

    $dbh->{AutoCommit} or $dbh->commit;
    } # create_bg

sub create_all
{
    my $dbh = shift;

    all_tables ($dbh);

    my $n = 0;
    $n +=  create_table ($dbh, $_, @{$tmp_def{$_}}) for keys %tmp_def;
    $n and create_index ($dbh, @$_)                 for @tmp_idx;

    $dbh->{AutoCommit} or $dbh->commit;
    } # create_all

1;
