#!/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-2'";

my $dbh = Orchard::Session::getdbh('abacusqa');
my $sth = $dbh->prepare(
    qq{
SELECT
    sub.contract_id,
    sub.contract_term_name,
    sub.md5terms,
    sub.ctids,
    sub.sids,
    GROUP_CONCAT(sub.ctcid ORDER BY 1) ctcids,
    COUNT(DISTINCT ctcid) total_conditions
FROM (
    SELECT
        ct.contract_id,
        CONCAT('Contributions - ',c.contract_name) contract_term_name,
        MD5(GROUP_CONCAT(DISTINCT MD5(ct.attachments) ORDER BY ct.contract_term_id  SEPARATOR ',')) md5terms,
        GROUP_CONCAT(DISTINCT ct.contract_term_id ORDER BY ct.contract_term_id) ctids,
        GROUP_CONCAT(DISTINCT res.parent_table_id ORDER BY 1) sids,
        MIN(ctc.contract_term_condition_id) ctcid
    FROM contract c
    JOIN contract_term ct ON ct.contract_id=c.contract_id
    JOIN account_contract ac ON ac.contract_id=ct.contract_id
    JOIN contract_term_condition ctc ON ctc.contract_term_id=ct.contract_term_id
    JOIN run_controller_contract rcc ON rcc.contract_id=ct.contract_id AND rcc.run_controller_id=6
    JOIN reference_external_source res ON res.parent_table_name='schedule' AND res.external_source=MD5(ct.attachments)
    WHERE TRUE
        AND ct.deleted_at IS NULL -- dont include deleted terms
        AND ctc.deleted_at IS NULL
        AND JSON_LENGTH(ct.attachments) > 1
        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,538758,533685
        )
    GROUP BY
        ct.contract_id,
        c.contract_name,
        MD5(CONCAT_WS(":", ctc.commission, ctc.term_rate, ctc.conditions))
) sub
GROUP BY 1,2,3,4;
}
);
$sth->execute;

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

-- changeset jgetter:1

!

if ( $sth->rows ) {
    while ( my $row = $sth->fetchrow_hashref() ) {
        my $cid    = $row->{'contract_id'};
        my $ctcids = $row->{'ctcids'};
        my $ctids = $row->{'ctids'};
        my $sids = $row->{'sids'};
        my $name   = $dbh->quote( $row->{'contract_term_name'} );
        $sql .= <<"!";
-- ----------------------------------------------
-- contract $cid - new contribution term
-- ----------------------------------------------
INSERT INTO contract_term VALUES (
NULL,$cid,$name,
'contribution_schedule','[]',NULL,0,$dbuser,NOW(),$dbuser,NOW(),NULL,NULL
);

SET \@contract_term_id=LAST_INSERT_ID();

INSERT INTO contract_term_condition
SELECT NULL,\@contract_term_id,contract_term_condition_name,conditions,term_rate,commission,priority,
$dbuser,NOW(),$dbuser,NOW(),NULL,NULL
FROM contract_term_condition WHERE contract_term_condition_id IN ($ctcids);

INSERT INTO contract_term_schedule
SELECT NULL,\@contract_term_id,schedule_id,$dbuser,NOW(),$dbuser,NOW(),NULL,NULL
FROM schedule WHERE schedule_id IN ($sids);

INSERT INTO reference_external_source
SELECT NULL,\@contract_term_id,'contract_term',contract_term_id,'contract_term',$dbuser,NOW(),$dbuser,NOW()
FROM contract_term WHERE contract_term_id IN ($ctids);

!

    }

    $sql .= <<"!";
--rollback DELETE FROM contract_term WHERE created_by=$dbuser;
--rollback DELETE FROM contract_term_condition WHERE created_by=$dbuser;
--rollback DELETE FROM contract_term_schedule WHERE created_by=$dbuser;
--rollback DELETE FROM reference_external_source WHERE created_by=$dbuser;
!
	print $sql;

}