--
-- Host: localhost    Database: meteo
-- ------------------------------------------------------


--
-- Dumping routines for database 'meteo'
--
/*!50003 DROP FUNCTION IF EXISTS `unitconv` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `unitconv`(pValue float, pFromUnit integer, pToUnit integer) RETURNS float
BEGIN
  declare result float;
  declare a,b,c float;
  if pFromUnit=pToUnit then 
    set result = pValue;
  else
    select u.a,u.b,u.c into a,b,c from unit_conv u where from_unit=pFromUnit and to_unit=pToUnit;
    set result=b;
    set result = (pValue+a)*b+c;
  end if;
  return result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `submit_value` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float,pTimestamp datetime)
BEGIN
  declare lSTID int;
  declare lSID int;
  declare lSTPID int;
  declare lTime DATETIME;
  declare lDay DATE;
  declare lCnt integer;
  select max(id) into lSTID from sensor_types where st_name=pSType;
  if lSTID is not null then 
    select max(id) into lSID from sensors where st_id=lSTID and s_id=pSID;
    if lSID is not null then
	  select max(id) into lSTPID from st_parameters where st_id=lSTID and st_name=pParam;
      if lSTPID is not null then

        if lSTPID>=0 then

          if pTimestamp is null then
		    set lTime:=current_timestamp();
		  else
            set lTime:=pTimestamp;
          end if;

          insert into sensor_values(sensor_id,parameter_id,timestamp,value)
            values(lSID,lSTPID,lTime,pValue);

          set lDay:=DATE(lTime);
          select count(*) into lCnt from calendar where sensor=lSID and day=lDay;
          if lCnt=0 then
            insert into calendar(day,sensor) values(lDay,lSID);
          end if;

          select count(*) into lCnt from sensors_ranges where sensor=lSID and day=lDay and parameter=lSTPID;
          if lCnt=0 then
            insert into sensors_ranges(day,sensor,parameter,min,max) values (lDay,lSID,lSTPID,pValue,pValue);
          else
            update sensors_ranges
            set
              min=LEAST(min,pValue),
              max=GREATEST(max,pValue)
            where
              day=lDay and sensor=lSID and parameter=lSTPID;
          end if;
        end if;
      else
        insert into error_log(timestamp,text)
        values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
      end if;
    else
      insert into error_log(timestamp,text)
      values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
    end if;
  else
    insert into error_log(timestamp,text)
    values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
  end if;
END ;;
DELIMITER ;


DELIMITER $$
CREATE DEFINER=`admin`@`%` PROCEDURE `Reduce`(pUpTo datetime,pRID integer)
BEGIN
  declare lNewRID integer;
  declare lSeconds integer;

  declare lStart,lEnd timestamp;

  set lNewRID:=pRID+1;
  select seconds into lSeconds from reduce_ranges where id=lNewRID;

  delete from tmp_sensor_values;

  insert into tmp_sensor_values(sensor_id,parameter_id,timestamp,value)
  select sensor_id,parameter_id,from_unixtime(avg(unix_timestamp(timestamp))),avg(value) 
    from sensor_values
    where timestamp<pUpTo and reduced=pRID
    group by sensor_id,parameter_id,floor(unix_timestamp(timestamp)/lSeconds);

  select date(min(timestamp)),date(max(timestamp)) into lStart,lEnd from tmp_sensor_values;

  delete from sensor_values where timestamp<pUpTo and reduced=pRID;

  insert into sensor_values(sensor_id,parameter_id,timestamp,value,reduced)
  select sensor_id,parameter_id,timestamp,value,lNewRID from tmp_sensor_values;

  delete from sensors_ranges
  where day>=lStart and day<=lEnd;

  insert into sensors_ranges(day,sensor,parameter,timestamp,min,max)
  select date(timestamp),sensor_id,parameter_id,max(timestamp),min(value),max(value)
  from sensor_values
  where timestamp>=lStart and timestamp<date_add(lEnd, interval 1 day)
  group by date(timestamp),sensor_id,parameter_id;

  delete from tmp_sensor_values; 

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`admin`@`%` PROCEDURE `Cron_Reduce`(pMonths integer)
BEGIN
  declare lTime datetime;
  select date_sub(date_format(now(),'%Y-%m-01'), interval pMonths month) into lTime;
  call meteo.reduce(lTime,0);
END$$
DELIMITER ;



