import lombok.Getter;
import lombok.NonNull;
import lombok.SneakyThrows;
import org.broken.cheststorage.ChestStorage;
import org.broken.cheststorage.data.ChestRegistry;
import org.broken.cheststorage.data.ChestRegistry.ChestData;
import org.broken.cheststorage.data.ChestRegistry.TypeOfContainer;
import org.broken.cheststorage.database.sqlite.Errors;
import org.bukkit.Location;
import org.bukkit.Material;
import org.bukkit.inventory.ItemStack;
import org.mineacademy.fo.Common;
import org.mineacademy.fo.SerializeUtil;

import java.io.File;
import java.io.IOException;
import java.math.BigInteger;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;

public class SQLite {
    @Getter
    private static final SQLite Instance = new SQLite();
    private final File chestStorage = ChestStorage.getData();// A reference to Main Class

    private Connection connection = connect();

    private static final ConvertJson converting = new ConvertJson();

    private boolean loadDataStartup = true;

    private final String TableNameChest = "ContainerData", TableOneFirstRow = "Location", TableOnesecondRow = "Player_UUID",
            TableOneThirdRow = "FilterWhiteBlack", TableOneFourthRow = "Chest_Type", TableOneFifthRow = "Suction_Range",
            TableOneSixthRow = "Link_Range", TableOneSeventhRow = "Link_Amount", TableOneEighthRow = "Next_Upgrade",
            TableOneNineRow = "Chest_contents", TableOneTenRow = "SettingsName", TableOneElevenRow = "AmountOfPages",
            TableOneTwelveRow = "FilterItems", TableOneThirteenRow = "Link_location", TableOneFourteenRow = "AmountOfItems";
    private final String TableTwoUsers = "Users", TableTwoFirstRow = "PLAYER_NAME", TableTwosecondRow = "UUID";

    private boolean batchUpdateGoingOn = false;


    public Connection connect() {


        try {
            File dbFile = new File(chestStorage, "database.db");
            if (!dbFile.exists()) {
                try {
                    dbFile.createNewFile();

                    //String url = "jdbc:sqlite:" + dbFile.getPath();
                } catch (IOException ex) {
                    ChestStorage.getInstance().getLogger().log(Level.SEVERE, "File write error: " + dbFile + ".db", ex);
                }
            }

            if (this.connection != null && !this.connection.isClosed()) {
                return this.connection;
            }
            Class.forName("org.sqlite.JDBC");
            this.connection = DriverManager.getConnection("jdbc:sqlite:" + dbFile);
            return this.connection;


        } catch (SQLException ex) {
            ChestStorage.getInstance().getLogger().log(Level.SEVERE, "SQLite exception on initialize", ex);
        } catch (ClassNotFoundException ex) {
            ChestStorage.getInstance().getLogger().log(Level.SEVERE, "You need the SQLite JBDC library. Google it. Put it in /lib folder.");
        }
        return null;
    }


