<?php

$query=$_REQUEST['query'];

$client_ip = $_SERVER["REMOTE_ADDR"];

include('config_local.php');

function startsWith($haystack, $needle)
{
     $length = strlen($needle);
     return (substr($haystack, 0, $length) === $needle);
}

if (! ($db = new PDO("mysql:host=$mysql_host;port=$mysql_port;dbname=$mysql_schema",$mysql_user,$mysql_pwd,array( PDO::ATTR_PERSISTENT => false)))) {
  die($err);
}  

$db -> exec('SET CHARACTER SET utf8');

$auth_token = $_COOKIE["auth-token"];
$auth = 0;

if ($auth_token) {

  $sql = "
    SELECT COUNT(*) AS auth FROM tokens WHERE str=:s and expires>now()
  ";
  
  $q = $db -> prepare( $sql );
  $q -> bindParam(':s',$auth_token,PDO::PARAM_INT);
  $q -> execute();

  $res = [];

  $row = $q -> fetch(PDO::FETCH_ASSOC);
  $auth = $row['auth'];

} 

if ($auth || (strpos($client_ip, "192.168.") === 0) || (strpos($client_ip, "10.8.") === 0)) {
  $filter = '';
} else {
  $filter = ' and s.is_public=1';
}

$hash = md5($_SERVER['QUERY_STRING'].":".$filter);
$redis = new Redis();
$redis->pconnect('127.0.0.1', 6379);
$results = $redis->get('meteo-'.$hash);
if ($results) {

  $results = unserialize($results);
  print(json_encode($results));
  return;

}

function getYears($db) {

  $sql = "
    SELECT DISTINCT DATE_FORMAT(day,'%Y') y FROM sensors_ranges ORDER BY y DESC
  ";
  
  $q = $db -> prepare( $sql );
  $q -> execute();

  $res = [];

  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
    array_push($res, $row['y']);
  }

  return $res;  

}

function getMonths($db,$year) {

  $y = intval($year);

  $sql = "
    SELECT DISTINCT DATE_FORMAT(day,'%m') m FROM sensors_ranges WHERE day>=STR_TO_DATE('".strval($y)."-01-01','%Y-%m-%d') and day<STR_TO_DATE('".strval($y+1)."-01-01','%Y-%m-%d') ORDER BY m DESC
  ";

  $q = $db -> prepare( $sql );
  $q -> execute();

  $res = [];

  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
    array_push($res, $row['m']);
  }

  return $res;  

}

function getDays($db,$year,$month) {

  $y = intval($year);
  $m = intval($month);

  $sql = "
    SELECT DISTINCT DATE_FORMAT(day,'%d') d FROM sensors_ranges WHERE day>=STR_TO_DATE('".strval($y)."-".strval($m)."-01','%Y-%m-%d') and DATE_ADD(STR_TO_DATE('".strval($y)."-".strval($m)."-01','%Y-%m-%d'), INTERVAL 1 MONTH) ORDER BY d DESC
  ";

  $q = $db -> prepare( $sql );
  $q -> execute();

  $res = [];

  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
    array_push($res, $row['d']);
  }

  return $res;  

}

function getSensors($db,$year,$month,$day) {

  global $filter;

  $y = intval($year);
  $m = intval($month);
  $d = intval($day);

  $sql = "
    SELECT DISTINCT 
      CONCAT(s_id,'.',t.st_abbr,'.',p.st_name) id
    FROM
      sensors_ranges r, sensors s, sensor_types t, st_parameters p
    WHERE 
      r.sensor=s.id and r.parameter=p.id and s.st_id=t.id and p.st_id=t.id ".$filter."
      and r.day=STR_TO_DATE('".strval($y)."-".strval($m)."-".strval($d)."','%Y-%m-%d')
    UNION
    SELECT DISTINCT
      CONCAT(s_id,'.',t.st_abbr,'.*') id
    FROM
      sensors_ranges r, sensors s, sensor_types t
    WHERE 
      r.sensor=s.id and s.st_id=t.id and t.st_common_description is not null ".$filter."
      and r.day=STR_TO_DATE('".strval($y)."-".strval($m)."-".strval($d)."','%Y-%m-%d')
  ";

  $q = $db -> prepare( $sql );
  $q -> execute();

  $res = [];

  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
    array_push($res, $row['id']);
  }

  return $res;  

}

