function get_all_comps() {
    global $db;
    $q = "SELECT * FROM comp order by id desc";
    $r = $db->query($q);
    return $r->fetch_all();
}

function all_judges() {
    global $db;
    $q = "SELECT id, reg_date, active, name, status FROM users WHERE id > 2 ORDER by status DESC, id ASC;";
    $r = $db->query($q);
    return $r->fetch_all();
}

function get_flight($id) {
    global $db;
    $q = "SELECT * FROM flights WHERE id =".$id;
    $r = $db->query($q);
    return $r->fetch_object();
}

function get_marks($flight_id) {
    global $db;
}

function is_judges($round, $db) {
        //get all judges from dis
        $q = "select j_teams_id from rounds_judges where round_id=".$round;
        $result = $db->query($q)->num_rows;
        return ($result > 0);
}

function get_round_id($round_name) {
    global $db;
    $cdi = $_SESSION['dis_id'];
    $q = "SELECT id FROM `rounds` WHERE comp_dis_id = ".$cdi." AND name = '".$round_name."'";
    $id = $db->query($q)->fetch_object()->id;
    return($id);
}
function get_judges_team($round_id) {
    global $db, $comp_id;
    $q = "SELECT j_teams_id FROM rounds_judges WHERE round_id =". $round_id;
    $j = $db->query($q)->fetch_object();
    $jTeam['id'] = $j->j_teams_id;
    $q = "
SELECT * FROM j_teams
LEFT JOIN j_teams_users ON j_teams.id = j_teams_users.j_teams_id
LEFT JOIN users ON j_teams_users.user_id = users.id
WHERE j_teams.comp_id = ".$comp_id." AND j_teams.id =".$jTeam['id'];

    $result =  $db->query($q);
    while($row = $result->fetch_object()) {
        $jTeam['main_user_id'] = $row->main_user_id;
        $jTeam['team'][$row->user_id] = ["name" => $row->name,
                                         "status" =>$row->status,
];
    }
    return $jTeam;
}

function get_starts($judges, $hash) {
    global $db;
    $ids = implode(",",array_keys($judges["team"]));
    $q = "SELECT * FROM start_time WHERE user_id IN ($ids) AND video_hash='".$hash."'";
    $result = $db->query($q); $out=[];
    while($row = $result->fetch_object()) {
        $out['$row->user_id'] = $row->value;
    }
    return $out;
}

function comp_results() {
    global $db;
    $scores = getallscores();
    $alldis['results'] = $scores;
    $q = "SELECT *, comp_disciplines.id as did FROM `comp_disciplines` LEFT JOIN disciplines ON comp_disciplines.dis_id=disciplines.id";
    $result = $db->query($q);
    while ($row = $result->fetch_object())
    {
            $di = $row->did;
            $row->rounds = getrounds($di);
            $row->teams = getteams($di);
            $alldis["competitions"][$row->name." ".$row->class] = $row;
    }
    print(json_encode($alldis));
    exit;
}


function getrounds($dis_id) {
    global $db;
    $rounds = [];
    $q = "SELECT * FROM `rounds` WHERE comp_dis_id =".$dis_id." ORDER BY id ASC";
    $result = $db->query($q);
    while ($row = $result->fetch_object()) {
        $rounds[$row->id] = $row->name;
    }
    return $rounds;
}

function getteams($dis_id) {
    global $db;
    $rounds = [];
    $q = "SELECT * FROM `teams` WHERE comp_dis_id =".$dis_id;
    $result = $db->query($q);
    $teams = [];
    $results = [];
    while ($row = $result->fetch_object()) {
        $teams[$row->num] = [$row->name];
    }
    return $teams;

}

function get_flight_result($dis_id, $team_num) {
    global $db;
    $q = "SELECT * FROM `flights` WHERE comp_dis_id = ".$dis_id." and team_num = '".$team_num."' AND status = 3 ORDER by round_id";
    $result = $db->query($q);
    if($result->num_rows > 0) {
           $value = $result->fetch_object()->result;
           return $value;
    }
}

