package com.battlecraft.zonedabone.battlehomes.controllers;

import java.io.File;
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.Location;
import org.bukkit.World;
import org.bukkit.entity.Player;
import org.bukkit.util.config.Configuration;

import com.battlecraft.zonedabone.battlehomes.BattleHomes;

public class MYSQLController {

    public Connection con;

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

    public MYSQLController(String url, String port, String username, String password, boolean debug, BattleHomes 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 BattleHomes";
        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 +"/BattleHomes", USERNAME,PASSWORD);
        } catch (SQLException e1) {
            e1.printStackTrace();
            return false;
        }

        createTable("desc Homes"
                , "CREATE TABLE Homes (Player char(32) NOT NULL, Number INTEGER UNSIGNED, World char(32) NOT NULL, X INTEGER SIGNED, Y FLOAT, Z INTEGER SIGNED, Yaw INTEGER SIGNED, Pitch INTEGER SIGNED, PRIMARY KEY (Player,Number))"
                ,"CREATE INDEX IDX_PLAYER_HOME on Homes (Player,Number)");
        try {
            con.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        if(plugin.config.getBoolean("imported", false)){return true;}

        System.out.println("IMPORTING OLD DATA");

        Configuration config = new Configuration(new File("plugins/BattleHomes/old.yml"));
        config.load();

        for(String key:config.getKeys()){
            System.out.println(key);
            for(int i = 0;i<10;i++){
                if(config.getBoolean(key+".home_"+i+".exists", false)){
                    int x = (int) ((int) config.getDouble(key+".home_"+i+".x", 0)-.5);
                    int y = (int) config.getDouble(key+".home_"+i+".y", 0);
                    int z = (int) ((int) config.getDouble(key+".home_"+i+".z", 0)-.5);
                    String world = plugin.config.getString(key+".home_"+i+".world", "world");
                    int yaw = config.getInt(key+".home_"+i+".yaw", 0);
                    int pitch = config.getInt(key+".home_"+i+".pitch", 0);
                    //Start duplicate code ftw!
                    PreparedStatement ps;
                    try {
                        ps = con.prepareStatement("INSERT INTO Homes VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE World = ?, X = ?, Y = ?, Z = ?, Yaw = ?, Pitch = ?");
                        ps.setString(1, key);
                        ps.setInt(2, i);
                        ps.setString(3, world);
                        ps.setInt(4, x);
                        ps.setFloat(5, y);
                        ps.setInt(6, z);
                        ps.setInt(7, yaw);
                        ps.setInt(8, pitch);
                        ps.setString(9, world);
                        ps.setInt(10, x);
                        ps.setFloat(11, y);
                        ps.setInt(12, z);
                        ps.setInt(13, yaw);
                        ps.setInt(14, pitch);
                        ps.execute();
                        con.commit();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        return false;
                    }
                }
            }
        }
        plugin.config.setProperty("imported", true);

        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 boolean setHome(String player, int number, Location loc){
        try{
            PreparedStatement ps = con.prepareStatement("INSERT INTO Homes VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE World = ?, X = ?, Y = ?, Z = ?, Yaw = ?, Pitch = ?");
            ps.setString(1, player);
            ps.setInt(2, number);
            ps.setString(3, loc.getWorld().getName());
            ps.setInt(4, (int) Math.round(loc.getX()-.5));
            ps.setFloat(5, (int) loc.getY());
            ps.setInt(6, (int) Math.round(loc.getZ()-.5));
            ps.setInt(7, (int) Math.round(loc.getYaw()/10)*10);
            ps.setInt(8, (int) Math.round(loc.getPitch()/10)*10);
            ps.setString(9, loc.getWorld().getName());
            ps.setInt(10, (int) Math.round(loc.getX()-.5));
            ps.setFloat(11, (int) loc.getY());
            ps.setInt(12, (int) Math.round(loc.getZ()-.5));
            ps.setInt(13, (int) Math.round(loc.getYaw()/10)*10);
            ps.setInt(14, (int) Math.round(loc.getPitch()/10)*10);
            ps.execute();
            con.commit();
        }catch (SQLException e){
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean setHome(Player player, int number){
        return setHome(player.getName(),number,player.getLocation());
    }

    public Object[] getHome(String player, int number){
        try{
            Statement ps = con.createStatement();
            ResultSet rs = ps.executeQuery("select Player,World,X,Y,Z,Yaw,Pitch from Homes WHERE Player LIKE '"+player+"%' AND Number = "+number);
            if(!rs.next()){return null;}
            Location loc = new Location(plugin.getServer().getWorld(rs.getString("World")), ((float)rs.getInt("X"))+.5, rs.getFloat("Y"), ((float)rs.getInt("Z"))+.5, rs.getInt("Yaw"), rs.getInt("Pitch"));
            String p = rs.getString("Player");
            Object[] obj = new Object[2];
            obj[0] = loc;
            obj[1] = p;
            return obj;
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }
    }
}