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

## db connections
my $dbh = Orchard::Session::getdbh('abacusqa');
my $sth = $dbh->prepare(
    qq{
SELECT
	sub.contract_id,
	sub.contract_term_name,
	sub.ctids,
	sub.sids,
	GROUP_CONCAT(sub.ctcid ORDER BY 1) ctcids
FROM (
SELECT
	ct.contract_id,
    CONCAT('Contributor - ',c.contract_name) contract_term_name,
    GROUP_CONCAT(ct.contract_term_id ORDER BY 1) ctids,
    GROUP_CONCAT(DISTINCT sa.schedule_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
    AND ac.is_primary_for_calc = 1
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 schedule_attachment sa 
    ON sa.target_id = TRIM(BOTH '"' FROM JSON_EXTRACT(ct.attachments_relations, '\$.contributor_ids[0]'))
    AND sa.target_type = 'contributor'
WHERE TRUE
    AND ct.deleted_at IS NULL -- dont include deleted terms
    AND is_base_term = 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
}
);
$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;

}