function getallscores() {
    global $db, $comp_id;
    $q = "SELECT * FROM `flights` WHERE `result` IS NOT NULL AND status = 3
          AND comp_dis_id IN (SELECT id FROM `comp_disciplines` WHERE comp_id=$comp_id) ORDER BY flights.id ASC";
    $result = $db->query($q);
    while ($row = $result->fetch_object())
    {
           $scores[$row->team_num][$row->round] = $row->result;
    }
    return $scores;
}

function getallteams() {
    global $db, $comp_id;
    $q = "SELECT * FROM `flights` WHERE `result` IS NOT NULL AND status = 3 
    AND comp_dis_id IN (SELECT id FROM `comp_disciplines` WHERE comp_id=$comp_id) ORDER BY flights.id ASC";
    $result = $db->query($q);
    while ($row = $result->fetch_object())
    {
           $ids[$row->team_num][$row->round] = $row->id;
    }
    return $ids;
}

function render_tour($dis_id) {
    global $init;
    $rounds = getrounds($dis_id);
    $count = count($rounds) + 1;
    $proc = round(75/$count);
    $teams = getteams($dis_id);
//var_dump($teams);
//var_dump($rounds);
    $allscores = getallscores();
    $allteams = getallteams();
//var_dump($allscores);
    $out = "<table style='width:100%'><tr style='height:60px;text-align:center;font-weight:bold'><td style='width:20%'>TEAMS</td>";
        foreach ($rounds as $rkey => $rvalue) {
            $out .= "<td style='width:$proc%'>".$rvalue."</td>";
        }
    $out .="<td style='width:$proc%'>AVE</td>";
    $out .="<td style='width:$proc%'>RESULTS</td></tr>";
    $sum = [];
    foreach ($teams as $tkey => $tvalue) {
        foreach ($rounds as $rkey => $rvalue) {
            if(!isset($sum[$tkey])) $sum[$tkey] = 0;
            if($rvalue == "TB") {
                if(isset($allscores[$tkey][$rvalue])) $sum[$tkey] += $allscores[$tkey][$rvalue] / 100;
                continue;
            }
            $sum[$tkey] += (isset($allscores[$tkey][$rvalue])) ? $allscores[$tkey][$rvalue] : 0;
            $pub_results[$tkey] += (isset($allscores[$tkey][$rvalue])) ? 1 : 0;
            $ave[$tkey] = ($pub_results[$tkey] > 0) ? round($sum[$tkey] / $pub_results[$tkey], 2) : 0;
        }
    }
    $dis_id = $init->comp_dis[$dis_id]->dis_id;
    if($dis_id=="17") {
        asort($ave);
    } else {
        arsort($ave);
    }
    $c = 0; $style = ["t-orange","t-blue","t-red"];
    foreach($ave as $key => $value) {
        $color = ($c < 3) ? $style[$c] : "";
        $out.="<tr class='team-row ".$color."'><td class='team-coll'><img src='teams/--s".$key.".jpg'><div class='team-cell' >".$key." ".$teams[$key][0]."</div></td>";
        foreach($rounds as $r=>$round) {
            $result = "";
            $link = "";
            if(isset($allscores[$key][$round])) {
                $result = $allscores[$key][$round];
                $style1 = ($c < 3) ? "style='color:black'" : "";
                $link = "<a $style1 href='/?view=".$allteams[$key][$round]."'><div class='r'>".$result."</div></a>";
//                $link = "<a $style1 href=''><div class='r'>".$result."</div></a>";
            }
            $out .= "<td class='rcell'>".$link."</td>";
        }
//        $out .= "<td style='text-align:center'>".round($sum[$key])."</td></tr>";
        $out .= "<td style='text-align:center'>".$ave[$key]."</td>";
        $out .= "<td style='text-align:center'>".$sum[$key]."</td></tr>";
    $c++;
    }
    $out .="</table>";
    return $out;
}
function make_marks($f, $u, $db) {
    $logged_id = (!isset($_SESSION['user'])) ? 0 : $_SESSION['user']->id;
    $me = ($u == $logged_id);
    $name = $db->query('select name from users where id ='.$u)->fetch_object()->name;
    $q = "SELECT id,value,seconds FROM `marks` WHERE `flight_id` = ".$f." AND `user_id` = ".$u." AND `rm` IS NULL order by id ASC";
    $result = $db->query($q);
    $skips=0;$busts=0;$summ=0;$count=0;
    $str_marks="<div";
    if($me) { $str_marks.=" id='result'"; }
    $str_marks.=" class='result' id='results'>";
    $values = [];
    $timeline = [];
    while ($row = $result->fetch_assoc()) {
        $colors=["-1"=>"black","0"=>"red","1"=>"green"];
        $val = $row['value'];
        $summ += $val;
        $count++;
        if($val=="0") $busts++;
        if($val=="-1") $skips++;
        $values[] = $val;
        $timeline[] = $row["seconds"];
        $str_marks .="<div id='".$row['id']."' class='marks'";
        if($me) { $str_marks .=" onclick=\"changeMark('".$row['id']."')\""; }
        $str_marks .=" style='background: ".$colors[$row['value']]."'>".$row['value']."</div>";
    }
    $str_marks.= "<div style='float:right;padding-right:10px;font-size:18pt'>".$name."</div></div>";

    return ["str_marks"=>$str_marks,
            "skips"=>$skips,
            "busts"=>$busts,
            "summ"=>$summ,
            "count"=>$count,
            "name"=>$name,
            "timeline"=>$timeline,
            "values"=>$values];
}

