#!/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 $sfdbh = Orchard::Session::getdbh('snowflake');

## get all contributor schedule
my $scheds = $dbh->selectall_hashref(
    qq{
SELECT schedule_id, target_id
FROM schedule_attachment
WHERE target_type='contributor'
AND created_by='ACC-6283-1'
}, 'target_id'
);

## get all NR contibutors
my $nr_contribs = $sfdbh->selectall_hashref("SELECT id,name FROM facts.prod.nr_contributor",'ID');

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

-- changeset jgetter:1

!

my $rows;
foreach my $tid (sort keys %{$scheds}) {
	my $sid = $scheds->{$tid}->{'schedule_id'};
	my $name = $dbh->quote($nr_contribs->{$tid}->{'NAME'});
	push @{$rows}, "($sid,$name,'contributor','$tid',NULL,$dbuser,NOW(),$dbuser,NOW())";
	# $sql .= "UPDATE schedule SET schedule_name=$name,last_modified_by=$dbuser,last_modified=NOW() WHERE schedule_id=$sid;\n"
}
my $dup_rows = join ",\n", @{$rows};

$sql .=<<"!";
INSERT INTO schedule VALUES 
$dup_rows
ON DUPLICATE KEY UPDATE schedule_name=VALUES(schedule_name),last_modified_by=VALUES(last_modified_by);

--rollback UPDATE schedule SET schedule_name='Contributor Only Schedule' WHERE last_modified_by=$dbuser;

!

print $sql;