package dbFixer;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

public class Fixer {

    private static int counter = 1;
    private static Map<String, String> spriteNameLookup = new HashMap<>();
    private static Map<String, String> nameSpriteLookup = new HashMap<>();
    private static Map<String, String> spriteRevisionLookup = new HashMap<>();

    public static void main(String[] args) {

        try {

            Storage storage = new Storage("127.0.01", "root", "", "icarusdb");

            if (storage.isConnected()) {
                System.out.println("Connected to MySQL!");
            }

            //fixSpriteIds(storage);
            //recountFurnitureRecords(storage);
            createSpriteLookup(storage);
            findLoneItems(storage);

        } catch (Exception e) {
            e.printStackTrace();
        }

        System.out.println("Done!");
    }
    

    public static String getValue(Element element, String node) {
        
    
        return element.getChildNodes().item(0).getTextContent();//element.getElementsByTagName(node).item(0).getTextContent();
    }

    private static void recountFurnitureRecords(Storage storage) {

        Connection sqlConnection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            sqlConnection = storage.getConnection();
            preparedStatement = storage.prepare("SELECT * FROM furniture", sqlConnection);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {

                int newId = counter;
                int oldId = resultSet.getInt("id");

                storage.execute("UPDATE furniture SET id = '" + newId + "' WHERE id = '" + oldId + "'");
                storage.execute("UPDATE catalog_items SET item_id = '" + newId + "' WHERE item_id = '" + oldId + "'");
                storage.execute("UPDATE item_data SET item_id = '" + newId + "' WHERE item_id = '" + oldId + "'");

                System.out.println("Completed " + counter + " queries!");

                counter++;

                Thread.sleep(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Storage.closeSilently(resultSet);
            Storage.closeSilently(preparedStatement);
            Storage.closeSilently(sqlConnection);
        }

    }

    private static void fixSpriteIds(Storage storage) throws Exception {

        File file = new File("furnidata.xml");

        DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
        Document document = documentBuilder.parse(file);

        Element furniData = document.getDocumentElement();
        NodeList roomItemTypeNodes = furniData.getElementsByTagName("roomitemtypes");

        for (int r = 0; r < roomItemTypeNodes.getLength(); r++) {

            Element roomItemTypes = (Element)furniData.getElementsByTagName("roomitemtypes").item(r);
            NodeList furniTypes = roomItemTypes.getElementsByTagName("furnitype");

            for (int i = 0; i < furniTypes.getLength(); i++) {

                Element furniType = (Element)furniTypes.item(i);

                String id = furniType.getAttribute("id");
                String classname = furniType.getAttribute("classname");

                storage.execute("UPDATE furniture SET sprite_id = '" + id + "' WHERE item_name = '" + classname + "'");
                System.out.println("Completed " + counter + " queries!");
                counter++;
            }
        }
    }

    private static void createSpriteLookup(Storage storage) throws Exception {

        File file = new File("furnidata.xml");

        DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
        Document document = documentBuilder.parse(file);

        Element furniData = document.getDocumentElement();
        NodeList roomItemTypeNodes = furniData.getElementsByTagName("roomitemtypes");

        for (int r = 0; r < roomItemTypeNodes.getLength(); r++) {

            Element roomItemTypes = (Element)furniData.getElementsByTagName("roomitemtypes").item(r);
            NodeList furniTypes = roomItemTypes.getElementsByTagName("furnitype");

            for (int i = 0; i < furniTypes.getLength(); i++) {

                Element furniType = (Element)furniTypes.item(i);

                String id = furniType.getAttribute("id");
                String classname = furniType.getAttribute("classname");

                if (spriteNameLookup.containsKey(id) && !spriteNameLookup.get(id).equalsIgnoreCase(classname)) {
                    //System.out.println("Woah bro! The ID " + id + " with classname '" + classname + "' already exists!");
                    continue;
                }

                String revision = furniType.getChildNodes().item(0).getTextContent();
                
                spriteRevisionLookup.put(id, revision);
                spriteNameLookup.put(id, classname);
                nameSpriteLookup.put(classname, id);

                //storage.execute("UPDATE furniture SET sprite_id = '" + id + "' WHERE item_name = '" + classname + "'");
                //System.out.println("Completed " + counter + " queries!");
                counter++;
            }
        }
    }

    @SuppressWarnings("unchecked")
    private static void findLoneItems(Storage storage) throws Exception {

        int cataloguePageId = 2053;
        
        Set<Integer> itemsToAdd = new HashSet<>();
        
        JSONParser parser = new JSONParser();
        JSONObject jsonObject = (JSONObject)parser.parse(new FileReader("furni.json"));
        JSONArray items = (JSONArray) jsonObject.get("items");

        Iterator<JSONObject> iterator = (Iterator<JSONObject>)items.iterator();
        
        int i = 0;
        while (iterator.hasNext()) {

            JSONObject itemObj = iterator.next();
            
            int spriteId = (int)((long) itemObj.get("spriteId"));
            
            if (!storage.exists("SELECT id FROM furniture WHERE sprite_id = " + spriteId)) {
                itemsToAdd.add(spriteId);
                i++;
            }
        }
        
        System.out.println("Items to add: " + itemsToAdd.size() + " (actual number is " + i + ")");
        
        for (int spriteId : itemsToAdd) {
            
            System.out.print("https://images.habbo.com/dcr/hof_furni/" + spriteRevisionLookup.get(String.valueOf(spriteId)) +  "/" + spriteNameLookup.get(String.valueOf(spriteId)) + ".swf");
            System.out.print("|");
            
            int itemId = createFurnitureRecord(spriteId, spriteNameLookup.get(String.valueOf(spriteId)), storage);
            createCataloguePageItem(itemId, cataloguePageId, spriteNameLookup.get(String.valueOf(spriteId)), storage);
        }

    }
    
    private static int createFurnitureRecord(int spriteId, String itemName, Storage storage) {

        Connection sqlConnection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int id = -1;

        try {

            sqlConnection = storage.getConnection();
            preparedStatement = storage.prepare("INSERT INTO furniture (item_name, public_name, sprite_id) VALUES (?, ?, ?)", sqlConnection);
            preparedStatement.setString(1, itemName);
            preparedStatement.setString(2, itemName);
            preparedStatement.setInt(3, spriteId);
            preparedStatement.executeUpdate();

            ResultSet row = preparedStatement.getGeneratedKeys();

            if (row != null && row.next()) {
                id = row.getInt(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Storage.closeSilently(resultSet);
            Storage.closeSilently(preparedStatement);
            Storage.closeSilently(sqlConnection);
        }

        return id;
    }
    
    private static int createCataloguePageItem(int itemId, int pageId, String name, Storage storage) {

        Connection sqlConnection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int id = -1;

        try {

            sqlConnection = storage.getConnection();
            preparedStatement = storage.prepare("INSERT INTO catalog_items (item_id, catalog_name, page_id) VALUES (?, ?, ?)", sqlConnection);
            preparedStatement.setInt(1, itemId);
            preparedStatement.setString(2, name);
            preparedStatement.setInt(3, pageId);
            preparedStatement.executeUpdate();

            ResultSet row = preparedStatement.getGeneratedKeys();

            if (row != null && row.next()) {
                id = row.getInt(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Storage.closeSilently(resultSet);
            Storage.closeSilently(preparedStatement);
            Storage.closeSilently(sqlConnection);
        }

        return id;
    }

    static String readFile(String path, Charset encoding) 
            throws IOException 
    {
        byte[] encoded = Files.readAllBytes(Paths.get(path));
        return new String(encoded, encoding);
    }
}