function getCurrentValues($db,$s_id,$p_id) {

  global $filter;

  $sql = "
    SELECT 
      u.id stored_unit,du.id display_unit 
    FROM 
      sensors s,sensor_types t,st_parameters p,units u,units du
    WHERE s.st_id=t.id and p.st_id=t.id and p.st_unit=u.id and u.unit_group=du.unit_group and du.is_default=1 and s.id=:id and p.id=:param
  ";

  $q = $db -> prepare( $sql );
  $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
  $q -> bindParam(':param',$p_id,PDO::PARAM_INT);
  $q -> execute();
  $units = $q -> fetch(PDO::FETCH_ASSOC);

  $sql = "
      SELECT 
        CONCAT(subset.t,'5:00') t,UnitConv(avg(subset.y),:stored,:display) y
      FROM (
        SELECT 
          substr(date_format(timestamp,'%Y-%m-%dT%H:%i'),1,15) t,value y
        FROM 
          sensor_values
        WHERE 
          sensor_id = :id and parameter_id=:param 
          and timestamp>adddate(now(),-1)
        ) subset 
      GROUP BY subset.t
      ORDER BY subset.t
    ";
  
  $q = $db -> prepare( $sql );
  $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
  $q -> bindParam(':param',$p_id,PDO::PARAM_INT);
  $q -> bindParam(':stored',$units['stored_unit'],PDO::PARAM_INT);
  $q -> bindParam(':display',$units['display_unit'],PDO::PARAM_INT);
  $q -> execute();

  return $q -> fetchAll(PDO::FETCH_ASSOC);
  
}

function getCurrent($db,$id,$type,$param) {

  global $filter;

  if ($param == "*") {

    $sql = "
      SELECT s.id s_id,p.id p_id, p.st_name p_name 
      FROM sensors s,sensor_types t, st_parameters p
      WHERE s.st_id=t.id and p.st_id=t.id and s_id=:id and t.st_abbr=:type ".$filter."
    ";

    $q = $db -> prepare( $sql );
    $q -> bindParam(':id',$id,PDO::PARAM_STR);
    $q -> bindParam(':type',$type,PDO::PARAM_STR);
    $q -> execute();
    $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);

    $results = array();

    foreach ($sensors as $sensor) {

      $results[$sensor['p_name']] = getCurrentValues($db,$sensor['s_id'],$sensor['p_id']);
    
    }

    return $results;
  
  } else {

    $sql = "
      SELECT s.id s_id,p.id p_id 
      FROM sensors s,sensor_types t, st_parameters p
      WHERE s.st_id=t.id and p.st_id=t.id and s_id=:id and t.st_abbr=:type and p.st_name=:param ".$filter."
    ";

    $q = $db -> prepare( $sql );
    $q -> bindParam(':id',$id,PDO::PARAM_STR);
    $q -> bindParam(':type',$type,PDO::PARAM_STR);
    $q -> bindParam(':param',$param,PDO::PARAM_STR);
    $q -> execute();
    $sensor = $q -> fetch(PDO::FETCH_ASSOC);

    return getCurrentValues($db,$sensor['s_id'],$sensor['p_id']);

  }

}

