package com.battlecraft.zonedabone.battlesnitch.controllers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.bukkit.ChatColor;
import org.bukkit.Material;
import org.bukkit.block.Block;
import org.bukkit.entity.Player;

import com.battlecraft.zonedabone.battlesnitch.BattleSnitch;

public class MYSQLController {

    public Connection con;

    final String new_table_snitches =   "CREATE  TABLE `battlesnitch`.`snitches` ("+
                                        "`idsnitches` INT NOT NULL AUTO_INCREMENT ,"+
                                        "`x` INT NOT NULL ,"+
                                        "`y` INT NOT NULL ,"+
                                        "`z` INT NOT NULL ,"+
                                        "`world` VARCHAR(45) NOT NULL ,"+
                                        "PRIMARY KEY (`idsnitches`) );";

    final String new_table_blocks =     "CREATE  TABLE `battlesnitch`.`blocks` ("+
                                        "`idblocks` INT NOT NULL ,"+
                                        "`x` INT NOT NULL ,"+
                                        "`y` INT NOT NULL ,"+
                                        "`z` INT NOT NULL ,"+
                                        "`world` VARCHAR(45) NOT NULL ,"+
                                        "PRIMARY KEY (`idblocks`,x,y,z,world) );";



    final String new_table_snitched =   "CREATE  TABLE `battlesnitch`.`snitched` ("+
                                        "`idsnitched` INT NOT NULL ,"+
                                        "`player` VARCHAR(45) NOT NULL ,"+
                                        "`time` MEDIUMTEXT  NULL ,"+
                                        "PRIMARY KEY (`idsnitched`, `player`));";

    public boolean DEBUG;
    public String URL;
    public String PORT;
    public String USERNAME;
    public String PASSWORD;
    public BattleSnitch plugin;

    public MYSQLController(String url, String port, String username, String password, boolean debug, BattleSnitch instance){
        DEBUG = debug;
        URL = url;
        PORT = port;
        USERNAME = username;
        PASSWORD = password;
        plugin = instance;
        load();
    }

    public boolean load(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            if (DEBUG) System.out.println("Got Driver");
        } catch (ClassNotFoundException e1) {
            System.err.println("Failed getting driver");
            e1.printStackTrace();
            return false;
        }
        String strStmt = "CREATE DATABASE IF NOT EXISTS battlesnitch";
        try {
            con = DriverManager.getConnection("jdbc:mysql://"+URL+":" + PORT, USERNAME,PASSWORD);
            Statement st = con.createStatement();
            st.executeUpdate(strStmt);
            if (DEBUG) System.out.println("Creating db");
        } catch (SQLException e) {
            System.err.println("Failed creating db: "  + strStmt);
            e.printStackTrace();
            return false;
        }
        try {
            con = DriverManager.getConnection("jdbc:mysql://"+URL+":" + PORT +"/battlesnitch", USERNAME,PASSWORD);
        } catch (SQLException e1) {
            e1.printStackTrace();
            return false;
        }

        createTable("desc snitches", new_table_snitches, null);
        createTable("desc blocks", new_table_blocks, null);
        createTable("desc snitched", new_table_snitched, null);
        try {
            con.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }



