package com.scoresSystem.Vendor;


import com.scoresSystem.Models.Score;
import com.scoresSystem.Models.User;

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

/**
 * Created by Bashar on 9/11/2017.
 */
public class DBManager {
    //Connection
    private static Connection connection;
    private static Statement statement;

    private static void connect(){
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/scoresSystem", "root", "");
            statement = connection.createStatement();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    private static void closeConnection(){
        try {
            connection.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    public static User getUser(String id) {
        User targetUser = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            connect();
            ResultSet result = statement.executeQuery("SELECT * FROM Users WHERE ID=" + id);

            while (result.next()) {
                targetUser = new User(

                        result.getString("iid"),
                        result.getString("id"),
                        result.getString("name"),
                        result.getString("email"),
                        result.getString("password"),
                        result.getString("type"),
                        result.getString("createdBy"),
                        result.getString("updatedBy"),
                        result.getTimestamp("createdAt") != null ? new Date(result.getTimestamp("createdAt").getTime()) : null,
                        result.getTimestamp("updatedAt") != null ? new Date(result.getTimestamp("updatedAt").getTime()) : null,
                        getAllScores(result.getString("id"))

                );
                break;
            }


            result.close();
            closeConnection();


        } catch (SQLException e) {
            e.printStackTrace();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        return targetUser;

    }



    public static Score getLastScore(String id) {
        Score targetScore = null;

        try{
            Class.forName("com.mysql.jdbc.Driver");

            connect();
            ResultSet result = statement.executeQuery("SELECT * FROM Scores WHERE ID=" + id);

            while (result.next()) {
                targetScore = new Score(

                        result.getString("iid"),
                        result.getString("id"),
                        result.getDouble("score"),
                        result.getString("createdBy"),
                        result.getString("updatedBy"),
                        result.getTimestamp("createdAt") != null ? new Date(result.getTimestamp("createdAt").getTime()) : null,
                        result.getTimestamp("updatedAt") != null ? new Date(result.getTimestamp("updatedAt").getTime()) : null

                );
                break;
            }
            result.close();
            closeConnection();

        }catch(ClassNotFoundException e){
            e.printStackTrace();
        }catch (SQLException e){
            e.printStackTrace();
        }

        return targetScore;
    }


    public static List<Score> getAllScores(String id) {
        Score targetScore = null;
        List<Score> scores = new ArrayList<>();

        try{
            Class.forName("com.mysql.jdbc.Driver");

            //Connection
            connect();
            ResultSet result = statement.executeQuery("SELECT * FROM Scores WHERE ID=" + id);

            while (result.next()) {
                targetScore = new Score(

                        result.getString("iid"),
                        result.getString("id"),
                        result.getDouble("score"),
                        result.getString("createdBy"),
                        result.getString("updatedBy"),
                        result.getTimestamp("createdAt") != null ? new Date(result.getTimestamp("createdAt").getTime()) : null,
                        result.getTimestamp("updatedAt") != null ? new Date(result.getTimestamp("updatedAt").getTime()) : null

                );
                scores.add(targetScore);
            }

            result.close();
            closeConnection();


        }catch(ClassNotFoundException e){
            e.printStackTrace();
        }catch (SQLException e){
            e.printStackTrace();
        }

        return scores;
    }


}