package de.iani.treasurechest.database;

import de.iani.cubesideutils.sql.MySQLConnection;
import de.iani.cubesideutils.sql.SQLConnection;
import de.iani.treasurechest.TreasureChest;
import de.iani.treasurechest.TreasureChestItem;
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 java.util.logging.Level;
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;

public class TreasureChestDatabase {
    private final TreasureChest plugin;
    private final SQLConnection connection;

    private final String tableName;

    private final String insertContent;
    private final String selectContent;
    private final String removeContent;
    private final String removeOldContent;
    private final String selectAllContent;
    private final String updateContent;

    public TreasureChestDatabase(TreasureChest plugin, SQLConfig config) throws SQLException {
        this.plugin = plugin;
        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 < ?";

        selectAllContent = "SELECT id, uuid, time, content FROM " + tableName + " ORDER BY id DESC";

        updateContent = "UPDATE " + tableName + " SET content = ? WHERE id = ?";

        this.connection.runCommands((connection, sqlConnection) -> {
            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((connection, sqlConnection) -> {
            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() == null ? null : item.getDisplayItem().serializeAsBytes());
            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 == null ? null : stack.serializeAsBytes());
                }
            }
            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 boolean updateItem(DatabaseTreasureChestItem item) throws SQLException {
        return this.connection.runCommands((connection, sqlConnection) -> {
            PreparedStatement smt = sqlConnection.getOrCreateStatement(updateContent);

            YamlConfiguration conf = new YamlConfiguration();
            conf.set("display", item.getDisplayItem() == null ? null : item.getDisplayItem().serializeAsBytes());
            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 == null ? null : stack.serializeAsBytes());
                }
            }
            conf.set("money", item.getPriceMoney());
            smt.setString(1, conf.saveToString());
            smt.setInt(2, item.getId());
            int count = smt.executeUpdate();
            return count > 0;
        });
    }

    public ArrayList<DatabaseTreasureChestItem> getPlayerItems(UUID owner) throws SQLException {
        return this.connection.runCommands((connection, sqlConnection) -> {
            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) {
                    plugin.getLogger().log(Level.WARNING, "Could not load treasure chest item " + id + " for player " + owner, e);
                    continue;
                }
                ItemStack displayItem = getStack(conf, "display");
                if (displayItem == null || displayItem.getAmount() == 0 || displayItem.getType() == Material.AIR) {
                    plugin.getLogger().log(Level.WARNING, "No display item for item " + id + " for player " + owner);
                    continue;
                }
                ArrayList<ItemStack> stacks = new ArrayList<>();
                ConfigurationSection itemsSection = conf.getConfigurationSection("items");
                for (String key : itemsSection.getKeys(false)) {
                    ItemStack stack = getStack(itemsSection, 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;
        });
    }

    private ItemStack getStack(ConfigurationSection conf, String key) {
        Object object = conf.get(key);
        return object instanceof ItemStack stack ? stack : object instanceof byte[] bytes ? ItemStack.deserializeBytes(bytes) : null;
    }

    public ArrayList<DatabaseTreasureChestItem> getAllItems() throws SQLException {
        return this.connection.runCommands((connection, sqlConnection) -> {
            ArrayList<DatabaseTreasureChestItem> result = new ArrayList<>();
            PreparedStatement smt = sqlConnection.getOrCreateStatement(selectAllContent);
            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) {
                    plugin.getLogger().log(Level.WARNING, "Could not load treasure chest item " + id, e);
                    continue;
                }
                ItemStack displayItem = getStack(conf, "display");
                if (displayItem == null || displayItem.getAmount() == 0 || displayItem.getType() == Material.AIR) {
                    plugin.getLogger().log(Level.WARNING, "No display item for item " + id);
                    continue;
                }
                ArrayList<ItemStack> stacks = new ArrayList<>();
                ConfigurationSection itemsSection = conf.getConfigurationSection("items");
                for (String key : itemsSection.getKeys(false)) {
                    ItemStack stack = getStack(itemsSection, 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((connection, sqlConnection) -> {
            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((connection, sqlConnection) -> {
            PreparedStatement smt = sqlConnection.getOrCreateStatement(removeOldContent);
            long minTime = System.currentTimeMillis() - days * 24 * 60 * 60 * 1000;
            smt.setLong(1, minTime);
            int updated = smt.executeUpdate();
            return updated;
        });
    }
}