-- DROP FUNCTION IF EXISTS public.savings_balances_func(text, timestamp without time zone, timestamp without time zone);

CREATE OR REPLACE FUNCTION public.savings_balances_func(
	query_string text,
	as_at timestamp without time zone,
	start_date timestamp without time zone DEFAULT NULL::timestamp without time zone)
    RETURNS TABLE(id bigint, account_no character varying, deleted boolean, status character varying, is_active boolean, opened_by_id bigint, product_name character varying, product_id bigint, min_balance double precision, currency_name character varying, currency_symbol character varying, currency_code character varying, opened_by_name character varying, customer_type_id bigint, customer_type_name character varying, has_members boolean, source_customer_type_id integer, organisation_id bigint, organisation_name character varying, gender character varying, telephone character varying, customer_id bigint, customer_name character varying, customer_member_number character varying, customer_old_member_number character varying, branch_id bigint, branch_name character varying, branch_short_name character varying, physical_address character varying, villagename character varying, regionname character varying, credits_bf double precision, debits_bf double precision, blocked_amount_bf double precision, total_held_amount_bf double precision, credits double precision, debits double precision, blocked_amount double precision, total_held_amount double precision, last_updated timestamp with time zone, latest_date timestamp with time zone) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
if start_date is null then
    RETURN QUERY EXECUTE format('
	SELECT 
	acc.id,
    acc.account_no,
    acc.deleted,
    acc.status,
    acc.is_active,
    acc.opened_by_id,
    acc.product_name,
    acc.product_id,
    acc.min_balance,
    acc.currency_name,
    acc.currency_symbol,
    acc.currency_code,
    acc.opened_by_name,
    acc.customer_type_id,
    acc.customer_type_name,
    acc.has_members,
    acc.source_customer_type_id,
    acc.organisation_id,
    acc.organisation_name,
    acc.gender,
    acc.telephone,
    acc.customer_id,
    acc.customer_name,
    acc.customer_member_number,
    acc.customer_old_member_number,
    acc.branch_id,
    acc.branch_name,
    acc.branch_short_name,
    acc.physical_address,
    acc.villagename,
    acc.regionname,
    COALESCE((acc.min_balance * 0), 0::double precision) AS credits_bf,
    COALESCE((acc.min_balance * 0), 0::double precision) AS debits_bf,
     COALESCE((acc.min_balance * 0), 0::double precision) AS blocked_amount_bf,
    COALESCE((acc.min_balance * 0), 0::double precision) AS total_held_amount_bf,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.credit_chart_id = acc.accounts_chart_id AND DATE(record_date) <= %L), 0::double precision) AS credits,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.debit_chart_id = acc.accounts_chart_id AND DATE(record_date) <= %L), 0::double precision) AS debits,
    COALESCE(( SELECT sum(savings_blocked_amount.amount) AS sum
           FROM savings_blocked_amount
          WHERE savings_blocked_amount.customer_account_id = acc.id AND DATE(date_added) <= %L), 0::double precision) AS blocked_amount,
    COALESCE(( SELECT sum(loan_application_withhold.amount) AS sum
           FROM loan_application_withhold
          WHERE loan_application_withhold.account_id = acc.id AND DATE(date_added) <= %L), 0::double precision) AS total_held_amount,
    acc.last_updated AS last_updated,
    COALESCE(( SELECT max(savings_account_transaction_view.record_date) AS max
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE), acc.open_date::timestamp without time zone::timestamp with time zone) AS latest_date
   FROM savings_account_search_view acc '|| query_string ||'',as_at,as_at,as_at,as_at);