    public void load(String sql) {
        this.connection = connect();
        try {
            Statement s = this.connection.createStatement();
            s.executeUpdate(sql);
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        initialize();
    }
/*
    public Statement Statements() {
        try {
            connect().createStatement();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return statement;
    }*/

    public void disconnect() {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
    }


    /*private void update(String sql) {
        try {
            connect().executeupdate(sql);
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
    }

    public ResultSet getResult(String sql) {
        try {
            return connect().executeQuery(sql);
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        return null;
    }*/

    public void createTables() {
        String test;
        String tableOne = "CREATE TABLE IF NOT EXISTS " + TableNameChest + " (" + TableOneFirstRow + " VARCHAR(64) PRIMARY KEY, " +
                TableOnesecondRow + " VARCHAR(64) , " + TableOneThirdRow + " VARCHAR(64), " + TableOneFourthRow + " VARCHAR(64), " +
                TableOneFifthRow + " VARCHAR(64), " + TableOneSixthRow + " VARCHAR(64), " + TableOneSeventhRow + " VARCHAR(64), " +
                TableOneEighthRow + " VARCHAR(64), " + TableOneNineRow + " VARCHAR(200)," + TableOneTenRow + " VARCHAR(64)," +
                TableOneElevenRow + " VARCHAR(64)," + TableOneTwelveRow + " VARCHAR(64)," + TableOneThirteenRow + " VARCHAR(200)," + TableOneFourteenRow + " VARCHAR(120));";
        String tableTwo = "CREATE TABLE IF NOT EXISTS " + TableTwoUsers + " (" + TableTwoFirstRow + " VARCHAR(64) , " + TableTwosecondRow + " VARCHAR(64) PRIMARY KEY);";
        //Common.log(tableOne);


        String[] tabels = {tableOne, tableTwo};

        for (int i = 0; i < tabels.length; i++) {
            test = tabels[i];
            //System.out.println(test);
            load(test);
        }
    }

    //TODO Fix more of load code and cleanup/fixes?
    public void LoadFromSQlite() {
        PreparedStatement ps = null;
        this.connection = connect();
        ResultSet rs;

        try {
            String sql = "SELECT * FROM " + TableNameChest;
            ps = this.connection.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {

                Location location = SerializeUtil.deserializeLocation(rs.getObject(TableOneFirstRow));
                UUID playerUUID = UUID.fromString(rs.getString(TableOnesecondRow));
                boolean filterWhiteBlack = rs.getBoolean(TableOneThirdRow);
                TypeOfContainer typeOfContainer = TypeOfContainer.valueOf(rs.getString(TableOneFourthRow));
                int suctionRange = rs.getInt(TableOneFifthRow);
                int linkRange = rs.getInt(TableOneSixthRow);
                int linkAmount = rs.getInt(TableOneSeventhRow);
                String nextUpgrade = rs.getString(TableOneEighthRow);
                ItemStack[] chestItems = SerializDeserialzItemStackFromToDatabase.itemStackArrayFromBase64(rs.getString(TableOneNineRow));
                String containerSettingsName = rs.getString(TableOneTenRow);
                int numberOfPages = rs.getInt(TableOneElevenRow);
                ItemStack[] filterItems = rs.getString(TableOneTwelveRow) != null ?
                        (SerializDeserialzItemStackFromToDatabase.itemStackArrayFromBase64(rs.getString(TableOneTwelveRow))) : (new ItemStack[]{(new ItemStack(Material.AIR))});
                String linkedLocations = rs.getString(TableOneThirteenRow);
                String amountOfItems = rs.getString(TableOneFourteenRow);
                BigInteger convertedToBigInteger = new BigInteger(amountOfItems);

                ArrayList<Location> linkedlocations = converting.convertFromJsonList(linkedLocations);

                ChestRegistry.getInstance().addChestToCacheFromSQL(location, new ChestData(playerUUID,
                        nextUpgrade, typeOfContainer, containerSettingsName, numberOfPages,
                        filterWhiteBlack, suctionRange, linkRange, linkAmount, filterItems,
                        chestItems, linkedlocations, convertedToBigInteger));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (ps != null)
                    ps.close();
                if (this.connection != null)
                    this.connection.close();
            } catch (SQLException ex) {
                ChestStorage.getInstance().getLogger().log(Level.SEVERE, Errors.sqlConnectionClose(), ex);
            }
        }
    }

    public void removeChestfromSQlites(Location location) {
        List<String> sqls = new ArrayList<>();
        String sql = "DELETE FROM  " + TableNameChest + " WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";

        sqls.add(sql);
        this.batchUpdate(sqls);
    }

    public void saveItemsToSQlites(Location location, ItemStack[] itemStack) {
        List<String> sqls = new ArrayList<>();
        String sql = "UPDATE " + TableNameChest + " SET " + TableOneNineRow + " = '" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(itemStack) + "' WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";

        sqls.add(sql);
        this.batchUpdate(sqls);
    }

    public void saveFilterListToSQlites(Location location, ItemStack[] itemStack) {
        List<String> sqls = new ArrayList<>();
        String sql = "UPDATE " + TableNameChest + " SET " + TableOneTwelveRow + " = '" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(itemStack) + "' WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";

        sqls.add(sql);
        this.batchUpdate(sqls);
    }

    @SneakyThrows
    public void saveDataOnBlockPlace(ChestData chestData, Location location) {
        List<String> sqls = new ArrayList<>();

        String sql = "REPLACE INTO " + TableNameChest + " (" + TableOneFirstRow
                + "," + TableOnesecondRow + "," + TableOneThirdRow
                + "," + TableOneFourthRow + "," + TableOneFifthRow + "," + TableOneSixthRow
                + "," + TableOneSeventhRow + "," + TableOneEighthRow + "," + TableOneNineRow
                + "," + TableOneTenRow + "," + TableOneElevenRow
                + ") VALUES('" + SerializeUtil.serializeLoc(location)
                + "','" + chestData.getPlayerId().toString() + "','" + chestData.isFilterWhiteBlack()
                + "','" + chestData.getTypeOfContainer() + "','" + chestData.getSuctionRange()
                + "','" + chestData.getLinkRange() + "','" + chestData.getLinkAmount()
                + "','" + chestData.getNextUpgrade()
                + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getChestContents())
                + "','" + chestData.getContainerFileName() + "','" + chestData.getNumberOfPages() + "')";
        sqls.add(sql);


        sqls.add(sql);

        //System.out.println(sqls);
        this.batchUpdate(sqls);
    }

