#!/usr/bin/perl -w
###############################################################################
=pod

=head1 NAME

mig-quicksheet 

Quickly produces a simple Excel spreadsheet based on the tracked file suitable
for simple end-user mapping.  The new file is named after the tracked file, but
ends in .mapping.xls

=head1 SYNOPSIS

B<mig-quicksheet> <file>

=cut

###############################################################################

use strict;
use Switch;
use Env qw(
    HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
    MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
);
use Pod::Usage;
use DBI;
use Spreadsheet::WriteExcel;
use Cwd 'abs_path';
use FindBin;
my $mig_bin = "$FindBin::Bin/";
use lib "$FindBin::Bin/";
use Mig;

pod2usage(-verbose => 2) if ! $ARGV[0] || $ARGV[0] eq '--help';

Mig::die_if_no_env_migschema();
Mig::die_if_mig_tracking_table_does_not_exist();

my $has_x_source = 0;
my $file = abs_path($ARGV[0]);
my $fdata;
my $tracked_file_id = Mig::check_for_tracked_file($file);
if ($tracked_file_id) {
    $fdata = Mig::status_this_file($file);
} else {
    die "File not currently tracked: $file\n";
}

my $table = $fdata->{staged_table};
if (!$table) {
    die "No staged staged table for file: $file\n";
}

my $workbook = Spreadsheet::WriteExcel->new($file . '.mapping.xls');
my @worksheets = ();
print "Writing $file.mapping.xls\n";
print "Sheet: Field Summary\n";
my $first_sheet = $workbook->add_worksheet('Field Summary');
my $bold = $workbook->add_format();
$bold->set_bold();
$bold->set_align('left');
my $left = $workbook->add_format();
$left->set_align('left');
$first_sheet->set_column(0,6,30);