function make_result($marks_array) {
    $count = count($marks_array);
    $summ = [];
    $fastest=[];
    $marks = $marks_array[0];
    foreach($marks['values'] as $key => $value) {
        for($i=0;$i<$count;$i++) {
//echo "[$i]";
            $value = (isset($marks_array[$i]['values'][$key])) ? $marks_array[$i]['values'][$key] : 0;
            $mt = (isset($marks_array[$i]['timeline'][$key])) ? $marks_array[$i]['timeline'][$key] : 0;
//var_dump($key);
//var_dump($mt);
            if(isset($summ[$key])) {
                $summ[$key] = $summ[$key] + $value;
            } else {
                $summ[$key] = $value;
            }
            if($mt == 0) continue;
            if($mt == 1) continue;
            if($key > 0) { if($mt == $marks_array[$i]['timeline'][$key-1]) continue; }
            if($key > 0) { if($mt < $fastest[$key-1]) continue; }
            if(isset($fastest[$key])) {
                if($mt < $fastest[$key]) {
                    $fastest[$key] = $mt;
                }
            } else {
                if($key > 0) {
//echo "lt:".end($fastest)."\n";
                    if($mt < end($fastest)) continue;
                }
                $fastest[$key] = $mt;
            }
//var_dump($fastest[$key]);
        }
    }
    foreach($summ as $key => $value) {
        $result['result'][]=round($value/$count);
        $result['timeline'][]=$fastest[$key];
    }
    return $result;
}