function getArchiveValues($db,$year,$month,$day,$s_id,$p_id) {

  global $filter;

  $sql = "
    SELECT 
      u.id stored_unit,du.id display_unit 
    FROM 
      sensors s,sensor_types t,st_parameters p,units u,units du
    WHERE s.st_id=t.id and p.st_id=t.id and p.st_unit=u.id and u.unit_group=du.unit_group and du.is_default=1 and s.id=:id and p.id=:param
  ";

  $q = $db -> prepare( $sql );
  $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
  $q -> bindParam(':param',$p_id,PDO::PARAM_INT);
  $q -> execute();
  $units = $q -> fetch(PDO::FETCH_ASSOC);

  $sql = "
    SELECT 
      CONCAT(subset.t,'5:00') t,UnitConv(avg(subset.y),:stored,:display) y
    FROM (
      SELECT 
        substr(date_format(timestamp,'%Y-%m-%dT%H:%i'),1,15) t,value y
      FROM 
        sensor_values
      WHERE 
        sensor_id = :id and parameter_id=:param 
        and timestamp>=STR_TO_DATE(:d,'%Y-%m-%d')
        and timestamp<DATE_ADD(STR_TO_DATE(:d,'%Y-%m-%d'), interval 1 day)
      ) subset 
    GROUP BY subset.t
    ORDER BY subset.t
  ";

  $date = $year.'-'.$month.'-'.$day;

  $q = $db -> prepare( $sql );
  $q -> bindParam(':id',$s_id,PDO::PARAM_INT);
  $q -> bindParam(':param',$p_id,PDO::PARAM_INT);
  $q -> bindParam(':d',$date,PDO::PARAM_STR);
  $q -> bindParam(':stored',$units['stored_unit'],PDO::PARAM_INT);
  $q -> bindParam(':display',$units['display_unit'],PDO::PARAM_INT);
  $q -> execute();

  return $q -> fetchAll(PDO::FETCH_ASSOC);

}

function getArchive($db,$year,$month,$day,$id,$type,$param) {

  global $filter;

  $y = intval($year);
  $m = intval($month);
  $d = intval($day);
  
  $date = strval($y).'-'.strval($m).'-'.strval($d);

  if ($param == "*") {

    $sql = "
      SELECT s.id s_id,p.id p_id, p.st_name p_name 
      FROM sensors s,sensor_types t, st_parameters p
      WHERE s.st_id=t.id and p.st_id=t.id and s_id=:id and t.st_abbr=:type ".$filter."
    ";

    $q = $db -> prepare( $sql );
    $q -> bindParam(':id',$id,PDO::PARAM_STR);
    $q -> bindParam(':type',$type,PDO::PARAM_STR);
    $q -> execute();
    $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);

    $results = array();

    foreach ($sensors as $sensor) {

      $result[$sensor['p_name']] = getArchiveValues($db,$year,$month,$day,$sensor['s_id'],$sensor['p_id']);

    }
    
    return $result;
    
  } else {

    $sql = "
      SELECT s.id s_id,p.id p_id 
      FROM sensors s,sensor_types t, st_parameters p
      WHERE s.st_id=t.id and p.st_id=t.id and s_id=:id and t.st_abbr=:type and p.st_name=:param ".$filter."
    ";

    $q = $db -> prepare( $sql );
    $q -> bindParam(':id',$id,PDO::PARAM_STR);
    $q -> bindParam(':type',$type,PDO::PARAM_STR);
    $q -> bindParam(':param',$param,PDO::PARAM_STR);
    $q -> execute();
    $sensor = $q -> fetch(PDO::FETCH_ASSOC);

    return getArchiveValues($db,$year,$month,$day,$sensor['s_id'],$sensor['p_id']);

  }

}

