<commit_before>/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.untamedears.JukeAlert.storage;

import com.untamedears.JukeAlert.JukeAlert;
import com.untamedears.JukeAlert.chat.ChatFiller;
import com.untamedears.JukeAlert.group.GroupMediator;
import com.untamedears.JukeAlert.manager.ConfigManager;
import com.untamedears.JukeAlert.model.LoggedAction;
import com.untamedears.JukeAlert.model.Snitch;
import com.untamedears.JukeAlert.tasks.GetSnitchInfoTask;
import com.untamedears.JukeAlert.util.SparseQuadTree;
import com.untamedears.citadel.entity.Faction;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.bukkit.Bukkit;

import org.bukkit.ChatColor;
import org.bukkit.Location;
import org.bukkit.Material;
import org.bukkit.World;
import org.bukkit.block.Block;
import org.bukkit.entity.Entity;
import org.bukkit.entity.Player;
import org.bukkit.inventory.ItemStack;

/**
 *
 * @author <NAME>
 */
public class JukeAlertLogger {

    private JukeAlert plugin;
    private ConfigManager configManager;
    private GroupMediator groupMediator;
    private Database db;
    private String snitchsTbl;
    private String snitchDetailsTbl;
    private PreparedStatement getSnitchIdFromLocationStmt;
    private PreparedStatement getAllSnitchesStmt;
    private PreparedStatement getAllSnitchesByWorldStmt;
    private PreparedStatement getLastSnitchID;
    private PreparedStatement getSnitchLogStmt;
    private PreparedStatement deleteSnitchLogStmt;
    private PreparedStatement insertSnitchLogStmt;
    private PreparedStatement insertNewSnitchStmt;
    private PreparedStatement deleteSnitchStmt;
    private PreparedStatement updateGroupStmt;
    private PreparedStatement updateCuboidVolumeStmt;
    private PreparedStatement updateSnitchNameStmt;
    private PreparedStatement updateSnitchGroupStmt;
    private int logsPerPage;
    private int lastSnitchID;

    public JukeAlertLogger() {
        plugin = JukeAlert.getInstance();
        configManager = plugin.getConfigManager();
        groupMediator = plugin.getGroupMediator();

        String host = configManager.getHost();
        int port = configManager.getPort();
        String dbname = configManager.getDatabase();
        String username = configManager.getUsername();
        String password = configManager.getPassword();
        String prefix = configManager.getPrefix();

        snitchsTbl = prefix + "snitchs";
        snitchDetailsTbl = prefix + "snitch_details";

        db = new Database(host, port, dbname, username, password, prefix, this.plugin.getLogger());
        boolean connected = db.connect();
        if (connected) {
            genTables();
            initializeStatements();
        } else {
            this.plugin.getLogger().log(Level.SEVERE, "Could not connect to the database! Fill out your config.yml!");
        }

        logsPerPage = configManager.getLogsPerPage();
    }

    public Database getDb() {
        return db;
    }

    /**
     * Table generator
     */
    private void genTables() {
        //Snitches
        db.execute("CREATE TABLE IF NOT EXISTS `" + snitchsTbl + "` ("
                + "`snitch_id` int(10) unsigned NOT NULL AUTO_INCREMENT,"
                + "`snitch_world` varchar(40) NOT NULL,"
                + "`snitch_name` varchar(40) NOT NULL,"
                + "`snitch_x` int(10) NOT NULL,"
                + "`snitch_y` int(10) NOT NULL,"
                + "`snitch_z` int(10) NOT NULL,"
                + "`snitch_group` varchar(40) NOT NULL,"
                + "`snitch_cuboid_x` int(10) NOT NULL,"
                + "`snitch_cuboid_y` int(10) NOT NULL,"
                + "`snitch_cuboid_z` int(10) NOT NULL,"
                + "`snitch_should_log` BOOL,"
                + "PRIMARY KEY (`snitch_id`));");
        //Snitch Details
        // need to know:
        // action: (killed, block break, block place, etc), can't be null
        // person who initiated the action (player name), can't be null
        // victim of action (player name, entity), can be null
        // x, (for things like block place, bucket empty, etc, NOT the snitch x,y,z) can be null
        // y, can be null
        // z, can be null
        // block_id, can be null (block id for block place, block use, block break, etc)
        db.execute("CREATE TABLE IF NOT EXISTS `" + snitchDetailsTbl + "` ("
                + "`snitch_details_id` int(10) unsigned NOT NULL AUTO_INCREMENT,"
                + "`snitch_id` int(10) unsigned NOT NULL," // reference to the column in the main snitches table
                + "`snitch_log_time` datetime,"
                + "`snitch_logged_action` tinyint unsigned NOT NULL,"
                + "`snitch_logged_initiated_user` varchar(16) NOT NULL,"
                + "`snitch_logged_victim_user` varchar(16), "
                + "`snitch_logged_x` int(10), "
                + "`snitch_logged_Y` int(10), "
                + "`snitch_logged_z` int(10), "
                + "`snitch_logged_materialid` smallint unsigned," // can be either a block, item, etc
                + "PRIMARY KEY (`snitch_details_id`));");
    }
    
