#!/usr/bin/perl

use Env qw(
    HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
    MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
);
use Net::Google::Spreadsheets;
use Net::Google::DataAPI::Auth::OAuth2;
use Net::OAuth2::AccessToken;
use Storable;
use DBI;
use FindBin;
use lib "$FindBin::Bin/";
my $mig_bin = "$FindBin::Bin/";
use Mig;
use strict;
use Env qw(
    HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
    MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
    );
use Switch;
use Cwd 'abs_path';
use Pod::Usage;
use Data::Dumper;

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

Mig::die_if_no_env_migschema();
die_if_gsheet_tracked_table_does_not_exist();
die_if_gsheet_tracked_column_does_not_exist();

my $cmd_push;
my $next_arg_is_push;
my $cmd_pull;
my $next_arg_is_pull;
my @worksheet_names;
my $cmd_export = 0;
my @table_names;
my $sql;
my $sth;
my @ws;
my @tracked_ws_names;

foreach my $arg (@ARGV) {
    if ($arg eq '--push') {
        $next_arg_is_push = 1;
        next;
    }
    if ($next_arg_is_push) {
        $cmd_push = $arg;
        $next_arg_is_push = 0;
        next;
    }
    if ($arg eq '--pull') {
        $next_arg_is_pull = 1;
        next;
    }
    if ($next_arg_is_pull) {
        $cmd_pull = $arg;
        $next_arg_is_pull = 0;
        next;
    }
    if ($arg eq '--export') {
        $cmd_export = 1;
        next;
    }
}

abort('must specify --push or --pull') unless (defined $cmd_push or defined $cmd_pull);
if (defined $cmd_push and defined $cmd_pull) { abort('you can not specify both a --push and --pull on the same command'); }

my $dbh = Mig::db_connect();
my $spreadsheet = connect_gsheet();
abort('could not connect to google sheet') unless (defined $spreadsheet);

$sql = 'SELECT tab_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_table;';
$sth = $dbh->prepare($sql);
my $ra = $sth->execute();
while (my @row = $sth->fetchrow_array) {
    push @tracked_ws_names, $row[0];
}

if (defined $cmd_pull) {
    print "Pulling ";
    if ($cmd_pull eq 'all') {
        print "all worksheets.\n";
        @ws = $spreadsheet->worksheets;
        foreach my $wsn (@ws) { push @worksheet_names, $wsn->title; }
    } else {
        print "only worksheet $cmd_pull.\n";
        if (!defined $cmd_pull) { abort('command incomplete'); } 
        push @worksheet_names, $cmd_pull;
    }
    my @m = array_match(\@worksheet_names,\@tracked_ws_names);
    foreach my $w (@m) { 
        my $pull_ws = $spreadsheet->worksheet( {title => $w} ); 
        my $push_tb = get_table_name($MIGSCHEMA,$w,$dbh);
        my @rows = $pull_ws->rows;
        my @content;
        map { $content[$_->row - 1][$_->col - 1] = $_->content } $pull_ws->cells;
        #print Dumper($pull_ws->cells);
        #print Dumper(@content);
        my @tab_headers = shift @content;
        my $tab_headers_length = $#{ $tab_headers[0] };
        my @pg_headers;
        for my $i ( 0 .. $tab_headers_length ) {
            push @pg_headers, $tab_headers[0][$i];
        }
        shift @content;
        #todo: check for clean headers at some point ...
        truncate_table($MIGSCHEMA,$push_tb,$dbh);
        print "Inserting from $w to $push_tb.\n";
        for my $j (@content) {
            insert_row($MIGSCHEMA,$push_tb,$dbh,\@pg_headers,$j);
        }
        timestamp($MIGSCHEMA,$push_tb,$dbh,'pull');
    }
}

