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

use constant 'OUTDIR' => 'royalty_accounting';

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

use JSON::XS;
use FileHandle;
use Array::Utils qw/:all/;
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::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" ],
    [ 'file|f=s'        => 'File to parse',                                     { 'required' => 1 } ],
    [ 'git_version|v=i' => 'git tree version <ACC>-<1234>-<VERSION> default 1', { 'default'  => 1 } ],
);
if ( $opt->help ) { 
    logMessage( 'info', $usage->text, 'options' );
    exit 1;
}

my ($jira_ticket) = $opt->file =~ m!.*?/ACC(....)/.*$!;

my $jira     = Orchard::Accounting::JIRA->new( $jira_ticket, $opt->git_version, OUTDIR );
my $branch   = $jira->branch;
my $filename = $jira->getfilepath("remove_contributions");

my $ttlContributions;
my $ttlContractTerms;
my @lines;
my $ctids;
my $seen = { 'contract_id' => 'contribution_id' };
my $csv  = Orchard::ETL::Extract::CSV->new( $opt->file );
while ( my $row = $csv->getrow() ) {
	next if defined $csv->getcol( $row, 'J' );

    my $ctid = $csv->getcol( $row, 'F' );
    next if not defined $ctid;

    my $cnid = $csv->getcol( $row, 'G' );

    ## make sure contribution is unique to term
    next if exists $seen->{$ctid}->{$cnid};
    $seen->{$ctid}->{$cnid}++;

    $ctids->{$ctid}++;

    $ttlContributions++;

    my $sql;
    $sql .= "UPDATE contract_term SET last_modified_by='${branch}',last_modified=NOW(),\n";
    $sql .= "attachments=JSON_REMOVE(attachments, JSON_UNQUOTE(JSON_SEARCH(attachments, 'one','${cnid}')))\n";
    $sql .= "WHERE contract_term_id=$ctid\n";
    $sql .= "AND JSON_CONTAINS(attachments,'\"${cnid}\"','\$');\n\n";
    push @lines, $sql;
}

my $inids = join ',', sort keys %{$ctids};
$ttlContractTerms = ( scalar keys %{$ctids} ) - 1;

my $body .= <<"!";
-- liquibase formatted sql

-- changeset jgetter:1

DROP TABLE IF EXISTS `ROLLBACK-DATA-${branch}`;

CREATE TABLE `ROLLBACK-DATA-${branch}`
SELECT ct.* FROM contract_term ct WHERE ct.contract_term_id IN (
$inids
);

!

map { print $body .= $_ } @lines;

$body .= <<"!";
--rollback UPDATE contract_term ct
--rollback JOIN `ROLLBACK-DATA-${branch}` rb USING (contract_term_id)
--rollback SET ct.attachments=rb.attachments;
!

$jira->finish( $filename, $body );

logMessage('info',"Total contributions: $ttlContributions");
logMessage('info',"Total contract terms: $ttlContractTerms");