<?php
include_once("db_info_mediaq.php");

const VIDEOID = 0;
const FOVNUM = 1;
const PLAT = 2;
const PLNG = 3;
const THETAX = 4;
const R = 7;
const ALPHA = 8;
const TIMESTAMP = 9;

// connect to DB
$c = new mysqli ($host, $user, $password, $database);

if ($c->connect_error) {
    //   echo "could not connect to db: " . $c->connect_error . "</br>";
    echo json_encode(array("response_text" => "could not connect to db: " . $c->connect_error));
} else {
    // echo "Connection to database has been established</br>";
}


function getMyVideoIds($username)
{

    global $c;
    $sql_query = "SELECT DISTINCT VideoId FROM VIDEO_USER NATURAL JOIN USERS_PROFILES WHERE LOWEREDUSERNAME = ?";
    $prepStatementCheck = $c->prepare($sql_query);
//echo "here3.1";
    $prepStatementCheck->bind_param('s', strtolower($username));
    $prepStatementCheck->execute();
//echo "here3";
    $i = 0;
    $result_array = array();
    $result = iimysqli_stmt_get_result($prepStatementCheck);
//echo "here4";

//echo json_encode($result);

    while ($row = iimysqli_result_fetch_array($result)) {
	//	echo "here5";
//echo json_encode($row);
       foreach ($row as $r) {

//echo "here6";


            $result_array[$i] = $r;
            $i++;

        }
    }

    return $result_array;

}


function getVideoMaxLat($videoId)
{
    global $c;
    $sql_query = "SELECT MAX(PLAT) FROM VIDEO_METADATA WHERE VIDEOID=?";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('s', $videoId);
    $prepStatementCheck->execute();

    $result = iimysqli_stmt_get_result($prepStatementCheck);
    $row = iimysqli_result_fetch_array($result);
    return $row[0];
}

function getVideoMinLat($videoId)
{
    global $c;
    $sql_query = "SELECT MIN(PLAT) FROM VIDEO_METADATA WHERE VIDEOID=?";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('s', $videoId);
    $prepStatementCheck->execute();

    $result = iimysqli_stmt_get_result($prepStatementCheck);
    $row = iimysqli_result_fetch_array($result);
    return $row[0];
}


function getVideoMaxLon($videoId)
{
    global $c;
    $sql_query = "SELECT MAX(PLNG) FROM VIDEO_METADATA WHERE VIDEOID=?";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('s', $videoId);
    $prepStatementCheck->execute();

    $result = iimysqli_stmt_get_result($prepStatementCheck);
    $row = iimysqli_result_fetch_array($result);
    return $row[0];
}

function getVideoMinLon($videoId)
{
    global $c;
    $sql_query = "SELECT MIN(PLNG) FROM VIDEO_METADATA WHERE VIDEOID=?";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('s', $videoId);
    $prepStatementCheck->execute();

    $result = iimysqli_stmt_get_result($prepStatementCheck);
    $row = iimysqli_result_fetch_array($result);
    return $row[0];
}

function getVideoStartTime($videoId)
{
    global $c;
    $sql_query = "SELECT MIN(TIMECODE) FROM VIDEO_METADATA WHERE VIDEOID=?";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('s', $videoId);
    $prepStatementCheck->execute();

    $result = iimysqli_stmt_get_result($prepStatementCheck);
    $row = iimysqli_result_fetch_array($result);
    return $row[0];
}

function getVideoEndTime($videoId)
{
    global $c;
    $sql_query = "SELECT MAX(TIMECODE) FROM VIDEO_METADATA WHERE VIDEOID=?";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('s', $videoId);
    $prepStatementCheck->execute();

    $result = iimysqli_stmt_get_result($prepStatementCheck);
    $row = iimysqli_result_fetch_array($result);
    return $row[0];
}


function getVideosSegmentsWhichAreInsideOfAllThreeDimensionIntervals($myvideo, $myvideoStartTimestamp, $myvideoEndTimestamp, $minlat, $maxlat, $minlon, $maxlon)
{
    global $c;
    $sql_query = "SELECT * FROM VIDEO_METADATA WHERE TIMECODE>? AND TIMECODE<? AND PLAT>? AND PLAT<? AND PLNG>? AND PLNG<? AND VIDEOID NOT IN (?) ORDER BY VIDEOID ASC, FOVNUM ASC";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('ssdddds', $myvideoStartTimestamp, $myvideoEndTimestamp, $minlat, $maxlat, $minlon, $maxlon, $myvideo);
    $prepStatementCheck->execute();

    $i = 0;
    $result_array = array();
    
    $result = iimysqli_stmt_get_result($prepStatementCheck);

    while ($row = iimysqli_result_fetch_array($result)) {
        $result_array[$i] = $row;
        $i++;
    }
    return $result_array;
}


