CREATE TRIGGER update_comercial_tables
AFTER INSERT
ON Requests FOR EACH ROW
BEGIN
	/*parametros de entrada */
	set @RequestId = NEW.Id;
	set @SuburbId = NEW.SuburbId;
	set @colonia = (select GetColoniaNameByID(@SuburbId));
	set @categoria = cast((select categoria from Colonias where id = @SuburbId) as signed);
	set @categoriaCompleta = (select categoria from Colonias where id = @SuburbId);
	set @buildingStatusPonderation = (select bs.Ponderation from BuildingStatus bs where Id = NEW.BuildingStatusId); 
	set @area = IFNULL(NEW.LandArea,0);
	set @areaC = IFNULL(NEW.ConstructionArea,0);

	set @cochera = (select Cochera from TCVVRN where Categoria = @categoria);
	set @porche = (select porche from TCVVRN where Categoria = @categoria);
	set @teraza = (select Terraza from TCVVRN where Categoria = @categoria);
	set @pasillo = (select Pasillo from TCVVRN where Categoria = @categoria);
	set @utileria = (select utileria from TCVVRN where Categoria = @categoria);
	set @volados = (select volados from TCVVRN where Categoria = @categoria);
	set @tejaban = (select tejaban from TCVVRN where Categoria = @categoria);
	set @mallasombra = (select mallasombra from TCVVRN where Categoria = @categoria);

	/*tablas para valores sobre construccion adicional*/
	-- cochera
	set @CocheraFinM2 = (select IFNULL(FinishedGarage, 0) from Requests where id = @RequestId);
	set @VNRCocheraM2 = @CocheraFinM2 * @cochera * @buildingStatusPonderation;
	-- porche
	set @porcheFinM2 = (select IFNULL(FinishedPorch, 0) from Requests where id = @RequestId);
	set @VNRporcheM2 = @porcheFinM2 * @porche * @buildingStatusPonderation;
	-- teraza
	set @terrazaFinM2 = (select IFNULL(FinishedTerrace, 0) from Requests where id = @RequestId);
	set @VNRterrazaM2 = @terrazaFinM2 * @teraza * @buildingStatusPonderation;
	-- pasillo
	set @pasilloFinM2 = (select IFNULL(FinishedHallway, 0) from Requests where id = @RequestId);
	set @VNRpasilloM2 = @pasilloFinM2 * @pasillo * @buildingStatusPonderation;
	-- utileria
	set @utileriaFinM2 = (select IFNULL(FinishedUtilityRoom, 0) from Requests where id = @RequestId);
	set @VNRutileriaM2 = @utileriaFinM2 * @utileria * @buildingStatusPonderation;
	-- volados
	set @voladoFinM2 = (select IFNULL(FinishedFlown, 0) from Requests where id = @RequestId);
	set @VNRvoladoM2 = @voladoFinM2 * @volados * @buildingStatusPonderation;
	-- tejaban
	set @tejabanFinM2 = (select IFNULL(FinishedRoofTiles, 0) from Requests where id = @RequestId);
	set @VNRtejabanaM2 = @tejabanFinM2 * @tejaban * @buildingStatusPonderation;
	-- mallasombra
	set @mallaFinM2 = (select IFNULL(FinishedShadeMeshSurface, 0) from Requests where id = @RequestId);
	set @VNRmallaM2 = @mallaFinM2 * @mallasombra * @buildingStatusPonderation;

	
	-- ValorActM2Terreno -M2T- Precio por m2 de terreno
	-- ValorActM2Construccion -M2C -  Precio por m2 de construccion

	/*Valores de acuerdo a la Coloina - Comercial*/
	set @precioM2Terreno = (select cast(truncate(avg(ValorActM2Terreno), 2) as SIGNED) as M2T
	 from AppData 
	 where colonia = @colonia);
	 set @precioM2TerrenoFull = (select avg(ValorActM2Terreno) as M2T
	 from AppData 
	 where colonia = @colonia);
	 set @ValorActM2Construccion = (select cast(truncate(avg(ValorActM2Construccion), 2) as SIGNED) as M2T
	 from AppData 
	 where colonia = @colonia);
	 set @ValorActM2ConstruccionFull = (select avg(ValorActM2Construccion) as M2T
	 from AppData 
	 where colonia = @colonia);
	 set @precioMinTerreno = (select cast(truncate(MIN(ValorActM2Terreno), 2) as SIGNED) as M2T
	 from AppData 
	 where colonia = @colonia);
	 set @precioMaxTerreno = (select cast(truncate(MAX(ValorActM2Terreno), 2) as SIGNED) as M2T
	 from AppData 
	 where colonia = @colonia);
	set @promedioM2Terreno = (select cast(truncate(avg(CantMSupTerrenoM2), 2) as SIGNED) as M2T
	 from AppData 
	 where colonia = @colonia);
	
	set @buildingProcessProgress = (select case when isnull(NEW.BuildingProcessId) then 0 else (select Progress from BuildingProcess where Id = NEW.BuildingProcessId) end as result);
	set @buildingCommercialProgress = (select case when isnull(NEW.BuildingProcessId) then 0 else (select CommercialProgress from BuildingProcess where Id = NEW.BuildingProcessId)end as result);
	-- proceso de contruccion 
	set @constructionProcess = (select IFNULL(InConstructionProcess, 0) from Requests where id = @RequestId);
	set @VNRconstructionProcess = @constructionProcess * @ValorActM2Construccion * @buildingCommercialProgress; -- ValorActM2Construccion poner condicion por si colonia es nueva tomaria de la categoria el valor
	-- cochera
	set @CocheraConstM2 = (select IFNULL(InConstructionGarage, 0) from Requests where id = @RequestId);
	set @VNRCocheraConstM2 = @CocheraConstM2 * @cochera * @buildingProcessProgress; -- reemplazar por condicion del inmueble
	-- porche
	set @porcheConstM2 = (select IFNULL(InConstructionPorch, 0) from Requests where id = @RequestId);
	set @VNRporcheConstM2 = @porcheConstM2 * @porche * @buildingProcessProgress;
	-- teraza
	set @terrazaConstM2 = (select IFNULL(InConstructionTerrace, 0) from Requests where id = @RequestId);
	set @VNRterrazaConstM2 = @terrazaConstM2 * @teraza * @buildingProcessProgress;
	-- pasillo
	set @pasilloConstM2 = (select IFNULL(InConstructionHallway, 0) from Requests where id = @RequestId);
	set @VNRpasilloConstM2 = @pasilloConstM2 * @pasillo * @buildingProcessProgress;
	-- utileria
	set @utileriaConstM2 = (select IFNULL(InConstructionUtilityRoom, 0) from Requests where id = @RequestId);
	set @VNRutileriaConstM2 = @utileriaConstM2 * @utileria * @buildingProcessProgress;
	-- volados
	set @voladoConstM2 = (select IFNULL(InConstructionFlown, 0) from Requests where id = @RequestId);
	set @VNRvoladoConstM2 = @voladoConstM2 * @volados * @buildingProcessProgress;
	-- tejaban
	set @tejabanConstM2 = (select IFNULL(InConstructionRoofTiles, 0) from Requests where id = @RequestId);
	set @VNRtejabanConstM2 = @tejabanConstM2 * @tejaban * @buildingProcessProgress;
	-- mallasombra
	set @mallaConstM2 = (select IFNULL(InConstructionShadeMeshSurface, 0) from Requests where id = @RequestId);
	set @VNRmallaConstM2 = @mallaConstM2 * @mallasombra * @buildingProcessProgress;

	/*condicion del inmueble*/

	set @ciPrecioTerrenom2 =  (@area * @precioM2TerrenoFull);
	set @ciPrecioTerrenoConstm2 =  (@areaC * @ValorActM2ConstruccionFull) * @buildingStatusPonderation;
	set @constAdditionaSum = @VNRCocheraM2 + @VNRporcheM2 + @VNRterrazaM2+@VNRpasilloM2+ @VNRutileriaM2+ @VNRvoladoM2+ @VNRtejabanaM2+ @VNRmallaM2;
	set @constProcessSum = ifnull(@VNRCocheraConstM2, 0) + ifnull(@VNRporcheConstM2,0) + ifnull(@VNRterrazaConstM2,0) + IFNULL(@VNRpasilloConstM2,0)+ ifnull(@VNRutileriaConstM2,0)+ ifnull(@VNRvoladoConstM2,0)+ ifnull(@VNRtejabanConstM2,0)+ ifnull(@VNRmallaConstM2,0) + ifnull(@VNRconstructionProcess,0);
	set @valorTotalPropiedad = @ciPrecioTerrenom2 + @ciPrecioTerrenoConstm2 + (@ciPrecioTerrenoConstm2*.10) + @constAdditionaSum + @constProcessSum;
	set @minValPropiedad = @valorTotalPropiedad * .9;
	set @maxValPropiedad = @valorTotalPropiedad * 1.1;
	set @valorRenta = (@valorTotalPropiedad * .08)/12;
	set @TotalPrecioM2Construccion = (@ciPrecioTerrenoConstm2 * .10) + @ciPrecioTerrenoConstm2;
	/*Fin Valores de acuerdo a la Coloina - Comercial*/

	/*Valores de acuerdo a la Categoria - Comercial*/
	set @CatprecioM2Terreno = (select cast(truncate(avg(ValorActM2Terreno), 2) as SIGNED) as M2T
	 from AppData 
	 where Categoria = @categoriaCompleta);
	 set @CatprecioM2TerrenoFull = (select avg(ValorActM2Terreno) as M2T
	 from AppData 
	 where Categoria = @categoriaCompleta);
	 set @CatValorActM2Construccion = (select cast(truncate(avg(ValorActM2Construccion), 2) as SIGNED) as M2T
	 from AppData 
	 where Categoria = @categoriaCompleta);
	 set @CatValorActM2ConstruccionFull = (select avg(ValorActM2Construccion) as M2T
	 from AppData 
	 where Categoria = @categoriaCompleta);
	 set @CatprecioMinTerreno = (select cast(truncate(MIN(ValorActM2Terreno), 2) as SIGNED) as M2T
	 from AppData 
	 where Categoria = @categoriaCompleta);
	 set @CatprecioMaxTerreno = (select cast(truncate(MAX(ValorActM2Terreno), 2) as SIGNED) as M2T
	 from AppData 
	 where Categoria = @categoriaCompleta);
	set @CatpromedioM2Terreno = (select cast(truncate(avg(CantMSupTerrenoM2), 2) as SIGNED) as M2T
	 from AppData 
	 where Categoria = @categoriaCompleta);

	/*condicion del inmueble*/
	set @CatciPrecioTerrenom2 =  (@area * @CatprecioM2TerrenoFull);
	set @CatciPrecioTerrenoConstm2 =  (@areaC * @CatValorActM2ConstruccionFull) * @buildingStatusPonderation;
	set @CatvalorTotalPropiedad = @CatciPrecioTerrenom2 + @CatciPrecioTerrenoConstm2 + (@CatciPrecioTerrenoConstm2*.10) + @constAdditionaSum + @constProcessSum;
	set @CatminValPropiedad = @CatvalorTotalPropiedad * .9;
	set @CatmaxValPropiedad = @CatvalorTotalPropiedad * 1.1;
	set @CatvalorRenta = (@CatvalorTotalPropiedad * .08)/12;
	set @CatTotalPrecioM2Construccion = (@CatciPrecioTerrenoConstm2 * .10) + @CatciPrecioTerrenoConstm2;
	/*Fin Valores de acuerdo a la Categoria - Comercial*/
	/**Inserts**/
	insert into ConstruccionAdicional(CocheraM2,PorcheM2,TerrazaM2,PasilloM2,UtileriaM2,VoladosM2, TejabanesM2,MallasombraM2, RequestId)
	values(@VNRCocheraM2, @VNRporcheM2,@VNRterrazaM2, @VNRpasilloM2, @VNRutileriaM2, @VNRvoladoM2, @VNRtejabanaM2, @VNRmallaM2, @RequestId);

	insert into ConstruccionProceso (CocheraM2,PorcheM2,TerrazaM2,PasilloM2,UtileriaM2,VoladosM2, TejabanesM2,MallasombraM2, RequestId, InConstructionProcess)
	values(@VNRCocheraConstM2, @VNRporcheConstM2,@VNRterrazaConstM2, @VNRpasilloConstM2, @VNRutileriaConstM2, @VNRvoladoConstM2, @VNRtejabanConstM2, @VNRmallaConstM2, @RequestId, @VNRconstructionProcess);

	insert into ValoresAcuerdoColoniaComercial (PrecioM2Terreno, PrecioM2Construccion, 
	ValorTotalPropiedad,ValorMinimoRecomendado, ValorMaximoRecomendado, ValorCasoRenta, PrecioMinM2Terreno, PrecioMaxM2Terreno,
	PromedioM2Terreno, PrecioM2TerrenoInmueble, PrecioM2ConstruccionInmueble, PrecioM2ConstruccionInmuebleTotal, RequestId)
	values(@precioM2Terreno, @ValorActM2Construccion, @valorTotalPropiedad,  @minValPropiedad, @maxValPropiedad, @valorRenta,
	@precioMinTerreno, @precioMaxTerreno, @promedioM2Terreno, @ciPrecioTerrenom2 , @ciPrecioTerrenoConstm2, @TotalPrecioM2Construccion, @RequestId);

	insert into ValoresAcuerdoCategoriaComercial (PrecioM2Terreno, PrecioM2Construccion, 
	ValorTotalPropiedad,ValorMinimoRecomendado, ValorMaximoRecomendado, ValorCasoRenta, PrecioMinM2Terreno, PrecioMaxM2Terreno,
	PromedioM2Terreno, PrecioM2TerrenoInmueble, PrecioM2ConstruccionInmueble, PrecioM2ConstruccionInmuebleTotal, RequestId)
	values(@CatprecioM2Terreno, @CatValorActM2Construccion, @CatvalorTotalPropiedad,  @CatminValPropiedad, @CatmaxValPropiedad, @CatvalorRenta,
	@CatprecioMinTerreno, @CatprecioMaxTerreno, @CatpromedioM2Terreno, @CatciPrecioTerrenom2 , @CatciPrecioTerrenoConstm2, @CatTotalPrecioM2Construccion, @RequestId);
/*fin inserts*/
END;