    public PreparedStatement getNewInsertSnitchLogStmt() {
    	 return db.prepareStatement(String.format(
                "INSERT INTO %s (snitch_id, snitch_log_time, snitch_logged_action, snitch_logged_initiated_user,"
                + " snitch_logged_victim_user, snitch_logged_x, snitch_logged_y, snitch_logged_z, snitch_logged_materialid) "
                + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)",
                snitchDetailsTbl));
    }
    private void initializeStatements() {

        getAllSnitchesStmt = db.prepareStatement(String.format(
                "SELECT * FROM %s", snitchsTbl));

        getAllSnitchesByWorldStmt = db.prepareStatement(String.format(
                "SELECT * FROM %s WHERE snitch_world = ?", snitchsTbl));

        getLastSnitchID = db.prepareStatement(String.format(
                "SHOW TABLE STATUS LIKE '%s'", snitchsTbl));

        // statement to get LIMIT entries OFFSET from a number from the snitchesDetailsTbl based on a snitch_id from the main snitchesTbl
        // LIMIT ?,? means offset followed by max rows to return 
        getSnitchLogStmt = db.prepareStatement(String.format(
                "SELECT * FROM %s"
                + " WHERE snitch_id=? ORDER BY snitch_log_time DESC LIMIT ?,?",
                snitchDetailsTbl));

        // statement to get the ID of a snitch in the main snitchsTbl based on a Location (x,y,z, world)<commit_msg>Add indexes and FK constraint<commit_after>/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.untamedears.JukeAlert.storage;

import com.untamedears.JukeAlert.JukeAlert;
import com.untamedears.JukeAlert.chat.ChatFiller;
import com.untamedears.JukeAlert.group.GroupMediator;
import com.untamedears.JukeAlert.manager.ConfigManager;
import com.untamedears.JukeAlert.model.LoggedAction;
import com.untamedears.JukeAlert.model.Snitch;
import com.untamedears.JukeAlert.tasks.GetSnitchInfoTask;
import com.untamedears.JukeAlert.util.SparseQuadTree;
import com.untamedears.citadel.entity.Faction;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.bukkit.Bukkit;

import org.bukkit.ChatColor;
import org.bukkit.Location;
import org.bukkit.Material;
import org.bukkit.World;
import org.bukkit.block.Block;
import org.bukkit.entity.Entity;
import org.bukkit.entity.Player;
import org.bukkit.inventory.ItemStack;

/**
 *
 * @author <NAME>
 */
public class JukeAlertLogger {

    private JukeAlert plugin;
    private ConfigManager configManager;
    private GroupMediator groupMediator;
    private Database db;
    private String snitchsTbl;
    private String snitchDetailsTbl;
    private PreparedStatement getSnitchIdFromLocationStmt;
    private PreparedStatement getAllSnitchesStmt;
    private PreparedStatement getAllSnitchesByWorldStmt;
    private PreparedStatement getLastSnitchID;
    private PreparedStatement getSnitchLogStmt;
    private PreparedStatement deleteSnitchLogStmt;
    private PreparedStatement insertSnitchLogStmt;
    private PreparedStatement insertNewSnitchStmt;
    private PreparedStatement deleteSnitchStmt;
    private PreparedStatement updateGroupStmt;
    private PreparedStatement updateCuboidVolumeStmt;
    private PreparedStatement updateSnitchNameStmt;
    private PreparedStatement updateSnitchGroupStmt;
    private int logsPerPage;
    private int lastSnitchID;

    public JukeAlertLogger() {
        plugin = JukeAlert.getInstance();
        configManager = plugin.getConfigManager();
        groupMediator = plugin.getGroupMediator();

        String host = configManager.getHost();
        int port = configManager.getPort();
        String dbname = configManager.getDatabase();
        String username = configManager.getUsername();
        String password = configManager.getPassword();
        String prefix = configManager.getPrefix();

        snitchsTbl = prefix + "snitchs";
        snitchDetailsTbl = prefix + "snitch_details";

        db = new Database(host, port, dbname, username, password, prefix, this.plugin.getLogger());
        boolean connected = db.connect();
        if (connected) {
            genTables();
            initializeStatements();
        } else {
            this.plugin.getLogger().log(Level.SEVERE, "Could not connect to the database! Fill out your config.yml!");
        }

        logsPerPage = configManager.getLogsPerPage();
    }

    public Database getDb() {
        return db;
    }

    /**
     * Table generator
     */
    private void genTables() {
        //Snitches
        db.execute("CREATE TABLE IF NOT EXISTS `" + snitchsTbl + "` ("
                + "`snitch_id` int(10) unsigned NOT NULL AUTO_INCREMENT,"
                + "`snitch_world` varchar(40) NOT NULL,"
                + "`snitch_name` varchar(40) NOT NULL,"
                + "`snitch_x` int(10) NOT NULL,"
                + "`snitch_y` int(10) NOT NULL,"
                + "`snitch_z` int(10) NOT NULL,"
                + "`snitch_group` varchar(40) NOT NULL,"
                + "`snitch_cuboid_x` int(10) NOT NULL,"
                + "`snitch_cuboid_y` int(10) NOT NULL,"
                + "`snitch_cuboid_z` int(10) NOT NULL,"
                + "`snitch_should_log` BOOL,"
                + "PRIMARY KEY (`snitch_id`),"
                + "INDEX `idx_y` (`snitch_y` ASC));");
        //Snitch Details
        // need to know:
        // action: (killed, block break, block place, etc), can't be null
        // person who initiated the action (player name), can't be null
        // victim of action (player name, entity), can be null
        // x, (for things like block place, bucket empty, etc, NOT the snitch x,y,z) can be null
        // y, can be null
        // z, can be null
        // block_id, can be null (block id for block place, block use, block break, etc)
        db.execute("CREATE TABLE IF NOT EXISTS `" + snitchDetailsTbl + "` ("
                + "`snitch_details_id` int(10) unsigned NOT NULL AUTO_INCREMENT,"
                + "`snitch_id` int(10) unsigned NOT NULL," // reference to the column in the main snitches table
                + "`snitch_log_time` datetime,"
                + "`snitch_logged_action` tinyint unsigned NOT NULL,"
                + "`snitch_logged_initiated_user` varchar(16) NOT NULL,"
                + "`snitch_logged_victim_user` varchar(16), "
                + "`snitch_logged_x` int(10), "
                + "`snitch_logged_Y` int(10), "
                + "`snitch_logged_z` int(10), "
                + "`snitch_logged_materialid` smallint unsigned," // can be either a block, item, etc
                + "PRIMARY KEY (`snitch_details_id`),"
                + "INDEX `idx_snitch_id` (`snitch_id` ASC),"
                + "CONSTRAINT `fk_snitchs_snitch_id` FOREIGN KEY (`snitch_id`)"
                + "  REFERENCES `snitchs` (`snitch_id`) ON DELETE CASCADE ON UPDATE CASCADE);");
    }
    
    public PreparedStatement getNewInsertSnitchLogStmt() {
    	 return db.prepareStatement(String.format(
                "INSERT INTO %s (snitch_id, snitch_log_time, snitch_logged_action, snitch_logged_initiated_user,"
                + " snitch_logged_victim_user, snitch_logged_x, snitch_logged_y, snitch_logged_z, snitch_logged_materialid) "
                + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)",
                snitchDetailsTbl));
    }
    private void initializeStatements() {

        getAllSnitchesStmt = db.prepareStatement(String.format(
                "SELECT * FROM %s", snitchsTbl));

        getAllSnitchesByWorldStmt = db.prepareStatement(String.format(
                "SELECT * FROM %s WHERE snitch_world = ?", snitchsTbl));

        getLastSnitchID = db.prepareStatement(String.format(
                "SHOW TABLE STATUS LIKE '%s'", snitchsTbl));

        // statement to get LIMIT entries OFFSET from a number from the snitchesDetailsTbl based on a snitch_id from the main snitchesTbl
        // LIMIT ?,? means offset followed by max rows to return 
        getSnitchLogStmt = db.prepareStatement(String.format(
                "SELECT * FROM %s"
                + " WHERE snitch_id=? ORDER BY snitch_log_time DESC LIMIT ?,?",
                snitchDetailsTbl));

        // statement to get the ID of a snitch in the main snitchsTbl based on a Location (x,y,z, world)
{'fraction_non_alphanumeric': 0.09959189518149925, 'fraction_numerical': 0.004868618887377389, 'mean_word_length': 2.7802435723951286, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1789355', 'n_tokens_mistral': 4327, 'n_tokens_neox': 4084, 'n_words': 1031}