        return true;
    }

    private boolean createTable(String sql_table_exists,
            String sql_create_table,String sql_create_index) {
        String strStmt;
        strStmt = sql_table_exists;
        /// Check to see if our table exists;
        boolean table_exists = false;
        try {
            Statement st = con.createStatement();
            st.executeUpdate(strStmt);
            if (DEBUG) System.out.println("table exists");
            table_exists = true;
        } catch (SQLException e) {
            if (DEBUG) System.out.println("table does not exist");
        }
        /// If the table exists nothing left to do
        if (table_exists)
            return true;
        /// Create our table and index
        strStmt = sql_create_table;
        Statement st = null;
        int result =0;
        try {
            st = con.createStatement();
            result = st.executeUpdate(strStmt);
            if (DEBUG) System.out.println("Created Table with stmt=" + strStmt);

            if (sql_create_index != null){
                try{
                    st = con.createStatement();
                    st.executeUpdate(sql_create_index);
                    if (DEBUG) System.out.println("Created Index");
                } catch (Exception e){
                    if (DEBUG) System.err.println("Failed in creating Index");
                    return false;
                }
            }
        } catch (SQLException e) {
            if (DEBUG) System.err.println("Failed in creating Table " +
                    strStmt + "   result=" + result);
            e.printStackTrace();
            return false;
        }

        return true;
    }

    public int getID(Block block){
        int x = block.getX();
        int y = block.getY();
        int z = block.getZ();
        String world = block.getWorld().getName();
        try{
            PreparedStatement ps = con.prepareStatement("SELECT idsnitches FROM snitches WHERE x = ? AND y = ? AND z = ? and world = ?");
            ps.setInt(1, x);
            ps.setInt(2, y);
            ps.setInt(3, z);
            ps.setString(4, world);
            ResultSet rs = ps.executeQuery();
            if(!rs.next()){
                return 0;
            }
            return (rs.getInt("idsnitches"));
        }catch (SQLException e){
            System.out.println("ERROR");
            e.printStackTrace();
            return 0;
        }
    }

    public boolean makeSnitch(Block block){
        if(getID(block)==0){
            int x = block.getX();
            int y = block.getY();
            int z = block.getZ();
            String world = block.getWorld().getName();
            try{
                PreparedStatement ps = con.prepareStatement("INSERT INTO snitches (x,y,z,world) VALUES (?,?,?,?)");
                ps.setInt(1, x);
                ps.setInt(2, y);
                ps.setInt(3, z);
                ps.setString(4, world);
                ps.execute();
                con.commit();
                int id = getID(block);
                for(int xs = x-5;xs<=x+5;xs++){
                    for(int ys = y-5;ys<=y+5;ys++){
                        for(int zs = z-5;zs<=z+5;zs++){
                            ps = con.prepareStatement("INSERT INTO blocks (idblocks,x,y,z,world) VALUES (?,?,?,?,?)");
                            ps.setInt(1, id);
                            ps.setInt(2, xs);
                            ps.setInt(3, ys);
                            ps.setInt(4, zs);
                            ps.setString(5, world);
                            ps.execute();
                        }
                    }
                }
                con.commit();
                return true;
            }catch (SQLException e){
                e.printStackTrace();
                return false;
            }
        }else{
            breakSnitch(block);
            return makeSnitch(block);
        }
    }

    public boolean breakSnitch(Block block){
        int id = getID(block);
        if(id!=0){
            try{
                PreparedStatement ps = con.prepareStatement("DELETE FROM blocks WHERE idblocks = ?");
                ps.setInt(1,id);
                ps.execute();
                ps = con.prepareStatement("DELETE FROM snitched WHERE idsnitched = ?");
                ps.setInt(1,id);
                ps.execute();
                ps = con.prepareStatement("DELETE FROM snitches WHERE idsnitches = ?");
                ps.setInt(1,id);
                ps.execute();
                con.commit();
                return true;
            }catch (SQLException e){
                e.printStackTrace();
                return false;
            }
        }else{
            return false;
        }
    }

    public void checkBlock(String player, Block block){
        int x = block.getX();
        int y = block.getY();
        int z = block.getZ();
        String world = block.getWorld().getName();
        try{
            PreparedStatement ps = con.prepareStatement("SELECT idblocks FROM blocks WHERE x=? and y=? and z=? and world=?");
            ps.setInt(1, x);
            ps.setInt(2, y);
            ps.setInt(3, z);
            ps.setString(4, world);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                ps = con.prepareStatement("INSERT INTO snitched VALUES (?,?,?) ON DUPLICATE KEY UPDATE time=?");
                ps.setInt(1, rs.getInt(1));
                ps.setString(2, player);
                ps.setLong(3, System.currentTimeMillis());
                ps.setLong(4, System.currentTimeMillis());
                ps.execute();
                con.commit();
            }
        }catch (SQLException e){
            e.printStackTrace();
            return;
        }
    }

    public void checkHistory(Player player, Block block){
        int id = getID(block);
        if(id!=0){
            try{
                PreparedStatement ps = con.prepareStatement("SELECT count(*) FROM snitched WHERE idsnitched = ?");
                ps.setInt(1, id);
                ResultSet rs = ps.executeQuery();
                rs.next();
                if(rs.getInt(1)==0){
                    player.sendMessage(ChatColor.GOLD+"Nobody has been here in the past week.");
                }else{
                    if(rs.getInt(1)==1){
                        player.sendMessage(ChatColor.GOLD+"1 person has been here in the past week:");
                    }else{
                        player.sendMessage(ChatColor.GOLD+""+rs.getInt(1)+" people have been here in the past week:");
                    }
                    ps = con.prepareStatement("SELECT player FROM snitched WHERE idsnitched = ?");
                    ps.setInt(1, id);
                    rs = ps.executeQuery();
                    while(rs.next()){
                        player.sendMessage(ChatColor.GOLD+rs.getString(1));
                    }
                }
            }catch (SQLException e){
                e.printStackTrace();
                return;
            }
        }else{
            makeSnitch(block);
            player.sendMessage(ChatColor.GOLD+"This jukebox is now a snitch block.");
        }
    }

    public void cleanRecords(){
        try{
            PreparedStatement ps = con.prepareStatement("DELETE FROM snitched WHERE time < ?");
            ps.setLong(1, System.currentTimeMillis()-15000);
            ps.execute();
        }catch (SQLException e){
            return;
        }
    }
}