#!/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("move_contributions");

my $ttlContributions;
my $ttlContractTerms;
my $ctids;
my $addterms;
my @rmlines;
my $csv = Orchard::ETL::Extract::CSV->new( $opt->file );
while ( my $row = $csv->getrow() ) {
    next if defined $csv->getcol( $row, 'P' );

    my $from_ctid = $csv->getcol( $row, 'F' );
    my $to_ctid   = $csv->getcol( $row, 'K' );
    my $contrib   = $csv->getcol( $row, 'M' );

    $ttlContributions++;

    $ctids->{$from_ctid}++;
    $ctids->{$to_ctid}++;

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

    push @{ $addterms->{$to_ctid} }, $contrib;

}

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

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
);

!

## remove contribs
map { $body .= $_ } @rmlines;

## add contribs to terms
foreach my $id ( sort keys %{$addterms} ) {
    my $list = JSON::XS::encode_json( $addterms->{$id} );
    my $sql;
    $sql  .= "UPDATE contract_term SET last_modified_by='${branch}',last_modified=NOW(),\n";
    $sql  .= "attachments=JSON_MERGE_PRESERVE(attachments,'$list')\n";
    $sql  .= "WHERE contract_term_id=$id;\n\n";
    $body .= $sql;
}

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

$jira->printfile( $filename, $body );
$jira->showfile($filename);

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