<commit_before>BEGIN;

/* NOTE: Create or replace view... is bugged! Using drop view in stead. */
/* V_REP_PROGRESSPERYEAR */
--DROP VIEW V_REP_PROGRESSPERYEAR;
CREATE VIEW V_REP_PROGRESSPERYEAR
as
select
    res.account,
    date_part('year', res.date) as year,
    sum(res.amount)
from
(
    select
	a.name as account,
	f.date,
	case
	    when f.account_id = 4 and f.category_id = 21 and f.subcategory_id = 6 then 0
	    else
                case
		    when c.flg_income = 0 then -1*f.amount
		    when c.flg_income = 1 then f.amount
	        end
	end as amount
    from T_FINANCE f
        inner join T_ACCOUNT a on f.account_id = a.account_id
        inner join T_CATEGORY c on f.category_id = c.category_id
        inner join T_SUBCATEGORY sc on f.subcategory_id = sc.subcategory_id
	group by a.name, f.date, c.name, sc.name, c.flg_income, f.amount
) res
group by res.account, date_part('year', res.date)
order by date_part('year', res.date), res.account;

/* V_REP_PROGRESSPERACCPERMONTH */
--DROP VIEW V_REP_PROGRESSPERACCPERMONTH;
CREATE VIEW V_REP_PROGRESSPERACCPERMONTH
AS
select
    date_part('year', res.date) as year,
	date_part('month', res.date) as month,
	res.account,
	sum(res.amount)
from
(
	select
		a.name as account,
		f.date,
		case
			when f.account_id = 4 and f.category_id = 21 and f.subcategory_id = 6 then 0
			else
				case
					when c.flg_income = 0 then -1*f.amount
					when c.flg_income = 1 then f.amount
				end
		end as amount
	from T_FINANCE f
        inner join T_ACCOUNT a on f.account_id = a.account_id
        inner join T_CATEGORY c on f.category_id = c.category_id
        inner join T_SUBCATEGORY sc on f.subcategory_id = sc.subcategory_id
	group by a.name, f.date, c.name, sc.name, c.flg_income, f.amount
) res
group by date_part('year', res.date), date_part('month', res.date), res.account
order by date_part('year', res.date), date_part('month', res.date), res.account;

/* V_REP_NETWORTH */
-- TODO: check v_rep_check???
--DROP VIEW V_REP_NETWORTH;
CREATE VIEW V_REP_NETWORTH
AS
select
    date_part('year', date),
	sum(res.amount) as "Net worth"
from
(
    select
        a.name as account,
	f.date,
        case
            when c.flg_income = 0 then -1*f.amount
            when c.flg_income = 1 then f.amount
        end as amount
    from T_FINANCE f
        inner join T_ACCOUNT a on f.account_id = a.account_id
        inner join T_CATEGORY c on f.category_id = c.category_id
        inner join T_SUBCATEGORY sc on f.subcategory_id = sc.subcategory_id
    where
        f.category_id in (1,2, 22)
    group by a.name, f.date, c.name, c.flg_income, f.amount
) res
group by date_part('year', date);

/* V_REP_PROGRESSCUMUL */
--DROP VIEW V_REP_PROGRESSCUMUL;
CREATE VIEW V_REP_PROGRESSCUMUL
AS
select
    t1.finance_id,
    date_part('year', t1.date),
    t1.amount,
    sum(t1.amount)
from
(
    select
		f.finance_id,<commit_msg>refactored v_rep_networth<commit_after>BEGIN;

/* NOTE: Create or replace view... is bugged! Using drop view in stead. */
/* V_REP_PROGRESSPERYEAR */
--DROP VIEW V_REP_PROGRESSPERYEAR;
CREATE VIEW V_REP_PROGRESSPERYEAR
as
select
    res.account,
    date_part('year', res.date) as year,
    sum(res.amount)
from
(
    select
	a.name as account,
	f.date,
	case
	    when f.account_id = 4 and f.category_id = 21 and f.subcategory_id = 6 then 0
	    else
                case
		    when c.flg_income = 0 then -1*f.amount
		    when c.flg_income = 1 then f.amount
	        end
	end as amount
    from T_FINANCE f
        inner join T_ACCOUNT a on f.account_id = a.account_id
        inner join T_CATEGORY c on f.category_id = c.category_id
        inner join T_SUBCATEGORY sc on f.subcategory_id = sc.subcategory_id
	group by a.name, f.date, c.name, sc.name, c.flg_income, f.amount
) res
group by res.account, date_part('year', res.date)
order by date_part('year', res.date), res.account;

/* V_REP_PROGRESSPERACCPERMONTH */
--DROP VIEW V_REP_PROGRESSPERACCPERMONTH;
CREATE VIEW V_REP_PROGRESSPERACCPERMONTH
AS
select
    date_part('year', res.date) as year,
	date_part('month', res.date) as month,
	res.account,
	sum(res.amount)
from
(
	select
		a.name as account,
		f.date,
		case
			when f.account_id = 4 and f.category_id = 21 and f.subcategory_id = 6 then 0
			else
				case
					when c.flg_income = 0 then -1*f.amount
					when c.flg_income = 1 then f.amount
				end
		end as amount
	from T_FINANCE f
        inner join T_ACCOUNT a on f.account_id = a.account_id
        inner join T_CATEGORY c on f.category_id = c.category_id
        inner join T_SUBCATEGORY sc on f.subcategory_id = sc.subcategory_id
	group by a.name, f.date, c.name, sc.name, c.flg_income, f.amount
) res
group by date_part('year', res.date), date_part('month', res.date), res.account
order by date_part('year', res.date), date_part('month', res.date), res.account;

/* V_REP_NETWORTH */
--DROP VIEW V_REP_NETWORTH;
CREATE VIEW V_REP_NETWORTH;
AS
select
    date_part('year', f.date),
    sum(f.amount) as "Net worth"
from T_FINANCE f
    inner join T_ACCOUNT a on f.account_id = a.account_id
where
    a.account_id <> 5 -- betting account not counted
group by date_part('year', res.date);

/* V_REP_PROGRESSCUMUL */
--DROP VIEW V_REP_PROGRESSCUMUL;
CREATE VIEW V_REP_PROGRESSCUMUL
AS
select
    t1.finance_id,
    date_part('year', t1.date),
    t1.amount,
    sum(t1.amount)
from
(
    select
		f.finance_id,
{'fraction_non_alphanumeric': 0.09767008387698042, 'fraction_numerical': 0.008946877912395153, 'mean_word_length': 3.0285285285285286, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '652045', 'n_tokens_mistral': 2194, 'n_tokens_neox': 2085, 'n_words': 559}