else
	 RETURN QUERY EXECUTE format('SELECT 
	acc.id,
    acc.account_no,
    acc.deleted,
    acc.status,
    acc.is_active,
    acc.opened_by_id,
    acc.product_name,
    acc.product_id,
    acc.min_balance,
    acc.currency_name,
    acc.currency_symbol,
    acc.currency_code,
    acc.opened_by_name,
    acc.customer_type_id,
    acc.customer_type_name,
    acc.has_members,
    acc.source_customer_type_id,
    acc.organisation_id,
    acc.organisation_name,
    acc.gender,
    acc.telephone,
    acc.customer_id,
    acc.customer_name,
    acc.customer_member_number,
    acc.customer_old_member_number,
    acc.branch_id,
    acc.branch_name,
    acc.branch_short_name,
    acc.physical_address,
    acc.villagename,
    acc.regionname,
	COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.credit_chart_id = acc.accounts_chart_id AND DATE(record_date) < %L), 0::double precision) AS credits_bf,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.debit_chart_id = acc.accounts_chart_id AND DATE(record_date) < %L), 0::double precision) AS debits_bf,
     COALESCE(( SELECT sum(savings_blocked_amount.amount) AS sum
           FROM savings_blocked_amount
          WHERE savings_blocked_amount.customer_account_id = acc.id AND DATE(date_added) < %L), 0::double precision) AS blocked_amount_bf,
    COALESCE(( SELECT sum(loan_application_withhold.amount) AS sum
           FROM loan_application_withhold
          WHERE loan_application_withhold.account_id = acc.id AND DATE(date_added) < %L), 0::double precision) AS total_held_amount_bf,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.credit_chart_id = acc.accounts_chart_id AND DATE(record_date) >= %L AND DATE(record_date) <= %L), 0::double precision) AS credits,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.debit_chart_id = acc.accounts_chart_id AND DATE(record_date) >= %L AND DATE(record_date) <= %L), 0::double precision) AS debits,
    COALESCE(( SELECT sum(savings_blocked_amount.amount) AS sum
           FROM savings_blocked_amount
          WHERE savings_blocked_amount.customer_account_id = acc.id  AND DATE(date_added) >= %L AND DATE(date_added) <= %L), 0::double precision) AS blocked_amount,
    COALESCE(( SELECT sum(loan_application_withhold.amount) AS sum
           FROM loan_application_withhold
          WHERE loan_application_withhold.account_id = acc.id AND DATE(date_added) >= %L AND DATE(date_added) <= %L), 0::double precision) AS total_held_amount,
    acc.last_updated AS last_updated,
    COALESCE(( SELECT max(savings_account_transaction_view.record_date) AS max
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE), acc.open_date::timestamp without time zone::timestamp with time zone) AS latest_date
   FROM savings_account_search_view acc '|| query_string ||'',start_date,start_date,start_date,start_date,start_date,as_at,start_date,as_at,start_date,as_at,start_date,as_at);
	 END if;
END
$BODY$;


-- DROP FUNCTION IF EXISTS public.cummulated_savings_balances_func(text, timestamp without time zone);

CREATE OR REPLACE FUNCTION public.cummulated_savings_balances_func(
	query_string text,
	as_at timestamp without time zone DEFAULT NULL::timestamp without time zone)
    RETURNS TABLE(id bigint, account_no character varying, deleted boolean, status character varying, is_active boolean, opened_by_id bigint, product_name character varying, product_id bigint, min_balance double precision, currency_name character varying, currency_symbol character varying, currency_code character varying, opened_by_name character varying, customer_type_id bigint, customer_type_name character varying, has_members boolean, source_customer_type_id integer, organisation_id bigint, organisation_name character varying, gender character varying, telephone character varying, customer_id bigint, customer_name character varying, customer_member_number character varying, customer_old_member_number character varying, branch_id bigint, branch_name character varying, branch_short_name character varying, physical_address character varying, villagename character varying, regionname character varying, credits double precision, debits double precision, deposits double precision, withdrawals double precision, transfers_recieved double precision, transfers_sent double precision, share_purchases double precision, loan_payments double precision, blocked_amount double precision, total_held_amount double precision) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
if as_at is null then
   RETURN QUERY EXECUTE format('SELECT 
	acc.id,
    acc.account_no,
    acc.deleted,
    acc.status,
    acc.is_active,
    acc.opened_by_id,
    acc.product_name,
    acc.product_id,
    acc.min_balance,
    acc.currency_name,
    acc.currency_symbol,
    acc.currency_code,
    acc.opened_by_name,
    acc.customer_type_id,
    acc.customer_type_name,
    acc.has_members,
    acc.source_customer_type_id,
    acc.organisation_id,
    acc.organisation_name,
    acc.gender,
    acc.telephone,
    acc.customer_id,
    acc.customer_name,
    acc.customer_member_number,
    acc.customer_old_member_number,
    acc.branch_id,
    acc.branch_name,
    acc.branch_short_name,
    acc.physical_address,
    acc.villagename,
    acc.regionname,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.credit_chart_id = acc.accounts_chart_id), 0::double precision) AS credits,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.debit_chart_id = acc.accounts_chart_id), 0::double precision) AS debits,
     COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.credit_chart_id = acc.accounts_chart_id AND saving_type = %L), 0::double precision) AS deposits, 
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.debit_chart_id = acc.accounts_chart_id AND saving_type = %L), 0::double precision) AS withdrawals,
	 COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.credit_chart_id = acc.accounts_chart_id AND saving_type = %L), 0::double precision) AS transfers_recieved,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.debit_chart_id = acc.accounts_chart_id AND saving_type = %L), 0::double precision) AS transfers_sent,
    COALESCE(( SELECT sum(shares_transaction_view.amount) AS sum
           FROM shares_transaction_view
          WHERE shares_transaction_view.customer_id = acc.customer_id AND shares_transaction_view.credit_chart_id = acc.accounts_chart_id), 0::double precision) AS share_purchases,
	COALESCE(( SELECT sum(loans_main_transactions_view.amount) AS sum
           FROM loans_main_transactions_view
          WHERE loans_main_transactions_view.customer_id = acc.customer_id AND loans_main_transactions_view.debit_chart_id = acc.accounts_chart_id), 0::double precision) AS loan_payments,
	COALESCE(( SELECT sum(savings_blocked_amount.amount) AS sum
           FROM savings_blocked_amount
          WHERE savings_blocked_amount.customer_account_id = acc.id), 0::double precision) AS blocked_amount,
    COALESCE(( SELECT sum(loan_application_withhold.amount) AS sum
           FROM loan_application_withhold
          WHERE loan_application_withhold.account_id = acc.id), 0::double precision) AS total_held_amount
   FROM savings_account_search_view acc '|| query_string ||'','deposit','withdrawal', 'transfer', 'transfer');
