package de.bruuff.testplugin;

import com.google.inject.Inject;

import org.slf4j.Logger;
import org.spongepowered.api.Game;
import org.spongepowered.api.entity.player.Player;
import org.spongepowered.api.event.Subscribe;
import org.spongepowered.api.event.entity.player.PlayerInteractBlockEvent;
import org.spongepowered.api.event.state.ServerStartedEvent;
import org.spongepowered.api.item.ItemTypes;
import org.spongepowered.api.item.inventory.ItemStack;
import org.spongepowered.api.plugin.Plugin;
import org.spongepowered.api.service.sql.SqlService;
import org.spongepowered.api.text.Texts;

import java.sql.*;

@Plugin(id = "TestPlugin", name = "TestPlugin", version = "0.1")
public class TestPlugin {

    public static final String JDBC_URL = "jdbc:h2:./mods/TestPlugin/data.db";
    public static Game game;

    @Inject
    protected Logger logger;

    @Subscribe
    public void onInitialization(ServerStartedEvent event) {
        game = event.getGame();
        logger.info("Starting TestPlugin");
    }

    @Subscribe
    public void onPlayerInteract(PlayerInteractBlockEvent event){
        Player player = event.getPlayer();
        if(player.getItemInHand().isPresent()) {
            ItemStack itemstack = player.getItemInHand().get();
            if (event.getInteractionType().getName().equals("ATTACK")) {
                if (itemstack.getItem().equals(ItemTypes.STICK)) {
                    player.sendMessage(Texts.of("Trying to create table."));
                    try{
                        createDB();
                    }catch(SQLException e){
                        logger.error("CreateDB: SQLException");
                    }
                }else if (itemstack.getItem().equals(ItemTypes.CARROT)) {
                    player.sendMessage(Texts.of("Trying to query table."));
                    try{
                        queryDB();
                    }catch(SQLException e){
                        logger.error("QueryDB: SQLException");
                    }
                }
            }
        }
    }

    private SqlService sql;
    public javax.sql.DataSource getDataSource(String jdbcUrl) throws SQLException {
        if (sql == null) {
            sql = game.getServiceManager().provide(SqlService.class).get();
        }
        return sql.getDataSource(jdbcUrl);
    }

    public void createDB() throws SQLException {
        Connection conn = getDataSource(JDBC_URL).getConnection();
        try {

            conn.prepareStatement("create table test(test1 varchar(20), test2 varchar(20), test3 varchar(20) )").execute();
            conn.prepareStatement("insert into test values " +
                    "('value11', 'value12', 'value13'), " +
                    "('value21', 'value22', 'value23'), " +
                    "('value31', 'value32', 'value33')").execute();

        } finally {
            conn.close();
            if (conn.isClosed() ){
                logger.info("Connection closed.");
            }
            logger.info("Table created.");
        }
    }
    public void queryDB() throws SQLException {
        Connection conn = getDataSource(JDBC_URL).getConnection();
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = conn.prepareStatement("SELECT * FROM test");
            resultSet = stmt.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            String output = "";
            for (int x = 1; x <= columnCount; x++) output += resultSetMetaData.getColumnName(x) + ", ";
            logger.info("Columns: " + output);
            //while(resultSet.next()){ ... }
        } finally {
            if (resultSet != null)  resultSet.close();
            if (stmt != null) stmt.close();
            conn.close();
            if (conn.isClosed() ){
                logger.info("Connection closed.");
            }
        }
    }

}