<?php
/*  provides a table to store environmental data submitted by bosch bme sensors
 *
 *  the table radondb.bosch_bme680 stores a timestamp time as seconds since unix epoch,
 *  the sensor sending the data is stored, eg: 'H' is stored as 72,
 *  and the sensors readings as integers, [celsius, humidity, pressure as pascals,
 *  gas resistance, indoor air quality index, and iaq accuracy]
 *
 *  to use as viewer:
 *    provide name for the Bosch BME680 sensor and optionally a unix epoch range (...)
 *
 *    function name(parameters)              returns array:
 *    get_BME860_humidityData('H', ...):     ["time"=>{time}, "reading"=>{reading},  ...]
 *    get_BME860_celsiusData('H', ...):      ["time"=>{time}, "reading"=>{reading},  ...]
 *    get_BME860_pressureData('H', ...):     ["time"=>{time}, "reading"=>{reading},  ...]
 *    get_BME860_gasData('H', ...):          ["time"=>{time}, "reading"=>{reading},  ...]
 *    get_BME860_iaqData('H', ...):          ["time"=>{time}, "reading"=>{reading},  ...]
 *    get_BME860_iaqAccuracyData('H', ...):  ["time"=>{time}, "reading"=>{reading},  ...]
 *    get_BME860_krumaylData('H', ...):      ["time"=>{time}, "reading"=>{reading},  ...]
 *    get_BME860_ukrumaylData('H', ...):     ["time"=>{time}, "reading"=>{reading},  ...]
 *    get_BME860_wesDust('H', ...):          ["time"=>{time}, "reading"=>{reading},  ...]
 *
 *  to use as submitter:
 *      use HTTP POST, data will be saved to radondb.bosch_bme680
 */
define("BME680_COLUMNS", "time sensor celsius humidity pascal gas_resistance indoor_air_quality iaq_accuracy");

function save() {
    // connect to mysql database radondb
    $db = mysql_connect("localhost", "root", "secom8703");
    mysql_select_db("radondb", $db);

    $columns = explode(" ", BME680_COLUMNS);

    // make sure events table exists
    if(false === mysql_query("SELECT 1 FROM bosch_bme680 LIMIT 1", $db)) {
        $create_cols = implode(" INTEGER, ", $columns) . " INTEGER";
        mysql_query("CREATE TABLE bosch_bme680 ({$create_cols}, CONSTRAINT reading UNIQUE (time, sensor))", $db);
    }
    // prepare data - first store column names, then match column names to bme680 submitted data
    $table_cols = implode(",", $columns);
    if(false !== ($key = array_search("celsius", $columns))) $columns[$key] = "temperature";
    if(false !== ($key = array_search("pascal", $columns))) $columns[$key] = "pressure";
    if(false !== ($key = array_search("indoor_air_quality", $columns))) $columns[$key] = "iaq_(accuracy)";
    $values = post2toast($_POST, $columns);

    // data should just be a bunch of integers
    $values = implode(",", $values);
    if(false === mysql_query("INSERT INTO bosch_bme680 ({$table_cols}) VALUES ({$values})", $db)) {
        // event previously recorded
    } else /* event sucessfully stored */;

    // mysql needs clean-up
    mysql_close($db);
    exit("thanks");
}

// convert alphabetic sensor  name to integer [A..Z] to [65..90]
function eMa_sensorName($str) {
    return (int) ord(strtoupper($str[0]));
}

function post2toast($post, $columns) {
    // either manually provide time or overwrite it
    $post["time"] = time();
    // convert sensor ASCII character (A..Z) into integer value represented using a string
    if(isset($post["sensor"])) $post["sensor"] = strval(eMa_sensorName($post["sensor"][0])) . ' ';

    $arr = array();
    foreach($columns as $k => $v) {
        if(isset($post[$v])) $arr[$k] = trim($post[$v]);
    }
    // handle splitting iaq into index and accuracy
    if(false !== ($key = array_search("iaq_accuracy", $columns))) {
        if(false !== ($key2 = array_search("iaq_(accuracy)", $columns))) {
            $chomp = strtok($arr[$key2], ' ');
            $arr[$key] = trim(strtok(''), " \n()") . /* needs whitespace for later */ ' ';
        }
    }
    // convert all values into integers, round floats
    foreach($arr as $k => $v) {
        $arr[$k] = (int) round(getFirstWord($v));
    }
    return $arr;
}

// confusing, word is meant to be first number
function getFirstWord($v) {
    $v = str_split(strtok(trim($v), ' '), 1);
    $s = "";
    foreach($v as $t) {
        if('.' === $t) { $s .= '.'; continue; }
        if((0x29 < ord($t)) && (0x40 > ord($t))) $s .= $t;
        else break;
    }
    return $s;
}

