use strict ;
use warnings ;

use Data::Dumper ;
use Spreadsheet::XLSX;
use DateTime::Format::Excel ;
use File::Slurp ;

use Safelib::Dbutils ;

my @tab_descs ;
my $dbh ;

my $excel_file = shift @ARGV or die 'missing excel file argument' ;

my $tab_info = preprocess_tab_descs() ;

#print Dumper $tab_info ;
#exit ;

my $tab_rows = get_tab_data( $tab_info, $excel_file ) ;

#print Dumper $tab_rows ;

output_tab_files( $tab_info, $tab_rows ) ;

exit ;

sub get_tab_data {

	my( $tab_info, $excel_file ) = @_ ;

	my $excel = Spreadsheet::XLSX->new( $excel_file );

	my $cells = $excel->{Worksheet}[0]{Cells} ;

	my $tab_xl_num	= $tab_info->{tab_xl_num} ;

#print Dumper $cells ;
#exit ;

# skip the first excel row which is just column names

	shift @{$cells} ;

	my @tab_rows ;

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

		my %tab_row ;

		while( my( $tab_name, $xl_col ) = each %{$tab_xl_num} ) {

			$tab_row{$tab_name} = $row->[$xl_col]{Val} ;
		}

# get the pif data and add it to the tab row data

		get_pif_data( $tab_info, \%tab_row,  ) ;

		push @tab_rows, \%tab_row ;
 	}

#print Dumper \@tab_rows ;

	return \@tab_rows ;
}

sub get_pif_data {

	my( $tab_info, $tab_row ) = @_ ;

	my $pif_sql = $tab_info->{pif_sql} ;

	$dbh ||= Safelib::Dbutils->connect( $ENV{TAB_DB} || 'LocalServer' ) or
	 	die "Connect failed: $DBI::errstr" ;

	my $master_id = get_pif_master_id( $tab_row ) ;

	while( my( $pif_rec, $sql ) = each %{$pif_sql} ) {

# get the rows from this pif table

		my $pif_recs = $dbh->exec_select( $sql, $master_id ) ;

# we only use the first row (can we have multiple rows from the db?)

		my $rec = $pif_recs->[0] ;

# copy all the values to the 

		@{$tab_row}{ keys %{$rec} } = values %{$rec} ;
	}
}

sub get_pif_master_id {

	my( $tab_row ) = @_ ;

	my $excel_date = $tab_row->{POLICY_EFFECTIVE_DATE} ;

	my $datetime = DateTime::Format::Excel->parse_datetime( $excel_date );
	my $eff_date = $datetime->ymd('') ;

	my $policy_num = "$tab_row->{POLICY_SYMBOL}$tab_row->{POLICY_NUMBER}" ;

	my $sql = <<SQL ;
SELECT id
FROM pif_master
WHERE
	policy_num = ? AND
	eff_date = ?
SQL

	my $master_row = $dbh->exec_select( $sql, $policy_num, $eff_date ) ;

	return $master_row->[0]{id} ;
}

sub output_tab_files {

	my( $tab_info, $tab_rows ) = @_ ;

	my @tab_names = @{$tab_info->{tab_names}} ;
	my $defaults = $tab_info->{defaults} ;

	my $tab_header = join( "\t", map qq{"$_"}, @tab_names ) ;

	my %tab_texts ;

	foreach my $tab_row ( @{$tab_rows} ) {

		foreach my $tab_name ( @tab_names ) {

# set any defaults or null string and strip surrounding blanks

			$tab_row->{$tab_name} //= $defaults->{$tab_name} ;
			s/^\s+//, s/\s+$// for $tab_row->{$tab_name} ;
		}

		my $tab_line = join "\t", map qq{"$_"},
			@{$tab_row}{@tab_names} ;

		my $cat_firm = $tab_row->{CAT_FIRM} ;

# put in a tab column header line if we haven't seen this cat firm before

		$tab_texts{$cat_firm} ||= "$tab_header\n" ;

# add in the data row

		$tab_texts{$cat_firm} .= "$tab_line\n" ;
	}

	while( my( $cat_firm, $tab_text ) = each %tab_texts ) {

		$cat_firm =~ tr/ /_/ ;

		write_file( "$cat_firm.tsv", $tab_text ) ;
	}
}

