CREATE OR REPLACE VIEW rent_info AS


SELECT 
	greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date) AS effective_date,
--	least(COALESCE(income_date_end,current_date),COALESCE(residence_date_end,current_date),COALESCE(housing_unit_date_end,current_date),COALESCE(housing_unit_subsidy_date_end,current_date)) AS effective_date_end,
	least(income_date_end,residence_date_end,housing_unit_date_end,housing_unit_subsidy_date_end) AS effective_date_end,
	-- Monthly Rent
	ROUND((
	-- Monthly Rent
	(
	-- Adjusted Annual Income
	GREATEST(
	annual_income
	-- Disability Deduction
	- 400 
	-- $480 / Dependent
	- (dependent_count * 480)
	-- Childcare
	- COALESCE(child_care,0)
	-- Medical expenses over 3% of income
	- GREATEST(
		--COALESCE(medical_expense,0)-annual_income,0)*0.03
		COALESCE(medical_expense,0)-annual_income*0.03,0)
	,0) -- Adjusted Annual Income
	/ 12 * 0.3
	- COALESCE(utility_allowance,0)
	) :: decimal(7,2))) AS rent_amount_tenant_calculated,

/*
	ROUND((
	-- Adjusted Annual Income
	(
	annual_income
	-- Disability Deduction
	- 400
	-- $480 / Dependent
	- (dependent_count * 480)
	-- Childcare
	- COALESCE(child_care,0)
	-- Medical expenses over 3% of income
	- GREATEST(
		COALESCE(medical_expense,0)-annual_income,0)*0.03
	) -- Adjusted Annual Income
	/ (12 * 0.3)
	- COALESCE(utility_allowance,0)
	) :: decimal(6,2)) AS rent_amount_tenant_calculated,
*/
	-- Repeat all of above
	ROUND(COALESCE(rent_amount_tenant_manual,
	-- Monthly Rent
	(
	-- Adjusted Annual Income
	GREATEST(
	annual_income
	-- Disability Deduction
	- 400 
	-- $480 / Dependent
	- (dependent_count * 480)
	-- Childcare
	- COALESCE(child_care,0)
	-- Medical expenses over 3% of income
	- GREATEST(
--		COALESCE(medical_expense,0)-annual_income,0)*0.03
		COALESCE(medical_expense,0)-annual_income*0.03,0)
	,0) -- Adjusted Annual Income
	/ 12 * 0.3
	- COALESCE(utility_allowance,0)
	)::decimal(7,2))) AS rent_amount_tenant,
	unit_rent - 
	-- Repeat all of above (again)
	GREATEST(ROUND(COALESCE(rent_amount_tenant_manual,
	-- Monthly Rent
	(
	-- Adjusted Annual Income
	GREATEST(
	annual_income
	-- Disability Deduction
	- 400 
	-- $480 / Dependent
	- (dependent_count * 480)
	-- Childcare
	- COALESCE(child_care,0)
	-- Medical expenses over 3% of income
	- GREATEST(
--		COALESCE(medical_expense,0)-annual_income,0)*0.03
		COALESCE(medical_expense,0)-annual_income*0.03,0)
	,0) -- Adjusted Annual Income
	/ 12 * 0.3
	- COALESCE(utility_allowance,0)
	) :: decimal(7,2))),0) AS rent_amount_spc,
	*,
	client_id::text || '_' || 
	greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date)::text
	AS fake_key
/*
	reg_spc_date,
	reg_spc_date_end,
	grant_number_code,
	agency_code
*/


 FROM
