## Please see file perltidy.ERR
#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

use Git;
use JSON::XS;
use DateTime;
use FileHandle;
use Array::Utils qw(:all);
use Digest::MD5  qw(md5 md5_hex md5_base64);
use Text::CSV::Easy(qw/csv_build csv_parse/);
use Getopt::Long::Descriptive(qw/describe_options/);

use lib '/var/app/orchard/collab/jgetter/perllib';
use Orchard::Session;
use Orchard::NameCase;
use Orchard::DB::Connect;
use Orchard::Accounting::JIRA;
use Orchard::ETL::Extract::CSV;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'help|?|h'     => "print usage message and exit" ],
    [ 'contract|c=i' => 'Contract ID to test',    { 'required' => 1 } ],
    [ 'neo|n=s'      => 'Export Neo4j default=1', { 'default'  => 1 } ],
);

if ( $opt->help ) {
    logMessage( 'info', $usage->text, 'options' );
    exit 1;
}

## contract id
my $cid = $opt->contract;

## dbuser
my $dbuser = 'jgtest';

## db connections
my $dbx = Orchard::Session::getdbx('abacusqa');
my $dbh = Orchard::Session::getdbh('abacusqa');

my $sth = $dbh->prepare(
    qq{
SELECT
	c.contract_id,
	c.contract_name,
	c.term_start,
	c.term_end,
	ct.contract_term_id,
	ct.is_base_term,
	ct.contract_term_name,
	ct.term_type,
	ct.deleted_at,
	ct.deleted_by,
	ct.attachments_relations,
	ct.attachments,
	md5(ct.attachments) md5sum
FROM
	contract c
	JOIN contract_term ct ON ct.contract_id = c.contract_id
WHERE ct.contract_id=?
}
);
$sth->execute($cid);
my $seen  = {};
my $dupes = {};

