public class DatabaseHandler {


private static DatabaseHandler instance = null;
/** MySQL port to connect. */
private static final int port = 3306; //Default is port 3306
/** The url of the database we wat to connect. */
private static String url = "j******************" + port +"/";
/** The username to connect to database. */
private static final String dbUser = "*******";
/** The password to connecto to database */
private static final String dbPassword = "**********";
/** The name of the database we want to query */
private static final String dbName = "**********";

private static Connection connect = null;
private  Statement statement = null;
private PreparedStatement preparedStatement = null;
private  ResultSet resultSet = null;

/** Constructor load the MySQL Driver */
public DatabaseHandler()
{
    try{
        // Load the MySQL Driver
        Class.forName("com.mysql.jdbc.Driver");
    }catch(ClassNotFoundException e)
    {
        System.out.println("MySQL Driver could not loaded!");
        e.printStackTrace();
    }
}

/**
 * Returns a list with the houses coordinates needed from the {@link GroundBuilder#buildMissingGround(World, int)} to spawn the ground.
 * @param rowID the ID of the row we want the coordinates
 */
public List<MissingGroundCoordinates> getMissingGroundCoordinates(int rowID)
{
    List<MissingGroundCoordinates> missingGroundCoordinates = new ArrayList<MissingGroundCoordinates>();

    try
    {
        statement = connect.createStatement();
        resultSet = statement.executeQuery("SELECT startBuildingPosX, startBuildingPosY, startBuildingPosZ, blocksInX, blocksInZ FROM houses_coordinates WHERE rowID = " + rowID);

        while (resultSet.next())
        {
            MissingGroundCoordinates mGroundCoordinates = new MissingGroundCoordinates();

            mGroundCoordinates.setStartBuildingPosX(resultSet.getInt("startBuildingPosX"));
            mGroundCoordinates.setStartBuildingPosY(resultSet.getInt("startBuildingPosY"));
            mGroundCoordinates.setStartBuildingPosZ(resultSet.getInt("startBuildingPosZ"));
            mGroundCoordinates.setBlocksInX(resultSet.getInt("blocksInX"));
            mGroundCoordinates.setBlocksInZ(resultSet.getInt("blocksInZ"));

            missingGroundCoordinates.add(mGroundCoordinates);

            //System.out.println(mGroundCoordinates); // DEBUG
        }
        return missingGroundCoordinates;
    }
    catch(SQLException e)
    {
        System.out.println("There is been a problem in DatabaseHandler.getMissingGroundCoordinates.Try block caught an sql exception and the method returns a null valued list.");
        return null;
    }
    finally
    {
        close();
    }
}

/**
 * Query the database to get maxBlocksInZ value for a row.
 * @param rowID id of the row needed the maxBlocksInZ
 * @return the maxBlocksInZ for the row it passed as arguement
 */
public int getMaxBlocksInZForARow(int rowID)
{
    int maxBlocksInZ = 0;
    try
    {
        statement = connect.createStatement();
        resultSet = statement.executeQuery("SELECT maxBlocksInZ FROM houses_rows WHERE rowID = " + rowID);

        while(resultSet.next())
        {
            maxBlocksInZ = resultSet.getInt("maxBlocksInZ");
        }
        return maxBlocksInZ;
    }
    catch(SQLException e)
    {
        System.out.println("SQL Exception occured. getMaxBlocksInZForARow returns 0");
        return 0;
    }
    finally
    {
        close();
    }


}

/**
 * Query the database to fetch the color of the block used to build the under construction house.
 * @return the name of the color
 */
public String getBlockColor()
{
    String color = null;
    try
    {
        statement = connect.createStatement();
        resultSet = statement.executeQuery("SELECT blockColor FROM tc_coordinates");

        while(resultSet.next())
        {
            color = resultSet.getString("blockColor");
        }
        //System.out.println(color); // DEBUG
        return color;
    }
    catch(SQLException e)
    {
        return null;
    }
    finally
    {
        close();
    }
}

/**
 * This method is called when a block from our city is clicked. It queries the database searching the ID
 * that matches the coordinates of the block it was clicked. Then we use that ID to fetch the tweet data.
 * @param x the x coordinate of the block that was clicked
 * @param y the y coordinate of the block that was clicked
 * @param z the z coordinate of the block that was clicked
 * @return a {@link Tweet} instance with its fields setted.
 */
public Tweet getTweet(int x,int y, int z)
{
    int id = 0;
    Tweet tweet = null;

    try {
        preparedStatement = connect.prepareStatement("SELECT ID FROM spawned_blocks WHERE posX LIKE ? AND posY LIKE ? AND posZ LIKE ?");

        preparedStatement.setInt(1,x);
        preparedStatement.setInt(2,y);
        preparedStatement.setInt(3,z);
        resultSet = preparedStatement.executeQuery();

        while(resultSet.next())
        {
            id = resultSet.getInt("ID");
        }

        if(id > 0){
            statement = connect.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM tweets WHERE ID LIKE " + id);

            while (resultSet.next())
            {
                tweet = new Tweet();

                tweet.setID(resultSet.getInt("ID"));
                tweet.setUsername(resultSet.getString("user_name"));
                tweet.setTweetText(resultSet.getString("tweet_text"));
                tweet.setTweetDate(resultSet.getString("tweet_date"));
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        close();
    }
    return tweet;
}

/**
 * We fetch the latest ID in the table we define.
 * @param tableName the name of the table we need the latest id from
 * @return the latest ID from the table we gave as parameter
 */
public int getLatestID(String tableName)
{
    int latestID = 0;
    try
    {
        String columnName = "";
        statement = connect.createStatement();

        // If the table name is houses_coordinates we need the latest houseID
        if (tableName.equals("houses_coordinates"))
        {
            columnName = "houseID";
        }
        else{
            columnName = "ID";
        }
        resultSet = statement.executeQuery("SELECT " + columnName + " FROM " + tableName + " ORDER BY " + columnName + " DESC LIMIT 1");

        while(resultSet.next())
        {
            latestID = resultSet.getInt(columnName);
        }
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }

    return latestID;
}

/**
 * Spawns the blocks need to be spawned.
 * @param world the world object
 * @param latestBuildedBlockIDInWorld the last block's id that was builded in this world
 */
public void spawnUnspawnedBlocks(World world, int latestBuildedBlockIDInWorld)
{
    try
    {
        statement = connect.createStatement();

        resultSet = statement.executeQuery("SELECT * FROM spawned_blocks WHERE ID > " + latestBuildedBlockIDInWorld);

        while (resultSet.next()) {
            // System.out.println("To id einai: " + resultSet.getInt("ID") + ", to posX einai: " +
            // resultSet.getInt("posX") +", to posY einai: " + resultSet.getInt("posY") +", to posZ einai: " + resultSet.getInt("posZ"));
            world.setBlock(resultSet.getInt("posX"), resultSet.getInt("posY"), resultSet.getInt("posZ"), BlockPicker.getBlockByColor(resultSet.getString("blockColor")));
        }

    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }
}

/**
 * Spawns the floor,stairs,doors or/and roof where is necessary.
 * @param world the world object
 * @param currentlyConstructingHouseIDInThisWorld the houses ID that is currently under construction in this world.
 */
public void spawnUnspawnedFloorStairsDoorsRoof(World world, int currentlyConstructingHouseIDInThisWorld)
{
    try
    {
        int i = 1;
        statement = connect.createStatement();

        resultSet = statement.executeQuery("SELECT * FROM houses_coordinates WHERE houseID >= " + currentlyConstructingHouseIDInThisWorld);

        while(resultSet.next())
        {
            if(resultSet.getBoolean("finishedBuilding"))
            {
                //Build the roof
                RoofBuilder.buildRoof(world, resultSet.getInt("startBuildingPosX"), resultSet.getInt("boundsY"),
                                        resultSet.getInt("startBuildingPosZ"), resultSet.getInt("blocksInX"), resultSet.getInt("blocksInZ"));

                //Build door and Stairs
                DoorAndStairsBuilder.buildDoorAndStairs(world, resultSet.getInt("startBuildingPosX"), resultSet.getInt("startBuildingPosY"),
                                        resultSet.getInt("startBuildingPosZ"), resultSet.getInt("blocksInZ"));

                //If the houseID is the first result we got we skip building ground because it is already builded
                if (resultSet.getInt("houseID") != currentlyConstructingHouseIDInThisWorld)
                {

                    GroundBuilder.buildGround(world, resultSet.getInt("startBuildingPosX"), resultSet.getInt("startBuildingPosY"),
                            resultSet.getInt("startBuildingPosZ"),  resultSet.getInt("blocksInX"),  resultSet.getInt("blocksInZ"));
                }
            }
            else
            {
                GroundBuilder.buildGround(world, resultSet.getInt("startBuildingPosX"), resultSet.getInt("startBuildingPosY"),
                        resultSet.getInt("startBuildingPosZ"),  resultSet.getInt("blocksInX"),  resultSet.getInt("blocksInZ"));
            }
        }

        int rowIDForCurrentlyConstructingHouse = 0;
        resultSet = statement.executeQuery("SELECT rowID FROM houses_coordinates WHERE houseID = " + currentlyConstructingHouseIDInThisWorld);
        while(resultSet.next())
        {
            rowIDForCurrentlyConstructingHouse = resultSet.getInt("rowID");
        }

        resultSet = statement.executeQuery("SELECT rowID FROM houses_rows WHERE rowID >= " + rowIDForCurrentlyConstructingHouse);

        //If result.next() returns false then the resultSet is empty
        if (resultSet.next())
        {
            resultSet.last();
            int[] rowIDs = new int[resultSet.getRow()];
            resultSet.beforeFirst();

            int loopCounter = 0;
            while(resultSet.next())
            {
                rowIDs[loopCounter] = resultSet.getInt("rowID");
                loopCounter++;
            }

            for (int l = 0; l < rowIDs.length; l++)
            {
                GroundBuilder.buildMissingGround(world, rowIDs[l]);
            }
        }

    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }
}


/**
 * Get the values of the tc_coordinates table's fields and assign to the fields of the data object class {@link Coordinates}
 */
public void coordinatesInitialization()
{
    try
    {
        statement = connect.createStatement();
        resultSet = statement.executeQuery("SELECT * FROM tc_coordinates");
        while(resultSet.next())
        {

            Coordinates.getInstance().buildedHouses = resultSet.getInt("buildedHouses");
            Coordinates.getInstance().maxBuildedHousesInXAxis = resultSet.getInt("maxBuildedHousesInXAxis");

            Coordinates.getInstance().startBuildingPosX = resultSet.getInt("startBuildingPosX");
            Coordinates.getInstance().startBuildingPosY = resultSet.getInt("startBuildingPosY");
            Coordinates.getInstance().startBuildingPosZ = resultSet.getInt("startBuildingPosZ");

            Coordinates.getInstance().blocksInX = resultSet.getInt("blocksInX");
            Coordinates.getInstance().blocksInY = resultSet.getInt("blocksInY");
            Coordinates.getInstance().blocksInZ = resultSet.getInt("blocksInZ");

            Coordinates.getInstance().boundsX = resultSet.getInt("boundsX");
            Coordinates.getInstance().boundsY = resultSet.getInt("boundsY");
            Coordinates.getInstance().boundsZ = resultSet.getInt("boundsZ");

            Coordinates.getInstance().nextPosX = resultSet.getInt("nextPosX");
            Coordinates.getInstance().nextPosY = resultSet.getInt("nextPosY");
            Coordinates.getInstance().nextPosZ = resultSet.getInt("nextPosZ");

            Coordinates.getInstance().currentRowID = resultSet.getInt("currentRowID");
            Coordinates.getInstance().buildedHousesInCurrentRow = resultSet.getInt("buildedHousesInCurrentRow");
            Coordinates.getInstance().maxBlocksInZForCurrentRow = resultSet.getInt("maxBlocksInZForCurrentRow");
        }
    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }
}


/**
 * Update the table tc_coordinates with values' of the fields from the data object class {@link Coordinates}
 */
public void updateCoordinates()
{
    Coordinates coords = Coordinates.getInstance();

    try
    {
        preparedStatement = connect.prepareStatement("UPDATE tc_coordinates SET "
                                                    + "buildedHouses = ?, maxBuildedHousesInXAxis = ?, startBuildingPosX = ?, startBuildingPosY = ?, startBuildingPosZ = ?, "
                                                    + "blocksInX = ?, blocksInY = ?, blocksInZ = ?, boundsX = ?, boundsY = ?, boundsZ = ?, "
                                                    + "nextPosX = ?, nextPosY = ?, nextPosZ = ?, currentRowID = ?, buildedHousesInCurrentRow =?, maxBlocksInZForCurrentRow = ?");

        preparedStatement.setInt(1, coords.buildedHouses);
        preparedStatement.setInt(2, coords.maxBuildedHousesInXAxis);
        preparedStatement.setInt(3, coords.startBuildingPosX);
        preparedStatement.setInt(4, coords.startBuildingPosY);
        preparedStatement.setInt(5, coords.startBuildingPosZ);
        preparedStatement.setInt(6, coords.blocksInX);
        preparedStatement.setInt(7, coords.blocksInY);
        preparedStatement.setInt(8, coords.blocksInZ);
        preparedStatement.setInt(9, coords.boundsX);
        preparedStatement.setInt(10, coords.boundsY);
        preparedStatement.setInt(11, coords.boundsZ);
        preparedStatement.setInt(12, coords.nextPosX);
        preparedStatement.setInt(13, coords.nextPosY);
        preparedStatement.setInt(14, coords.nextPosZ);
        preparedStatement.setInt(15, coords.currentRowID);
        preparedStatement.setInt(16, coords.buildedHousesInCurrentRow);
        preparedStatement.setInt(17, coords.maxBlocksInZForCurrentRow);

        preparedStatement.execute();

    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }
}

/**
 * Update the column blockColor in the table tc_coordinates with the color of the block the under construction house is building.
 * @param color the color of the block we need to update
 */
public void updateBlockColor(String color)
{
    try
    {
        statement = connect.createStatement();
        statement.executeUpdate("UPDATE tc_coordinates SET blockColor = " + """ + color + """);
    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }
}

/**
 * Called when the current under construction building finishes and sets the finishedBuilding value to 1 (true)
 */
public void updateFinishedBuildingValue()
{
    try
    {
        int houseID = 0;
        statement = connect.createStatement();
        resultSet = statement.executeQuery("SELECT MAX(houseID) FROM houses_coordinates");
        while(resultSet.next())
        {
            houseID = resultSet.getInt("MAX(houseID)");
        }

        statement.executeUpdate("UPDATE houses_coordinates SET finishedBuilding = 1 WHERE houseID = " + houseID);
    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }
}

/**
 * When a row is finished we add in the houses_rows table
 * @param buildedHousesInCurrentRow how many houses are builded to the row
 * @param maxBlocksInZForCurrentRow what is the maxBlocksInZ for the row
 */
public void addNewRowInHousesRowsTable(int buildedHousesInCurrentRow, int maxBlocksInZForCurrentRow)
{
    try
    {
        preparedStatement = connect.prepareStatement("INSERT INTO houses_rows (buildedHousesInRow, maxBlocksInZ) VALUES (?, ?)");

        preparedStatement.setInt(1, buildedHousesInCurrentRow);
        preparedStatement.setInt(2, maxBlocksInZForCurrentRow);

        preparedStatement.execute();
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }
}

/**
 * Adds a spawned block's coordinates in the spawned_blocks table
 * @param x x coordinate of the block
 * @param y y coordinate of the block
 * @param z z coordinate of the block
 * @param blockColor the block's color
 */
public void addSpawnedBlock(int x, int y, int z, String blockColor)
{
    try
    {
        preparedStatement = connect.prepareStatement("INSERT INTO spawned_blocks (posX, posY, posZ, blockColor) VALUES (?, ?, ?, ?)");

        preparedStatement.setInt(1, x);
        preparedStatement.setInt(2, y);
        preparedStatement.setInt(3, z);
        preparedStatement.setString(4, blockColor);

        preparedStatement.execute();

    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }
}

/**
 * Called when a house finished constructing to add the next's house ,that is going to be builded, coordinates.
 */
public void addNextConstructingHouseInHousesCoordinatesTable()
{
    Coordinates coords = Coordinates.getInstance();

    try
    {
        preparedStatement = connect.prepareStatement("INSERT INTO houses_coordinates (rowID, startBuildingPosX, startBuildingPosY, startBuildingPosZ,"
                                            + " blocksInX, blocksInY, blocksInZ, boundsX, boundsY, boundsZ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        preparedStatement.setInt(1, coords.currentRowID);
        preparedStatement.setInt(2, coords.startBuildingPosX);
        preparedStatement.setInt(3, coords.startBuildingPosY);
        preparedStatement.setInt(4, coords.startBuildingPosZ);
        preparedStatement.setInt(5, coords.blocksInX);
        preparedStatement.setInt(6, coords.blocksInY);
        preparedStatement.setInt(7, coords.blocksInZ);
        preparedStatement.setInt(8, coords.boundsX);
        preparedStatement.setInt(9, coords.boundsY);
        preparedStatement.setInt(10, coords.boundsZ);

        preparedStatement.execute();
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        close();
    }
}

/**
 * Closes the resultSet, statement and connect objects.
 */
public void close() {
  try
  {
      if (resultSet != null)
      {
          resultSet.close();
      }

      if (statement != null)
      {
          statement.close();
      }

      if (connect != null)
      {
          connect.close();
      }
  }
  catch (Exception e)
  {
      System.out.println("There is been a problem while closing the MySQL ResultSet, Statement or Connection!");
  }
}

/**
 * Singleton Pattern so it cannot exist more than one instance of this class.
 * If there is no instance of this class we create one.
 * Every time this method is called we check if the connection is OK and
 * if is not we try to initialize it.
 */
public static DatabaseHandler getInstance ()
{
    if(instance  == null)
    {
        instance = new DatabaseHandler();
    }

    if (connect != null)
    {
        try {
            if(connect.isClosed())
            {
                connect = DriverManager.getConnection(url + dbName + "?user=" + dbUser + "&password=" + dbPassword);
            }
        } catch (SQLException e) {

            //e.printStackTrace();
        }
    }
    else
    {
         try {
            connect = DriverManager.getConnection(url  + dbName + "?user=" + dbUser + "&password=" + dbPassword);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return instance;
}

/**
 * This method issues a lightweight query to the database in order determine if the connection with the database is fine.
 * @return true if the query where succesfully executed to the database
 */
public boolean isConnected()
{
    String PING_MARKER = "/* ping */ SELECT 1";

   /*
    * If (connect != null) is true means we had a connection with MySQL before and we need to CHECK if it
    *  is still OK.
    */
    if(connect != null)
    {
        /*
         * If an excpetion get caught that means the query did not execute.
         * If not, our connection is OK and we return true
         */
        try
        {
                statement = connect.createStatement();
                resultSet = statement.executeQuery(PING_MARKER);
                return true;
        }
        catch(SQLException e)
        {
            return false;
        }
        finally
        {
            close();
        }
    }
    //If (connect == null) means we never had a connection with the MySQL database
    else
    {
        return false;
    }
}
}