function getVideosWhichCouldIntersectWithMyVideo($myvideo, $myvideoStartTimestamp, $myvideoEndTimestamp)
{
    global $c;
    $sql_query = "SELECT * FROM VIDEO_METADATA WHERE TIMECODE>? AND TIMECODE<? AND VIDEOID NOT IN (?) ORDER BY VIDEOID ASC, FOVNUM ASC";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('sss', $myvideoStartTimestamp, $myvideoEndTimestamp, $myvideo);
    $prepStatementCheck->execute();

    $i = 0;
    $result_array = array();

    $result = iimysqli_stmt_get_result($prepStatementCheck);
    
    while ($row = iimysqli_result_fetch_array($result)) {
        $result_array[$i] = $row;
        $i++;
    }

    return $result_array;
}

function getVideoMetaEntries($myvideo)
{
    global $c;
    $sql_query = "SELECT * FROM VIDEO_METADATA WHERE VIDEOID=? ORDER BY VIDEOID ASC, FOVNUM ASC";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('s', $myvideo);
    $prepStatementCheck->execute();

    $i = 0;
    $result_array = array();
    $result = iimysqli_stmt_get_result($prepStatementCheck);
    while ($row = iimysqli_result_fetch_array($result)) {
        $result_array[$i] = $row;
        $i++;
    }

    return $result_array;
}


function getAllVideosExceptOfUser($username)
{
    global $c;
    $sql_query = "SELECT * FROM VIDEO_METADATA WHERE VIDEOID NOT IN (SELECT DISTINCT VideoId FROM VIDEO_USER NATURAL JOIN USERS_PROFILES WHERE LOWEREDUSERNAME = ?) ORDER BY VIDEOID ASC, FOVNUM ASC";
    $prepStatementCheck = $c->prepare($sql_query);

    $prepStatementCheck->bind_param('s', strtolower($username));
    $prepStatementCheck->execute();

    $i = 0;
    $result_array = array();
    $result = iimysqli_stmt_get_result($prepStatementCheck);
    while ($row = iimysqli_result_fetch_array($result)) {
        $result_array[$i] = $row;
        $i++;
    }

    return $result_array;
}


function splitVideoSegmentsDueToVideo($orderedVideoSegments)
{
    $result = array();
    foreach ($orderedVideoSegments as $segment) {
        $result[$segment[0]][] = $segment;
    }
    return $result;


}


function closeSqlConnection()
{
    global $c;
    mysqli_close($c);
}

class iimysqli_result
{
    public $stmt, $nCols;
}   

function iimysqli_stmt_get_result($stmt)
{
    /**    EXPLANATION:
     * We are creating a fake "result" structure to enable us to have
     * source-level equivalent syntax to a query executed via
     * mysqli_query().
     *
     *    $stmt = mysqli_prepare($conn, "");
     *    mysqli_bind_param($stmt, "types", ...);
     *
     *    $param1 = 0;
     *    $param2 = 'foo';
     *    $param3 = 'bar';
     *    mysqli_execute($stmt);
     *    $result _mysqli_stmt_get_result($stmt);
     *        [ $arr = _mysqli_result_fetch_array($result);
     *            || $assoc = _mysqli_result_fetch_assoc($result); ]
     *    mysqli_stmt_close($stmt);
     *    mysqli_close($conn);
     *
     * At the source level, there is no difference between this and mysqlnd.
     **/
    $metadata = mysqli_stmt_result_metadata($stmt);
    $ret = new iimysqli_result;
    if (!$ret) return NULL;

    $ret->nCols = mysqli_num_fields($metadata);
    $ret->stmt = $stmt;

    mysqli_free_result($metadata);
    return $ret;
}

function iimysqli_result_fetch_array(&$result)
{
    $ret = array();
    $code = "return mysqli_stmt_bind_result(\$result->stmt ";

    for ($i=0; $i<$result->nCols; $i++)
    {
        $ret[$i] = NULL;
        $code .= ", \$ret['" .$i ."']";
    };

    $code .= ");";
    if (!eval($code)) { return NULL; };

    // This should advance the "$stmt" cursor.
    if (!mysqli_stmt_fetch($result->stmt)) { return NULL; };

    // Return the array we built.
    return $ret;
}


?>
