package moe.oko.opennaw.storage;

import moe.oko.opennaw.OpenNAW;
import moe.oko.opennaw.model.City;
import moe.oko.opennaw.model.Nation;
import org.bukkit.Bukkit;
import org.bukkit.Location;
import org.bukkit.OfflinePlayer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

public class NAWDatabase {

    private Connection connection; //SQL Conn instance

    private final String INITIALIZE_NATION_PLAYERS = "CREATE TABLE IF NOT EXISTS `nation_players`" +
            "(`nation_name` VARCHAR(50) NOT NULL COMMENT 'The name of the nation for the player to join'," +
            "`player_uuid` VARCHAR(50) NOT NULL COMMENT 'The UUID of the player who is in that nation', PRIMARY KEY (`player_uuid`));";
    private final String INITIALIZE_NATIONS = "CREATE TABLE IF NOT EXISTS `nations` (" +
            "`nation_name` VARCHAR(50) NOT NULL COMMENT 'Nation name', PRIMARY KEY (`nation_name`)," +
            "`groupName` VARCHAR(50) NOT NULL COMMENT 'Name for the luckperms group.', " +
            "`dynmapIcon` VARCHAR(50) NOT NULL COMMENT 'National Icon for Dynmap'," +
            "`colour` VARCHAR(50) NOT NULL COMMENT 'Colour for the nation'," +
            "`spawnworld` VARCHAR(50) NOT NULL," +
            "`spawnx` INT NOT NULL DEFAULT 0," +
            "`spawny` INT NOT NULL DEFAULT 0," +
            "`spawnz` INT NOT NULL DEFAULT 0);";
    private final String INITIALIZE_CITIES = "CREATE TABLE IF NOT EXISTS `cities` (" +
            "`city_name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'City Name', PRIMARY KEY (`city_name`)," +
            "`resource` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'Resource the city provides'," +
            "`owner` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'The name of the nation that owns it'," +
            "`pointworld` VARCHAR(50) NOT NULL DEFAULT ''," +
            "`pointx` INT NOT NULL DEFAULT 0," +
            "`pointy` INT NOT NULL DEFAULT 0," +
            "`pointz` INT NOT NULL DEFAULT 0) COMMENT='Cities on the NAW map';";
    private final String SELECT_CITIES = "SELECT * FROM `cities`;";
    private final String SELECT_NATION = "SELECT * FROM `nations`;";
    private final String SELECT_PLAYERS = "SELECT * FROM `nation_players`;";

    //name 1,9; group 2,10; icon 3,11; colour 4, 12; world 5,13; x 6,14; y 7,15; z 8,16;
    private final String SAVE_NATION = "INSERT INTO `nations`(nation_name,groupName,dynmapIcon, colour, spawnworld,spawnx,spawny,spawnz) VALUES " +
            "(?,?,?,?,?,?,?,?)" +
            "ON DUPLICATE KEY UPDATE nation_name=?, groupName=?, dynmapIcon=?, colour=?, spawnworld=?, spawnx=?, spawny=?, spawnz=?;";
    //MAKE SURE YOU RUN AS BATCH (nation, 1,3) (playeruuid, 2,4);
    private final String SAVE_PLAYER = "INSERT INTO `nation_players`(nation_name, player_uuid) VALUES (?,?) " +
            "ON DUPLICATE KEY UPDATE nation_name=?, player_uuid=?;";
    //name 1,8; resource 2,9; owner 3,10; world 4,11; x 5,12; y 6,13; z 7,14;
    private final String SAVE_CITY = "INSERT INTO `cities`(city_name, resource, OWNER, pointworld, pointx, pointy, pointz) VALUES (?,?,?,?,?,?,?)" +
            "ON DUPLICATE KEY UPDATE city_name=?, resource=?, OWNER=?, pointworld=?, pointx=?, pointy=?, pointz=?;";

    public NAWDatabase(String username, String password, String host, int port, String database) {
        //all relevant
        try {
            connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
        } catch (Exception ex) {
            ex.printStackTrace();
            OpenNAW.getInstance().getLogger().severe("FAILED TO INITIALIZE PERSISTENT MYSQL DATABASE.");
        }
        //Unfugg Tables on startup
        initializeTables();
    }

    private void initializeTables() {
        try {
            OpenNAW.getInstance().getLogger().info("Table initialization start.");
            PreparedStatement nationPlayersInit = connection.prepareStatement(INITIALIZE_NATION_PLAYERS);
            nationPlayersInit.execute();
            PreparedStatement nationsInit = connection.prepareStatement(INITIALIZE_NATIONS);
            nationsInit.execute();
            PreparedStatement cityInit = connection.prepareStatement(INITIALIZE_CITIES);
            cityInit.execute();
        } catch (Exception ex) {
            ex.printStackTrace();
            OpenNAW.getInstance().getLogger().severe("Failed to initialize tables for sql.");
        }
    }

    public List<City> loadCities() {
        try {
            OpenNAW.getInstance().getLogger().info("Loading Cities...");
            PreparedStatement pullCity = connection.prepareStatement(SELECT_CITIES);
            ResultSet rs = pullCity.executeQuery();
            List<City> cityLoad = new ArrayList<>();
            var i = 0;
            while(rs.next()) {
                var name = rs.getString(1);
                var resource = rs.getString(2);
                var nation = OpenNAW.getInstance().getNationHandler().getNationByName(rs.getString(3));
                var loc = new Location(Bukkit.getWorld(rs.getString(4)), rs.getInt(5), rs.getInt(6), rs.getInt(7));
                cityLoad.add(new City(name, resource, loc, nation));
                i++;
            }
            OpenNAW.getInstance().getLogger().info(i + " cities loaded.");
            return cityLoad;
        } catch (Exception ex) {
            ex.printStackTrace();
            OpenNAW.getInstance().getLogger().severe("Failed to pull cities from db.");
            return null;
        }
    }

