CREATE OR REPLACE FUNCTION pro_rate_amount(amount float, p_start date, p_end date) returns decimal(7,2)
language plpgsql AS $$
	-- Function to calculate SPC pro-rate amounts
	-- Can handle multiple months
	-- Doesn't round--need to do it yourself
    DECLARE
		result	float = 0;
		tmp_date date;
		work_start date;
		work_end date;

        argcnt  int = 1;
        chrcnt  int = 0;
        fmtlen  int;
        CHR     text;
        output  text = '';
    BEGIN
		work_start = p_start;
		work_end = p_end;
		-- If multiple months, break into separate pieces and call recursively
		LOOP
			IF (date_part('month',work_start) <> date_part('month',work_end)) or (date_part('year',work_start) <> date_part('year',work_end)) THEN
				tmp_date = date_trunc('month',work_start) + '1 month - 1 day';
				result = result + pro_rate_amount(amount,work_start,tmp_date);
				work_start = tmp_date + 1;
			ELSE	
				EXIT;
			END IF;
		END LOOP;
		result = result + (amount / days_in_month(work_start) * (work_end - work_start + 1));
        RETURN result::decimal(7,2);
    END;
$$;

--CREATE OR REPLACE FUNCTION generate_payments ( date, text[], int[] ) RETURNS SETOF record AS $FUNC$
CREATE OR REPLACE FUNCTION generate_payments ( date, text, int, int ) RETURNS SETOF tbl_payment AS $FUNC$
DECLARE
    mdate ALIAS FOR $1;
	mtype ALIAS FOR $2;
	mclient ALIAS FOR $3;
	by_who ALIAS FOR $4;
	mdate_text text;
	quer_assist text;
	quer_assist_pri text;
	quer_security text;
	quer_utility text;
	quer_utility_pr text;
	quer_assist_x text;
	quer_assist_pri_x text;
	quer_security_x text;
	quer_utility_x text;
	quer_utility_pr_x text;
	duplicate_clause text;
	final_query text;
	union_clause text;
	cid_clause text;
	payment record;
--	pay_test tbl_payment_test%rowtype;
	pay_test tbl_payment%rowtype;

BEGIN

-- Define ASSISTANCE query

	quer_assist = $$
	 SELECT
		ro.client_id,
		'%'::date AS payment_date,
		'ASSIST'::text AS payment_type_code,
		rent_amount_spc AS amount,
		ro.housing_project_code,
		ro.housing_unit_code,
		ri.grant_number_code,
		NULL AS comment,
		vendor_number
--	FROM residence_own ro
	FROM residence_own_current ro
		LEFT JOIN rent_info ri ON 
		(ri.client_id=ro.client_id AND 
		'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
	WHERE '%' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'%')
	AND ro.is_active_manual
	$$;

-- Define UTILITY query
	quer_utility := $$
	SELECT
		ro.client_id,
		'%'::date AS payment_date,
		'UTILITY'::text AS payment_type_code,
		0 - rent_amount_tenant AS amount,
		ro.housing_project_code,
		ro.housing_unit_code,
		grant_number_code,
		NULL AS comment,
		tenant_vendor_number AS vendor_number
--	FROM residence_own ro
	FROM residence_own_current ro
		LEFT JOIN rent_info ri ON 
		(ri.client_id=ro.client_id AND 
		'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
	WHERE '%' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'%')
	AND ro.is_active_manual
	AND rent_amount_tenant <= -5 -- Under $5 utility payments are not issued.
	$$;

-- Define SECURITY query
	quer_security := $$
	SELECT
		ro.client_id,
		'%'::date AS payment_date,
		'SECURITY'::text AS payment_type_code,
		security_deposit AS amount,
		ro.housing_project_code,
		ro.housing_unit_code,
		grant_number_code,
		NULL AS comment,
		vendor_number
--	FROM residence_own ro
	FROM residence_own_current ro
		LEFT JOIN rent_info ri ON 
		(ri.client_id=ro.client_id AND 
		'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
--	WHERE ro.residence_date BETWEEN ('%'::date - '1 month'::interval)::date AND ('%'::date -1)
	-- For Security and prior payments, test whether record was added during this period
	WHERE ( (ro.added_at > target_date_effective_at()) AND (ro.residence_date < '%'::date) )
	AND ro.is_active_manual
	AND (NOT ro.tenant_pays_deposit)
	AND COALESCE(security_deposit,0) > 0
	-- Test for moved out or not, for security deposit?
	--AND '%' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'%')
	$$;

-- Define ASSIST_PRI query
	quer_assist_pri := $$
	SELECT
		ro.client_id,
		'%'::date AS payment_date,
		'ASSIST_PRI'::text AS payment_type_code,
		--ROUND(rent_amount_spc / days_in_month(ro.residence_date) * ('%' - ro.residence_date))::decimal(7,2),
		ROUND(pro_rate_amount(rent_amount_spc,ro.residence_date,target_date()-1)) AS amount,
		ro.housing_project_code,
		ro.housing_unit_code,
		grant_number_code,
		'Tenant pro-rated amount = $' || ROUND(pro_rate_amount(rent_amount_tenant,ro.residence_date,target_date()-1))::text AS comment,
		vendor_number
