<?php

/*  provides a table to store interesting information on descriptions submitted by users
 *
 *  to use:
 *      scanDescription("it rained, 37F and 40% humid -- we had dutch letter ice cream")
 *
 *  it works using a table called "events".  The table stores integer values representing various
 *  observations a user has made while recording radon reading samples.  The table includes a column
 *  "type" that encodes things like rain, or snow, making the "events" table more flexible.  Each event
 *  stored in the table has a start and end time, encoded as seconds since unix epoch.
 *
 *  to make this code useful for end users, these functions are provided to show the users interesting
 *  insights from the descriptions they provided
 *
 *    getRainPeriods(...):         returns array(["start"=>{start}, "end"=>{end}],  ...)
 *    getSnowPeriods(...):         returns array(["start"=>{start}, "end"=>{end}],  ...)
 *    getTemperatureData(...):     returns array(["time"=>{time}, "reading"=>{reading},  ...)
 *    getHumidityData(...):        returns array(["time"=>{time}, "reading"=>{reading},  ...)
 *    getRainData(...):            returns array(["time"=>{time}, "reading"=>{reading},  ...)
 *    getSnowData(...):            returns array(["time"=>{time}, "reading"=>{reading},  ...)
 *
 *    [?] these functions all take an optional start and end time parameters eg:
 *        $lastWeekTemperatures = getTemperatureData(time() - strtotime("7 days", 0), time());
 *
 *  to extend this, three functions are modified and optionally a frontend oriented helper function could be
 *  defined here to retrieve stored data.  The functions to be modified are regexParse, which handles
 *  searching the user description, and it's sister function typeLookup which encodes the type of data, like
 *  "celcius", "rain", or "snow" as a number.  One may want to modify the third function, saveEvents if
 *  additional processing needs to occur on data as regexParse should only be finding the data of interest
 */

/*  order 2021 Nov 14 6:30pm
 *  aroydee 1-515-528-8009
 *  14. vermicelli combo 
 *  29. pad thai chicken
 *  H4. green curry beef
 *  vietnamese eggnog (2)
 *  order will be ready in 25 minutes
 */

function scanDescription($text = false, $time = false) {
    # connect to mysql database radondb
    $db = mysql_connect("localhost", "root", "secom8703");
    mysql_select_db("radondb", $db);

    # make sure events table exists
    if(false === mysql_query("SELECT 1 FROM `events` LIMIT 1", $db)) {
       mysql_query("CREATE TABLE `events` (start INTEGER, end INTEGER, type INTEGER, value INTEGER, CONSTRAINT event UNIQUE (start, end, type))", $db);
    }

    # either perform extraction on entire table, or just user text
    if(false /* entire table */ === $text) {
        fwrite(STDERR, "scanning radondb for temperature and humidity data" . PHP_EOL);
        $result = mysql_query("SELECT time, description FROM locationLog WHERE id = 'note';", $db);
        while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            //printRow($row);
            saveEvents($db, regexParse($row["description"]), $row["time"]);
        }
        mysql_free_result($result);
    } else saveEvents($db, regexParse($text), (false === $time) ?time() :$time);

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

// returns stats from user descriptions
function regexParse($s) {
   $stats = $t = array();
   $s = strtolower($s);
   // do simple mathing based using NNN{symbol} pattern
   $terms = array("farenheit" => "f", "celcius" => "c", "humidity" => "%");
   foreach($terms as $k=>$v) if(1 === preg_match("/\d{1,3}(?={$v})/", $s, $t)) $stats[$k] = $t[0];
   // check for rain, and other keywords (do snow separately)
   $terms = array("rain");
   foreach($terms as $v) if(false !== strpos($s, $v)) $stats[$v] = true;
   // when checking for snow, ignore if you also find melt
   if(false !== strpos($s, "snow")) {
       if(false === strpos($s, "melt")) $stats["snow"] = true;
   }
   return $stats;
}

