#!/usr/local/bin/perl -w
use strict;

use lib $ENV{LIBS};

use DBExtensions;
use File::Spec;
use LogHandles;
use Utilities::Data qw(deffed);

use constant SUMMARY_RECIPIENTS => qw(foo@bar.com bar@foo.com);

use constant LOOKUP_SP     => 'instore..tlog_detail_lookup_sp';
use constant LOOKUP_TITLE  => ' Record Hit Count TLog Id  Order Id Line Number List Price         Title';
use constant LOOKUP_TITLE2 => ' ====== ========= ======= ========= =========== ========== =====================';
use constant LOOKUP_FORMAT => ' %6d %9d %7d %9d %11s %10.2f %21s';

use constant MMDDYY => sprintf '%02d' x 3, map { $_ % 100 } (split /-/, InstanceData::START_DATE)[1,2,0];

use constant FEEDBACK_SP      => 'instore..feedback_sp';
use constant FEEDBACK_TABLE   => 'instore..feedback';
use constant FEEDBACK_COLUMNS => qw(store_id transaction_id register_id transaction_date line_number magazine_code
                                    magazine_name entry_date batch_date site_id order_id customer_id);
use constant FEEDBACK_DIR     => File::Spec->catdir($ENV{FTP_HOME}, 'BN_instore', 'feedback');
use constant FEEDBACK_TEMPL   => File::Spec->catfile(FEEDBACK_DIR, 'bn_subscriptions%s.txt');
use constant FEEDBACK_FILE    => sprintf FEEDBACK_TEMPL, MMDDYY;
use constant FEEDBACK_LINK    => sprintf FEEDBACK_TEMPL, '';
use constant FEEDBACK_FORMAT  => "%05d%06d%03d%8s%03d%03d%-18s%8s%8s%012d%012d%012d@{[' ' x 20]}\r\n";
use constant FEEDBACK_GLOB    => File::Spec->catfile(FEEDBACK_DIR, '*.don');

use constant REFUND_SP      => 'instore..refund_sp';
use constant REFUND_TABLE   => 'instore..westbury_refunds';
use constant REFUND_COLUMNS => (qw(store_id register_id transaction_id trans_order_date order_id line_number
                                   customer_id), 'convert(varchar, order_date, 112)', '100 * amount',
                                qw(method item_id quantity first_name last_name
                                   address_line_1 address_line_2 city state zip_code),
                                '100 * retail_amount', qw(email));
use constant REFUND_DIR     => File::Spec->catdir($ENV{FTP_HOME}, 'BN_instore', 'refund');
use constant REFUND_TEMPL   => File::Spec->catfile(REFUND_DIR, 'bn_return_subs%s.txt');
use constant REFUND_FILE    => sprintf REFUND_TEMPL, MMDDYY;
use constant REFUND_LINK    => sprintf REFUND_TEMPL, '';
use constant REFUND_FORMAT  =>
    "I%05d%03d%08d%8s%010d%03d%010d%8s%010d%1s%-15sMO%03d%-25s%-25s%-30s%-30s%-20s%-2s%-10s@{[' ' x 15]}%010d%-50s@{[' ' x 46]}\r\n";
use constant REFUND_GLOB    => File::Spec->catfile(REFUND_DIR, '*.don');

my $Database = DBExtensions::login($ENV{BE_CONNECT});

LogHandles::redirect_summary(join ',' => SUMMARY_RECIPIENTS);

&lookup_details && &report_feedback && &report_refund;

sub lookup_details {

    my $query = $Database->ct_sql("EXECUTE @{[LOOKUP_SP]}");
    # result will be the last row, 0 is good
    my $result = pop @$query;
    if ($result->[0] == 0) {
        $SUMMARY_LOG->print("Stored procedure @{[LOOKUP_SP]} completed successfully.");
        $SUMMARY_LOG->print("");
        my $update_q1 = shift @$query;
        $SUMMARY_LOG->printf("Order IDs to Update Q1: %5d", $update_q1->[0]);
        my $update_q2 = shift @$query;
        $SUMMARY_LOG->printf("Order IDs to Update Q2: %5d", $update_q2->[0]);
        $SUMMARY_LOG->print("");
        $SUMMARY_LOG->print(LOOKUP_TITLE);
        $SUMMARY_LOG->print(LOOKUP_TITLE2);
        $SUMMARY_LOG->print("");
        while (defined (my $hitref = shift @$query)) {
            my $hit_count = $hitref->[0];
            my ($record_count, $tlog_id, $order_id, $list_price, $line_number, $title) = @{shift @$query};
            if ($hit_count) {
                $SUMMARY_LOG->printf(LOOKUP_FORMAT, $record_count, $hit_count, $tlog_id, $order_id, $line_number,
                                     $list_price, $title);
            } else {
                $SUMMARY_LOG->printf(LOOKUP_FORMAT, $record_count, $hit_count, $tlog_id, $order_id, '',
                                     $list_price, '');
            }
        }
        1;
    } else {
        $SUMMARY_LOG->print("Stored procedure @{[LOOKUP_SP]} completed unsuccessfully, exit status = $result->[0]");
        undef;
    }
    $SUMMARY_LOG->print("");
}

sub report_gen {
    my $report = shift;
    return sub {
        no strict 'refs';
        my ($stored_procedure) = &{"${report}_SP"};
        my ($table)            = &{"${report}_TABLE"};
        my ($columns_ref)      = [&{"${report}_COLUMNS"}];
        my ($output_file)      = &{"${report}_FILE"};
        my ($output_link)      = &{"${report}_LINK"};
        my ($output_format)    = &{"${report}_FORMAT"};
        my ($done_glob)        = &{"${report}_GLOB"};
        use strict 'refs';
                                
        my $query = $Database->ct_sql("EXECUTE $stored_procedure", undef, 1); # fetch as hashes
        my $result = pop @$query;
        if ((my $exit_status = (values %$result)[0]) == 0) {
            $SUMMARY_LOG->print("Stored procedure $stored_procedure completed successfully.");
            $SUMMARY_LOG->print("");
            foreach my $row (@$query) {
                while (my ($key, $value) = each %$row) {
                    $key =~ s/_/ /g;
                    $SUMMARY_LOG->print("$key: $value");
                }
            }
            $SUMMARY_LOG->print("");
            open REPORT, ">>$output_file"
                or do { $ERROR_LOG->print("Unable to open $output_file");
                        return;
                    };
            my $rows =
                $Database->ct_sql("SELECT @{[join ',' => @$columns_ref]}
                                   FROM $table
                                   WHERE sent_date IS NULL");
            my $count = 0;
            foreach my $row (@$rows) {
                printf REPORT $output_format, deffed @$row;
                ++$count;
            }
            close REPORT;
            link $output_file, $output_link
                or $ERROR_LOG->print("Unable to create link $output_link");
            $SUMMARY_LOG->print("$count lines written to $output_file");
            $Database->ct_sql("UPDATE $table SET sent_date = getdate() WHERE sent_date IS NULL");            
        } else {
            $SUMMARY_LOG->print("Stored procedure $stored_procedure completed unsuccessfully, exit status = $exit_status");
        }
        $SUMMARY_LOG->print("");
        my $files_deleted = unlink glob $done_glob;
        {
            my $s = $files_deleted == 1 ? '' : 's';
            $SUMMARY_LOG->print("Deleted $files_deleted file$s matching $done_glob");
            $SUMMARY_LOG->print("");
        }
        1;
    }
}

BEGIN {
    *report_feedback = report_gen('FEEDBACK');
    *report_refund   = report_gen('REFUND');
}