// save data sent to us (HTTP POST terminate here)
if(!empty($_POST)) save();

// otherwise we are providing some additional functionality ...

define("GET_LAST_READING", PHP_INT_MAX);
function getColumnHelper($type, $sensor = 'H', $queryOptions = NULL) {

    /*  Prior to using query options start and end could have been used to
     *  specify sensor samples between two dates, this feature was not really
     *  used.  Query Options overlap this feature.  Under normal conditions
     *  using Query Options also retrieves data from a cache rather than from
     *  the MySQL database.
     *
     *  using a default QueryOptions object should not use the cache, if you
     *  want to still make use of start and end, setting them in the QueryOptions
     *  is supported
     */
    $start = false;
    $end = false;

    // expand query options if they are provided
    if((NULL !== $queryOptions) and ("QueryOptions" ===  get_class($queryOptions))) {
        if($queryOptions->useCache()) {
            $rows = CACHE_DB::getCachedData($sensor, $type, 
                $queryOptions->zoom,
                $queryOptions->fir,
                $queryOptions->start,
                $queryOptions->end
            );
            //return $rows;
            // add the most recent non-cached bosch bme680 sensor readings [mySQL]
            $lastReading = bosch_bme_680_query($type, $sensor, GET_LAST_READING);
            if($lastReading) {
                if(defined("ARRAY_OF_TIME_READING_PAIRS")) {
                    $rows[] = array_pop($lastReading);
                } else {
                    $rows[0][] = $lastReading[0]["time"];
                    $rows[1][] = $lastReading[0]["reading"];
                }
            }
            return $rows;
        } // else - possibly get the start/end, use MySQL instead
        /*  continuing down this path will just use the MySQL database, we
         *  can try to get the start and end seconds since UNIX epoch from
         *  the Query Options though...
         */
        $start = $queryOptions->start;
        $end = $queryOptions->end;
    } // else
    return bosch_bme_680_query($type, $sensor, $start, $end);
}
function bosch_bme_680_query($type, $sensor = 'H', $start = false, $end = false) {
    // check if we are only supposed to get last reading, do this before start is modified
    $lastReading = GET_LAST_READING === $start;
    // create a list of columns that are okay to query
    $okay_columns = explode(" ", BME680_COLUMNS);
    if(false !== ($k = array_search("time", $okay_columns))) unset($okay_columns[$k]);
    if(false !== ($k = array_search("sensor", $okay_columns))) unset($okay_columns[$k]);

    // cancel this query if type is invalid
    if(false === array_search($type, $okay_columns)) {
        error_log("the requested type, {$type} must be one of: " . implode(',', $okay_columns));
        return array(/* empty array for you */);
    }

    // the sensor that you want has a different name, I will look it up
    $sensor = strval(eMa_sensorName($sensor));
    
    # connect to mysql database radondb
    $db = mysql_connect("localhost", "root", "secom8703");
    mysql_select_db("radondb", $db);

    // prevent injection in time interval input
    $start = intval($start);
    $end = intval($end);
    $and = (/* php zero is false */ $start and $end) ?"time >= {$start} AND time <= {$end} AND " :"";
    // add optional time interval to query
    $sql  = "SELECT time, {$type} AS reading FROM `bosch_bme680` WHERE {$and}";
    // lookup by sensor, make sure query sorts results oldest event to newest
    $sql .= "sensor = {$sensor} ORDER BY time ASC";

    if($lastReading) {
        $sql  = "SELECT time, {$type} AS reading FROM `bosch_bme680` WHERE ";
        $sql .= "sensor = {$sensor} ORDER BY time DESC LIMIT 1";
    }

    if(false === ($result = mysql_query($sql, $db))) {
        fwrite(STDERR, "{$type} query failed:" . PHP_EOL . mysql_error($db));
        return false;
    }

    $rows = array();
    while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $rows[] = $row;
        //printRow($row);
    }
    mysql_free_result($result);

    # mysql needs clean-up
    mysql_close($db);
    
    return $rows;
}

