/*	===============================================================================================
	Project: ICST | BI2I | Tax Calculator
	-----------------------------------------------------------------------------------------------
	Date: Aug 11, 2017
	Details: 
		1) 	Compute short & defult potential 
	============================================================================================= */

	
/*	Step 01: Compute "years_defaulted" as the year in which tax was last paid
	=============================================================================================== 
*/
create table bi2i_tax_calc.int_007_final_potential_using_new_tax_calc as 
select * from bi2i_tax_calc.int_005_potential_calucation;
	
alter table bi2i_tax_calc.int_007_final_potential_using_new_tax_calc add years_defaulted double;
update bi2i_tax_calc.int_007_final_potential_using_new_tax_calc
set	years_defaulted = (2016 - last_year);





/*	Step 02: Replace ideal tax as per new tax calculator for data score = 10
	-----------------------------------------------------------------------------------------------
	Change in logic as captured in the mail sent on Aug 30, 2017
	===============================================================================================
*/
update bi2i_tax_calc.int_007_final_potential_using_new_tax_calc set 
ideal_tax_08 = (case when data_score = 10 then new_ideal_tax_2008 else ideal_tax_08 end),
ideal_tax_09 = (case when data_score = 10 then new_ideal_tax_2008 else ideal_tax_09 end),
ideal_tax_10 = (case when data_score = 10 then new_ideal_tax_2008 else ideal_tax_10 end),
ideal_tax_11 = (case when data_score = 10 then new_ideal_tax_2008 else ideal_tax_11 end),
ideal_tax_12 = (case when data_score = 10 then new_ideal_tax_2008 else ideal_tax_12 end),
ideal_tax_13 = (case when data_score = 10 then new_ideal_tax_2008 else ideal_tax_13 end),
ideal_tax_14 = (case when data_score = 10 then new_ideal_tax_2008 else ideal_tax_14 end),
ideal_tax_15 = (case when data_score = 10 then new_ideal_tax_2008 else ideal_tax_15 end),
ideal_tax_16 = (case when data_score = 10 then new_ideal_tax_2016 else ideal_tax_16 end),
ideal_tax_17 = (case when data_score = 10 then new_ideal_tax_2016 else ideal_tax_17 end);





/*	Step 02: Create a new column "Actual Tax Payable" for each property
	-----------------------------------------------------------------------------------------------
	Change in logic as captured in the mail sent on Aug 30, 2017
	===============================================================================================
*/
alter table bi2i_tax_calc.int_007_final_potential_using_new_tax_calc 
add tax_payable_2008 double,
add tax_payable_2009 double,
add tax_payable_2010 double,
add tax_payable_2011 double,
add tax_payable_2012 double,
add tax_payable_2013 double,
add tax_payable_2014 double,
add tax_payable_2015 double,
add tax_payable_2016 double,
add tax_payable_2017 double;


update bi2i_tax_calc.int_007_final_potential_using_new_tax_calc set 
tax_payable_2008 = ideal_tax_08,
tax_payable_2009 = ideal_tax_09 + (tax_payable_2008 - tax_paid_08) *
					(case when (tax_paid_08 >= 0.95 * tax_payable_2008) then 0 else 1.24 end),
tax_payable_2010 = ideal_tax_10 + (tax_payable_2009 - tax_paid_09) * 
					(case when (tax_paid_09 >= 0.95 * tax_payable_2009) then 0 else 1.24 end),
/* Tax cannot be collected for pending amounts above 6 years i.e before 2011 */					
tax_payable_2011 = ideal_tax_11,              
tax_payable_2012 = ideal_tax_12 + (tax_payable_2011 - tax_paid_11) * 
					(case when (tax_paid_11 >= 0.95 * tax_payable_2011) then 0 else 1.24 end),
tax_payable_2013 = ideal_tax_13 + (tax_payable_2012 - tax_paid_12) *
					(case when (tax_paid_12 >= 0.95 * tax_payable_2012) then 0 else 1.24 end),
tax_payable_2014 = ideal_tax_14 + (tax_payable_2013 - tax_paid_13) *
					(case when (tax_paid_13 >= 0.95 * tax_payable_2013) then 0 else 1.24 end),
tax_payable_2015 = ideal_tax_15 + (tax_payable_2014 - tax_paid_14) *
					(case when (tax_paid_14 >= 0.95 * tax_payable_2014) then 0 else 1.24 end),
tax_payable_2016 = ideal_tax_16 + (tax_payable_2015 - tax_paid_15) *
					(case when (tax_paid_15 >= 0.95 * tax_payable_2015) then 0 else 1.24 end),
tax_payable_2017 = ideal_tax_17 + (tax_payable_2016 - tax_paid_16) *
					(case when (tax_paid_16 >= 0.95 * tax_payable_2016) then 0 else 1.24 end);


	
	
	
/*	Step 03: Identify tax potential for each property
	-----------------------------------------------------------------------------------------------
	Change in logic as captured in the mail sent on Aug 30, 2017
	===============================================================================================
	
*/
alter table bi2i_tax_calc.int_007_final_potential_using_new_tax_calc add tax_default_potential double;

update bi2i_tax_calc.int_007_final_potential_using_new_tax_calc set 
tax_default_potential = (tax_payable_2016) - (tax_paid_16);