--	FROM residence_own ro
	FROM residence_own_current ro
		LEFT JOIN rent_info ri ON 
		(ri.client_id=ro.client_id AND 
		'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
	--WHERE ro.residence_date BETWEEN ('%'::date - '1 month'::interval)::date AND ('%'::date -1)
	-- For Security and prior payments, test whether record was added during this period
	WHERE ( (ro.added_at > target_date_effective_at()) AND (ro.residence_date < '%'::date) )
	AND ro.is_active_manual
	-- Does we need this clause?
	--AND '2013-10-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-10-01')
	$$;

-- Define UTILITY_PR query
	quer_utility_pr := $$
	SELECT
		ro.client_id,
		'%'::date AS payment_date,
		'UTILITY_PR'::text AS payment_type_code,
		--0- ROUND(rent_amount_tenant / days_in_month(ro.residence_date) * ('%' - ro.residence_date))::decimal(7,2),
		0 - ROUND(pro_rate_amount(rent_amount_tenant,ro.residence_date,target_date()-1)) AS amount,
		ro.housing_project_code,
		ro.housing_unit_code,
		grant_number_code,
		NULL AS comment,
		tenant_vendor_number AS vendor_number
--	FROM residence_own ro
	FROM residence_own_current ro
		LEFT JOIN rent_info ri ON 
		(ri.client_id=ro.client_id AND 
		'%' BETWEEN effective_date AND COALESCE(effective_date_end,'%'))
--	WHERE ro.residence_date BETWEEN ('%'::date - '1 month'::interval)::date AND ('%'::date -1)
	-- For Security and prior payments, test whether record was added during this period
	WHERE ( (ro.added_at > target_date_effective_at()) AND (ro.residence_date < '%'::date) )
	AND ro.is_active_manual
	AND rent_amount_tenant < 0
	$$;

	-- Limited to one client?
	cid_clause := COALESCE(' AND ro.client_id =' || mclient::text || ' ','');

	-- Don't post already existing payments
	duplicate_clause := ' AND NOT ROW(client_id,payment_type_code,payment_date) IN (SELECT client_id,payment_type_code,payment_date FROM payment_valid) ';

	union_clause := ' UNION ';
	-- Plug the date into all the queries
	mdate_text := mdate::text;
	quer_assist_x := REPLACE(quer_assist,'%',mdate_text) || cid_clause;
	quer_assist_pri_x := REPLACE(quer_assist_pri,'%',mdate_text) || cid_clause;
	quer_utility_x := REPLACE(quer_utility,'%',mdate_text) || cid_clause;
	quer_utility_pr_x := REPLACE(quer_utility_pr,'%',mdate_text) || cid_clause;
	quer_security_x := REPLACE(quer_security,'%',mdate_text) || cid_clause;

	final_query := CASE COALESCE(mtype,'ALL')
		WHEN 'ASSIST' THEN quer_assist_x
		WHEN 'ASSIST_PRI' THEN quer_assist_pri_x
		WHEN 'UTILITY' THEN quer_utility_x
		WHEN 'SECURITY' THEN quer_security_x
		WHEN 'UTILITY_PR' THEN quer_utility_pr_x
		WHEN 'ALL' THEN
			quer_assist_x || union_clause ||
			quer_assist_pri_x || union_clause ||
			quer_utility_x || union_clause ||
			quer_security_x || union_clause ||
			quer_utility_pr_x 
		END;
--RAISE NOTICE 'Final query %',final_query;
	-- Do the query and return the payments
    FOR payment IN EXECUTE final_query LOOP
		pay_test.payment_id := nextval('tbl_payment_payment_id_seq');
		pay_test.client_id := payment.client_id;
		pay_test.payment_date := payment.payment_date;
		pay_test.payment_type_code := payment.payment_type_code;
		pay_test.amount := payment.amount;
		pay_test.housing_project_code := payment.housing_project_code;
		pay_test.housing_unit_code := payment.housing_unit_code;
		pay_test.grant_number_code := payment.grant_number_code;
		pay_test.vendor_number := payment.vendor_number;
		pay_test.added_by=COALESCE(by_who,sys_user());
		pay_test.added_at=current_timestamp;
		pay_test.changed_at=current_timestamp;
		pay_test.changed_by=COALESCE(by_who,sys_user());
		pay_test.sys_log='Payment from generate payments';
		pay_test.comment=payment.comment;
		pay_test.is_deleted=false;
		pay_test.old_is_checkissued=false;
		pay_test.old_is_manualentry=false;
		pay_test.old_is_duplicate_unit=false;
		pay_test.old_is_paidalready=false;
		pay_test.old_is_checknotsent=false;
		pay_test.is_subsidy=false;
		pay_test.is_void=false;
		pay_test.target_date=target_date();
--		IF payment IS NOT NULL THEN
			RETURN NEXT pay_test;
--		END IF;
    END LOOP;
    RETURN;
END;
$FUNC$ LANGUAGE PLPGSQL;