// the database has a table events schema that includes types, defined in regexParse, the keys are immutable
function typeLookup($s, $getArray = false) {
    $types = array(
        "farenheit" => 0,
        "celcius" => 1,
        "humidity" => 2,
        "rain" => 3,
        "snow" => 4
        /* ... no changes to previous types and keys, add new types below */
    );
    if(!isset($types[$s])) return false;
    return $types[$s];
}


// save the stats from a scanned description into the database as events
function saveEvents($db, $stats, $time) {
    foreach($stats as $plaintextType=>$value) {
        // convert canonical integers to SQL INTEGER
        if(ctype_digit($value)) $value = intval($value);
        // convert boolean to SQL INTEGER
        if(true === $value) $value = 1;
        if(false === $value) $value = 0;
        // other values should be integers
        if(!is_int($value)) { 
            fwrite(STDERR, "[!] stat {$plaintextType} has value {$value}, not an integer" . PHP_EOL);
            continue;
        }
        // stat type must be valid, also prevents unwanted SQL
        if(false === ($type = typeLookup($plaintextType))) {
            fwrite(STDERR, "[!] stat type {$plaintextType} is not defined in events schema" . PHP_EOL);
            continue;
        }
        // and also make sure nothing unsafe is coming through time
        $start = intval($time);
        $end = $start;
        // make rain or snow occur over time, 8 hours minimum
        if("rain" === $plaintextType || "snow" === $plaintextType) $end += (8/*hours*/ * 60 /*minutes*/ * 60 /*seconds*/);
        // save record
        if(false === mysql_query("INSERT INTO events (start, end, type, value) VALUES ({$start},{$end},{$type},{$value})", $db)) {
            // event previously recorded
        } else /* event sucessfully stored */;
    }
}

/*  sometimes users input time intervals without an end time:
 *  I might record that it is raining, then go take a six hour nap, upon waking I
 *  might notice that it is still raining and wait until it stops and then record
 *  that it stops.  This will probably never happen.  More likely I will record that
 *  it is raining and then never say anything again.  There is a possibility that
 *  when I wake up I might record again that it is raining.  We just assume that rain
 *  always lasts at least 8-hours and that whenever there are overlapping mentions
 *  of rain the intervals are merged, eg: not summed:
 *
 *  |----------- rain 8-hours ------------|
 *                         |----------- rain 8-hours ------------|
 *
 *  |-------------------------- merged --------------------------|
 */
function eightHourIntervalMerge($type, $start = false, $end = false) {
    if(false === ($rows = helperFunctionTemplate($type, $start, $end))) return false;
    $intervals = array();
    $start = false;
    // rain/snow are added in 8-hour intervals, merge overlapping intervals
    foreach($rows as $k=>$row) {
        // record the begining of the interval
        if(false === $start) $start = $row["start"];
        // goto next interval if current interval overlaps
        if(isset($rows[$k + 1]) and ($rows[$k + 1]["start"] <= $row["end"])) continue;
        // store the interval when there are no more overlaps
        $intervals[] = array("start"=>$start, "end"=>$row["end"]);
        $start = false;
    }
    return $intervals;    
}

// returns array of ["time"=>{time}, "reading"=>{reading}] from events by type
function getReadings($type, $start = false, $end = false) {
    if(false === ($rows = helperFunctionTemplate($type, $start, $end))) return false;
    $arr = array( /* [time => ..., reading => ...] */ );
    foreach($rows as $row) $arr[] = array("time"=>$row["start"], "reading"=>$row["value"]);
    return $arr;
}

// returns merged time intervals of rain, with optional time range (unix epoch)
function getRainPeriods($start = false, $end = false) {
    return eightHourIntervalMerge("rain", $start = false, $end = false);
}
// returns merged time intervals of snow, with optional time range (unix epoch)
function getSnowPeriods($start = false, $end = false) {
    return eightHourIntervalMerge("snow", $start = false, $end = false);
}
// returns farenheit readings, with optional time range (unix epoch)
function getTemperatureData($start = false, $end = false) {
    return getReadings("farenheit", $start, $end);
}
// returns humidity percentage readings, with optional time range (unix epoch)
function getHumidityData($start = false, $end = false) {
    return getReadings("humidity", $start, $end);
}
// merged time intervals of snow, sampled and filtered
function getSnowData($start = false, $end = false) {
    return intervalsToSamples($start, $end, "snow");
}
// merged time intervals of rain, sampled and filtered
function getRainData($start = false, $end = false) {
    return intervalsToSamples($start, $end, "rain");
}