if (defined $cmd_push) {
    print "Pushing ";
    if ($cmd_push eq 'all') {
        print "all tables.\n";
        $sql = 'SELECT table_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_table';
        $sth = $dbh->prepare($sql);
        $ra = $sth->execute();
        while (my @row = $sth->fetchrow_array) {
            push @table_names, $row[0];
        }
    } else {
        print "only table $cmd_push.\n";
        if (!defined $cmd_push) { abort('command incomplete'); }
        push @table_names, $cmd_push;
    }
    foreach my $t (@table_names) {
        my $pull_tb = $MIGSCHEMA . "." . $t;;
        my @table_headers = get_pg_column_headers($t,$MIGSCHEMA);
        my $push_ws_name = get_worksheet_name($MIGSCHEMA,$t,$dbh);
        my $push_ws = $spreadsheet->worksheet( {title => $push_ws_name} );
        if (!defined $push_ws) { next; }
        my @rows;
        my $i = 0;
        foreach my $rth (@table_headers) { $rows[0][$i] = $rth; $i++; }         
        $sql = "SELECT * FROM $pull_tb;";
        $sth = $dbh->prepare($sql);
        $sth->execute();
        my $grabhash = $sth->fetchall_arrayref({});
        erase_sheet($push_ws,$push_ws_name);

        #get from postgres the headers to use in the sheet from tracked columns
        $sql = 'SELECT column_name FROM ' . $MIGSCHEMA . '.gsheet_tracked_column WHERE table_id = (SELECT id FROM ' . $MIGSCHEMA . '.gsheet_tracked_table WHERE table_name = \'' . $t . '\')';
        $sth = $dbh->prepare($sql);
        $sth->execute();
        my $sheet_headers = $sth->fetchall_arrayref();
        my $sheet_headers_length = @$sheet_headers;
        #now I need to do new rows using those headers
        my @content;
        foreach my $row ( @{$grabhash} ) {
            my $record = {};
            for my $column ( sort keys %{ $row } ) {
                print Dumper(@$sheet_headers);
                print "column: $column\n";
                my $clean_column = $column;
                $clean_column =~ s/_//g;
                print "clean column: $clean_column\n";
                if ( $column ~~ @$sheet_headers ) {
                    $record->{$clean_column} = $row->{$column}; 
                }
            }
            push @content, $record;
        }

        #print Dumper(@content); 
        foreach my $fillsheet (@content) {
            my $new_row = $push_ws->add_row (
                $fillsheet
            );
        }
        timestamp($MIGSCHEMA,$pull_tb,$dbh,'push');
    }
}   

sub die_if_gsheet_tracked_table_does_not_exist {
    if (!check_for_gsheet_tracked_table()) {
        die "Table $MIGSCHEMA.gsheet_tracked_table does not exist.  Bailing...\n";
    }
}

sub array_match {
    my ($xa,$xb) = @_;
    my @a = @{ $xa };
    my @b = @{ $xb };
    my @r;

    foreach my $av (@a) {
        foreach my $bv (@b) {
            if ($av eq $bv) { push @r, $bv; }
        }
    }    
    return @r;
}

sub get_pg_column_headers {
    my $table_name = shift;
    my $schema_name = shift;
    my @headers;
    my $dbh = Mig::db_connect();
    $sql = 'SELECT column_name FROM information_schema.columns WHERE table_schema = ' . $dbh->quote( $schema_name ) . ' AND table_name = ' . $dbh->quote( $table_name ) . ';';
    $sth = $dbh->prepare($sql);
    $ra = $sth->execute();
    while (my @row = $sth->fetchrow_array) {
        push @headers, $row[0];
    }
    return @headers;
}

sub erase_sheet {
    my $ws = shift;
    my $ws_name = shift;

    print "Erasing $ws_name.\n";
    my @rows = $ws->rows;
    my $i;
    my $j = @rows;
    $j = int(($j / 2))-1;
    if ($j < 2) { $j = 2; }
    while ($j > 0) {
        #bodge until I figure out why google sheets is only deleting even numbered rows
        $i = 1;
        foreach my $row (@rows) {
            if ($i != 1) { $row->delete; }
            $i++;
        };
        $j--;
    };
    return;
}

sub check_for_gsheet_tracked_table {
    my $dbh = Mig::db_connect();
    my $sth = $dbh->prepare("
        SELECT EXISTS(
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
            AND table_name = 'gsheet_tracked_table'
        );"
    );
    my $rv = $sth->execute()
        || die "Error checking for table (tracked_gsheet_table): $!";
    my @cols = $sth->fetchrow_array;
    $sth->finish;
    Mig::db_disconnect($dbh);
    return $cols[0];
}