function render_result($result) {
    $skips=0;$busts=0;$summ=0;$count=0;
    $str_marks="<div class='result total'>";
    foreach($result['result'] as $key => $val) {
        $result["timeline"][$key] = round($result["timeline"][$key], 1)."";
        $colors=["-1"=>"black","0"=>"red","1"=>"green"];
        $summ += $val;
        $count++;
        if($val=="0") $busts++;
        if($val=="-1") $skips++;
        $str_marks .="<div id='result".$count."' class='marks'";
//        $title = $result["timeline"][$key] - 1;
        $str_marks .=" style='background: ".$colors[$val]."'>".$val."</div>";
    }
    $str_marks.= "<div style='float:right;padding-right:10px;font-size:18pt'>TOTAL</div></div>";
    $timeline = json_encode(array_flip($result["timeline"]));
    return ["str_marks"=>$str_marks,
            "skips"=>$skips,
            "busts"=>$busts,
            "summ"=>$summ,
            "count"=>$count,
            "name"=>"<b>TOTAL</b>",
            "values"=>$result,
            "ready"=>true,
            "timeline"=>$timeline,
           ];
}
function superadmin() {
    global $user, $init;

    $out = "";
    if($user->id == 1) {
        $comp_id = $init->comp->id;
        $comp_name = $init->comp->name;
        $cams_count = count($init->cams);
        $out = "
<table style='font-size:16px;text-align:left'>
<tr><td>
Current comp : $comp_id [ $comp_name ]
<br>
<br>
  <form method = 'post' action = '/?action=create_competition'>
    <input type = 'text' name = 'name'>
    <input type = 'date' name = 'date'>
    <input type = 'submit' value = 'Создать соревнования'>
  </form>
Количество камер: $cams_count [<a href='/?action=add_cam'>добавить</a>]
<table style='font-size:16px;width:26%'>";
foreach($init->cams as $cam) {
    $out .= "<tr>
<td>".$cam->id."</td>
<td>".$cam->cam_id."</td>
<td><input style='width:50px' type='number' value=".$cam->mtop."></td>
<td><input style='width:50px' type='number' value=".$cam->mleft."></td>
<td><input style='width:50px' type='number' value=".$cam->zoom."></td>
<td><input style='width:80px' type='submit' value='обновить'></td>
</tr>";
}
$out .= "</table>";

$out .= "<br>Дисциплины:<br>
<table style='font-size:16px;text-align:left'>
<tr><td colspan = 3></td><td>bust</td><td>time</td><td>video</td><td>rounds</td><td>camera</td><td>num</td><td>count</td></tr>";
foreach($init->comp_dis as $comp_dis) {
    $out .= "<form method='post' action='/?action=update_dis_params'><tr>
<td>".$comp_dis->id."<input type='hidden' name='comp_dis_id' value='".$comp_dis->id."'></td>
<td>".$init->dis[$comp_dis->dis_id]->name."</td>
<td>".$init->dis[$comp_dis->dis_id]->class."</td>
<td><input style='width:50px' type='number' name='bust' value=".$comp_dis->bust."></td>
<td><input style='width:50px' type='number' name='round_time' value=".$comp_dis->round_time."></td>
<td><input style='width:50px' type='number' name='video_time' value=".$comp_dis->video_time."></td>
<td><input style='width:50px' type='number' name='round_count' value=".$comp_dis->round_count."></td>
<td><input style='width:50px' type='number' name='type' value=".$comp_dis->type."></td>
<td><input style='width:50px' type='number' name='num' value=".$comp_dis->num."></td>
<td><input style='width:50px' type='number' name='teams_count' value=".$comp_dis->teams_count."></td>
<td><input style='width:80px' type='submit' value='обновить'></td>
</tr></form>";
}
$out .= "</table>";
$options = "<option value =''> -- </option>";
foreach($init->dis as $dis) {
    if(in_array($dis->id, $init->dis_ids)) continue;
    $options .= "<option value=".$dis->id.">".$dis->name." ".$dis->class."</option>";
}
$out .= "
<form method = 'post' action='/?action=add_comp_dis'>
<select name='dis_id'>$options</select>&nbsp;<input style='width:80px' type='submit' value='добавить'>
</form>
</td><td>
";
$out .= "<br>Команды судей:<br>
<table style='font-size:16px;text-align:left'>";
foreach($init->j_teams as $jt) {
    $out .= "<tr>
<td>".$jt->id."</td>
<td>".date('d.m.y', strtotime($jt->day))."</td>
<td>".$init->judges[$jt->main_user_id]->name."</td>
</tr>";

$options = "<option value =''> -- </option>";
foreach($init->judges as $j) {
    $options .= "<option value=".$j->id.">".$j->name."</option>";
}

foreach($init->j_teams_users[$jt->id] as $jtu) {
if($jtu->user_id == $jt->main_user_id) continue;
$out .="
<tr>
<td></td>
<td></td>
<td>".$init->judges[$jtu->user_id]->name."</td>
</tr>";
}
$out .= "<tr><td colspan=3 style='text-align:center'>
<form method = 'post' action='/?action=add_j_team_user'>
<input type = 'hidden' name = 'team_id' value = ".$jt->id.">
<select name='j_id'>$options</select>&nbsp;<input style='width:80px' type='submit' value='добавить'>
</form>
</td></tr>";
}
$out .= "</table>";
$out .= "
<form method = 'post' action='/?action=add_j_team'>
<input type = 'date' name = 'date'>
<select name='j_id'>$options</select>&nbsp;<input style='width:80px' type='submit' value='добавить'>
</form>


</td></tr></table>
<table style='font-size:16px;text-align:left;'><tr><td style='vertical-align:top'>
";
foreach($init->comp_dis as $cdi => $cd) {
    $comp_dis_name = $init->dis[$cd->dis_id]->name." ".$init->dis[$cd->dis_id]->class;
    $out .= "<table style='font-size:16px;text-align:left'><tr><td>$comp_dis_name</td></tr>";
    if(isset($init->rounds[$cdi])) {
        foreach($init->rounds[$cdi] as $round) {
            $round_blocks = str_replace("'","",$round->blocks);
            $out .="<tr><td>".$round->name."</td><td><input type='text' value='".$round_blocks."'></td></tr>";
        }
    } else {
        $out .= "<tr><td><form method='post' action='/?action=add_rounds'><input type='submit' value='Создать раунды'><input type='hidden' name='cd_id' value='$cdi'></form></td><tr>";
    }
$out .= "<tr><td>&nbsp</td></tr>";
    if(isset($init->teams[$cdi])) {
        foreach($init->teams[$cdi] as $team) {
            $out .="<tr><td>".$team->num."</td><td><input type='text' value='".$team->name."'></td></tr>";
        }
    } else {
        $out .= "<tr><td><form method='post' action='/?action=add_teams'><input type='submit' value='Добавить команды'><input type='hidden' name='cd_id' value='$cdi'></form></td><tr>";
    }
    $out .="</table></td><td style='vertical-align:top'>";
}
$out .= "</td></tr></table>";
    }
    return $out;
}