(
SELECT
    continuous_housing_project_own(a.client_id) AS project_date,
	continuous_housing_own(a.client_id) AS own_date,
	a.is_active_manual,
    a.residence_own_id,
    a.client_id,
    a.housing_project_code,
    lhp.description AS housing_project_label,
    a.housing_unit_code,
    a.residence_date,
    a.residence_date_end,
    a.was_received_hap,
    a.was_received_compliance,
    a.moved_from_code,
    a.chronic_homeless_status_code,
    a.move_in_type,
    a.lease_on_file,
    a.moved_to_code,
    a.moved_to_unit,
    a.departure_type_code,
    a.departure_reason_code,
    a.move_out_was_code,
    a.returned_homeless,
	a.unit_rent_manual,
	a.tenant_pays_deposit,
	a.comment_damage,
    a.comment_deposit,
    a.comment,
    inc.income_id,
    inc.income_date,
    inc.income_date_end,
    inc.annual_income,
    inc.monthly_income_total,
    inc.monthly_income_primary,
    inc.income_primary_code,
    inc.monthly_income_secondary,
    inc.income_secondary_code,
    inc.monthly_income_tertiary,
    inc.income_tertiary_code,
    inc.monthly_interest_income,
    inc.other_assistance_codes,
    inc.income_certification_type_code,
    inc.child_care,
    inc.handicap_assistance,
    inc.medical_expense,
    inc.fund_type_code,
    inc.rent_date_effective,
    inc.rent_date_end,
    hu.housing_unit_id,
    hu.housing_unit_label,
    hu.housing_unit_date,
    hu.housing_unit_date_end,
    hu.unit_type_code,
    hu.tax_credit,
    hu.max_occupant,
    hu.alternate_address_id,
	hu.mailing_address AS mailing_address_unit,
	hu.street_address,
	COALESCE (name_prefix || ' ','')
	|| name_first || ' '
	|| COALESCE(name_middle || ' ','')
	|| name_last
	|| E'\n'
	|| hu.mailing_address AS mailing_address_client,
    hus.housing_unit_subsidy_id,
    hus.housing_unit_subsidy_date,
    hus.housing_unit_subsidy_date_end,
    hus.unit_subsidy_amount,
	COALESCE(a.unit_rent_manual,hus.unit_subsidy_amount) AS unit_rent,
--	COALESCE(a.tenant_vendor_number_manual,(SELECT tenant_vendor_number FROM client WHERE client_id=a.client_id)) AS tenant_vendor_number,
	--COALESCE(a.tenant_vendor_number_manual,tenant_vendor_number) AS tenant_vendor_number,
	client.tenant_vendor_number,
	lhp.vendor_number,
	a.utility_allowance_manual,
	hus.utility_allowance AS utility_allowance_unit,
	COALESCE(a.utility_allowance_manual,hus.utility_allowance) AS utility_allowance,
    hus.utility_allowance_code,
    hus.security_deposit,
    hus.fair_market_rent,
    inc.rent_amount_tenant_manual,
    (SELECT COUNT(*) FROM family_member fm LEFT JOIN client USING (client_id) 
		WHERE fm.household_head_id=a.client_id 
		AND family_member_date <= COALESCE(a.residence_date_end,family_member_date) 
		AND COALESCE(fm.family_member_date_end,residence_date) >= a.residence_date 
		AND fm.client_id!=a.client_id 
		AND (
			COALESCE(is_dependent_manual,false)
			OR (
			(COALESCE(is_dependent_manual,true)) AND
				(fm.family_relation_code IN ('GRANDDAUGH','GRANDSON','DAUGHTER','SON') 
				AND ( ((inc.income_date - dob)/365.25) < 18)
				)
			) 
		)
	) AS dependent_count

FROM residence_own AS a
		LEFT JOIN client USING (client_id),
	housing_unit_subsidy hus
		LEFT JOIN housing_unit hu USING (housing_unit_code,housing_project_code)
		LEFT JOIN l_housing_project lhp USING (housing_project_code),
--    housing_unit AS hu LEFT JOIN l_housing_project lhp USING (housing_project_code),
--    housing_unit_subsidy AS hus,
    income AS inc
WHERE 
--ri.is_active_manual
--
--AND hu.housing_unit_code = a.housing_unit_code
hus.housing_unit_code = a.housing_unit_code
--AND hus.housing_project_code = a.housing_project_code
--AND hu.housing_project_code = a.housing_project_code
AND inc.client_id = a.client_id

AND inc.income_date <= COALESCE(a.residence_date_end,inc.income_date)
AND COALESCE(inc.income_date_end,a.residence_date) >= a.residence_date
AND hus.housing_unit_subsidy_date <= COALESCE(a.residence_date_end,hus.housing_unit_subsidy_date)
AND COALESCE( hus.housing_unit_subsidy_date_end,a.residence_date) >= a.residence_date
AND hu.housing_unit_date <= COALESCE(a.residence_date_end,hus.housing_unit_subsidy_date)
AND COALESCE( hu.housing_unit_date_end,a.residence_date) >= a.residence_date

) foo
--	LEFT JOIN (SELECT * FROM reg_spc)  rs ON (rs.client_id=foo.client_id AND effective_date BETWEEN reg_spc_date AND COALESCE(reg_spc_date_end,effective_date,current_date))
	LEFT JOIN (SELECT client_id AS cid,reg_spc_date,reg_spc_date_end,grant_number_code,agency_code,phone AS agency_phone,la.description AS agency_label,la.contact AS agency_contact FROM reg_spc LEFT JOIN l_agency la USING (agency_code))  rs ON (rs.cid=foo.client_id AND 
	greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date) -- effective_date,
	BETWEEN reg_spc_date AND COALESCE(reg_spc_date_end,greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date))
)
WHERE

	COALESCE(income_date_end,residence_date_end,housing_unit_date_end,housing_unit_subsidy_date_end) IS NULL OR
	least(income_date_end,residence_date_end,housing_unit_date_end,housing_unit_subsidy_date_end) 
	>
	greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date)
;

CREATE OR REPLACE view rent_info_current AS
--SELECT DISTINCT ON (client_id) * FROM rent_info ORDER BY client_id,effective_date DESC;
SELECT * FROM rent_info WHERE effective_date <= target_date() AND COALESCE(effective_date_end,target_date()) >= target_date() ORDER BY effective_date DESC;