my $dbh = Mig::db_connect();
my $sth = $dbh->prepare("
    SELECT COUNT(*)
    FROM $MIGSCHEMA.$table
    LIMIT 1;
");
my $rv = $sth->execute()
    || die "Error retrieving data from information_schema: $!";

my @cols = $sth->fetchrow_array;
$sth->finish;
my $count = $cols[0];
Mig::db_disconnect($dbh);

$first_sheet->write(0,0,'Source File:',$bold);
$first_sheet->write(0,1,$file,$left);
$first_sheet->write(1,0,'Number of Rows:',$bold);
$first_sheet->write(1,1,$count,$left);

my $sheet_row_start = 4;

$first_sheet->write($sheet_row_start,0,'Legacy Column',$bold);
$first_sheet->write($sheet_row_start,1,'Non-Empty Rows',$bold);
$first_sheet->write($sheet_row_start,2,'Distinct Non-NULL Values',$bold);
$first_sheet->write($sheet_row_start,3,'Min Value',$bold);
$first_sheet->write($sheet_row_start,4,'Min Length',$bold);
$first_sheet->write($sheet_row_start,5,'Max Value',$bold);
$first_sheet->write($sheet_row_start,6,'Max Length',$bold);

handle_columns();

$workbook->close();

exit 0;

###############################################################################

sub handle_columns {
    my $dbh = Mig::db_connect();
    my $sth = $dbh->prepare("
        SELECT *
        FROM information_schema.columns
        WHERE table_schema = " . $dbh->quote($MIGSCHEMA) . "
        AND table_name = " . $dbh->quote($table) . "
        ORDER BY dtd_identifier::INTEGER ASC;
    ");
    my $rv = $sth->execute()
        || die "Error retrieving data from information_schema: $!";

    my $sheet_row_offset = 0;

    while (my $data = $sth->fetchrow_hashref) {
        my $column = $data->{column_name};
        if ($column eq 'x_source') {
            $has_x_source = 1;
        }
        if ($column =~ /^l_/
            || ($column =~ /^x_/
                && (   $column ne 'x_migrate'
                    && $column ne 'x_source'
                    && $column ne 'x_egid'
                    && $column ne 'x_hseq'
                )
            )
        ) {
            $sheet_row_offset++;
            my $cdata = column_summary($column);
            $first_sheet->write($sheet_row_start + $sheet_row_offset,0,$column,$left);
            $first_sheet->write($sheet_row_start + $sheet_row_offset,1,$cdata->{non_empty_count},$left);
            $first_sheet->write($sheet_row_start + $sheet_row_offset,2,$cdata->{distinct_value_count},$left);
            $first_sheet->write($sheet_row_start + $sheet_row_offset,3,$cdata->{min_value},$left);
            $first_sheet->write($sheet_row_start + $sheet_row_offset,4,$cdata->{min_length},$left);
            $first_sheet->write($sheet_row_start + $sheet_row_offset,5,$cdata->{max_value},$left);
            $first_sheet->write($sheet_row_start + $sheet_row_offset,6,$cdata->{max_length},$left);
            if ($cdata->{distinct_value_count} > 1 && $cdata->{distinct_value_count} <= 500) {
                group_by($column);
            }
        }
    }
    $sth->finish;
    Mig::db_disconnect($dbh);
}

sub column_summary {

    my $column = shift;

    my $dbh = Mig::db_connect();

    ### non_empty_count
    my $sth = $dbh->prepare("
        SELECT COUNT(*)
        FROM $MIGSCHEMA.$table
        WHERE $column IS NOT NULL AND BTRIM($column) <> '';
    ");
    my $rv = $sth->execute()
        || die "Error retrieving data from $MIGSCHEMA.$table: $!";
    my @cols = $sth->fetchrow_array;
    $sth->finish;
    my $non_empty_count = $cols[0];
 
    ### distinct_value_count
    $sth = $dbh->prepare("
        SELECT COUNT(DISTINCT $column)
        FROM $MIGSCHEMA.$table;
    ");
    $rv = $sth->execute()
        || die "Error retrieving data from $MIGSCHEMA.$table: $!";
    @cols = $sth->fetchrow_array;
    $sth->finish;
    my $distinct_value_count = $cols[0];

    ### min_value
    $sth = $dbh->prepare("
        SELECT MIN($column)
        FROM $MIGSCHEMA.$table;
    ");
    $rv = $sth->execute()
        || die "Error retrieving data from $MIGSCHEMA.$table: $!";
    @cols = $sth->fetchrow_array;
    $sth->finish;
    my $min_value = $cols[0];

    ### min_length
    $sth = $dbh->prepare("
        SELECT MIN(LENGTH($column))
        FROM $MIGSCHEMA.$table;
    ");
    $rv = $sth->execute()
        || die "Error retrieving data from $MIGSCHEMA.$table: $!";
    @cols = $sth->fetchrow_array;
    $sth->finish;
    my $min_length = $cols[0];

    ### max_value
    $sth = $dbh->prepare("
        SELECT MAX($column)
        FROM $MIGSCHEMA.$table;
    ");
    $rv = $sth->execute()
        || die "Error retrieving data from $MIGSCHEMA.$table: $!";
    @cols = $sth->fetchrow_array;
    $sth->finish;
    my $max_value = $cols[0];

    ### max_length
    $sth = $dbh->prepare("
        SELECT MAX(LENGTH($column))
        FROM $MIGSCHEMA.$table;
    ");
    $rv = $sth->execute()
        || die "Error retrieving data from $MIGSCHEMA.$table: $!";
    @cols = $sth->fetchrow_array;
    $sth->finish;
    my $max_length = $cols[0];

    return {
         non_empty_count        => $non_empty_count
        ,distinct_value_count   => $distinct_value_count
        ,min_value              => defined $min_value ? $min_value : '<NULL>'
        ,min_length             => defined $min_length ? $min_length : '<NULL>'
        ,max_value              => defined $max_value ? $max_value : '<NULL>'
        ,max_length             => defined $max_length ? $max_length : '<NULL>'
    };
}

sub group_by {
    my ($column,$option) = (shift,"GROUP BY 2 ORDER BY 2");

    print "Sheet: $column\n";
    my $col_sheet = $workbook->add_worksheet(substr($column,0,31));
    push @worksheets, $col_sheet;
    $col_sheet->set_column(0,6,30);

    my $col_sheet_row_start = 0;
    my $col_sheet_row_offset = 0;

    $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,'Count',$bold);
    if ($has_x_source) {
        $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,'Source',$bold);
        $option = "GROUP BY 2,3 ORDER BY 2,3";
    }
    $col_sheet->write(
        $col_sheet_row_start + $col_sheet_row_offset
        ,$has_x_source ? 2 : 1
        ,"Legacy Value for $column"
        ,$bold
    );

    my $dbh = Mig::db_connect();
    my $sth;
    my $rv;

    $sth = $dbh->prepare("
        SELECT COUNT(*), " . ($has_x_source ? 'x_source, ' : '') . "$column
        FROM $MIGSCHEMA.$table
        $option;
    ");
    $rv = $sth->execute()
        || die "Error retrieving data from $MIGSCHEMA.$table: $!";

    while (my @cols = $sth->fetchrow_array) {
        $col_sheet_row_offset++;
        my $count = $cols[0];
        $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,$count,$left);
        my $value;
        if ($has_x_source) {
            my $source = defined $cols[1] ? $cols[1] : '<NULL>';
            $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$source,$left);
            $value = defined $cols[2] ? $cols[2] : '<NULL>';
            $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,2,$value,$left);
        } else {
            $value = defined $cols[1] ? $cols[1] : '<NULL>';
            $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$value,$left);
        }
    }
    $sth->finish;
}