    //TODO Make data values more cleaner.
    @SneakyThrows
    public void saveToSQlites(Map<Location, ChestData> chests) {
        List<String> sqls = new ArrayList<>();
        for (Map.Entry<Location, ChestData> entry : chests.entrySet()) {
            Location location = entry.getKey();
            ChestData chestData = entry.getValue();

            String sql = "REPLACE INTO " + TableNameChest + " (" + TableOneFirstRow
                    + "," + TableOnesecondRow + "," + TableOneThirdRow
                    + "," + TableOneFourthRow + "," + TableOneFifthRow + "," + TableOneSixthRow
                    + "," + TableOneSeventhRow + "," + TableOneEighthRow + "," + TableOneNineRow
                    + "," + TableOneTenRow + "," + TableOneElevenRow + "," + TableOneTwelveRow
                    + "," + TableOneThirteenRow + "," + TableOneFourteenRow
                    + ") VALUES('" + SerializeUtil.serializeLoc(location)
                    + "','" + chestData.getPlayerId().toString() + "','" + chestData.isFilterWhiteBlack()
                    + "','" + chestData.getTypeOfContainer() + "','" + chestData.getSuctionRange()
                    + "','" + chestData.getLinkRange() + "','" + chestData.getLinkAmount()
                    + "','" + chestData.getNextUpgrade()
                    + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getChestContents())
                    + "','" + chestData.getContainerFileName() + "','" + chestData.getNumberOfPages()
                    + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getFilterItems() != null ? chestData.getFilterItems() : new ItemStack[]{(new ItemStack(Material.AIR))})
                    + "','" + converting.convertToJsonList("Locations", chestData.getLocations()) + "','" + chestData.getAmountOfItems().toString() + "')";


            sqls.add(sql);
        }
        //System.out.println(sqls);
        this.batchUpdate(sqls);
    }

    protected final void batchUpdate(@NonNull List<String> sqls) {
        this.connection = connect();

        if (sqls.size() == 0)
            return;
        try {


            final Statement batchStatement = this.connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            final int processedCount = sqls.size();

            // Prevent automatically sending db instructions
            this.connection.setAutoCommit(false);


            for (final String sql : sqls)
                batchStatement.addBatch(sql);
            if (processedCount > 10_000)
                Common.log("Updating your database (" + processedCount + " entries)... PLEASE BE PATIENT THIS WILL TAKE "
                        + (processedCount > 50_000 ? "10-20 MINUTES" : "5-10 MINUTES") + " - If server will print a crash report, ignore it, update will proceed.");

            // Set the flag to start time notifications timer
            batchUpdateGoingOn = true;

            // Notify console that progress still is being made
            new Timer().scheduleAtFixedRate(new TimerTask() {

                @Override
                public void run() {
                    if (batchUpdateGoingOn)
                        Common.log("Still executing, DO NOT SHUTDOWN YOUR SERVER.");
                    else
                        cancel();
                }
            }, 1000 * 30, 1000 * 30);

            // Execute
            batchStatement.executeBatch();

            // This will block the thread
            this.connection.commit();

            //Common.log("Updated " + processedCount + " database entries.");

        } catch (final Throwable t) {
            t.printStackTrace();

        } finally {
            try {
                this.connection.setAutoCommit(true);

            } catch (final SQLException ex) {
                ex.printStackTrace();
            }

            // Even in case of failure, cancel
            batchUpdateGoingOn = false;
        }
    }


    public void initialize() {
        try {
            PreparedStatement preparedStatement = this.connection.prepareStatement("SELECT * FROM " + TableNameChest + " WHERE " + TableOnesecondRow + " = ?");
            ResultSet resultSet = preparedStatement.executeQuery();
            close(preparedStatement, resultSet);

        } catch (SQLException ex) {
            ChestStorage.getInstance().getLogger().log(Level.SEVERE, "Unable to retreive connection", ex);
        }
    }

    public void close(PreparedStatement preparedStatement, ResultSet resultSet) {
        try {
            if (preparedStatement != null)
                preparedStatement.close();
            if (resultSet != null)
                resultSet.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
            /*Error.close(ChestStorage.getInstance(), ex);*/
        }
    }

}