<?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);

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

    $i = 0;
    $result_array = array();
    $result = $prepStatementCheck->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        foreach ($row as $r) {
            $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 = $prepStatementCheck->get_result();
    $row = $result->fetch_array(MYSQLI_NUM);
    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 = $prepStatementCheck->get_result();
    $row = $result->fetch_array(MYSQLI_NUM);
    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 = $prepStatementCheck->get_result();
    $row = $result->fetch_array(MYSQLI_NUM);
    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 = $prepStatementCheck->get_result();
    $row = $result->fetch_array(MYSQLI_NUM);
    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 = $prepStatementCheck->get_result();
    $row = $result->fetch_array(MYSQLI_NUM);
    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 = $prepStatementCheck->get_result();
    $row = $result->fetch_array(MYSQLI_NUM);
    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 = $prepStatementCheck->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        $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 = $prepStatementCheck->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        $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 = $prepStatementCheck->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        $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 = $prepStatementCheck->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        $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);
}


?>