/*
CREATE TABLE ptest AS 
SELECT

--ro.residence_own_id, -- delete me
ro.client_id,
'2013-11-01'::date AS payment_date,
'ASSIST'::text AS payment_type_code,
rent_amount_spc AS amount,
ro.housing_project_code,
ro.housing_unit_code,
ri.grant_number_code,
sys_user() AS added_by,
sys_user() AS changed_by,
'Make me a good sys_log message' AS sys_log

FROM residence_own ro
  LEFT JOIN rent_info ri ON (ri.client_id=ro.client_id AND '2013-11-01' BETWEEN effective_date AND COALESCE(effective_date_end,'2013-11-01'))
WHERE
true--	ro.housing_unit_code='2248'
AND
	'2013-11-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-11-01')
AND ro.is_active_manual
UNION

SELECT

--ro.residence_own_id, -- delete me
ro.client_id,
'2013-11-01'::date AS payment_date,
'UTILITY'::text AS payment_type_code,
0 - rent_amount_tenant AS amount,
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
sys_user() AS added_by,
sys_user() AS changed_by,
'Make me a good sys_log message' AS sys_log

FROM residence_own ro
  LEFT JOIN rent_info ri ON (ri.client_id=ro.client_id AND '2013-11-01' BETWEEN effective_date AND COALESCE(effective_date_end,'2013-11-01'))
WHERE
--	ro.housing_unit_code='2248'
	'2013-10-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-10-01')
AND
	rent_amount_tenant < 0
--) FOO group by 1,2,3,4,5,6,7 HAVING COUNT(*) > 1 
--ORDER BY 3,2
*/
/*
UNION

SELECT
ro.client_id,
'2013-10-01'::date,
'SECURITY'::text,
security_deposit,
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
sys_user(),
sys_user(),
'Make me a good sys_log message'

FROM residence_own ro
  LEFT JOIN rent_info ri ON (ri.client_id=ro.client_id AND '2013-10-01' BETWEEN effective_date AND COALESCE(effective_date_end,'2013-10-01'))
WHERE 
ro.residence_date BETWEEN '2013-09-01' AND '2013-10-01'
AND ro.is_active_manual

AND '2013-10-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-10-01')

UNION 

SELECT
ro.client_id,
'2013-10-01'::date,
'ASSIST_PRI'::text,
rent_amount_spc / 30 * (30 - LEAST(30,'2013-10-01' - ro.residence_date))::decimal(7,2),
ro.housing_project_code,
ro.housing_unit_code,
grant_number_code,
sys_user(),
sys_user(),
'Make me a good sys_log message'
FROM residence_own ro
  LEFT JOIN rent_info ri ON (ri.client_id=ro.client_id AND '2013-10-01' BETWEEN effective_date AND COALESCE(effective_date_end,'2013-10-01'))
WHERE 
ro.residence_date BETWEEN '2013-09-01' AND '2013-10-01'
AND ro.is_active_manual
AND '2013-10-01' BETWEEN ro.residence_date AND COALESCE(ro.residence_date_end,'2013-10-01')
;
*/


/*
create view ptest_mip as
SELECT 
p.client_id,
'APISPC' ||
to_char(current_date + '2 month'::interval, 'MMYY') ||
'20' ||
p.grant_number_code as month_grant,
'BP' as c2,
'Imported Session'as c3,
date_part('month',current_date + '2 month'::interval) ||
'/1/' ||
date_part('year',current_date + '2 month'::interval) as payment_date,
'API' as c4,
p.grant_number_code ||
'_' ||
(SELECT ro2.old_access_id
FROM residence_own ro2
WHERE ro2.client_id=p.client_id
ORDER BY ro2.residence_date DESC LIMIT 1) ||--FIXME ri.residence_own_id ||
'_' ||
date_part('year',current_date + '2 month'::interval) ||
'-' ||
date_part('month',current_date + '2 month'::interval) as coded_field,
--E'\"' ||
SUBSTRING(c.name_first from 1 for 1) ||
'. ' ||
SUBSTRING(c.name_last from 1 for 10) ||
', ' ||
(SELECT ri.housing_unit_label
FROM rent_info ri
WHERE ri.client_id=p.client_id
ORDER BY ri.residence_date DESC LIMIT 1) ||
--E'\"' as name_unit,
E'' as name_unit,
(SELECT ri2.vendor_number
FROM rent_info ri2
WHERE ri2.client_id=p.client_id
ORDER BY ri2.residence_date DESC LIMIT 1) as vendor_number,--'Vendor',--FIXME vendor
'N' as a_flag,
'10' as c5,
'7649' as c6,
'200' as c7,
'100' as c8,
'20' ||
p.grant_number_code as grant_no,
'1' as c9,
(p.amount*100)::INT as amount_pennies, --check amount
'0' as c10,
'Check' as check_address
FROM ptest p
--LEFT JOIN rent_info ri ON
--ri.client_id=p.client_id
--JOIN residence_own ro ON
--p.client_id=ro.client_id
LEFT JOIN client c ON
c.client_id=p.client_id;

*/