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 org.bukkit.Material;
import org.bukkit.configuration.ConfigurationSection;
import org.bukkit.configuration.InvalidConfigurationException;
import org.bukkit.configuration.file.YamlConfiguration;
import org.bukkit.inventory.ItemStack;

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, content) VALUES (?, ?, ?)";

        selectContent = "SELECT id, uuid, time, content FROM " + tableName + " WHERE uuid = ? ORDER BY time DESC";

        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 AUTO_INCREMENT,"//
                            + "`uuid` CHAR( 36 ) NOT NULL,"//
                            + "`time` BIGINT NOT NULL,"//
                            + "`content` LONGTEXT,"//
                            + "PRIMARY KEY ( `id` ), INDEX ( `uuid`, `time` ) ) ENGINE = innodb");
                    smt.close();
                }
                return null;
            }
        });
    }

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

    public DatabaseTreasureChestItem addItem(UUID owner, TreasureChestItem item) throws SQLException {
        return this.connection.runCommands(new SQLRunnable<DatabaseTreasureChestItem>() {
            @Override
            public DatabaseTreasureChestItem execute(Connection connection, SQLConnection sqlConnection) throws SQLException {
                PreparedStatement smt = sqlConnection.getOrCreateStatement(insertContent, Statement.RETURN_GENERATED_KEYS);

                smt.setString(1, owner.toString());
                long time = System.currentTimeMillis();
                smt.setLong(2, time);
                YamlConfiguration conf = new YamlConfiguration();
                conf.set("display", item.getDisplayItem());
                ConfigurationSection itemsSection = conf.createSection("items");
                ItemStack[] items = item.getPriceItems();
                if (items != null) {
                    for (int i = 0; i < items.length; i++) {
                        ItemStack stack = items[i];
                        itemsSection.set(Integer.toString(i), stack);
                    }
                }
                conf.set("money", item.getPriceMoney());
                smt.setString(3, conf.saveToString());
                smt.executeUpdate();
                ResultSet genKeys = smt.getGeneratedKeys();
                Integer id = null;
                if (genKeys.next()) {
                    id = genKeys.getInt(1);
                }
                genKeys.close();
                if (id == null) {
                    throw new SQLException("No id was generated!");
                }
                return new DatabaseTreasureChestItem(item.getDisplayItem(), item.getPriceItems(), item.getPriceMoney(), time, id);
            }
        });
    }

    public ArrayList<DatabaseTreasureChestItem> getPlayerItems(UUID owner) throws SQLException {
        return this.connection.runCommands(new SQLRunnable<ArrayList<DatabaseTreasureChestItem>>() {
            @Override
            public ArrayList<DatabaseTreasureChestItem> execute(Connection connection, SQLConnection sqlConnection) throws SQLException {
                ArrayList<DatabaseTreasureChestItem> result = new ArrayList<>();
                PreparedStatement smt = sqlConnection.getOrCreateStatement(selectContent);
                smt.setString(1, owner.toString());
                ResultSet rs = smt.executeQuery();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    long time = rs.getLong("time");
                    String content = rs.getString("content");
                    YamlConfiguration conf = new YamlConfiguration();
                    try {
                        conf.loadFromString(content);
                    } catch (InvalidConfigurationException e) {
                        e.printStackTrace();
                    }
                    ItemStack displayItem = conf.getItemStack("display");
                    if (displayItem == null || displayItem.getAmount() == 0 || displayItem.getType() == Material.AIR) {
                        displayItem = new ItemStack(Material.BEDROCK);
                    }
                    ArrayList<ItemStack> stacks = new ArrayList<>();
                    ConfigurationSection itemsSection = conf.getConfigurationSection("items");
                    for (String key : itemsSection.getKeys(false)) {
                        ItemStack stack = itemsSection.getItemStack(key);
                        if (stack != null && stack.getAmount() > 0 && stack.getType() != Material.AIR) {
                            stacks.add(stack);
                        }
                    }
                    ItemStack[] priceItems = stacks.toArray(new ItemStack[stacks.size()]);
                    int priceMoney = conf.getInt("money");
                    result.add(new DatabaseTreasureChestItem(displayItem, priceItems, priceMoney, time, id));
                }
                rs.close();
                return result;
            }
        });
    }

    public boolean deleteItem(UUID owner, int id) throws SQLException {
        return this.connection.runCommands(new SQLRunnable<Boolean>() {
            @Override
            public Boolean execute(Connection connection, SQLConnection sqlConnection) throws SQLException {
                PreparedStatement smt = sqlConnection.getOrCreateStatement(removeContent);
                smt.setInt(1, id);
                smt.setString(2, owner.toString());
                boolean updated = smt.executeUpdate() > 0;
                return updated;
            }
        });
    }

    public int deleteOldItems(long days) throws SQLException {
        return this.connection.runCommands(new SQLRunnable<Integer>() {
            @Override
            public Integer execute(Connection connection, SQLConnection sqlConnection) throws SQLException {
                PreparedStatement smt = sqlConnection.getOrCreateStatement(removeOldContent);
                long minTime = System.currentTimeMillis() - days * 24 * 60 * 60 * 1000;
                smt.setLong(1, minTime);
                int updated = smt.executeUpdate();
                return updated;
            }
        });
    }
}