minecraft-kajetans-mod/src/main/java/me/km/blockprotections/BlockProtectionBank.java

package me.km.blockprotections;

import java.sql.ResultSet;
import java.sql.SQLException;
import me.km.KajetansMod;
import java.util.ArrayList;
import me.km.databank.IStatement;
import me.km.world.ModDimensions;
import net.minecraft.entity.player.EntityPlayer;
import net.minecraft.util.math.BlockPos;
import net.minecraft.world.World;

public class BlockProtectionBank
{  
    public BlockProtectionBank() 
    {
        KajetansMod.databank.execute("CREATE TABLE IF NOT EXISTS block ("
                    + "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
                    + "x int(11) NOT NULL, "
                    + "y int(11) NOT NULL, "
                    + "z int(11) NOT NULL, "
                    + "world_name varchar(20) NOT NULL, "
                    + "UNIQUE KEY uniq_block_pos (world_name,x,y,z));");
        
        KajetansMod.databank.execute("CREATE TABLE IF NOT EXISTS block_grant ("
                    + "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
                    + "block_id int(11) NOT NULL, "
                    + "player_id int(11) NOT NULL, "
                    + "INDEX block_id (block_id), "
                    + "UNIQUE KEY (block_id, player_id), "
                    + "CONSTRAINT block_grant_ibfk_1 FOREIGN KEY (block_id) REFERENCES block (id) ON DELETE CASCADE);");
    }  
    
    private final IStatement addBlock = KajetansMod.databank.createStatement(
            "INSERT IGNORE INTO block (x, y, z, world_name) VALUES (?, ?, ?, ?);");
    
    private final IStatement addBlockGrant = KajetansMod.databank.createStatement(
            "INSERT IGNORE INTO block_grant (block_id, player_id) VALUES (LAST_INSERT_ID(), ?);");

    public void add(int x, int y, int z, String worldName, int playerId)
    {
        try
        {
            addBlock.validate();
            addBlock.setInt(1, x);
            addBlock.setInt(2, y);
            addBlock.setInt(3, z);
            addBlock.setString(4, worldName);
            addBlock.executeUpdate();
            
            addBlockGrant.validate();
            addBlockGrant.setInt(1, playerId);
            addBlockGrant.executeUpdate();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
    }
    
    private final IStatement deleteBlock = KajetansMod.databank.createStatement(
            "DELETE FROM block WHERE x=? AND y=? AND z=? AND world_name=?;");
    
    public void remove(int x, int y, int z, String worldName)
    {
        try
        {
            deleteBlock.validate();
            deleteBlock.setInt(1, x);
            deleteBlock.setInt(2, y);
            deleteBlock.setInt(3, z);
            deleteBlock.setString(4, worldName);
            deleteBlock.executeUpdate();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
    }
    
    public void remove(BlockPos pos, World w)
    {
        remove(pos.getX(), pos.getY(), pos.getZ(), ModDimensions.getWorldName(w));
    }
    
    private final IStatement addPlayer = KajetansMod.databank.createStatement(
            "INSERT IGNORE INTO block_grant (block_id, player_id) SELECT id, ? FROM block WHERE world_name=? AND x=? AND y=? AND z=?;");
    
    public boolean addPlayer(int x, int y, int z, String worldName, int playerId)
    {
        if(hasAccess(x, y, z, worldName, playerId))
        {
            return false;
        }
        try
        {
            addPlayer.validate();
            addPlayer.setInt(1, playerId);
            addPlayer.setString(2, worldName);
            addPlayer.setInt(3, x);
            addPlayer.setInt(4, y);
            addPlayer.setInt(5, z);
            addPlayer.executeUpdate();
            return true;
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
            return false;
        }
    }
    
    private final IStatement removePlayer = KajetansMod.databank.createStatement(
            "DELETE block_grant FROM block_grant LEFT JOIN block ON block.id = block_grant.block_id " +
            "WHERE block_grant.player_id = ? AND block.world_name=? AND block.x=? AND block.y=? AND block.z=?;");
    
    public boolean removePlayer(int x, int y, int z, String worldName, int playerId)
    {
        if(!hasAccess(x, y, z, worldName, playerId))
        {
            return false;
        }
        try
        {
            removePlayer.validate();
            removePlayer.setInt(1, playerId);
            removePlayer.setString(2, worldName);
            removePlayer.setInt(3, x);
            removePlayer.setInt(4, y);
            removePlayer.setInt(5, z);
            removePlayer.executeUpdate();
            return true;
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
            return false;
        }
    }
    
    private final IStatement hasAccess = KajetansMod.databank.createStatement(
            "Select id from block_grant WHERE id = ? AND player_id = ?;");
    
    public boolean hasAccess(int x, int y, int z, String worldName, int playerId)
    {
        int id = getId(x, y, z, worldName);
        if(id == -1)
        {
            return true;
        }
        try
        {
            hasAccess.validate();
            hasAccess.setInt(1, id);
            hasAccess.setInt(2, playerId);
            try(ResultSet rs = hasAccess.executeQuery())
            {
                return rs.next();
            }
            catch(SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        return false;
    }
    
    public boolean hasAccess(BlockPos pos, World w, EntityPlayer p)
    {
        return hasAccess(pos.getX(), pos.getY(), pos.getZ(), ModDimensions.getWorldName(w), KajetansMod.playerbank.getPlayerId(p.getUniqueID()));
    }
    
    private final IStatement getId = KajetansMod.databank.createStatement(
            "SELECT id FROM block WHERE world_name=? AND x=? AND y=? AND z=?;");
    
    public int getId(int x, int y, int z, String worldName)
    {
        try
        {
            getId.validate();
            getId.setString(1, worldName);
            getId.setInt(2, x);
            getId.setInt(3, y);
            getId.setInt(4, z);
            try(ResultSet rs = getId.executeQuery())
            {
                if(rs.next())
                {
                    return rs.getInt(1);
                }
                return -1;
            }
            catch(SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        return -1;
    }
    
    private final IStatement getPlayerIds = KajetansMod.databank.createStatement(
            "SELECT player_id FROM block LEFT JOIN block_grant ON block_grant.id = block.id WHERE world_name=? AND x=? AND y=? AND z=?;");
    
    public ArrayList<Integer> getAllIds(int x, int y, int z, String worldName)
    {
        ArrayList<Integer> list = new ArrayList<>();
        try
        {
            getPlayerIds.validate();
            getPlayerIds.setString(1, worldName);
            getPlayerIds.setInt(2, x);
            getPlayerIds.setInt(3, y);
            getPlayerIds.setInt(4, z);
            try(ResultSet rs = getPlayerIds.executeQuery())
            {
                while(rs.next())
                {
                    list.add(rs.getInt(1));
                }
                return list;
            }
            catch(SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        return list;
    } 
}