// merged time intervals of rain/snow, sampled and filtered
function intervalsToSamples($start = false, $end = false, $type = "rain") {
    // make a set of numbers, between zero and one to use as start and stop data
    $points = /* arbitrary number, consider HighCharts - more points, more load */ 8;
    $startStopLookup = generateSoftStart($points);
    // make up the amount of time for a "soft start"
    $softTime = /* fraction of "length of rain" in time */ strtotime("8 hours", 0) / (2 * $points);
    // make up the first data point where there is no rain (selected from database manually)
    $arr = array(array("time"=> /* SELECT MIN(time) FROM radonLog */ 1631631600, "reading" => 0));
    // remaining data points come from the "events" table
    $periods =  eightHourIntervalMerge($type, $start = false, $end = false);
    // generate the graph, use soft start/stop data at beginging/end of intervals
    foreach($periods as $a) {
        // [?] $a["reading"] value is always one, soft starts are between zero and one
        // generate soft start values, separated by a made up amount of time
        $start = $a["start"];
        for($i = 0;$i<$points; $i++) {
            $arr[] = array("time"=>$start, "reading" => $startStopLookup[$i]);
            $start += $softTime;
        }
        // generate soft stop values, separated by a made up amount of time
        $stop = $a["end"] - ($points * $softTime);
        for($i = $points; $i--;) {
            $arr[] = array("time"=>$stop, "reading" => $startStopLookup[$i]);
            $stop += $softTime;
        }
    }
    return $arr;
}

/*  generate a soft curve to use instead of a harsh edge when displaying data
 *  that would normally have a jarring on/off transition, user optionally
 *  provides the number of points used to represent the wave
 *
 *  [?] user should use the returned values in reverse for a "soft-stop"
 *
 *  |                    .-'``
 *  |                  .`
 *  |                ,`
 *  |               *             180 degrees of a sinewave, offset by one
 *  |              `              and a phase shift of PI/2 gives a "soft-
 *  |           .`                start" -- this opposes a "sharp" edge
 *  |          *                  of digital on/off, or a square wave
 *  |         '                   (to make values between zero and one, scaled by half)
 *  |       .`
 *  |     ,'
 *  |,.-'_____________________
 */
function generateSoftStart($n = 6) {
    $arr = array();
    for($i = 0; $i < $n; $i++) $arr[] = (1+sin(($i*2*M_PI_2)/($n-1) - M_PI_2))/2;
    return $arr;
}

function helperFunctionTemplate($type, $start = false, $end = false) {
    # 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) ?"start >= {$start} AND end <= {$end} AND" :"";
    // add optional time interval to query
    $sql  = "SELECT start, end, value FROM `events` WHERE {$and}";
    // lookup type, make sure query sorts results oldest event to newest
    $sql .= "type = " . typeLookup($type) . " ORDER BY start ASC";

    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;
}

//scanDescription();
//var_dump(getRainPeriods());
//var_dump(getSnowPeriods());
//var_dump(getHumidityData());
//var_dump(getTemperatureData());
//var_dump(getRainData());


// calling this without parameters will try to extract all data from all records in locationLog
//scanDescription();

/* ------  stuff for debugging ------ */

// row contains ["time" => "...", "description" => "..."]
function printRow($mySQL_assocRow) {
    $temp = "";
    foreach($mySQL_assocRow as $k=>$v) {
	echo "{$v}  ";
	if("description" !== $k) continue;
	$temp = implode(" ", regexParse($v));
    }
    if("" !== $temp) echo "---- @{$mySQL_assocRow["time"]} {$temp}"; 
    echo PHP_EOL;
}