CREATE DEFINER=`root`@`%` PROCEDURE `kpi_tec_sc`(IN id_pod INT)
BEGIN
insert ignore into kpi_history (id_kpi_info, id_pod, id_zo, kpi_value) SELECT 2, id_pod, id_area_omogenea, kpi_tec
from
(with agg_by_zo as (
select ep.sc_POD_id as id_pod, ha.id as id_area_omogenea, ha.HomogeneousAreaID as nome_area_omogenea, SUM(lss.NominalLuminousFlux/lss.NominalPower) as PL_SL_FLU_PL_SL_POW, cam.eff_luminosa as eff_cam,
		SUM(lss.NominalLuminousFlux/lss .NominalPower)/ SUM(cam.eff_luminosa) as kpi_tec, (SUM(lss.NominalLuminousFlux / lss .NominalPower)/ cam.eff_luminosa) * lsd.TerminalPower as Pi_KPIi,
			 SUM(lss.NominalPower) as pw_tot-- SUM(lss.NominalLuminousFlux / lss .NominalPower) / SUM(cam_eff_lampade_2018.eff_luminosa) AS KPI_tecnologico
		FROM sc_ElectricPanel ep
		JOIN sc_ElectricPanelGeneralData epgd ON ep.id = epgd.sc_ElectricPanel_id
        JOIN sc_LightSpot ls ON ep.id = ls.sc_ElectricPanel_id
		JOIN sc_LightSpotEquipment lse ON ls.id = lse.sc_LightSpot_id
        JOIN sc_LightSpotDevice lsd ON lse.id = lsd.sc_LightSpotEquipment_id
        JOIN sc_LightSource lss ON lsd.id = lss.sc_LightSpotDevice_id
        JOIN sc_HomogeneousArea ha ON ha.id = ls.sc_HomogeneousArea_id
		JOIN cam_eff_lampade_2018 cam ON cam.PL_SL_TY = lss.LightSourceType
			AND cam.PL_SL_POW = (SELECT
				b.PL_SL_POW
			FROM
				cam_eff_lampade_2018 AS b
			WHERE
				b.PL_SL_TY = lss.LightSourceType
			ORDER BY ABS(b.PL_SL_POW - lss.NominalPower) ASC
			LIMIT 1)
		WHERE
			ep.sc_POD_id = id_pod
		GROUP BY ha.id
)
select id_pod, id_area_omogenea, kpi_tec -- round(sum(Pi_KPIi)/ SUM(pw_tot),2) into kpi
from agg_by_zo WHERE eff_cam not like 'null') as t;
END