function getProps($db, $localNet) {

  global $filter;
  
  $sql = "
    SELECT 
      CONCAT(s_id,'.',t.st_abbr,'.',p.st_name) sensor_id,
      p.st_description name,
      du.name_short unit,
      du.prec prec,
      pl.place_name,
      p.st_line_color color
    FROM 
      sensors s, sensor_types t, st_parameters p,units u,units du,places pl
    WHERE 
      s.st_id=t.id and p.st_id=t.id and p.st_unit=u.id and u.unit_group=du.unit_group and du.is_default=1 and pl.idplaces=s.place_id ".$filter."
  ";
  
  $q = $db -> prepare( $sql );
  $q -> execute();
  $reply = [ 
    "names" => [], 
    "colors" => [], 
    "units" => [], 
    "scale" => [], 
    "places" => [],
    "fonts" => [ 
      "axes" => [ "color" => "black", "size" => 16, "style" => "normal" ], 
      "legend" => [ "color" => "black", "size" => 16, "style" => "normal" 
  ] ] ];

  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
    $reply["names"][$row["sensor_id"]] = $row["name"];
    $reply["colors"][$row["sensor_id"]] = $row["color"];
    $reply["units"][$row["sensor_id"]] = $row["unit"];
    $reply["places"][$row["sensor_id"]] = $row["place_name"];
    $reply["scale"][$row["sensor_id"]] = [ 0 => 1.0, 1 => $row["prec"] ];
  }

  $sql = "
    SELECT DISTINCT
      CONCAT(s_id,'.',t.st_abbr,'.*') sensor_id,
      t.st_common_description name,
      pl.place_name
    FROM
      sensors s, sensor_types t, places pl
    WHERE 
      pl.idplaces=s.place_id and s.st_id=t.id and t.st_common_description is not null ".$filter."
  ";

  $q = $db -> prepare( $sql );
  $q -> execute();
  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
    $reply["names"][$row["sensor_id"]] = $row["name"];
    $reply["places"][$row["sensor_id"]] = $row["place_name"];
  }
  
  return $reply;
  
}

function getState($db, $localNet) {

  global $filter;
  
  $sql = "
    SELECT 
      DISTINCT 
        s.s_id as sensor_id,
        st.st_abbr,
        v.sensor as sensor_int_id,
        pl.place_name s_description,
        p.id as param_id,
        p.st_name as param_name,
        p.st_description,
        s.place_id,
        u.id stored_unit_id,
        du.id unit_id
    FROM 
      sensors_ranges v,st_parameters p,sensors s,places pl,sensor_types st,units u,units du
    WHERE 
      v.timestamp>addtime(now(), -43200) 
      and s.st_id=st.id
      and v.sensor=s.id
      and s.st_id=st.id
      and v.parameter=p.id  
      and s.st_id=p.st_id
      and p.id>=0
      and s.place_id=pl.idplaces
      and p.st_unit=u.id 
      and u.unit_group=du.unit_group
      and du.is_default=1
      ".$filter."
    ORDER BY
      s_description,sensor_id,param_id
  ";

  $q = $db -> prepare( $sql );
  $q -> execute();

  $reply = [];

  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {

    $sensor_id = $row['sensor_id'];
    $st_id = $row['st_abbr'];
    $sensor_int_id = $row['sensor_int_id'];
    $param_id = $row['param_name'];
    $param_int_id = $row['param_id'];
    $unit_id = $row['unit_id'];
    $stored_unit_id = $row['stored_unit_id'];
    $place_description = $row['s_description'];

    $sql_last_val = "
      SELECT UnitConv(value,".$stored_unit_id.",".$unit_id.") as val,timestamp
      FROM
        sensor_values
      WHERE 
        sensor_id = ".$sensor_int_id." and parameter_id=".$param_int_id."
      ORDER BY
        timestamp DESC
      LIMIT 1
    ";

    $qv = $db -> prepare( $sql_last_val );
    $qv -> execute();

    $v_row = $qv -> fetch(PDO::FETCH_ASSOC);
    
    $value = $v_row["val"];
    $timestamp = $v_row["timestamp"];

    if (! array_key_exists($place_description,$reply)) {
      $reply[$place_description] = [];
    }

    if (! array_key_exists($sensor_id,$reply[$place_description])) {
      $reply[$place_description][$sensor_id] = [];
    }
    
    if (! array_key_exists($st_id,$reply[$place_description][$sensor_id])) {
      $reply[$place_description][$sensor_id][$st_id] = [];
    }

    $reply[$place_description][$sensor_id][$st_id][$param_id] = $value;
    $reply[$place_description][$sensor_id]['timestamp'] = $timestamp;

  }

  return $reply;
  
}

