fix_ignored_and_unignored_accounts.sql

-- ----------------------- Ledger Balance for both ignored and unignored---------------
delete from ledger_balance_event where id in
(
	select distinct lbe.id
	from ledger_balance_event lbe
	inner join ledger_balance lb on lbe.id = lb.id and lb.status <> 'REFERENCE'
	where lb.ledger_id in (<ignored_accounts,unignored_accounts>)
);


-- update ledger_balance for both ignored and unignored
delete from ledger_balance 
where ledger_id in (<ignored_accounts,unignored_accounts>)
and status <> 'REFERENCE';


<ignored_accounts,unignored_accounts>:
select distinct COALESCE(yia.ledgeraccountid, a.ledger_id)
from mainapp_production.yodleeitemaccount yia
join financial_data_service.account a on yia.itemAccountid = a.source_id
where yia.ignored and a.ledger_id is not null
UNION 
select distinct COALESCE(yia.ledgeraccountid, a.ledger_id)
from mainapp_production.yodleeitemaccount yia
join financial_data_service.account a on yia.itemAccountid = a.source_id
where not yia.ignored and yia.ledgeraccountid is not null and a.ledger_id is null;


-- ------------------------update account and account_event for UNignored--------------
-- create account_event for <unignored_accounts>
insert into account_event
next_val('account_event_event_id_seq'), id, connection_id,
CONCAT('{"jsonClass":"AccountMappedToLedger","entityId":',id,',"ledger_id":',ledger_id,',"isMasterBalance":true,"personId":0}')
FROM (
	-- execute in redshift and create table using select and union with the raw data
	select yia.ledgeraccountid as ledger_id, a.id as accountId, a.connection_id as connection_id, 
	from mainapp_production.yodleeitemaccount yia
	join financial_data_service.account a on yia.itemAccountid = a.source_id
	where not yia.ignored and yia.ledgeraccountid is not null and a.ledger_id is null;
) as X

 
-- update unignored accounts
update account A
set ledger_id = X.ledger_id, master_balance = True
FROM (
	-- execute in redshift and create table using select and union with the raw data
	select yia.ledgeraccountid as ledger_id, a.id as accountId 
	from mainapp_production.yodleeitemaccount yia
	join financial_data_service.account a on yia.itemAccountid = a.source_id
	where not yia.ignored and yia.ledgeraccountid is not null and a.ledger_id is null;
) as X
where A.id = X.accountId





-- ------------------------update account and account_event for ignored--------------
-- create account_event for <ignored_accounts>
insert into account_event
next_val('account_event_event_id_seq'), id, connection_id,
CONCAT('{"jsonClass":"AccountUnmapped","entityId":',id,',"personId":0}')
from (
	select id, connection_id
	from account
	where id in (
		-- run in RS and paste
		select distinct a.id
		from mainapp_production.yodleeitemaccount yia
		join financial_data_service.account a on yia.itemAccountid = a.source_id
		where yia.ignored and a.ledger_id is not null;
	)
)

-- Update ignored accounts
update account
set ledger_id = null, master_balance = FALSE
where source_id in 
(
	-- run in RS and paste:
	select distinct a.source_id
	from mainapp_production.yodleeitemaccount yia
	join financial_data_service.account a on yia.itemAccountid = a.source_id
	where yia.ignored and a.ledger_id is not null;
)