package fh.at.ooe.mc10;

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

import javax.jws.WebService;
import javax.jws.soap.SOAPBinding;

@WebService
@SOAPBinding (style = SOAPBinding.Style.RPC)

public class DBHandle {
    private static Connection conn;
    private static Statement stmt;
    private static ResultSet rs;

    public static void main(String[] args) throws SQLException {
        DBHandle dbh = new DBHandle();
        dbh.printUserDetails();
        dbh.getUsers();
    }

    public DBHandle() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost/citydetectivedatabase", "root", "");
            stmt = conn.createStatement();
        } catch (SQLException ex) {
            printEx(ex);
        }
    }

    /**
     *
     * Prints the detailed information of an SQLException.
     *
     * @param ex
     *            SQLException
     */
    public static void printEx(Exception ex) {
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ((SQLException) ex).getSQLState());
        System.out
                .println("VendorError: " + ((SQLException) ex).getErrorCode());
    }

    /**
     * @param groupname
     * @param password
     * @return true if password is correct
     */
    public boolean joinGroup(String groupname, String password) {
        try {
            stmt = conn.createStatement();
            rs = stmt
                    .executeQuery("SELECT password FROM citydetectivegroupdata WHERE groupname='"
                            + groupname + "'");
            if (rs.getString(1) == password) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            printEx(ex);
            return false;
        }
    }

    /**
     * Prints all user names, highscores and date to the console
     */
    public void printUserDetails() {
        User[] users = getUsers();
        System.out.println("Username \t Highscore \t Date \t\t Group");
        for (int i = 0; i < users.length; i++) {
            if (users[i].get_uname().length() < 8) {
                System.out.println(users[i].get_uname() + '\t' + '\t'
                        + users[i].get_highscore() + '\t' + '\t'
                        + users[i].get_date().toString() + '\t' + users[i].get_group());
            } else {
                System.out.println(users[i].get_uname() + '\t'
                        + users[i].get_highscore() + '\t' + '\t'
                        + users[i].get_date().toString() + '\t' + users[i].get_group());
            }
        }
    }

    /**
     * @return is a User[] of all users from the database
     */
    public User[] getUsers() {
        try {
            rs = stmt.executeQuery("SELECT * FROM citydetectivedata");
            int count = 0;
            while (rs.next()) { // count entries
                count++;
            }

            rs = stmt
                    .executeQuery("SELECT * FROM citydetectivedata ORDER BY citydetectivedata.highscore DESC");

            User[] users = new User[count];
            int i = 0;
            while (rs.next()) {
                User u = new User();
                u.set_uname(rs.getString(1));
                u.set_highscore(rs.getInt(2));
                u.set_date(rs.getDate(3));
                u.set_group(rs.getString(4));
                users[i] = u;
                i++;
            }
            return users;
        } catch (SQLException ex) {
            printEx(ex);
            return null;
        }
    }

    /**
     * @param name
     * @return false if user does not exist, true otherwise
     */
    public boolean existsUser(String name) {
        try {
            stmt = conn.createStatement();
            rs = stmt
                    .executeQuery("SELECT * FROM citydetectivedata WHERE username='"
                            + name + "'");
            if (rs == null) {
                return false;
            }
            return true;
        } catch (SQLException ex) {
            printEx(ex);
            return false;
        }
    }

    /**
     * @param groupname
     * @return
     */
    public boolean existsGroup(String groupname) {
        try {
            stmt = conn.createStatement();
            rs = stmt
                    .executeQuery("SELECT * FROM citydetectivegroupdata WHERE groupname='"
                            + groupname + "'");
            if (rs == null) {
                return false;
            }
            return true;
        } catch (SQLException ex) {
            printEx(ex);
            return false;
        }
    }

    /**
     * @param name
     * @param password
     * @return true if succeeded, false otherwise
     * @throws SQLException
     */
    public boolean createUser(String name) throws SQLException {
        try {
            Date d = new Date(System.currentTimeMillis());

            stmt = conn.createStatement();
            stmt.execute("INSERT INTO `citydetectivedatabase`.`citydetectivedata` (`username` ,`highscore`, `date`, `group`) VALUES ('"
                    + name
                    + "', '"
                    + 0
                    + "', '"
                    + d.toString()
                    + "', '"
                    + "null" + "');");
            return true;
        } catch (SQLException ex) {
            printEx(ex);
            return false;
        }
    }

    /**
     * @param groupname
     * @param password
     * @return
     * @throws SQLException
     */
    public boolean createGroup(String groupname, String password)
            throws SQLException {
        try {
            stmt = conn.createStatement();
            stmt.execute("INSERT INTO `citydetectivedatabase`.`citydetectivegroupdata` (`groupname` ,`password`) VALUES ('"
                    + groupname + "', '" + password + "');");
            return true;
        } catch (SQLException ex) {
            printEx(ex);
            return false;
        }
    }

    /**
     * @param name
     * @param newHighscore
     * @return
     */
    public boolean setNewUserHighscore(String name, int newHighscore) {
        try {
            stmt = conn.createStatement();
            stmt.execute("UPDATE citydetectivedata SET highscore ='"
                    + newHighscore + "' WHERE username='" + name + "'");
            return true;
        } catch (SQLException ex) {
            printEx(ex);
            return false;
        }
    }
}