sub preprocess_tab_descs {

# these will all get stuffed with munged data from the field specs

	my @tab_names ;		# list of tab field names
	my %defaults ;		# default value for each field
	my %tab_xl_num ;	# field name to excel column number
	my %tab_pif_map ;	# field name to pif field name

# map of default types to values. only needed one time so it can
# reside in this sub.

	my %default_vals = (

		date	=> '0001-01-01',
		zero	=> '0',
	) ;

# loop over all the tab descriptions to process and sort them.

	foreach my $desc ( @tab_descs ) {

# let's look at this tab field descriptor

		my( $tab_name, $rec_type, $col, $default_type ) = @{$desc} ;

# we keep all the names in sequence for printing the tab row

		push( @tab_names, $tab_name ) ;

# get the default value for this field or it is the null string

		$defaults{ $tab_name } = $default_type ?
			$default_vals{$default_type} : '' ;

# nothing more to do if this is just a placeholder (to be filled in manually)

		next unless $rec_type ;

# if an excel field, save the column number

		if ( $rec_type eq 'XL' ) {

			$tab_xl_num{ $tab_name } = $col ;
			next ;
		}

# must be a PIF field, save this field desc with others for this record type

		push( @{$tab_pif_map{$rec_type}}, $desc ) ;
	}

	my $pif_sql = generate_pif_sql( \%tab_pif_map ) ;

	return {
		tab_names	=> \@tab_names,
		tab_xl_num	=> \%tab_xl_num,
		pif_sql		=> $pif_sql,
		defaults	=> \%defaults,
	} ;
}

sub generate_pif_sql {

	my( $tab_pif_map ) = @_ ;

	my %pif_sql ;

	while( my( $pif_rec, $maps ) = each %{$tab_pif_map} ) {

		my $select = join ",\n\t", map "$_->[2] as $_->[0]", @{$maps} ;

		my $sql = <<SQL ;
SELECT
	$select
FROM "dbo"."pif_$pif_rec"
WHERE master_id = ?	
SQL

		$pif_sql{ $pif_rec } = $sql ;
	}

	return \%pif_sql ;
}


# these describe how to create a row of the tabbed file
# each entry can extract data from the excel file or pif db tables.
# first item is the tab field name.
# second item is the optional record type of the source (pif record or XL)
# third item is the pif field name or excel column number
# fourth item is an optional default type