function create_comp() {
    global $db, $user;
    if($user->id != 1) return "ACCESS DENIED";
    $request = $_REQUEST;
    $q = "insert into comp (name, date) values ('".$request['name']."','".$request['date']."')";
    $result = $db->query($q);
}

function add_cam() {
    global $db, $user, $init;
    if($user->id != 1) return "ACCESS DENIED";
    $comp_id = $init->comp->id;
    $cams_count = count($init->cams);
    $new_cam = $cams_count + 1;
    $q = "insert into cameras (comp_id, cam_id) values ('$comp_id', '$new_cam')";
    $result = $db->query($q);
}

function add_comp_dis() {
    global $db, $user, $init;
    if($user->id != 1) return "ACCESS DENIED";
    $comp_id = $init->comp->id;
    $request = $_REQUEST;
    $di = $request['dis_id'];
    if($request == "") return "---";
    //get prev params
    $q = "select * from dis_params where dis_params.comp_dis_id = (SELECT id FROM `comp_disciplines` WHERE `dis_id` = '$di' order by comp_id desc limit 1)";
    $result = $db->query($q);
    if($result->num_rows != 0) {
        $old_params = $result->fetch_object();
    }
    $q = "insert into comp_disciplines (comp_id, dis_id) values ('$comp_id','$di')";
    $result = $db->query($q);
    $cd_id = $db->insert_id;
    if(isset($old_params)) {
        $q = "insert into dis_params (comp_dis_id, round_time, video_time, round_count, type, num, teams_count)
         values ('$cd_id','".$old_params->round_time."','".$old_params->video_time."','".$old_params->round_count."','".$old_params->type."','".$old_params->num."','".$old_params->teams_count."')";
        $result = $db->query($q);
    }
}

