package Common::DB::Diff;

use strict;
use warnings;

use JSON;
use Encode qw(encode decode);

use base 'Class::Accessor';

__PACKAGE__->mk_accessors(qw/columns database dbh dbhost pkcol pkcols pkmax table/);

use lib '/app/tools/common/lib';
use Common::MD5Sum;
use Common::Session;
use Common::DB::Diff::Rowset;

sub new {
    my $class    = shift;
    my $dbconn   = shift || die __PACKAGE__ . ' requires a db host to connect to';
    my $database = shift || die __PACKAGE__ . ' requires a database';
    my $table    = shift || die __PACKAGE__ . ' requires a table';
    my $self     = {
        'dbh'      => Common::Session::getdbh($dbconn),
        'dbhost'   => $dbconn,
        'database' => $database,
        'table'    => $table
    };
    bless $self, $class;
    $self->_init();
    $self;
}

sub _init {
    my $self = shift;

    my $dbh = $self->dbh;
    my $db  = $self->database;
    my $tab = $self->table;

    my $pkey = $self->_pk_exception( $self->table );
    if ( defined $pkey ) {
        $self->pkcol( $pkey->[0] );
        $self->pkcols( $pkey->[1] );
    } else {
        my $pksql  = "SELECT column_name FROM information_schema.statistics WHERE table_schema=? AND table_name=? AND index_name='PRIMARY'";
        my $pkcols = $dbh->selectcol_arrayref( $pksql, undef, ( $db, $tab ) );
        $self->pkcols($pkcols);
        $self->pkcol( $pkcols->[0] );
    }

    my $maxsql;
    if ( scalar @{ $self->pkcols } == 1 ) {
        my $pkcol = $self->pkcol;
        $maxsql = "SELECT IFNULL(MAX($pkcol),0) FROM $db.$tab";
    } else {
        $maxsql = "SELECT COUNT(*) FROM $db.$tab";
    }
    my $max = $dbh->selectcol_arrayref($maxsql);
    $self->pkmax( $max->[0] );

    my $cols  = "SELECT column_name FROM information_schema.columns WHERE table_schema=? AND table_name=? ORDER BY ordinal_position";
    my $names = $dbh->selectcol_arrayref( $cols, undef, ( $db, $tab ) );
    $self->columns($names);

}

sub getrowset {
    my $self  = shift;
    my $min   = shift;
    my $max   = shift;
    my $nosig = shift;

    ## parallel process will need to connect for every process
    ## so we'll need to pass a dbh to get_data()
    my $dbh = shift || $self->dbh;

    my $key = $self->pkcol;

    my $sql;
    if ($nosig) {
        $sql = $self->select_json( $min, $max, $self->database . "." . $self->table, $key );
    } else {
        $sql = $self->select_coalesce( $min, $max, $self->database . "." . $self->table, $key );
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute;

    my $rset;
    my $keys;
    if ( $sth->rows ) {
        my $rows;
        while ( my $row = $sth->fetchrow_arrayref ) {

            ## row data in first colum - keys follow
            my $tuple = $row->[0];
            my $keyvals;
            for ( my $i = 1 ; $i < scalar @{$row} ; $i++ ) {
                push @{$keyvals}, $row->[$i];
            }
            my $key = join '-', @{$keyvals};

            if ($nosig) {
                my $json_bytes = encode( 'UTF-8', $tuple );
                my $href       = JSON->new->utf8->decode($json_bytes);
                $tuple = $href;
            }

            $rows->{$key} = $tuple;

        }
        $rset = Common::DB::Diff::Rowset->new($rows);
        if ( not $rset->minkey ) {
            $rset->minkey($min);
            $rset->maxkey($max);
        }
    } else {
        $rset = Common::DB::Diff::Rowset->new( {} );
    }
    $rset;

}

sub select_json {
    my $self = shift;
    my $min  = shift;
    my $max  = shift;
    my $tab  = shift;
    my $key  = shift;

    my $json_objects;
    foreach my $col ( @{ $self->columns } ) {
        push @{$json_objects}, "'$col'";
        push @{$json_objects}, $col;
    }
    my $json_object = 'JSON_OBJECT(' . ( join ',', @{$json_objects} ) . ')';

    my $selects;
    foreach my $id ( @{ $self->pkcols } ) {
        push @{$selects}, $id;
    }
    my $select = join ',', @{$selects};

    "SELECT $json_object, $select FROM $tab WHERE $key >= $min AND $key < $max";
}

sub select_coalesce {
    my $self = shift;
    my $min  = shift;
    my $max  = shift;
    my $tab  = shift;
    my $key  = shift;

    my $coalesces;
    foreach my $col ( @{ $self->columns } ) {
        push @{$coalesces}, "COALESCE($col,'a')";
    }
    my $coalesce = 'SHA1(CONCAT(' . ( join ',', @{$coalesces} ) . '))';

    my $selects;
    foreach my $id ( @{ $self->pkcols } ) {
        push @{$selects}, $id;
    }
    my $select = join ',', @{$selects};

    "SELECT $coalesce, $select FROM $tab WHERE $key >= $min AND $key < $max";
}

## work around tables without primary keys
sub _pk_exception {
    my $self = shift;
    my $name = shift;
    my $pkey = {
        'artist_contract_term_reserve_run'     => [ 'artist_royalty_run_id', [qw/artist_royalty_run_id artist_contract_term_reserve_id/] ],
        'artist_royalty_album_balance_account' => [ 'album_id',              [qw/album_id artist_payee_id payor_id artist_contract_id/] ],
        'ca_license_reserve_run'               => [ 'ca_license_reserve_id', [qw/ca_license_reserve_id ca_mechanical_run_id /] ],
        'ca_mechanical_reserve_pipeline' => [ 'track_id', [qw/upc track_id region_id ca_publisher_id ca_mechanical_run_id album_id/] ],
        'ca_publisher_indirect_balance_account'  => [ 'payor_id', [qw/payor_id ca_publisher_id indirect_publisher_id finance_account_id/] ],
        'catalog_import_region_included_country' => [ 'catalog_import_region_id', [qw/catalog_import_region_id country_id/] ],
        'contract_level_license_income_balance_account' => [ 'artist_payee_id',    [qw/artist_payee_id payor_id finance_account_id /] ],
        'input_conversion_rate'                         => [ 'file_id',            [qw/file_id currency/] ],
        'license_reserve_run'                           => [ 'license_reserve_id', [qw/license_reserve_id mechanical_run_id /] ],
        'mechanical_reserve_pipeline'        => [ 'track_id',       [qw/upc track_id region_id publisher_id mechanical_run_id album_id/] ],
        'product_digital'                    => [ 'product_id',     [qw/product_id/] ],
        'publisher_indirect_balance_account' => [ 'payor_id',       [qw/payor_id publisher_id indirect_publisher_id finance_account_id/] ],
        'region_country_map'                 => [ 'region_id',      [qw/region_id country_code/] ],
        'region_excluded_country'            => [ 'region_id',      [qw/region_id country_id/] ],
        'region_excluded_country'            => [ 'region_id',      [qw/region_id country_id/] ],
        'bonus_track'                        => [ 'bonus_track_id', [qw/product_id track_id service_id/] ],
        'format'                        => [ 'format_type', [qw/format_type format_name/] ],
        'mechanical_run_track_license'       =>
          [ 'run_id', [qw/run_id run_type track_license_id product_id track_id base_rate country_code year/] ],

    };
    $pkey->{$name};
}

1;