// returns humidity percentage readings, with optional time range (unix epoch)
function get_BME860_humidityData($sensor = 'H', $qo = NULL) {
    return getColumnHelper("humidity", $sensor, $qo);
}
// returns temperature Celsius readings, with optional time range (unix epoch)
function get_BME860_celsiusData($sensor = 'H', $qo = NULL) {
    return getColumnHelper("celsius", $sensor, $qo);
}
// returns pressure pascal readings, with optional time range (unix epoch)
function get_BME860_pressureData($sensor = 'H', $qo = NULL) {
    return getColumnHelper("pascal", $sensor, $qo);
}
// returns gas resistance readings, with optional time range (unix epoch)
function get_BME860_gasData($sensor = 'H', $qo = NULL) {
    return getColumnHelper("gas_resistance", $sensor, $qo);
}
// returns indoor air quality index readings, with optional time range (unix epoch)
function get_BME860_iaqData($sensor = 'H', $qo = NULL) {
    return getColumnHelper("indoor_air_quality", $sensor, $qo);
}
// returns IAQ accuracy readings, with optional time range (unix epoch)
function get_BME860_iaqAccuracyData($sensor = 'H', $qo = NULL) {
    return getColumnHelper("iaq_accuracy", $sensor, $qo);
}
// returns temperature Fahrenheit readings, with optional time range (Unix epoch)
function get_BME860_fahrenheitData($sensor = 'H', $qo = NULL) {
    $arr = getColumnHelper("celsius", $sensor, $qo);
    array_walk($arr, function(&$v) { $v["reading"] = 32 + round($v["reading"] * 9 / 5); });
    return $arr;
}

// crop integer values to 0..100
function kug($x) { return $x > 0 ?$x < 100 ?$x :100 :0; }

/*  returns realative pressure readings, with optional time range (unix epoch)
 *
 *  - a common elevation for people  living in Iowa might be 270 meters above sea level.
 *  - the highest pressure in Iowa occurred in 1989 and was recorded as 105452Pa
 *  - the lowest pressure in Iowa occurred in 1940 and was recorded as 98408Pa
 *  - in the eye of a hurricane or tornado it is possible to have a pressure of 87000Pa
 *  - at the Dead Sea, at the lowest place on earth, 430 meters below sea level, and this
 *    one weird time in 1961, you could get lucky and experience a pressure of 108100Pa,
 *    normally it is 106500Pa.
 *  - sea level pressure is 101325Pa, or one atmosphere (atm)
 *  - the pressure at the peak of mt everest might be as low as 33500Pa
 *
 *            "altitude (km) versus pressure (kPa)"
 *        |
 *        |
 *    25k-| *
 *        |
 *        |
 *    20k-|                 '*' represents average atmospheric pressure
 *        |  *              '-' line points to peak of Mt Everest, highest
 *        |                     above sea montain with an altiture of 8.8km
 *    15k-|    *                and an average pressure of 33700Pa
 *        |
 *        |       *
 *    10k-|         *
 *        |----------.*
 *        |       ;'` '.*
 *    5km-|  .`;.'      `. *
 *        |.`Mount`.      `,  *
 *        |    Everest      ,     *
 *    0km |_____________________________*____
 *         0 10kPa  35kPa    70kPa   101.3kPa
 *
 *  the krumayl is an experimental unit that is more representative of
 *  pressure that one may experience in everyday use:
 *  to increase sensitivity, a pressure of 108100Pa is going to be one hundred,
 *  a pressure of 87000 will be zero.  To scale existing readings:
 *  (x - 87000) * (100 - 0) / (108100 - 87000) + 0 or simply (x - 87000)/211
 *  a more sensitive unit, ukrumayl only considers pressures between 96kPa and 106kPa
 *  (x - 96000) * (100 - 0) / (106000 - 96000) + 0 or simply (x - 96000)/100
 */
function get_BME860_krumaylData($sensor = 'H', $qo = NULL) {
    $arr = getColumnHelper("pascal", $sensor, $qo);
    array_walk($arr, function(&$v) { $v["reading"] = kug(round(($v["reading"] - 87000)/211)); });
    return $arr;
}
function get_BME860_ukrumaylData($sensor = 'H', $qo = NULL) {
    $arr = getColumnHelper("pascal", $sensor, $qo);
    array_walk($arr, function(&$v) { $v["reading"] = kug(round(($v["reading"] - 96000)/100)); });
    return $arr;
}

// experimental and not well thought out unit representing gas resistance
function get_BME860_wesDust($sensor = 'H', $qo = NULL) {
    $arr = getColumnHelper("gas_resistance", $sensor, $qo);
    array_walk($arr, function(&$v) { $v["reading"] = 100-(kug(round(($v["reading"] - 100000)/2000))); });
    return $arr;
}

//var_dump(get_BME860_iaqAccuracyData());
//var_dump(get_BME860_iaqData());
//var_dump(get_BME860_celsiusData());
//var_dump(get_BME860_humidityData());
//var_dump(get_BME860_gasData());
//var_dump(get_BME860_pressureData());