BEGIN { 
@tab_descs = (

	[ qw( CAT_CODE ) ],
	[ qw( CLAIM_NUMBER		XL	0 ) ],
	[ qw( POLICY_SYMBOL		XL	1 ) ],
	[ qw( POLICY_NUMBER		XL	2 ) ],
	[ qw( POLICY_EFFECTIVE_DATE	XL	3	date ) ],
	[ qw( SEVERITY_CODE		XL	4 ) ],
	[ qw( TYPE_CODE			XL	5 ) ],
	[ qw( SAFETY_DESK_ADJUSTER	XL	8 ) ],
	[ qw( XACTID ) ],
	[ qw( CAT_FIRM			XL	9 ) ],
	[ qw( CLAIMANT_NAME		XL	11 ) ],
	[ qw( CLIENT_PHONE		XL	12 ) ],
	[ qw( CLIENT_PHONE_1 ) ],
	[ qw( CLIENT_PHONE_2 ) ],
	[ qw( CLIENT_PHONE_3 ) ],
	[ qw( MAILING_STREET		XL	13 ) ],
	[ qw( MAILING_CITY		XL	14 ) ],
	[ qw( MAILING_STATE		XL	15 ) ],
	[ qw( MAILING_POSTAL_CODE	XL	16 ) ],
	[ qw( REPORTED_DATE		XL	17	date ) ],
	[ qw( LOSS_DATE			XL	18	date ) ],
	[ qw( LOSS_DESCRIPTION		XL	19 ) ],
	[ qw( FIRST_NAMED_INSURED	XL	14 ) ],
	# [ qw( EMAIL			02	foo ) ],
	# [ qw( PROPERTY_STREET		02	foo ) ],
	# [ qw( PROPERTY_CITY		02	foo ) ],
	# [ qw( PROPERTY_STATE		02	foo ) ],
	[ qw( PROPERTY_POSTAL_CODE	17	zip_code ) ],
	[ qw( ALL_PERIL_DEDUCTIBLE	17	deductible_amount    zero ) ],
	[ qw( HURRICANE_DEDUCTIBLE	17	wind_hail_exclusion ) ],
	[ qw( COVERAGE_A		17	coverage_a_limit     zero ) ],
	[ qw( COVERAGE_B		17	coverage_b_limit     zero ) ],
	[ qw( COVERAGE_C		17	coverage_c_limit     zero ) ],
	[ qw( COVERAGE_D		17	coverage_d_limit     zero ) ],
	[ qw( COVERAGE_E		17	coverage_e_limit     zero ) ],
	[ qw( COVERAGE_F		17	coverage_f_limit     zero ) ],
	[ qw( ENDORSEMENT_1		37	entry_01 ) ],
	[ qw( ENDORSEMENT_2		37	entry_02 ) ],
	[ qw( ENDORSEMENT_3		37	entry_03 ) ],
	[ qw( ENDORSEMENT_4		37	entry_04 ) ],
	[ qw( ENDORSEMENT_5		37	entry_05 ) ],
	[ qw( ENDORSEMENT_6		37	entry_06 ) ],
	[ qw( ENDORSEMENT_7		37	entry_07 ) ],
	[ qw( ENDORSEMENT_8		37	entry_08 ) ],
	[ qw( ENDORSEMENT_9		37	entry_09 ) ],
	[ qw( ENDORSEMENT_10		37	entry_10 ) ],
	[ qw( ENDORSEMENT_11		37	entry_11 ) ],
	[ qw( ENDORSEMENT_12		37	entry_12 ) ],
	[ qw( ENDORSEMENT_13		37	entry_13 ) ],
	[ qw( ENDORSEMENT_14		37	entry_14 ) ],
	[ qw( ENDORSEMENT_15		37	entry_15 ) ],
	[ qw( ENDORSEMENT_16		37	entry_16 ) ],
	[ qw( ENDORSEMENT_17		37	entry_17 ) ],
	[ qw( ENDORSEMENT_18		37	entry_18 ) ],
	[ qw( ENDORSEMENT_19		37	entry_19 ) ],
	[ qw( ENDORSEMENT_20		37	entry_20 ) ],
	[ qw( ENDORSEMENT_21		37	entry_21 ) ],
	[ qw( ENDORSEMENT_22		37	entry_22 ) ],
	[ qw( ENDORSEMENT_23 ) ],
	# [ qw( ENDORSEMENT_24 ) ],
	# [ qw( ENDORSEMENT_25 ) ],
	# [ qw( ENDORSEMENT_26 ) ],
	# [ qw( ENDORSEMENT_27 ) ],
	# [ qw( ENDORSEMENT_28 ) ],
	# [ qw( ENDORSEMENT_29 ) ],
	# [ qw( ENDORSEMENT_30 ) ],
	# [ qw( ENDORSEMENT_31 ) ],
	# [ qw( ENDORSEMENT_32 ) ],
	# [ qw( ENDORSEMENT_33 ) ],
	# [ qw( ENDORSEMENT_34 ) ],
	# [ qw( ENDORSEMENT_35 ) ],
	# [ qw( ENDORSEMENT_36 ) ],
	# [ qw( ENDORSEMENT_37 ) ],
	# [ qw( ENDORSEMENT_38 ) ],
	# [ qw( ENDORSEMENT_39 ) ],
	# [ qw( ENDORSEMENT_40 ) ],
	# [ qw( ENDORSEMENT_41 ) ],
	# [ qw( ENDORSEMENT_42 ) ],
	# [ qw( ENDORSEMENT_43 ) ],
	# [ qw( ENDORSEMENT_44 ) ],
	# [ qw( ENDORSEMENT_45 ) ],
	# [ qw( ENDORSEMENT_46 ) ],
	# [ qw( ENDORSEMENT_47 ) ],
	# [ qw( ENDORSEMENT_48 ) ],
	# [ qw( ENDORSEMENT_49 ) ],
	# [ qw( ENDORSEMENT_50 ) ],
	# [ qw( ENDORSEMENT_51 ) ],
	# [ qw( ENDORSEMENT_52 ) ],
	# [ qw( ENDORSEMENT_53 ) ],
	# [ qw( ENDORSEMENT_54 ) ],
	# [ qw( ENDORSEMENT_55 ) ],
	# [ qw( ENDORSEMENT_56 ) ],
	# [ qw( ENDORSEMENT_57 ) ],
	# [ qw( ENDORSEMENT_58 ) ],
	# [ qw( ENDORSEMENT_59 ) ],
	# [ qw( ENDORSEMENT_60 ) ],
	# [ qw( ENDORSEMENT_61 ) ],
	# [ qw( ENDORSEMENT_62 ) ],
	# [ qw( ENDORSEMENT_63 ) ],
	# [ qw( ENDORSEMENT_64 ) ],
	# [ qw( ENDORSEMENT_65 ) ],
	# [ qw( ENDORSEMENT_66 ) ],
	# [ qw( ENDORSEMENT_67 ) ],
	# [ qw( ENDORSEMENT_68 ) ],
	# [ qw( ENDORSEMENT_69 ) ],
	# [ qw( ENDORSEMENT_70 ) ],
	# [ qw( ENDORSEMENT_71 ) ],
	# [ qw( ENDORSEMENT_72 ) ],
	# [ qw( ENDORSEMENT_73 ) ],
	# [ qw( ENDORSEMENT_74 ) ],
	# [ qw( ENDORSEMENT_75 ) ],
	# [ qw( ENDORSEMENT_76 ) ],
	# [ qw( ENDORSEMENT_77 ) ],
	# [ qw( ENDORSEMENT_78 ) ],
	# [ qw( ENDORSEMENT_79 ) ],
	# [ qw( ENDORSEMENT_80 ) ],
) ;
}