package me.yamas.tools.data.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

import org.bukkit.Bukkit;
import org.bukkit.Location;

import me.yamas.tools.Main;
import me.yamas.tools.objects.User;
import me.yamas.tools.objects.utils.UserUtils;
import me.yamas.tools.utils.Util;

public class MySQL {

	private static Connection conn;
	
	private static void checkTable(){
		openConnection();
		StringBuilder sb = new StringBuilder();
		sb.append("create table if not exists users(");
		sb.append("uuid varchar(100) not null,");
		sb.append("name varchar(50) not null,");
		sb.append("god int,");
		sb.append("mute bigint,");
		sb.append("homeWorld varchar(120),");
		sb.append("homeX int,");
		sb.append("homeY int,");
		sb.append("homeZ int,");
		sb.append("primary key(uuid));");
		
		sb.append("create table if not exists bans(");
		sb.append("uuid varchar(100) not null,");
		sb.append("victim varchar(50) not null,");
		sb.append("punisher varchar(100),");
		sb.append("reason varchar(100),");
		sb.append("giveDate bigint,");
		sb.append("deleteDate bigint,");
		sb.append("primary key(uuid));");
		try {
			conn.createStatement().executeUpdate(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    closeConnection();
	}
	
	
	/*
	 * USERS
	 */
	
	private static void loadUsers() throws SQLException{
		openConnection();
		int i = 0;
		ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM `users`");
		while(rs.next()){
			User u = User.get(UUID.fromString(rs.getString(1)));
			u.setName(rs.getString(2));
			int hX = rs.getInt(6);
			int hY = rs.getInt(7);
			int hZ = rs.getInt(8);
			String hWorld = rs.getString(5);
			long mute = rs.getLong(4);
			u.setHome(new Location(Bukkit.getWorld(hWorld), hX, hY, hZ));
			u.setMute(mute);
			u.setGod(rs.getInt(3) == 1 ? true : false);
			i++;
			
		}
		Util.sendMessage(Bukkit.getConsoleSender(), "&8[&4YamasTools&8] &7Zaladowano &c" + i + " &7graczy!");
		closeConnection();
	}
	
	private static void saveUsers() throws SQLException{
		openConnection();
		int i = 0;
		for(User u : UserUtils.getUsers()){
			StringBuilder sb = new StringBuilder();
			sb.append("INSERT INTO users (uuid, name, god, mute, homeWorld, homeX, homeY, homeZ) VALUES (");
			sb.append("'" + u.getUUID().toString() +"',");
			sb.append("'" + u.getName() +"',");
			sb.append("'" + u.isGod() +"',");
			sb.append("'" + u.getMute() +"',");
			sb.append("'" + u.getHome().getWorld().getName() +"',");
			sb.append("'" + u.getHome().getBlockX() +"',");
			sb.append("'" + u.getHome().getBlockY() +"',");
			sb.append("'" + u.getHome().getBlockZ() +"'");
			sb.append(") ON DUPLICATE KEY UPDATE ");
			sb.append("name='" + u.getName() +"',");
			sb.append("god='" + u.isGod() +"',");
			sb.append("mute='" + u.getMute() +"',");
			sb.append("homeWorld='" + u.getHome().getWorld().getName() +"',");
			sb.append("homeX='" + u.getHome().getBlockX() +"',");
			sb.append("homeY='" + u.getHome().getBlockY() +"',");
			sb.append("homeZ='" + u.getHome().getBlockZ() +"';");
			conn.createStatement().executeUpdate(sb.toString());
			i++;
		}
		Util.sendMessage(Bukkit.getConsoleSender(), "&8[&4YamasTools&8] &7Zapisano &c" + i + " &7graczy!");
		closeConnection();
	}
	
	/*
	 * BANS
	 */
	
	private static void loadBans() throws SQLException{
		openConnection();
		int i = 0;
		ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM `bans`");
		while(rs.next()){
			User u = User.get(UUID.fromString(rs.getString(1)));
			u.setName(rs.getString(2));
			u.setPunisher(rs.getString(3));
			u.setReason(rs.getString(4));
			u.setBanTime(rs.getLong(4));
			u.setValidateTill(rs.getLong(5));
			i++;
			
		}
		Util.sendMessage(Bukkit.getConsoleSender(), "&8[&4YamasTools&8] &7Zaladowano &c" + i + " &7banow!");
		closeConnection();
	}
	
	private static void saveBans() throws SQLException{
		openConnection();
		int i = 0;
		for(User u : UserUtils.getUsers()){
			StringBuilder sb = new StringBuilder();
			sb.append("INSERT INTO bans (uuid, victim, punisher, reason, giveDate, deleteDate) VALUES (");
			sb.append("'" + u.getUUID().toString() +"',");
			sb.append("'" + u.getName() +"',");
			sb.append("'" + u.getPunisher() +"',");
			sb.append("'" + u.getReason() +"',");
			sb.append("'" + u.getBanTime() +"',");
			sb.append("'" + u.getValidateTill() +"'");
			sb.append(") ON DUPLICATE KEY UPDATE ");
			sb.append("victim='" + u.getName() +"',");
			sb.append("punisher='" + u.getPunisher() +"',");
			sb.append("reason='" + u.getReason() +"',");
			sb.append("giveDate='" + u.getBanTime() +"',");
			sb.append("deleteDate='" + u.getValidateTill() +"';");
			conn.createStatement().executeUpdate(sb.toString());
			i++;
		}
		Util.sendMessage(Bukkit.getConsoleSender(), "&8[&4YamasTools&8] &7Zapisano &c" + i + " &7banow!");
		closeConnection();
	}
	
	private static synchronized void openConnection(){
		String host = Main.getInstance().getConfig().getString("mysql.host");
		int port = Main.getInstance().getConfig().getInt("mysql.port");
		String database = Main.getInstance().getConfig().getString("mysql.database");
		String user = Main.getInstance().getConfig().getString("mysql.user");
		String password = Main.getInstance().getConfig().getString("mysql.password");
		if(!isConnected()){
			try{
				conn = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database + "?user=" + user + "&password=" + password);
			} catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
	
	private static synchronized void closeConnection(){
		if(isConnected()){
			try{
				conn.close();
			} catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
	
	public static boolean isConnected() {
		try{
			if(conn == null) return false;
			if(conn.isClosed()) return false;
		} catch(SQLException e){
			e.printStackTrace();
		}
		return true;
	}
	
}