function update_dis_params() {
    global $db, $user, $init;
    if($user->id != 1) return "ACCESS DENIED";
    $request = $_REQUEST;
    $cdi = $request['comp_dis_id'];
    $q = "select * from dis_params where comp_dis_id = ".$cdi;
    $result = $db->query($q);
    $round_time = isset($request['round_time']) ? $request['round_time'] : 0;
    $insert = "insert into dis_params (comp_dis_id, bust, round_time, video_time, round_count, type, num, teams_count)
    values ('$cdi','".$request['bust']."','$round_time','".$request['video_time']."','".$request['round_count']."','".$request['type']."','".$request['num']."','".$request['teams_count']."')";
    $update = "update dis_params set bust = '".$request['bust']."', round_time = '$round_time', video_time = '".$request['video_time']."', round_count = '".$request['round_count']."',
    type = '".$request['type']."', num = '".$request['num']."', teams_count = '".$request['teams_count']."' where comp_dis_id = '$cdi'";
    $q = ($result->num_rows == 0) ? $insert : $update;
    $result = $db->query($q);
}

function finish_comp() {
    global $db, $user;
    if($user->id != 1) return "ACCESS DENIED";
    $q = "update users set active = '0' where id > 1";
    $result = $db->query($q);
}

function add_j_team() {
    global $db, $user, $init;
    if($user->id != 1) return "ACCESS DENIED";
    $comp_id = $init->comp->id;
    $request = $_REQUEST;
    if($request['j_id'] == "") return "---";
    $q = "insert into j_teams (comp_id, day, main_user_id) values ('$comp_id','".$request['date']."','".$request['j_id']."')";
    $result = $db->query($q);
    $team_id = $db->insert_id;
    $q = "insert into j_teams_users (j_teams_id, user_id, status) values ('$team_id','".$request['j_id']."', 3)";
    $result = $db->query($q);
}

function add_j_team_user() {
    global $db, $user, $init;
    if($user->id != 1) return "ACCESS DENIED";
    $comp_id = $init->comp->id;
    $request = $_REQUEST;
    if($request['j_id'] == "") return "---";
    $team_id = $request['team_id'];
    $q = "insert into j_teams_users (j_teams_id, user_id, status) values ('$team_id','".$request['j_id']."', 2)";
    $result = $db->query($q);
}

function add_rounds() {
    global $db, $user, $init;
    if($user->id != 1) return "ACCESS DENIED";
    $comp_id = $init->comp->id;
    $request = $_REQUEST;
    $cd_id = $request['cd_id'];
    $rounds_count = $init->comp_dis[$cd_id]->round_count;
    for ($i = 1; $i <= $rounds_count; $i++) {
        $name = "Round ".$i;
        $q = "insert into rounds (comp_dis_id, name, blocks) values ('$cd_id','$name','')";
        $result = $db->query($q);
    }
    $q = "insert into rounds (comp_dis_id, name, blocks) values ('$cd_id','TB','')";
    $result = $db->query($q);
}

function add_teams() {
    global $db, $user, $init;
    if($user->id != 1) return "ACCESS DENIED";
    $comp_id = $init->comp->id;
    $request = $_REQUEST;
    $cd_id = $request['cd_id'];
    $teams_count = $init->comp_dis[$cd_id]->teams_count;
    $num = $init->comp_dis[$cd_id]->num;
    for ($i = 1; $i <= $teams_count; $i++) {
        $num++;
        $q = "insert into teams (comp_dis_id, num, name) values ('$cd_id','$num','')";
        $result = $db->query($q);
    }
}