function getPanel($db) {

  $sql = "
    SELECT 
      DISTINCT 
        v.sensor as sensor_int_id,
        p.id as param_id,
        p.st_name as param_name,
        p.st_description,
        u.id stored_unit_id,
        du.id unit_id
    FROM 
      sensors_ranges v,st_parameters p,sensors s,places pl,sensor_types st,units u,units du
    WHERE 
      v.timestamp>addtime(now(), -43200) 
      and s.st_id=st.id
      and v.sensor=s.id
      and s.st_id=st.id
      and v.parameter=p.id  
      and s.st_id=p.st_id
      and p.id>=0
      and s.place_id=pl.idplaces
      and p.st_unit=u.id 
      and u.unit_group=du.unit_group
      and du.is_default=1
      and (s.s_id,p.st_name) in (select sensor_id,param_name from panel_sensors)
    ORDER BY
      param_id
    ";

  $q = $db -> prepare( $sql );
  $q -> execute();

  $reply = [];

  while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {

    $sensor_id = $row['sensor_id'];
    $sensor_int_id = $row['sensor_int_id'];
    $param_id = $row['param_name'];
    $param_int_id = $row['param_id'];
    $unit_id = $row['unit_id'];
    $stored_unit_id = $row['stored_unit_id'];

    $sql_last_val = "
      SELECT UnitConv(value,".$stored_unit_id.",".$unit_id.") as val,timestamp
      FROM
        sensor_values
      WHERE 
        sensor_id = ".$sensor_int_id." and parameter_id=".$param_int_id."
        and timestamp > NOW() - interval 30 minute
      ORDER BY
        timestamp DESC
      LIMIT 1
    ";

    $qv = $db -> prepare( $sql_last_val );
    $qv -> execute();

    $v_row = $qv -> fetch(PDO::FETCH_ASSOC);
    
    $value = $v_row["val"];

    if ($value && $value != "null") {
      $reply[strtoupper($param_id)] = $value;
    }

  }

  return $reply;
  
}

$expire = 60;

if ($query == 'props') {

  $reply = getProps($db, $local_net);
  $expire = 60;

} elseif ($query == 'state') {

  $reply = getState($db, $local_net);

} elseif ($query == 'panel') {

  $reply = getPanel($db);

} elseif (startsWith($query,'get/')) {

  $sensor = explode('/',substr($query,strlen('get/')));
  $reply = getCurrent($db,$sensor[0],$sensor[1],$sensor[2]);

} elseif ($query == 'years') {

  $reply = getYears($db);
  $expire = 3600;

} elseif (startsWith($query,'months/')) {

  $date = explode('/',substr($query,strlen('months/')));
  $reply = getMonths($db,$date[0]);
  $expire = 3600;

} elseif (startsWith($query,'days/')) {

  $date = explode('/',substr($query,strlen('days/')));
  $reply = getDays($db,$date[0],$date[1]);
  $expire = 3600;

} elseif (startsWith($query,'sensors/')) {

  $date = explode('/',substr($query,strlen('sensors/')));
  $reply = getSensors($db,$date[0],$date[1],$date[2]);
  $expire = 3600;

} elseif (startsWith($query,'get-archive/')) {

  $path = explode('/',substr($query,strlen('get-archive/')));
  $reply = getArchive($db,$path[0],$path[1],$path[2],$path[3],$path[4],$path[5]);
  $expire = 14400;

}

if ($reply) {

  $redis->set('meteo-'.$hash, serialize($reply));
  $redis->expire('meteo-'.$hash, $expire);
  
  print(json_encode($reply));

}

?>