package Safelib::AL3::DB ;

use strict ;
use warnings ;

use Safelib::Dbutils ;

use base 'Exporter' ;
our @EXPORT = qw(
	load_al3_formats_from_db
	load_al3_parent_child_from_db
	insert_al3_transaction
	insert_al3_records
	get_al3_trans_by_column
	get_al3_records_by_trans_id
	delete_al3_by_column
	store_eview_records
) ;

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

sub load_al3_formats_from_db {

	my $al3_fld_rows = $dbh->exec_select( <<SQL ) ;
select * from al3_record_formats
SQL

	return $al3_fld_rows ;
}

sub load_al3_parent_child_from_db {

	my $parent_child_rows = $dbh->exec_select( <<SQL ) ;
select parent_type, child_type from al3_parent_child
SQL

	my %al3_children ;
	foreach my $db_row ( @{$parent_child_rows} ) {

		$al3_children{ $db_row->{parent_type} }{ $db_row->{child_type} }
			= 1 ;
	}

	return \%al3_children ;
}

sub insert_al3_transaction {

	my( $common_data ) = @_ ;

	my $id = $dbh->insert_records( 'al3_transactions', $common_data ) ;

	return $id->[0] ;
}

sub insert_al3_records {

	my( $rec_type, @records ) = @_ ;

	return $dbh->insert_records( "al3_\L$rec_type", @records ) ;
}

sub get_al3_trans_by_column {

	my( $col_name, $col_val ) = @_ ;

	my $trans_rows = $dbh->exec_select( <<SQL, $col_val ) ;
select * from al3_transactions where $col_name = ?
SQL

	return $trans_rows ;
}


sub get_al3_records_by_trans_id {

	my( $rec_type, $trans_id ) = @_ ;

	my $rows = $dbh->exec_select( <<SQL, $trans_id  ) ;
select * from al3_$rec_type where trans_id = ?
SQL
	return $rows ;
}

sub store_eview_records {

	my( $eview_records, $truncate ) = @_ ;

	truncate_table( 'al3_eview' ) if $truncate ;

	$dbh->insert_records( 'al3_eview', @{$eview_records} ) ;
}

sub delete_al3_by_column {

	my( $col_name, @col_vals ) = @_ ;

	while( my @some_vals = splice( @col_vals, 0, 1000 ) ) {

		my $in_list = join ', ', ('?') x @some_vals ;
		my $where = "WHERE $col_name IN ($in_list)" ;

		foreach my $al3_table ( qw(
			transactions
			2trg 5aoi 5bis 5bpi 5drv 5lag 5ppi
			5pps 5rmk 5veh 6cva 6cvh 6hru 6pda
			6pdr 6pvh 9aoi 9bis ) ) {

			$dbh->exec_sql( <<SQL, @some_vals ) ;
delete from "dbo"."al3_$al3_table" $where ;
SQL

		}
	}
}

sub truncate_table {

	my( $table ) = @_ ;

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

1 ;