if ( $sth->rows ) {
    my $terms;
    my $c_only;
    while ( my $row = $sth->fetchrow_hashref() ) {
        my $contributor   = JSON::XS::decode_json( $row->{'attachments_relations'} );
        my $contributions = JSON::XS::decode_json( $row->{'attachments'} );

        my $ctid = $row->{'contract_term_id'};
        my $cnid = $contributor->{'contributor_ids'}->[0];

        ## schedule name
        my $schedule = $row->{'contract_term_name'};
        $schedule =~ s/[^a-zA-Z0-9,\s]//g;    # clean any special chars
        $schedule =~ s/NRS\d+?\s//g;          # clean SAX IDs
        $schedule =~ s/^\s+|\s+$//g;          # trim(name)
        $schedule =~ s/  */ /g;               # trim double spaces

        if ( defined $contributions and scalar @{$contributions} > 1 ) {
            map { exists $seen->{$_} ? $dupes->{$_}++ : $seen->{$_}++ } @{$contributions};
            $terms->{$ctid} = {
                'schedule'    => $schedule,
                'attachments' => $contributions,
                'contributor' => $cnid
            };
        }

        ##if ( $row->{'is_base_term'} > 0 ) {
            ##push @{ $c_only->{$cnid} }, $ctid;
        ##}
    }

    my $nodes;
    foreach my $tid ( sort keys %{$terms} ) {
        ## contract party
        my $term = $terms->{$tid};
        my $cnid = $term->{'contributor'};

        my $party = $dbx->resultset('RoyaltyAccounting::ContractParty')->search( {
                'contract_id' => $cid,
                'target_type' => 'contributor',
                'target_id'   => $cnid
            }
        )->first;

        if ( not defined $party ) {
            logMessage( 'info', "Creating contract_party for contract: $cid contributor: $cnid" );
            $party = $dbx->resultset('RoyaltyAccounting::ContractParty')->create( {
                    'contract_id'      => $cid,
                    'target_type'      => 'contributor',
                    'target_id'        => $cnid,
                    'created_by'       => $dbuser,
                    'created_at'       => Orchard::Session::mysql_now,
                    'last_modified_by' => $dbuser,
                    'last_modified'    => Orchard::Session::mysql_now
                }
            );
        } else {
            logMessage( 'info', "Using contract_party:" . $party->id . " for contributor: $cnid" );
        }

        ## create schedule
        my $schd = $dbx->resultset('RoyaltyAccounting::Schedule')->search( {
                'schedule_name' => $term->{'schedule'},
                'target_id'     => $cnid,
                'target_type'   => 'contributor'
            }
        )->first;
        if ( not defined $schd ) {
            logMessage( 'info', "Adding schedule for $cnid : $term->{'schedule'}" );
            $schd = $dbx->resultset('RoyaltyAccounting::Schedule')->create( {
                    'schedule_name'    => $term->{'schedule'},
                    'target_id'        => $cnid,
                    'target_type'      => 'contributor',
                    'created_by'       => $dbuser,
                    'created_at'       => Orchard::Session::mysql_now,
                    'last_modified_by' => $dbuser,
                    'last_modified'    => Orchard::Session::mysql_now
                }
            );
        } else {
            logMessage( 'info', "Using schedule id: " . $schd->id );
        }

        ## schedule attachments
        my $sid = $schd->id;
        my $attdata;
        my $inclause = [ map { "'$_'" } @{ $term->{'attachments'} } ];
        $inclause = join ",\n", @{$inclause};
        my $existing = $dbh->selectcol_arrayref(
            qq{
			SELECT target_id AS cnid FROM schedule_attachment 
			WHERE schedule_id=$sid AND target_type='contribution' AND target_id IN (
			$inclause
			)
		}
        );
        if ( defined $existing and scalar @{$existing} > 1 ) {
            $attdata = [ array_minus( @{ $term->{'attachments'} }, @{$existing} ) ];
        } else {
            $attdata = $term->{'attachments'};
        }

        if ( defined $attdata and scalar @{$attdata} ) {
            my $insert = "INSERT INTO schedule_attachment VALUES\n";
            $insert .= join ",\n", map { "(NULL,$sid,'contribution','$_','$dbuser',NOW(),'$dbuser',NOW(),NULL,NULL)" } @{$attdata};
            $insert .= ';';
            my $ins = $dbh->prepare($insert);
            $ins->execute;
            logMessage( 'info', $ins->rows . " contributions added to schedule_attachment for id: $sid" );
        } else {
            logMessage( 'info', "No contributions to add to schedule_attachment for id: $sid" );
        }

        ## contract term schedule
        my $cts = $dbx->resultset('RoyaltyAccounting::ContractTermSchedule')->search( {
                'contract_term_id' => $tid,
                'schedule_id'      => $schd->id
            }
        )->first;
        if ( not defined $cts ) {
            logMessage( 'info', "Attaching schedule: " . $schd->id . " to contract_term: $tid" );
            $cts = $dbx->resultset('RoyaltyAccounting::ContractTermSchedule')->create( {
                    'contract_term_id' => $tid,
                    'schedule_id'      => $schd->id,
                    'created_by'       => $dbuser,
                    'created_at'       => Orchard::Session::mysql_now,
                    'last_modified_by' => $dbuser,
                    'last_modified'    => Orchard::Session::mysql_now
                }
            );
        } else {
            logMessage( 'info', "Schedule: " . $schd->id . " already attached to contract_term: $tid" );
        }
		
        my $ct = $dbx->resultset('RoyaltyAccounting::ContractTerm')->find($tid);
		if ( $ct->term_type ne 'schedule') {
            logMessage( 'info', "Contract term: $tid setting term_type to schedule");
        	$ct->term_type('schedule');
			$ct->update;
		}

        $nodes->{$sid} = $term->{'attachments'};
    }

    ## contributor only schedules
    foreach my $cnid ( keys %{$c_only} ) {
        my $terms = [ @{ $c_only->{$cnid} } ];
        my $satt  = $dbx->resultset('RoyaltyAccounting::ScheduleAttachment')->search( {
                'target_type' => 'contributor',
                'target_id'   => $cnid
            }
        )->first;
        if ( defined $satt ) {
            my $schid = $satt->schedule_id;
            foreach my $tid ( @{$terms} ) {
                my $cs_term = $dbx->resultset('RoyaltyAccounting::ContractTermSchedule')->search( {
                        'contract_term_id' => $tid,
                        'schedule_id'      => $satt->id
                    }
                )->first;
                if ( not defined $cs_term ) {
                    logMessage( 'info', "Attaching schedule :$schid to term $tid" );
                    $cs_term = $dbx->resultset('RoyaltyAccounting::ContractTermSchedule')->create( {
                            'contract_term_id' => $tid,
                            'schedule_id'      => $schid,
                            'created_by'       => $dbuser,
                            'created_at'       => Orchard::Session::mysql_now,
                            'last_modified_by' => $dbuser,
                            'last_modified'    => Orchard::Session::mysql_now
                        }
                    );
                } else {
                    logMessage( 'info', "Schedule :$schid only attached to term $tid" );
                }
            }
        } else {
            logMessage( 'error', "No contributor only schedule found for $cnid" );
        }
    }

    if ( $opt->neo ) {
        print <<"!";
<?xml version="1.0" encoding="UTF-8"?>
<changelog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.liquigraph.org/schema/1.0/liquigraph.xsd">
    <changeset id="ACC-XXXX_update_contribution_schedules_role:1" author="jgetter" run-on-change="true">
!
        foreach my $sid ( keys %{$nodes} ) {
            foreach my $cnid ( @{ $nodes->{$sid} } ) {
                print " " x 8 . "<query>\n";
                print " " x 12 . "MATCH (n:NrContribution) WHERE n.id='$cnid' SET n.abacusScheduleId=$sid RETURN n;\n";
                print " " x 8 . "</query>\n";
            }
        }
        print <<"!";
    </changeset>
</changelog>
!
    }

} else {
    logMessage( 'fatal', "$cid returned no rows check the db" );
    exit 1;
}