#!/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 $dbuser = 'ACC-6284-1';

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

my $sth = $dbh->prepare(
    qq{
SELECT
	MD5(ct.attachments) sid,
    GROUP_CONCAT(CONCAT_WS(':',ct.contract_id,ct.contract_term_id)) myids,
    ct.contract_term_name schedule_name,
    REPLACE(JSON_EXTRACT(ct.attachments_relations,'\$.contributor_ids[0]'),'"','') cnid,
    ct.attachments atts
FROM contract_term ct
JOIN run_controller_contract rcc ON rcc.contract_id = ct.contract_id AND rcc.run_controller_id = 6
WHERE TRUE
AND JSON_LENGTH(ct.attachments) > 1
AND ct.deleted_at IS NULL
AND ct.contract_id IN (
    533691,533692,533693,534449,534679,
    534887,535036,535160,535268,535347,
    535350,535352,535368,535575,535575,
    535668,535763,535765,535767,535768,
    535769,535770,535988,537391,538232,
    539269,539372
)
-- AND MD5(ct.attachments)='adc4e99ce54c7ba50c155753b297af31'
GROUP BY 1;
}
);
$sth->execute();

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

-- changeset jgetter:1

!

if ( $sth->rows ) {
    while ( my $row = $sth->fetchrow_hashref() ) {
        my $atts = JSON::XS::decode_json( $row->{'atts'} );
        my $cid  = $row->{'cid'};
        my $sid  = $row->{'sid'};
        my $name = $dbh->quote( $row->{'schedule_name'} );
        my $cnid = $row->{'cnid'};

        my $contribs      = [ map { "(NULL,\@schedule_id,'contribution','$_','$dbuser',NOW(),'$dbuser',NOW(),NULL,NULL)" } sort @{$atts} ];
        my $contribs_rows = join ",\n", @{$contribs};

        $sql .= <<"!";
INSERT INTO schedule VALUES 
(NULL,$name,'contributor','$cnid',NULL,'$dbuser',NOW(),'$dbuser',NOW());

SET \@schedule_id=LAST_INSERT_ID();

INSERT INTO schedule_attachment VALUES 
$contribs_rows;

!
        my $refs;
		my $myids = [ split ',', $row->{'myids'} ];
        foreach my $ids (sort @{$myids} ) {
            my ( $cid, $ctid ) = split ':', $ids;
            push @{$refs}, "(NULL,$cid,'contract',$ctid,'$sid','$dbuser',NOW(),'$dbuser',NOW())";
            push @{$refs}, "(NULL,\@schedule_id,'schedule',$ctid,'$sid','$dbuser',NOW(),'$dbuser',NOW())";
        }

        my $ref_rows = join ",\n", @{$refs};

        $sql .= <<"!";
INSERT INTO reference_external_source VALUES 
$ref_rows;

!

    }

    $sql .= <<"!";
--rollback DELETE FROM schedule WHERE created_by='$dbuser';
--rollback DELETE FROM schedule_attachment WHERE created_by='$dbuser';
--rollback DELETE FROM reference_external_source WHERE created_by='$dbuser';
!

}
print $sql;