/**
 * This ckass communicates with the database and analyzes the data
 *
 * @author Eden
 * @version 1.0
 */

package battleship.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class SQLConnection {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;
    private Statement stmt;

    /**
     * Initializing the co
     * @return connection to datadase, null if can't connect
     */
    private Connection createConnection() {

        try {
            String url = "jdbc:sqlserver://localhost:1433;databaseName=Battleship"
                    + ";integratedSecurity=true";

            // Create the connection
            conn = DriverManager.getConnection(url);

            // printing a message
            System.out.println("database works !!!");

        } catch (SQLException ex) {

            // print error msg
            System.out.println("error: unable to connect");
            conn = null;
        }

        return conn;
    }

    /**
     * close the database connection objects
     * the fonction Closing only the open objects
     */
    private void closeConnections() {

        try {

            if (!rs.isClosed()){
                rs.close();
            }
            if (!ps.isClosed()){
                ps.close();
            }
            if (!conn.isClosed()){
                conn.close();
            }

        } catch (SQLException ex) {
            System.out.println("Error: close connection in SQLConnection class ");
        }
    }

    /**
     *
     * @param ip user IP, max len 15
     * @param mac the user mac address, max len 20
     * @param Nickname max len 15
     * @param mail max len 50
     * @param password max len 10
     * @return -1 mail in use, -2 connection Error, Positive number is the userID
     */
    public String Register(String ip, String mac, String Nickname,
            String mail, String password) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query, '?' for parameters
            String sql = "exec P_registrar ?, ?, ?, ?, ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setString(1, Nickname);
            ps.setString(2, mail);
            ps.setString(3, password);
            ps.setString(4, ip);
            ps.setString(5, mac);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

            // return clear result
            return result.trim();
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return "-2";
        }
    }

    /**
     *
     * @param id
     * @param ip
     * @param mac
     * @return -1 connection Error
     */
    public String logout(int id, String ip, String mac) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_logout ?, ?, ?, ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.setString(2, ip);
            ps.setString(3, mac);
            ps.setInt(4, 0);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

            // return clear result
            return result.trim();
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return "-1";
        }
    }

    /**
     *
     * @param mail
     * @param password
     * @param ip
     * @param mac
     * @return -1 user no exists, -2 user already logged in, -3 connection Error
     */
    public String login(String mail, String password, String ip, String mac) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_login ?, ?, ?, ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setString(1, mail);
            ps.setString(2, password);
            ps.setString(3, ip);
            ps.setString(4, mac);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

            // return clear result
            return result.trim();
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return "-3";
        }
    }

    /**
     *
     * @param mail
     * @param ip
     * @param mac
     * @return result[0] -1 Email does not exist on system, null: connection
     * Error
     */
    public String[] resetPassword(String mail, String ip, String mac) {

        String[] result = new String[2];

        result[0] = "null";
        result[1] = "null";

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_reset_password ?, ?, ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setString(1, mail);
            ps.setString(2, ip);
            ps.setString(3, mac);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            while (rs.next()) {
                result[0] = rs.getString(1).trim();
                result[1] = rs.getString(2).trim();
            }

            // close the objects
            closeConnections();

            // return clear result
            return result;
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return result;
        }
    }

    /**
     *
     * @param userID
     * @return result[0] game id, result[1] gust id, null: connection Error
     */
    public String[] findGame(int userID) {

        String[] result = new String[2];

        result[0] = "null";
        result[1] = "null";

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_find_game ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            while (rs.next()) {
                result[0] = rs.getString(1).trim();
                result[1] = rs.getString(2).trim();
            }

            // close the objects
            closeConnections();

            // return clear result
            return result;
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return result;
        }
    }

    /**
     *
     * @param userID
     * @param gameID
     * @param board
     * @return 1 succeeded, -1 connection Error
     */
    public String setBoard(int userID, int gameID, String board) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_insert_my_strategic_board ?, ?, ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);
            ps.setInt(2, gameID);
            ps.setString(3, board);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

            // return clear result
            return result;
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return "-1";
        }
    }

    /**
     * Disconnects inactive users
     *
     * @return -1 connection Error, number of users disconnected
     */
    public int disconnectUsers() {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_activity_check";

            // Set parameters to query
            stmt = conn.createStatement();

            // Run the query
            rs = stmt.executeQuery(sql);

            // Get the rusult from SQL
            int result = 7;

            while (rs.next()) {
                result = rs.getInt(1);
            }

            // close the objects
            stmt.close();
            rs.close();
            conn.close();

            return result;
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return -1;
        }
    }

    /**
     *
     * @param id
     * @return -1 The server cut you off, -2 connection Error
     */
    public String updateLastSeen(int id) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_update_last_seen ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

            // return clear result
            return result;
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return "-2";
        }
    }

    /**
     *
     * @param userID
     * @param gameID
     * @param move
     * @return -2connection Error, 0 miss, 1 hit, 2 win
     */
    public String setMove(int userID, int gameID, String move) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_guess_location ?, ?, ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);
            ps.setInt(2, gameID);
            ps.setString(3, move);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

            // return clear result
            return result;
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return "-2";
        }
    }

    /**
     *
     * @param gameID
     * @return -2 connection Error, 0 no partner else partner ID
     */
    public String LookForAPartner(int gameID) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_wait_to_player ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setInt(1, gameID);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

            // return clear result
            return result;
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return "-2";
        }
    }

    /**
     *
     * @param userID
     * @param gameID
     * @return -1, loss, 0 The opponent's turn, 1 Your turn, 2 win, -2
     * connection Error
     */
    public String checkGameUpdates(int userID, int gameID) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec what_new_in_game ?, ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);
            ps.setInt(2, gameID);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

            // return clear result
            return result;
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return "-2";
        }
    }

    /**
     *
     * @param userID
     * @return -2 connection Error, else the NickName
     */
    public String getNickName(int userID) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_userID_to_NickName ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userID);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

            // return clear result
            return result;
        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
            return "-2";
        }
    }

    public void FinishedGame(int gameID) {

        try {

            // create connection to datacase
            conn = createConnection();

            // the Query
            String sql = "exec P_finished_game ?";

            // Set parameters to query
            ps = conn.prepareStatement(sql);
            ps.setInt(1, gameID);

            // Run the query
            rs = ps.executeQuery();

            // Get the rusult from SQL
            String result = "";

            while (rs.next()) {
                result = rs.getString(1);
            }

            // close the objects
            closeConnections();

        } catch (SQLException ex) {
            System.out.println(" -> " + ex);
            closeConnections();
        }
    }

}