else
	RETURN QUERY EXECUTE format('SELECT 
	acc.id,
    acc.account_no,
    acc.deleted,
    acc.status,
    acc.is_active,
    acc.opened_by_id,
    acc.product_name,
    acc.product_id,
    acc.min_balance,
    acc.currency_name,
    acc.currency_symbol,
    acc.currency_code,
    acc.opened_by_name,
    acc.customer_type_id,
    acc.customer_type_name,
    acc.has_members,
    acc.source_customer_type_id,
    acc.organisation_id,
    acc.organisation_name,
    acc.gender,
    acc.telephone,
    acc.customer_id,
    acc.customer_name,
    acc.customer_member_number,
    acc.customer_old_member_number,
    acc.branch_id,
    acc.branch_name,
    acc.branch_short_name,
    acc.physical_address,
    acc.villagename,
    acc.regionname,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.credit_chart_id = acc.accounts_chart_id AND DATE(record_date) <= %L), 0::double precision) AS credits,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.debit_chart_id = acc.accounts_chart_id AND DATE(record_date) <= %L), 0::double precision) AS debits,
     COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.credit_chart_id = acc.accounts_chart_id AND saving_type = %L AND DATE(record_date) <= %L), 0::double precision) AS deposits,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.debit_chart_id = acc.accounts_chart_id AND saving_type = %L AND DATE(record_date) <= %L), 0::double precision) AS withdrawals,
	 COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.credit_chart_id = acc.accounts_chart_id AND saving_type = %L AND DATE(record_date) <= %L), 0::double precision) AS transfers_recieved,
    COALESCE(( SELECT sum(savings_account_transaction_view.amount) AS sum
           FROM savings_account_transaction_view
          WHERE savings_account_transaction_view.customer_account_id = acc.id AND savings_account_transaction_view.deleted = FALSE AND savings_account_transaction_view.debit_chart_id = acc.accounts_chart_id AND saving_type = %L AND DATE(record_date) <= %L), 0::double precision) AS transfers_sent,
	COALESCE(( SELECT sum(shares_transaction_view.amount) AS sum
           FROM shares_transaction_view
          WHERE shares_transaction_view.customer_id = acc.customer_id AND shares_transaction_view.credit_chart_id = acc.accounts_chart_id AND DATE(record_date) <= %L), 0::double precision) AS share_purchases,
	COALESCE(( SELECT sum(loans_main_transactions_view.amount) AS sum
           FROM loans_main_transactions_view
          WHERE loans_main_transactions_view.customer_id = acc.customer_id AND loans_main_transactions_view.debit_chart_id = acc.accounts_chart_id AND DATE(record_date) <= %L), 0::double precision) AS loan_payments,
	COALESCE(( SELECT sum(savings_blocked_amount.amount) AS sum
           FROM savings_blocked_amount
          WHERE savings_blocked_amount.customer_account_id = acc.id AND DATE(date_added) <= %L), 0::double precision) AS blocked_amount,
    COALESCE(( SELECT sum(loan_application_withhold.amount) AS sum
           FROM loan_application_withhold
          WHERE loan_application_withhold.account_id = acc.id AND DATE(date_added) <= %L), 0::double precision) AS total_held_amount
   FROM savings_account_search_view acc '|| query_string ||'',as_at,as_at,'deposit',as_at,'withdrawal',as_at,'transfer',as_at,'transfer',as_at,as_at,as_at,as_at,as_at);
	 END if;
END
$BODY$;