#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

my $rbtab = "`ROLLBACK-DATA-JGTEST-1`";
my $dbuser = 'jgtest';

print<<"!";
-- liquibase formatted sql

-- changeset jgetter:1

DROP TABLE IF EXISTS $rbtab;

CREATE TABLE $rbtab AS
SELECT ct.*
FROM contract_term ct
JOIN account_contract ac 
    ON ac.contract_id=ct.contract_id 
    AND ac.is_primary_for_calc=1
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
    JSON_LENGTH(ct.attachments) = 0
    AND ct.term_type = 'contribution'
    AND is_base_term = 1;

INSERT INTO contract_term_schedule
SELECT
    NULL,
    ct.contract_term_id,
    sa.schedule_id,
    '$dbuser',
    NOW(),
    '$dbuser',
    NOW(),NULL,NULL
FROM contract_term ct
JOIN account_contract ac 
    ON ac.contract_id=ct.contract_id 
    AND ac.is_primary_for_calc=1
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
    JSON_LENGTH(ct.attachments) = 0
    AND ct.term_type = 'contribution'
    AND is_base_term = 1
ORDER BY
    ct.contract_id;

UPDATE contract_term ct
JOIN $rbtab rb USING(contract_term_id)
SET ct.term_type='contributor',
    ct.last_modified=NOW(),
    ct.last_modified_by='$dbuser';

--rollback UPDATE contract_term ct
--rollback JOIN $rbtab rb USING (contract_term_id)
--rollback SET
--rollback ct.term_type=rb.term_type,
--rollback ct.last_modified_by=rb.last_modified_by,
--rollback ct.last_modified=rb.last_modified,
--rollback ct.deleted_by=rb.deleted_by,
--rollback ct.deleted_at=rb.deleted_at;

--rollback DELETE FROM contract_term_schedule WHERE created_by='$dbuser';
!