<?php

function handle_pdo_exception($e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

function check_user_id($con, $user_id) {
    try {
        $stmt = $con->prepare('SELECT user_id FROM users WHERE user_id = :user_id limit 1');
        $stmt->execute(['user_id' => $user_id]);

        if($stmt) {
            return true;
        } else {
            return false;
        }
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_user_data_name($con, $user_name) {
    try {
        $stmt = $con->prepare('SELECT * FROM users WHERE user_name = :user_name limit 1');
        $stmt->execute(['user_name' => $user_name]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt->fetch();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_user_data_id($con, $user_id) {
    try {
        $stmt = $con->prepare('SELECT * FROM users WHERE user_id = :user_id limit 1');
        $stmt->execute(['user_id' => $user_id]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt->fetch();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function add_user($con, $username, $user_id, $user_group, $password, $salt) {
    try {
        $stmt = $con->prepare("INSERT INTO users (user_id, password, user_name, salt, user_group) VALUES (?, ?, ?, ?, ?)");
        $stmt->bind_param("sssss", $user_id, $password, $username, $salt, $user_group);
        $stmt->execute();        
    } catch(PDOExeption $e) {
        handle_pdo_exception($e);
    }   
}

function get_stations($con) {
    try {
        $stmt = $con->prepare("SELECT name, standort FROM Station");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exceptio($e);
    }
}

function get_stations_all($con) {
    try {
        $stmt = $con->prepare("SELECT * FROM Station");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exceptio($e);
    }
}

function get_teams($con) {
    try {
        $stmt = $con->prepare("SELECT * FROM Mannschaft");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_total_score($con) {
    try {
        $stmt = $con->prepare("SELECT M.name as Mannschaftsname, M.feuerwehr as Feuerwehr, SUM(P.punkte) as Gesamtpunkte FROM Punkte as P, Mannschaft as M, Station as S WHERE P.m_id = M.m_id AND P.s_id = S.s_id AND S.gewertet = \"1\" GROUP BY M.m_id ORDER BY Gesamtpunkte DESC");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_station_ranking($con) {
    try {
        $stmt = $con->prepare("SELECT S.name as Stationsname, SUM(P.punkte) as Gesamtpunkte FROM Punkte as P, Station as S WHERE P.s_id = S.s_id AND S.gewertet = \"1\" GROUP BY S.s_id ORDER BY Gesamtpunkte DESC");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_station_name($con, $s_id) {
    try {
        $stmt = $con->prepare("SELECT name FROM Station WHERE s_id= :s_id");
        $stmt->execute(['s_id' => $s_id]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt->fetch();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_station($con, $s_id) {
    try {
        $stmt = $con->prepare("SELECT P.m_id as m_id, M.name as Name, M.feuerwehr as Feuerwehr, P.punkte as Punkte, P.zeit as Zeit FROM Punkte as P, Station as S, Mannschaft as M WHERE P.s_id = S.s_id AND S.s_id = :s_id AND M.m_id = P.m_id ORDER BY Punkte DESC");
        $stmt->execute(['s_id' => $s_id]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_teams_no_points($con, $s_id) {
    try {
        $stmt = $con->prepare("SELECT M.* FROM Mannschaft M WHERE M.m_id NOT IN (SELECT P.m_id FROM Punkte P WHERE P.s_id = :s_id)");
        $stmt->execute(['s_id' => $s_id]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_excepiton($e);
    }
}

function station_exists($con, $s_id) {
    try {
        $stmt = $con->prepare("SELECT s_id FROM Station WHERE s_id = :s_id");
        $stmt->execute(['s_id' => $s_id]);
        if($stmt->rowCount() > 0) {
            return true;
        } else {
            return false;
        }
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function write_points($con, $s_id, $m_id, $points, $time) {
    try {
        $stmt = $con->prepare("INSERT INTO Punkte (m_id, s_id, punkte, zeit) VALUES (?, ?, ?, ?)");
        $stmt->bindParam(1, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->bindParam(3, $points, PDO::PARAM_INT);
        $stmt->bindParam(4, $time, PDO::PARAM_STR);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function write_station($con, $station_name, $station_pos, $station_direct_points) {
    try {
        $stmt = $con->prepare("INSERT INTO Station (name, standort, direkte_punkte) VALUES (?, ?, ?)");
        $stmt->bindParam(1, $station_name, PDO::PARAM_STR);
        $stmt->bindParam(2, $station_pos, PDO::PARAM_STR);
        $stmt->bindParam(3, $station_direct_points, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exceptio($e);
    }
}

function get_fire_departments($con) {
    try {
        $stmt = $con->prepare("SELECT DISTINCT feuerwehr FROM Mannschaft");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function write_team($con, $team_name, $fire_department) {
    try {
        $stmt = $con->prepare("INSERT INTO Mannschaft (name, feuerwehr) VALUES (?, ?)");
        $stmt->bindParam(1, $team_name, PDO::PARAM_STR);
        $stmt->bindParam(2, $fire_department, PDO::PARAM_STR);
        $stmt->execute();
    } catch(PDOEXCEPTION $e) {
        handle_pdo_exception($e);
    }
}

function get_users($con) {
    try {
        $stmt = $con->prepare("SELECT id, user_name, user_group, s_id FROM users");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_user($con, $id) {
    try {
        $stmt = $con->prepare("SELECT * FROM users WHERE id = :id");
        $stmt->execute(['id' => $id]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function write_user($con, $user_name, $user_id, $phash, $salt, $user_group, $s_id) {
    try {
        $stmt = $con->prepare("INSERT INTO users (user_id, password, user_name, salt, user_group, s_id) VALUES (?, ?, ?, ?, ?, ?)");
        $stmt->bindParam(1, $user_id, PDO::PARAM_STR);
        $stmt->bindParam(2, $phash, PDO::PARAM_STR);
        $stmt->bindParam(3, $user_name, PDO::PARAM_STR);
        $stmt->bindParam(4, $salt, PDO::PARAM_STR);
        $stmt->bindParam(5, $user_group, PDO::PARAM_STR);
        $stmt->bindParam(6, $s_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function change_user_name($con, $id, $user_name) {
    try {
        $stmt = $con->prepare("UPDATE users SET user_name = ? WHERE id= ?");
        $stmt->bindParam(1, $user_name, PDO::PARAM_STR);
        $stmt->bindParam(2, $id, PDO::PARAM_STR);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function change_user_group($con, $id, $user_group, $s_id) {
    try {
        $stmt = $con->prepare("UPDATE users SET user_group = ?, s_id = ? WHERE id= ?");
        $stmt->bindParam(1, $user_group, PDO::PARAM_STR);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->bindParam(3, $id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function change_password($con, $id, $password) {
    try {
        $stmt = $con->prepare("UPDATE users SET password = ? WHERE id= ?");
        $stmt->bindParam(1, $password, PDO::PARAM_STR);
        $stmt->bindParam(2, $id, PDO::PARAM_STR);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function change_s_id($con, $id, $s_id) {
    try {
        $stmt = $con->prepare("UPDATE users SET s_id = ? WHERE id= ?");
        $stmt->bindParam(1, $s_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $id, PDO::PARAM_STR);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_result($con, $m_id, $s_id) {
    try {
        $stmt = $con->prepare("SELECT P.*, M.name as name, M.feuerwehr as feuerwehr FROM Punkte P, Mannschaft M WHERE M.m_id = P.m_id AND P.m_id = ? AND P.s_id = ?");
        $stmt->bindParam(1, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_time($con, $m_id, $s_id) {
    try {
        $stmt = $con->prepare("SELECT zeit FROM Punkte WHERE m_id = ? AND s_id = ?");
        $stmt->bindParam(1, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_minutes($con, $m_id, $s_id) {
    try {
        $stmt = $con->prepare("SELECT MINUTE(zeit) as minutes FROM Punkte WHERE m_id = ? AND s_id = ?");
        $stmt->bindParam(1, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_seconds($con, $m_id, $s_id) {
    try {
        $stmt = $con->prepare("SELECT SECOND(zeit) as seconds FROM Punkte WHERE m_id = ? AND s_id = ?");
        $stmt->bindParam(1, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_millis($con, $m_id, $s_id) {
    try {
        $stmt = $con->prepare("SELECT MICROSECOND(zeit) as millis FROM Punkte WHERE m_id = ? AND s_id = ?");
        $stmt->bindParam(1, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_points($con, $m_id, $s_id) {
    try {
        $stmt = $con->prepare("SELECT punkte FROM Punkte WHERE m_id = ? AND s_id = ?");
        $stmt->bindParam(1, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function change_points($con, $m_id, $s_id, $points) {
    try {
        $stmt = $con->prepare("UPDATE Punkte as P SET P.punkte = ? WHERE P.m_id = ? AND P.s_id = ?");
        $stmt->bindParam(1, $points, PDO::PARAM_INT);
        $stmt->bindParam(2, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(3, $s_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function change_time($con, $m_id, $s_id, $time) {
    try {
        $stmt = $con->prepare("UPDATE Punkte as P SET P.zeit = ? WHERE P.m_id = ? AND P.s_id = ?");
        $stmt->bindParam(1, $time, PDO::PARAM_INT);
        $stmt->bindParam(2, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(3, $s_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_station_all($con, $s_id) {
    try {
        $stmt = $con->prepare("SELECT * FROM Station WHERE s_id = :s_id");
        $stmt->execute(['s_id' => $s_id]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function update_station_name($con, $s_id, $name) {
    try {
        $stmt = $con->prepare("UPDATE Station SET name = ? WHERE s_id = ?");
        $stmt->bindParam(1, $name, PDO::PARAM_STR);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function update_station_pos($con, $s_id, $pos) {
    try {
        $stmt = $con->prepare("UPDATE Station SET standort = ? WHERE s_id = ?");
        $stmt->bindParam(1, $pos, PDO::PARAM_STR);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function update_station_gewertet($con, $s_id, $gewertet) {
    try {
        $stmt = $con->prepare("UPDATE Station SET gewertet = ? WHERE s_id = ?");
        $stmt->bindParam(1, $gewertet, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOExeption $e) {
        handle_pdo_exception($e);
    }
}

function update_station_direct_points($con, $s_id, $direct_points) {
    try {
        $stmt = $con->prepare("UPDATE Station SET direkte_punkte = ? WHERE s_id = ?");
        $stmt->bindParam(1, $direct_points, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOExeption $e) {
        handle_pdo_exception($e);
    }
}

function get_team($con, $m_id) {
    try {
        $stmt = $con->prepare("SELECT * FROM Mannschaft WHERE m_id = :m_id");
        $stmt->execute(['m_id' => $m_id]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function update_team_name($con, $m_id, $name) {
    try {
        $stmt = $con->prepare("UPDATE Mannschaft SET name = ? WHERE m_id = ?");
        $stmt->bindParam(1, $name, PDO::PARAM_STR);
        $stmt->bindParam(2, $m_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function update_team_fire_department($con, $m_id, $dep) {
    try {
        $stmt = $con->prepare("UPDATE Mannschaft SET feuerwehr = ? WHERE m_id = ?");
        $stmt->bindParam(1, $dep, PDO::PARAM_STR);
        $stmt->bindParam(2, $m_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_direct_points($con, $s_id) {
    try {
        $stmt = $con->prepare("SELECT direkte_punkte FROM Station WHERE s_id = :s_id");
        $stmt->execute(['s_id' => $s_id]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function write_result_db($con, $s_id, $m_id, $result) {
    try {
        $stmt = $con->prepare("INSERT INTO Ergebnisse (s_id, m_id, erg) VALUES (?, ?, ?)");
        $stmt->bindParam(1, $s_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $m_id, PDO::PARAM_INT);
        $stmt->bindParam(3, $result, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_results($con, $s_id) {
    try {
        $stmt = $con->prepare("SELECT * FROM Ergebnisse WHERE s_id = :s_id ORDER BY erg DESC");
        $stmt->execute(['s_id' => $s_id]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function update_result_db($con, $s_id, $m_id, $result) {
    try {
        $stmt = $con->prepare("UPDATE Ergebnisse SET erg = ? WHERE s_id = ? AND m_id = ?");
        $stmt->bindParam(1, $result, PDO::PARAM_INT);
        $stmt->bindParam(2, $s_id, PDO::PARAM_INT);
        $stmt->bindParam(3, $m_id, PDO::PARAM_INT);
        $stmt->execute();
    } catch(PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_result_team_station($con, $s_id, $m_id) {
    try {
        $stmt = $con->prepare("SELECT * FROM Ergebnisse WHERE s_id = ? AND m_id = ?");
        $stmt->bindParam(1, $s_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $m_id, PDO::PARAM_INT);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $result = $stmt->fetch()['erg'];
        return $result;
    } catch( PDOException $e) {
        handle_pdo_exception($e);
    }
}

function get_id_user_by_user_id($con, $user_id) {
    try {
        $stmt = $con->prepare("SELECT * FROM users WHERE user_id = ?");
        $stmt->bindParam(1, $user_id, PDO::PARAM_STR);
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt->fetch()['id'];
    } catch( PDOException $e) {
        handle_pdo_exception($e);
    }
}