package de.iani.treasurechest.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.UUID;

import de.iani.playerUUIDCache.CachedPlayer;
import de.iani.playerUUIDCache.PlayerUUIDCache;
import de.iani.treasurechest.TreasureChestItem;
import de.iani.treasurechest.util.sql.MySQLConnection;
import de.iani.treasurechest.util.sql.SQLConnection;
import de.iani.treasurechest.util.sql.SQLRunnable;

public class TreasureChestDatabase {
    private final SQLConnection connection;

    private final String tableName;

    private final String insertContent;

    private final String selectContent;

    private final String removeContent;

    private final String removeOldContent;

    public TreasureChestDatabase(SQLConfig config) throws SQLException {
        connection = new MySQLConnection(config.getHost(), config.getDatabase(), config.getUser(), config.getPassword());
        this.tableName = config.getTablePrefix() + "_content";

        insertContent = "INSERT INTO " + tableName + " (uuid, time, displayItem, content) VALUES (?, ?, ?, ?)";

        selectContent = "SELECT id, uuid, time, displayItem, content FROM " + tableName + " WHERE uuid = ?";

        removeContent = "DELETE FROM " + tableName + " WHERE id = ? AND uuid = ?";

        removeOldContent = "DELETE FROM " + tableName + " WHERE time < ?";

        this.connection.runCommands(new SQLRunnable<Void>() {
            @Override
            public Void execute(Connection connection, SQLConnection sqlConnection) throws SQLException {
                if (!sqlConnection.hasTable(tableName)) {
                    Statement smt = connection.createStatement();
                    smt.executeUpdate("CREATE TABLE `" + tableName + "` ("//
                            + "`id` INT NOT NULL,"//
                            + "`uuid` CHAR( 36 ) NOT NULL,"//
                            + "`time` BIGINT NOT NULL,"//
                            + "`displayItem` MEDIUMTEXT ,"//
                            + "`content` MEDIUMTEXT ,"//
                            + "PRIMARY KEY ( `id` ), INDEX ( `uuid` ) ) ENGINE = innodb");
                    smt.close();
                }
                return null;
            }
        });
    }

    public void disconnect() {
        connection.disconnect();
    }

    public void addItem(final UUID owner, final String displayItem, final String content) throws SQLException {
        this.connection.runCommands(new SQLRunnable<Void>() {
            @Override
            public Void execute(Connection connection, SQLConnection sqlConnection) throws SQLException {
                PreparedStatement smt = sqlConnection.getOrCreateStatement(insertContent);

                smt.setString(1, owner.toString());
                smt.setLong(2, System.currentTimeMillis());
                smt.setString(3, displayItem);
                smt.setString(4, content);
                smt.executeUpdate();
                return null;
            }
        });

    }

    public ArrayList<TreasureChestItem> getPlayerItems(final UUID uuid) throws SQLException {
        return this.connection.runCommands(new SQLRunnable<ArrayList<TreasureChestItem>>() {
            @Override
            public ArrayList<TreasureChestItem> execute(Connection connection, SQLConnection sqlConnection) throws SQLException {
                PreparedStatement smt = sqlConnection.getOrCreateStatement(selectContent);
                smt.setString(1, uuid.toString());
                ResultSet rs = smt.executeQuery();
                while (rs.next()) {
                    // TODO
                }
                rs.close();
                return null;
            }
        });
    }

    public CachedPlayer getPlayer(final String name) throws SQLException {
        return this.connection.runCommands(new SQLRunnable<CachedPlayer>() {
            @Override
            public CachedPlayer execute(Connection connection, SQLConnection sqlConnection) throws SQLException {
                String realName = name;
                PreparedStatement smt = sqlConnection.getOrCreateStatement(selectPlayerByName);
                smt.setString(1, name);
                ResultSet rs = smt.executeQuery();

                UUID uuid = null;
                long time = Long.MIN_VALUE;
                while (rs.next()) {
                    long thisTime = rs.getLong(3);
                    if (thisTime > time) {
                        try {
                            uuid = UUID.fromString(rs.getString(1));
                            realName = rs.getString(2);
                            time = thisTime;
                        } catch (IllegalArgumentException e) {
                            // ignore invalid uuid
                        }
                    }
                }
                rs.close();
                if (uuid != null) {
                    return new CachedPlayer(uuid, realName, time, System.currentTimeMillis());
                }
                return null;
            }
        });
    }

    public void deleteOldPlayerProfiles() throws SQLException {
        this.connection.runCommands(new SQLRunnable<Void>() {
            @Override
            public Void execute(Connection connection, SQLConnection sqlConnection) throws SQLException {
                PreparedStatement smt = sqlConnection.getOrCreateStatement(deleteOldPlayerProfiles);
                smt.setLong(1, System.currentTimeMillis() - PlayerUUIDCache.PROFILE_PROPERTIES_CACHE_EXPIRATION_TIME);
                smt.executeUpdate();
                return null;
            }
        });
    }
}