sub die_if_gsheet_tracked_column_does_not_exist {
    if (!check_for_gsheet_tracked_column()) {
        die "Table $MIGSCHEMA.gsheet_tracked_column does not exist.  Bailing...\n";
    }
}

sub get_table_name {
    my $migs = shift;
    my $worksheet = shift;
    my $dbh = shift;

    my $sql = 'SELECT table_name FROM ' . $migs . '.gsheet_tracked_table WHERE tab_name = \'' . $worksheet . '\';';
    my $sth = $dbh->prepare($sql);
    my $xs = $sth->execute();
    my $table_name;
    while (my @row = $sth->fetchrow_array) {
        $table_name = $row[0];
    }

    return $table_name;
}

sub get_worksheet_name {
    my $migs = shift;
    my $table = shift;
    my $dbh = shift;

    my $sql = 'SELECT tab_name FROM ' . $migs . '.gsheet_tracked_table WHERE table_name = \'' . $table . '\';';
    my $sth = $dbh->prepare($sql);
    my $xs = $sth->execute();
    my $worksheet_name;
    while (my @row = $sth->fetchrow_array) {
        $worksheet_name = $row[0];
    }

    return $worksheet_name;
}


sub check_for_gsheet_tracked_column {
    my $dbh = Mig::db_connect();
    my $sth = $dbh->prepare("
        SELECT EXISTS(
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = " . $dbh->quote( $MIGSCHEMA ) . "
            AND table_name = 'gsheet_tracked_column'
        );"
    );
    my $rv = $sth->execute()
        || die "Error checking for table (gsheet_tracked_column): $!";
    my @cols = $sth->fetchrow_array;
    $sth->finish;
    Mig::db_disconnect($dbh);
    return $cols[0];
}

sub insert_row {
    my ($schema, $table, $dbh, $headers_ref, $row_ref) = @_;
    my @headers = @{ $headers_ref };
    my @row_data = @{ $row_ref };

    my $header_string = '(' . join(",", @headers) . ')';
    map {s/\'/\'\'/g; } @row_data;
    my $row_string = '(' . join(",", map {qq/'$_'/} @row_data) . ')';
    print "INSERT INTO $schema.$table $header_string VALUES $row_string\n"; 
    $dbh->do(qq/
        INSERT INTO $schema.$table $header_string VALUES $row_string ;
    /);
    
    print "Row pushed to $table in schema $schema.\n";
}

sub timestamp {
    my ($schema, $table, $dbh, $action) = @_;

    my $column;
    if ($action eq 'pull') { $column = 'last_pulled' }
        else { $column = 'last_pushed' }; 

    $dbh->do(qq/
        UPDATE $schema.gsheet_tracked_table SET $column = NOW() WHERE table_name = '$table';
    /);

}


sub truncate_table {
    my $schema = shift;
    my $table = shift;
    my $dbh = shift;

    $dbh->do(qq/
        TRUNCATE TABLE $schema.$table;;
    /);
    print "Table $schema.$table truncated.\n";
}

sub abort {
    my $msg = shift;
    print STDERR "$0: $msg", "\n";
    exit 1;
}

sub connect_gsheet {
    if (!defined $ENV{'CLIENTID'}) {
        exec '/bin/bash', '--init-file', '~/.mig/oauth.env';
        print "Open Authentication settings were not loaded, please re-run.\n";
    }
    my $session_filename = $ENV{SESSIONFILE};
    my $oauth2 = Net::Google::DataAPI::Auth::OAuth2->new(
        client_id => $ENV{CLIENTID},
        client_secret => $ENV{CLIENTSECRET},
        scope => ['http://spreadsheets.google.com/feeds/'],
        redirect_uri => 'https://developers.google.com/oauthplayground',
    );
    my $session = retrieve($session_filename);
    my $restored_token = Net::OAuth2::AccessToken->session_thaw(
        $session,
        auto_refresh => 1,
        profile => $oauth2->oauth2_webserver,
    );
    $oauth2->access_token($restored_token);
    my $service = Net::Google::Spreadsheets->new(auth => $oauth2);

    my $spreadsheet = $service->spreadsheet(
        {  
            title => $MIGSCHEMA
        }
    );
    return $spreadsheet;
}