package sk.tuke.gamestudio.service;

import sk.tuke.gamestudio.entity.Rating;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static sk.tuke.gamestudio.game.slidealama.core.Field.GAME_NAME;

public class RatingServiceJDBC implements RatingService{

    public static final String URL = "jdbc:postgresql://localhost/gamestudio";
    public static final String USER = "postgres";
    public static final String PASSWORD = "boston";

    public static final String INSERT_RATING =
            "INSERT INTO rating (player,game, ratings, playedon) VALUES (?, ?, ?, ?)";

    public static final String SELECT_RATING =
            "SELECT player, game, ratings, playedon FROM rating WHERE game = ? ORDER BY ratings DESC LIMIT 10;";

    public static final String SELECT_AVG =
            "SELECT avg(ratings) FROM rating";

    public static final String SELECT_RATE2 =
            "SELECT ratings FROM rating\n"
                    + "where player = ?";


    @Override
    public void setRating(Rating rating) throws RatingException {
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            try(PreparedStatement ps = connection.prepareStatement(INSERT_RATING)){
                ps.setString(1, rating.getPlayer());
                ps.setString(2, rating.getGame());
                ps.setInt(3, rating.getRating());
                ps.setDate(4, new java.sql.Date(rating.getRatedon().getTime()));
                ps.executeUpdate();
            }
        } catch (SQLException e) {
            throw new RatingException("Error saving score", e);
        }
    }

    @Override
    public int getAverageRating(String game) throws RatingException {
        int result = 0;
        try(Connection connection=DriverManager.getConnection(URL,USER, PASSWORD);
            Statement stm = connection.createStatement();
            ResultSet rs = stm.executeQuery(SELECT_AVG)) {
            rs.next();
            result = rs.getInt(1);

        } catch (SQLException e) {
            throw new RatingException(e.getMessage());
        }
        return result;
    }


    @Override
    public int getRating(String game, String player) throws RatingException {

        int result = 0;
        ResultSet rs = null;
        try(Connection connection =
                    DriverManager.getConnection(URL, USER, PASSWORD);
            PreparedStatement stm = connection.prepareStatement(SELECT_RATE2)) {
            stm.setString(1, player);
            rs = stm.executeQuery();
            if(rs.next())
                result = rs.getInt(1);

        } catch (SQLException e) {
            throw new RatingException(e.getMessage());
        }
        return result;
    }



    @Override
    public List<Rating> getRatings(String game) throws ScoreException {
        List<Rating>ratings = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            try(PreparedStatement ps = connection.prepareStatement(SELECT_RATING)){
                ps.setString(1, game);
                try(ResultSet rs = ps.executeQuery()) {
                    while(rs.next()) {
                        Rating rating = new Rating(
                                rs.getString(1),
                                rs.getString(2),
                                rs.getInt(3),
                                rs.getTimestamp(4)
                        );
                        ratings.add(rating);
                    }
                }
            }
        } catch (SQLException e) {
            throw new ScoreException("Error loading score", e);
        }
        return ratings;
    }

    public static void main(String[] args) throws Exception {
        Rating rating=new Rating("vlado",GAME_NAME,5,new Date());
        RatingService ratingService=new RatingServiceJDBC();
        ratingService.setRating(rating);
        System.out.println(ratingService.getRatings(GAME_NAME));
    }
}