    public List<Nation> loadNations() {
        try {
            OpenNAW.getInstance().getLogger().info("Loading Nations...");
            PreparedStatement pullNation = connection.prepareStatement(SELECT_NATION);
            ResultSet rs = pullNation.executeQuery();
            List<Nation> nationLoad = new ArrayList<>();
            var i = 0;
            while(rs.next()) {
                var name = rs.getString(1);
                var group = OpenNAW.getInstance().getGroupHandler().getGroupFromString(rs.getString(2));
                var icon = rs.getString(3);
                var colour = rs.getString(4);
                var location = new Location(Bukkit.getWorld(rs.getString(5)), rs.getInt(6), rs.getInt(7), rs.getInt(8));
                nationLoad.add(new Nation(name, group, icon, colour, location));
                i++;
            }
            OpenNAW.getInstance().getLogger().info(i + " nations loaded.");
            return nationLoad;
        } catch (Exception ex) {
            ex.printStackTrace();
            OpenNAW.getInstance().getLogger().severe("Failed to pull nations from db.");
            return null;
        }
    }

    public void loadPlayers() {
        try {
            PreparedStatement pullPlayers = connection.prepareStatement(SELECT_PLAYERS);
            ResultSet rs = pullPlayers.executeQuery();
            OpenNAW.getInstance().getLogger().info("Loading Players...");
            var i = 0;
            while(rs.next()) {
                OpenNAW.getInstance().getNationHandler().insertPlayerIntoNation(UUID.fromString(rs.getString(2)), rs.getString(1));
                i++;
            }
            OpenNAW.getInstance().getLogger().info(i + " players loaded.");
        } catch (Exception ex) {
            ex.printStackTrace();
            OpenNAW.getInstance().getLogger().severe("Failed to load players.");
        }
    }

    public void saveNations(Collection<Nation> nations) {
        try {
            OpenNAW.getInstance().getLogger().info("Starting nation save...");
            //name 1,9; group 2,10; icon 3,11; colour 4, 12; world 5,13; x 6,14; y 7,15; z 8,16;
            PreparedStatement nationSave = connection.prepareStatement(SAVE_NATION);
            int i = 0;
            for(Nation nation : nations) {
                nationSave.setString(1, nation.getName());
                nationSave.setString(9, nation.getName());
                nationSave.setString(2, nation.getGroup().getName());
                nationSave.setString(10, nation.getGroup().getName());
                nationSave.setString(3, nation.getDynmapIcon());
                nationSave.setString(11, nation.getDynmapIcon());
                nationSave.setString(4, nation.getColour());
                nationSave.setString(12, nation.getColour());
                var loc = nation.getSpawn();
                nationSave.setString(5, loc.getWorld().getName());
                nationSave.setString(13, loc.getWorld().getName());
                nationSave.setInt(6, loc.getBlockX());
                nationSave.setInt(14, loc.getBlockX());
                nationSave.setInt(7, loc.getBlockY());
                nationSave.setInt(15, loc.getBlockY());
                nationSave.setInt(8, loc.getBlockZ());
                nationSave.setInt(16, loc.getBlockZ());

                //Values are set, this is autistic. Run.
                nationSave.addBatch();
                i++;

                PreparedStatement playersSave = connection.prepareStatement(SAVE_PLAYER);
                //MAKE SURE YOU RUN AS BATCH (nation, 1,3) (playeruuid, 2,4);
                int pc = 0;
                for(OfflinePlayer nationPlayer : nation.getPlayerMap().values()) {
                    //Oh nnononono
                    playersSave.setString(1, nation.getName());
                    playersSave.setString(3, nation.getName());
                    playersSave.setString(2, nationPlayer.getUniqueId().toString());
                    playersSave.setString(4, nationPlayer.getUniqueId().toString());
                    playersSave.addBatch();
                    pc++;
                }
                //Run all at once to avoid ratelimit.
                playersSave.executeBatch();
                OpenNAW.getInstance().getLogger().info("Saved " + pc + " players for " + nation.getName() + ".");
            }
            nationSave.executeBatch();
            OpenNAW.getInstance().getLogger().info("Saved " + i + " nations.");
        } catch (Exception ex) {
            ex.printStackTrace();
            OpenNAW.getInstance().getLogger().severe("Failed to save nations.");
        }
    }

    public void saveCities(Collection<City> cities) {
        try {
            PreparedStatement citySave = connection.prepareStatement(SAVE_CITY);
            int i = 0;
            for(City city : cities) {
                //name 1,8; resource 2,9; owner 3,10; world 4,11; x 5,12; y 6,13; z 7,14;
                citySave.setString(1, city.getName());
                citySave.setString(8, city.getName());
                citySave.setString(2, city.getResource());
                citySave.setString(9, city.getResource());
                citySave.setString(3, city.getOwner().getName());
                citySave.setString(10, city.getOwner().getName());
                var loc = city.getPoint();
                citySave.setString(4, loc.getWorld().getName());
                citySave.setString(11, loc.getWorld().getName());
                citySave.setInt(5, loc.getBlockX());
                citySave.setInt(12, loc.getBlockX());
                citySave.setInt(6, loc.getBlockY());
                citySave.setInt(13, loc.getBlockY());
                citySave.setInt(7, loc.getBlockZ());
                citySave.setInt(14, loc.getBlockZ());
                citySave.addBatch();
                i++;
            }
            citySave.executeBatch();
            OpenNAW.getInstance().getLogger().info("Saved " + i + " cities.");
        } catch (Exception ex) {
          ex.printStackTrace();
          OpenNAW.getInstance().getLogger().severe("Failed to save cities.");
        }
    }
}
