#!/usr/bin/perl

use strict ;
use warnings ;

use lib '.' ;

use Data::Dumper ;
use File::Slurp ;

use Safelib::Dbutils ;

my %skip_tables = (
	'1MHG'	=> 1,
	'2TCG'	=> 1,
	'3MTG'	=> 1,
	'5PAY'	=> 1,
	'5PPH'	=> 1,
) ;

my $dbh = Safelib::Dbutils->connect( 'LocalServer' )  ;
$dbh or die "Connect failed: $DBI::errstr";

make_transaction_table() ;

my $al3_rows = get_al3_specs($dbh) ;

my $al3_fld_decls = make_al3_fld_decls($al3_rows) ;

create_al3_tables( $al3_fld_decls ) ;


exit ;


sub make_transaction_table {

		eval { $dbh->exec_sql( <<SQL ) } ;
drop table "dbo"."al3_transactions" ;
SQL

	my $sql = <<SQL ;
create table "dbo"."al3_transactions" (
	id			int identity(1,1),
	timestamp		datetime default( getdate()),
	file_date		datetime not null,
	broker_num		char(10) default(''),
	policy_num		char(25) not null,
	eff_year		char(4) not null,
	primary key (id)
) ;

create index al3_policy_num ON "dbo"."al3_transactions" (policy_num) ;
create index al3_eff_year ON "dbo"."al3_transactions" (eff_year) ;
create index al3_file_date ON "dbo"."al3_transactions" (file_date) ;

SQL

	eval{ $dbh->exec_sql( $sql ) ; } ;
}

sub create_al3_tables {

	my( $al3_fld_decls ) = @_ ;

	foreach my $rec_type ( sort keys %{$al3_fld_decls} ) {

		my $lrec_type = lc $rec_type ;

		eval { $dbh->exec_sql( <<SQL ) } ;
drop table "dbo"."al3_$lrec_type" ;
SQL

		next if $skip_tables{ $rec_type } ;

		my $sql = <<SQL ;

create table "dbo"."al3_$lrec_type" (
	id			int identity(1,1),
	parent_type		char(4) not null,
	parent_id		int not null,
	trans_id		int not null,
	file_date		datetime not null,
	broker_num		char(10) default(''),
	policy_num		char(25) not null,
	eff_year		char(4) not null,

$al3_fld_decls->{$rec_type}
	primary key (id)
) ;

create index al3_${lrec_type}_ind ON "dbo"."al3_$lrec_type"
	(parent_type, parent_id) ;
create index al3_${lrec_type}_trans_ind ON "dbo"."al3_$lrec_type"
	(trans_id) ;
create index al3_${lrec_type}_eff_year_ind ON "dbo"."al3_$lrec_type"
	(eff_year) ;
create index al3_${lrec_type}_policy_num_ind ON "dbo"."al3_$lrec_type"
	(policy_num) ;
create index al3_${lrec_type}_file_date_ind ON "dbo"."al3_$lrec_type"
	(file_date) ;
SQL

		eval { $dbh->exec_sql( $sql ) } or
			print $dbh->{dbh}->errstr() ;
	}
}

sub get_al3_specs {

	my( $dbh ) = @_ ;

	return $dbh->exec_select( <<SQL ) ;
select * from al3_record_formats
SQL

}

sub make_al3_fld_decls {

	my( $al3_rows ) = @_ ;

	my %fld_decls ;

	foreach my $row ( @{$al3_rows} ) {

		my $fld_name = $row->{fld_name} ;
		$fld_name =~ tr/ //d ;
		next unless length $fld_name ;
		$fld_name = 

		$fld_decls{$row->{rec_type}} .= sprintf( <<FLD, $fld_name ) ;
	%-40s char($row->{fld_len}) default(''),
FLD
	}

	return \%fld_decls ;
}