""" DIRAC DirectoryTree base class """
from __future__ import absolute_import
from __future__ import division
from __future__ import print_function

__RCSID__ = "$Id$"

import six
import time
import threading
import os
import stat

from DIRAC import S_OK, S_ERROR, gLogger
from DIRAC.DataManagementSystem.DB.FileCatalogComponents.Utilities import getIDSelectString

DEBUG = 0

#############################################################################


class DirectoryTreeBase(object):

  def __init__(self, database=None):
    self.db = database
    self.lock = threading.Lock()
    self.treeTable = ''

############################################################################
#
# THE FOLLOWING METHODS NEED TO ME IMPLEMENTED IN THE DERIVED CLASS
#
############################################################################

  def findDir(self, path, connection=False):
    """  Find directory ID for the given path
    """
    return S_ERROR("To be implemented on derived class")

  def findDirs(self, paths, connection=False):
    """ Find DirIDs for the given path list
    """
    return S_ERROR("To be implemented on derived class")

  def makeDir(self, path):

    return S_ERROR("To be implemented on derived class")

  def removeDir(self, path):

    return S_ERROR("To be implemented on derived class")

  def getChildren(self, path, connection=False):
    return S_ERROR("To be implemented on derived class")

  def getDirectoryPath(self, dirID):
    """ Get directory name by directory ID
    """
    return S_ERROR("To be implemented on derived class")

  def countSubdirectories(self, dirId, includeParent=True):
    return S_ERROR("To be implemented on derived class")

  def getSubdirectoriesByID(self, dirID, requestString=False, includeParent=False):
    """ Get all the subdirectories of the given directory at a given level
    """
    return S_ERROR("To be implemented on derived class")

##########################################################################

  def _getConnection(self, connection):
    if connection:
      return connection
    res = self.db._getConnection()
    if res['OK']:
      return res['Value']
    gLogger.warn("Failed to get MySQL connection", res['Message'])
    return connection

  def getTreeTable(self):
    """ Get the string of the Directory Tree type
    """
    return self.treeTable

  def setDatabase(self, database):
    self.db = database

  def makeDirectory(self, path, credDict, status=0):
    """Create a new directory. The return value is the dictionary
       containing all the parameters of the newly created directory
    """
    if path[0] != '/':
      return S_ERROR('Not an absolute path')
    # Strip off the trailing slash if necessary
    if len(path) > 1 and path[-1] == '/':
      path = path[:-1]

    if path == '/':
      # Create the root directory
      l_uid = 0
      l_gid = 0
    else:
      result = self.db.ugManager.getUserAndGroupID(credDict)
      if not result['OK']:
        return result
      (l_uid, l_gid) = result['Value']

    dirDict = {}
    result = self.makeDir(path)
    if not result['OK']:
      return result
    dirID = result['Value']
    if result['NewDirectory']:
      req = "INSERT INTO FC_DirectoryInfo (DirID,UID,GID,CreationDate,ModificationDate,Mode,Status) Values "
      req = req + "(%d,%d,%d,UTC_TIMESTAMP(),UTC_TIMESTAMP(),%d,%d)" % (dirID, l_uid, l_gid, self.db.umask, status)
      result = self.db._update(req)
      if result['OK']:
        resGet = self.getDirectoryParameters(dirID)
        if resGet['OK']:
          dirDict = resGet['Value']
    else:
      return S_OK(dirID)

    if not dirDict:
      self.removeDir(path)
      return S_ERROR('Failed to create directory %s' % path)
    return S_OK(dirID)

#####################################################################
  def makeDirectories(self, path, credDict):
    """Make all the directories recursively in the path. The return value
       is the dictionary containing all the parameters of the newly created
       directory
    """

    if not path or path[0] != '/':
      return S_ERROR('Not an absolute path')

    result = self.existsDir(path)
    if not result['OK']:
      return result
    result = result['Value']
    if result['Exists']:
      return S_OK(result['DirID'])

    if path == '/':
      result = self.makeDirectory(path, credDict)
      return result

    parentDir = os.path.dirname(path)
    result = self.existsDir(parentDir)
    if not result['OK']:
      return result
    result = result['Value']
    if result['Exists']:
      result = self.makeDirectory(path, credDict)
    else:
      result = self.makeDirectories(parentDir, credDict)
      if not result['OK']:
        return result
      result = self.makeDirectory(path, credDict)

    return result

#####################################################################
  def exists(self, lfns):
    successful = {}
    failed = {}
    for lfn in lfns:
      res = self.findDir(lfn)
      if not res['OK']:
        failed[lfn] = res['Message']
      if not res['Value']:
        successful[lfn] = False
      else:
        successful[lfn] = lfn
    return S_OK({'Successful': successful, 'Failed': failed})

  def existsDir(self, path):
    """ Check the existence of the directory path
    """
    result = self.findDir(path)
    if not result['OK']:
      return result
    if result['Value']:
      result = S_OK(int(result['Value']))
      result['Exists'] = True
      result['DirID'] = result['Value']
    else:
      result = S_OK(0)
      result['Exists'] = False

    return result

  #####################################################################
  def isDirectory(self, paths):
    """ Checking for existence of directories
    """
    successful = {}
    failed = {}
    for dir in paths:
      result = self.existsDir(dir)
      if not result['OK']:
        failed[dir] = result['Message']
      elif result['Value']['Exists']:
        successful[dir] = True
      else:
        successful[dir] = False

    return S_OK({'Successful': successful, 'Failed': failed})

  #####################################################################
  def createDirectory(self, dirs, credDict):
    """ Checking for existence of directories
    """
    successful = {}
    failed = {}
    for dir in dirs:
      result = self.makeDirectories(dir, credDict)
      if not result['OK']:
        failed[dir] = result['Message']
      else:
        successful[dir] = True

    return S_OK({'Successful': successful, 'Failed': failed})

  #####################################################################
  def isEmpty(self, path):
    """ Find out if the given directory is empty
    """
    # Check if there are subdirectories
    result = self.getChildren(path)
    if not result['OK']:
      return result
    childIDs = result['Value']
    if childIDs:
      return S_OK(False)

    # Check if there are files
    result = self.__getDirID(path)
    if not result['OK']:
      return result
    dirID = result['Value']
    result = self.db.fileManager.getFilesInDirectory(dirID)
    if not result['OK']:
      return result
    files = result['Value']
    if files:
      return S_OK(False)

    return S_OK(True)

#####################################################################
  def removeDirectory(self, dirs, force=False):
    """Remove an empty directory from the catalog """
    successful = {}
    failed = {}

    # Check if requested directories exist in the catalog
    result = self.findDirs(dirs)
    if not result['OK']:
      return result
    dirDict = result['Value']
    for d in dirs:
      if d not in dirDict:
        successful[d] = "Directory does not exist"

    for dir in dirDict:
      result = self.isEmpty(dir)
      if not result['OK']:
        return result
      if not result['Value']:
        failed[dir] = 'Failed to remove non-empty directory'
        continue
      result = self.removeDir(dir)
      if not result['OK']:
        failed[dir] = result['Message']
      else:
        successful[dir] = result
    return S_OK({'Successful': successful, 'Failed': failed})

#####################################################################
  def __getDirID(self, path):
    """ Get directory ID from the given path or already evaluated ID
    """

    if isinstance(path, six.string_types):
      result = self.findDir(path)
      if not result['OK']:
        return result
      dirID = result['Value']
      if not dirID:
        return S_ERROR('%s: not found' % str(path))
      return S_OK(dirID)
    else:
      return S_OK(path)

#####################################################################
  def getDirectoryParameters(self, path):
    """ Get the given directory parameters
    """

    result = self.__getDirID(path)
    if not result['OK']:
      return result
    dirID = result['Value']

    query = "SELECT DirID,UID,GID,Status,Mode,CreationDate,ModificationDate from FC_DirectoryInfo"
    query = query + " WHERE DirID=%d" % dirID
    resQuery = self.db._query(query)
    if not resQuery['OK']:
      return resQuery

    if not resQuery['Value']:
      return S_ERROR('Directory not found')
    dirDict = {}
    dirDict['DirID'] = int(resQuery['Value'][0][0])
    uid = int(resQuery['Value'][0][1])
    dirDict['UID'] = uid
    owner = 'unknown'
    result = self.db.ugManager.getUserName(uid)
    if result['OK']:
      owner = result['Value']
    dirDict['Owner'] = owner
    gid = int(resQuery['Value'][0][2])
    dirDict['GID'] = int(resQuery['Value'][0][2])
    group = 'unknown'
    result = self.db.ugManager.getGroupName(gid)
    if result['OK']:
      group = result['Value']
    dirDict['OwnerGroup'] = group
    dirDict['Status'] = int(resQuery['Value'][0][3])
    dirDict['Mode'] = int(resQuery['Value'][0][4])
    dirDict['CreationDate'] = resQuery['Value'][0][5]
    dirDict['ModificationDate'] = resQuery['Value'][0][6]

    return S_OK(dirDict)

#####################################################################
  def _setDirectoryParameter(self, path, pname, pvalue):
    """ Set a numerical directory parameter

        :param mixed path: Directory path or paths as a string or directory ID as int,
                           list/tuple of ints or a string to select directory IDs
        :param str pname: parameter name
        :param int pvalue: parameter value
    """
    result = getIDSelectString(path)
    if not result['OK'] and isinstance(path, six.string_types):
      result = self.__getDirID(path)
      if not result['OK']:
        return result
      dirID = result['Value']
      result = getIDSelectString(dirID)
      if not result['OK']:
        return result

    dirIDString = result['Value']
    req = "UPDATE FC_DirectoryInfo SET %s=%d, " \
          "ModificationDate=UTC_TIMESTAMP() WHERE DirID IN ( %s )" % \
          (pname, pvalue, dirIDString)
    result = self.db._update(req)
    return result

#####################################################################
  def _setDirectoryGroup(self, path, gname):
    """ Set the directory group

        :param mixed path: directory path as a string or int or list of ints or select statement
        :param mixt group: new group as a string or int gid
    """

    result = self.db.ugManager.findGroup(gname)
    if not result['OK']:
      return result

    gid = result['Value']

    return self._setDirectoryParameter(path, 'GID', gid)

#####################################################################
  def _setDirectoryOwner(self, path, owner):
    """ Set the directory owner

        :param mixed path: directory path as a string or int or list of ints or select statement
        :param mixt owner: new user as a string or int uid
    """

    result = self.db.ugManager.findUser(owner)
    if not result['OK']:
      return result

    uid = result['Value']

    return self._setDirectoryParameter(path, 'UID', uid)

#####################################################################
  def changeDirectoryOwner(self, paths, recursive=False):
    """ Bulk setting of the directory owner

        :param dictionary paths: dictionary < lfn : owner >
    """
    return self._changeDirectoryParameter(paths,
                                          self._setDirectoryOwner,
                                          self.db.fileManager.setFileOwner,
                                          recursive=recursive)

#####################################################################
  def changeDirectoryGroup(self, paths, recursive=False):
    """ Bulk setting of the directory group

        :param dictionary paths: dictionary < lfn : group >
    """
    return self._changeDirectoryParameter(paths,
                                          self._setDirectoryGroup,
                                          self.db.fileManager.setFileGroup,
                                          recursive=recursive)

#####################################################################
  def _setDirectoryMode(self, path, mode):
    """ set the directory mode

        :param mixed path: directory path as a string or int or list of ints or select statement
        :param int mode: new mode
    """
    return self._setDirectoryParameter(path, 'Mode', mode)

#####################################################################
  def changeDirectoryMode(self, paths, recursive=False):
    """ Bulk setting of the directory mode

        :param dictionary paths: dictionary < lfn : mode >
    """
    return self._changeDirectoryParameter(paths,
                                          self._setDirectoryMode,
                                          self.db.fileManager.setFileMode,
                                          recursive=recursive)

#####################################################################
  def _changeDirectoryParameter(self, paths,
                                directoryFunction,
                                fileFunction,
                                recursive=False):
    """ Bulk setting of the directory parameter with recursion for all the subdirectories and files

        :param dictionary paths: dictionary < lfn : value >, where value is the value of parameter to be set
        :param function directoryFunction: function to change directory(ies) parameter
        :param function fileFunction: function to change file(s) parameter
        :param bool recursive: flag to apply the operation recursively
    """
    arguments = paths
    successful = {}
    failed = {}
    for path, attribute in arguments.items():
      result = directoryFunction(path, attribute)
      if not result['OK']:
        failed[path] = result['Message']
        continue
      if recursive:
        result = self.__getDirID(path)
        if not result['OK']:
          failed[path] = result['Message']
          continue
        dirID = result['Value']
        result = self.getSubdirectoriesByID(dirID, requestString=True, includeParent=True)
        if not result['OK']:
          failed[path] = result['Message']
          continue

        subDirQuery = result['Value']
        result = self.db.fileManager.getFileIDsInDirectory(subDirQuery, requestString=True)
        if not result['OK']:
          failed[path] = result['Message']
          continue
        fileQuery = result['Value']

        result = directoryFunction(subDirQuery, attribute)
        if not result['OK']:
          failed[path] = result['Message']
          continue
        result = fileFunction(fileQuery, attribute)
        if not result['OK']:
          failed[path] = result['Message']
        else:
          successful[path] = True
      else:
        successful[path] = True

    return S_OK({'Successful': successful, 'Failed': failed})

  #####################################################################
  def setDirectoryStatus(self, path, status):
    """ set the directory status
    """
    return self._setDirectoryParameter(path, 'Status', status)

  def getPathPermissions(self, lfns, credDict):
    """ Get permissions for the given user/group to manipulate the given lfns
    """
    successful = {}
    failed = {}
    for path in lfns:
      result = self.getDirectoryPermissions(path, credDict)
      if not result['OK']:
        failed[path] = result['Message']
      else:
        successful[path] = result['Value']

    return S_OK({'Successful': successful, 'Failed': failed})

  #####################################################################
  def getDirectoryPermissions(self, path, credDict):
    """ Get permissions for the given user/group to manipulate the given directory
    """
    result = self.db.ugManager.getUserAndGroupID(credDict)
    if not result['OK']:
      return result
    uid, gid = result['Value']

    result = self.getDirectoryParameters(path)
    if not result['OK']:
      if "not found" in result['Message'] or "not exist" in result['Message']:
        # If the directory does not exist, check the nearest parent for the permissions
        if path == '/':
          # Nothing yet exists, starting from the scratch
          resultDict = {}
          resultDict['Write'] = True
          resultDict['Read'] = True
          resultDict['Execute'] = True
          return S_OK(resultDict)
        else:
          pDir = os.path.dirname(path)
          if not pDir:
            return S_ERROR('Illegal Path')
          if pDir == path:
            # If pDir == path, then we're stuck in a loop
            # There is probably a "//" in the path
            return S_ERROR('Bad Path (double /?)')
          result = self.getDirectoryPermissions(pDir, credDict)
          return result
      else:
        return result

    dUid = result['Value']['UID']
    dGid = result['Value']['GID']
    mode = result['Value']['Mode']

    owner = uid == dUid
    group = gid == dGid

    resultDict = {}
    if self.db.globalReadAccess:
      resultDict['Read'] = True
    else:
      resultDict['Read'] = (owner and mode & stat.S_IRUSR > 0)\
          or (group and mode & stat.S_IRGRP > 0)\
          or mode & stat.S_IROTH > 0

    resultDict['Write'] = (owner and mode & stat.S_IWUSR > 0)\
        or (group and mode & stat.S_IWGRP > 0)\
        or mode & stat.S_IWOTH > 0

    resultDict['Execute'] = (owner and mode & stat.S_IXUSR > 0)\
        or (group and mode & stat.S_IXGRP > 0)\
        or mode & stat.S_IXOTH > 0

    return S_OK(resultDict)

  def getFileIDsInDirectoryWithLimits(self, dirID, credDict, startItem=1, maxItems=25):
    """ Get file IDs for the given directory
    """
    dirs = dirID
    if not isinstance(dirID, list):
      dirs = [dirID]

    if not dirs:
      dirs = [-1]

    dirListString = ','.join([str(dir) for dir in dirs])

    req = "SELECT COUNT( DirID ) FROM FC_Files USE INDEX (DirID) WHERE DirID IN ( %s )" % dirListString
    result = self.db._query(req)
    if not result['OK']:
      return result

    totalRecords = result['Value'][0][0]

    if not totalRecords:
      result = S_OK([])
      result['TotalRecords'] = totalRecords
      return result

    req = "SELECT FileID FROM FC_Files WHERE DirID IN ( %s ) LIMIT %s, %s " % (dirListString, startItem, maxItems)
    result = self.db._query(req)
    if not result['OK']:
      return result
    result = S_OK([fileId[0] for fileId in result['Value']])
    result['TotalRecords'] = totalRecords
    return result

  def getFileLFNsInDirectory(self, dirID, credDict):
    """ Get file lfns for the given directory or directory list
    """
    dirs = dirID
    if not isinstance(dirID, list):
      dirs = [dirID]

    dirListString = ','.join([str(dir) for dir in dirs])
    treeTable = self.getTreeTable()
    req = "SELECT CONCAT(D.DirName,'/',F.FileName) FROM FC_Files as F, %s as D WHERE D.DirID IN ( %s ) and D.DirID=F.DirID"
    req = req % (treeTable, dirListString)
    result = self.db._query(req)
    if not result['OK']:
      return result
    lfnList = [x[0] for x in result['Value']]
    return S_OK(lfnList)

  def getFileLFNsInDirectoryByDirectory(self, dirIDList, credDict):
    """ Get file LFNs and IDs for the given directory or directory list

        :param list dirIDList: List of directory IDs
        :param dict credDict: dictionary of user credentials

        :return: S_OK/S_ERROR with Value dictionary {"DirLFNDict": dirLfnDict, "IDLFNDict": idLfnDict}
                 where dirLfnDict has the structure <directory_name>:<list of contained file names>,
                 idLfnDict has structure <fileID>:<LFN>
    """
    dirs = dirIDList
    if not isinstance(dirIDList, list):
      dirs = [dirIDList]

    dirListString = ','.join([str(dir_) for dir_ in dirs])
    treeTable = self.getTreeTable()
    req = "SELECT D.DirName,F.FileName,F.FileID FROM FC_Files as F, %s as D WHERE D.DirID IN ( %s ) and D.DirID=F.DirID"
    req = req % (treeTable, dirListString)
    result = self.db._query(req)
    if not result['OK']:
      return result

    dirLfnDict = {}
    idLfnDict = {}
    for dir_, fname, fileID in result['Value']:
      dirLfnDict.setdefault(dir_, []).append(fname)
      idLfnDict[fileID] = dir_ + '/' + fname

    return S_OK({"DirLFNDict": dirLfnDict,
                 "IDLFNDict": idLfnDict})

  def _getDirectoryContents(self, path, details=False):
    """ Get contents of a given directory
    """
    result = self.findDir(path)
    if not result['OK']:
      return result
    directoryID = result['Value']
    directories = {}
    files = {}
    links = {}
    result = self.getChildren(path)
    if not result['OK']:
      return result

    # Get subdirectories
    dirIDList = result['Value']
    for dirID in dirIDList:
      result = self.getDirectoryPath(dirID)
      if not result['OK']:
        return result
      dirName = result['Value']
      if details:
        result = self.getDirectoryParameters(dirID)
        if not result['OK']:
          directories[dirName] = False
        else:
          directories[dirName] = result['Value']
      else:
        directories[dirName] = True
    result = self.db.fileManager.getFilesInDirectory(directoryID, verbose=details)
    if not result['OK']:
      return result
    files = result['Value']
    result = self.db.datasetManager.getDatasetsInDirectory(directoryID, verbose=details)
    if not result['OK']:
      return result
    datasets = result['Value']
    pathDict = {'Files': files, 'SubDirs': directories, 'Links': links, 'Datasets': datasets}

    return S_OK(pathDict)

  def listDirectory(self, lfns, verbose=False):
    """ Get the directory listing
    """
    successful = {}
    failed = {}
    for path in lfns:
      result = self._getDirectoryContents(path, details=verbose)
      if not result['OK']:
        failed[path] = result['Message']
      else:
        successful[path] = result['Value']

    return S_OK({'Successful': successful, 'Failed': failed})

  def getDirectoryReplicas(self, lfns, allStatus=False):
    """ Get replicas for files in the given directories
    """
    successful = {}
    failed = {}
    for path in lfns:
      result = self.findDir(path)
      if not result['OK']:
        failed[path] = result['Message']
        continue
      directoryID = result['Value']
      result = self.db.fileManager.getDirectoryReplicas(directoryID, path, allStatus)
      if not result['OK']:
        failed[path] = result['Message']
        continue
      fileDict = result['Value']
      successful[path] = {}
      for fileName in fileDict:
        successful[path][fileName] = fileDict[fileName]

    return S_OK({'Successful': successful, 'Failed': failed})

  def getDirectorySize(self, lfns, longOutput=False, rawFileTables=False, recursiveSum=True):
    """
        Get the total size of the requested directories. If longOutput flag
        is True, get also physical size per Storage Element

        :param bool longOutput: if True, also fetches the physical size per SE
        :param bool rawFileTables: if True, uses the File table instead of the pre-computed values
        :param bool recursiveSum: if True (default), takes into account subdirectories

    """
    start = time.time()

    result = self.db._getConnection()
    if not result['OK']:
      return result
    connection = result['Value']

    if rawFileTables:
      resultLogical = self._getDirectoryLogicalSize(lfns, recursiveSum=recursiveSum, connection=connection)
    else:
      resultLogical = self._getDirectoryLogicalSizeFromUsage(lfns, recursiveSum=recursiveSum, connection=connection)

    if not resultLogical['OK']:
      connection.close()
      return resultLogical

    resultDict = resultLogical['Value']
    if not resultDict['Successful']:
      connection.close()
      return resultLogical

    if longOutput:
      # Continue with only successful directories
      if rawFileTables:
        resultPhysical = self._getDirectoryPhysicalSize(
            resultDict['Successful'], recursiveSum=recursiveSum, connection=connection)
      else:
        resultPhysical = self._getDirectoryPhysicalSizeFromUsage(
            resultDict['Successful'], recursiveSum=recursiveSum, connection=connection)
      if not resultPhysical['OK']:
        resultDict['QueryTime'] = time.time() - start
        result = S_OK(resultDict)
        result['Message'] = "Failed to get the physical size on storage"
        connection.close()
        return result
      for lfn in resultPhysical['Value']['Successful']:
        resultDict['Successful'][lfn]['PhysicalSize'] = resultPhysical['Value']['Successful'][lfn]
    connection.close()
    resultDict['QueryTime'] = time.time() - start

    return S_OK(resultDict)

  def _getDirectoryLogicalSizeFromUsage(self, lfns, recursiveSum=True, connection=None):
    """ Get the total "logical" size of the requested directories

        :param recursiveSum: If false, don't take subdir into account
    """

    if not recursiveSum:
      return S_ERROR("Not implemented")

    successful = {}
    failed = {}
    for path in lfns:
      result = self.findDir(path)
      if not result['OK']:
        failed[path] = "Directory not found"
        continue
      if not result['Value']:
        failed[path] = "Directory not found"
        continue
      dirID = result['Value']
      req = "SELECT SESize, SEFiles FROM FC_DirectoryUsage WHERE SEID=0 AND DirID=%d" % dirID

      result = self.db._query(req, connection)
      if not result['OK']:
        failed[path] = result['Message']
      elif not result['Value']:
        successful[path] = {"LogicalSize": 0, "LogicalFiles": 0, 'LogicalDirectories': 0}
      elif result['Value'][0][0]:
        successful[path] = {"LogicalSize": int(result['Value'][0][0]),
                            "LogicalFiles": int(result['Value'][0][1])}
        result = self.countSubdirectories(dirID, includeParent=False)
        if result['OK']:
          successful[path]['LogicalDirectories'] = result['Value']
        else:
          successful[path]['LogicalDirectories'] = -1

      else:
        successful[path] = {"LogicalSize": 0, "LogicalFiles": 0, 'LogicalDirectories': 0}

    return S_OK({'Successful': successful, 'Failed': failed})

  def _getDirectoryLogicalSize(self, lfns, recursiveSum=True, connection=None):
    """ Get the total "logical" size of the requested directories

        :param bool recursiveSum: If false, don't take subdir into account
    """

    if not recursiveSum:
      return S_ERROR("Not implemented")

    successful = {}
    failed = {}
    treeTable = self.getTreeTable()
    for path in lfns:

      if path == "/":
        req = "SELECT SUM(Size),COUNT(*) FROM FC_Files"
        reqDir = "SELECT count(*) FROM %s" % treeTable
      else:
        result = self.findDir(path)
        if not result['OK']:
          failed[path] = "Directory not found"
          continue
        if not result['Value']:
          failed[path] = "Directory not found"
          continue
        dirID = result['Value']
        result = self.getSubdirectoriesByID(dirID, requestString=True, includeParent=True)
        if not result['OK']:
          failed[path] = result['Message']
          continue
        else:
          dirString = result['Value']
          req = "SELECT SUM(F.Size),COUNT(*) FROM FC_Files as F JOIN (%s) as T WHERE F.DirID=T.DirID" % dirString
          reqDir = dirString.replace('SELECT DirID FROM', 'SELECT count(*) FROM')

      result = self.db._query(req, connection)
      if not result['OK']:
        failed[path] = result['Message']
      elif not result['Value']:
        successful[path] = {"LogicalSize": 0, "LogicalFiles": 0, 'LogicalDirectories': 0}
      elif result['Value'][0][0]:
        successful[path] = {"LogicalSize": int(result['Value'][0][0]),
                            "LogicalFiles": int(result['Value'][0][1])}
        result = self.db._query(reqDir, connection)
        if result['OK'] and result['Value']:
          successful[path]['LogicalDirectories'] = result['Value'][0][0] - 1
        else:
          successful[path]['LogicalDirectories'] = -1

      else:
        successful[path] = {"LogicalSize": 0, "LogicalFiles": 0, 'LogicalDirectories': 0}

    return S_OK({'Successful': successful, 'Failed': failed})

  def _getDirectoryPhysicalSizeFromUsage(self, lfns, recursiveSum=True, connection=None):
    """ Get the total size of the requested directories

        :param recursiveSum: If false, don't take subdir into account
    """

    if not recursiveSum:
      return S_ERROR("Not implemented")

    successful = {}
    failed = {}
    for path in lfns:
      result = self.findDir(path)
      if not result['OK']:
        failed[path] = "Directory not found"
        continue
      if not result['Value']:
        failed[path] = "Directory not found"
        continue
      dirID = result['Value']

      req = "SELECT S.SEID, S.SEName, D.SESize, D.SEFiles FROM FC_DirectoryUsage as D, FC_StorageElements as S"
      req += "  WHERE S.SEID=D.SEID AND D.DirID=%d" % dirID
      result = self.db._query(req, connection)
      if not result['OK']:
        failed[path] = result['Message']
      elif not result['Value']:
        successful[path] = {}
      elif result['Value'][0][0]:
        seDict = {}
        totalSize = 0
        totalFiles = 0
        for seID, seName, seSize, seFiles in result['Value']:
          if seSize or seFiles:
            seDict[seName] = {'Size': seSize, 'Files': seFiles}
            totalSize += seSize
            totalFiles += seFiles
          else:
            req = 'DELETE FROM FC_DirectoryUsage WHERE SEID=%d AND DirID=%d' % (seID, dirID)
            result = self.db._update(req)
            if not result['OK']:
              gLogger.error('Failed to delete entry from FC_DirectoryUsage', result['Message'])
        seDict['TotalSize'] = int(totalSize)
        seDict['TotalFiles'] = int(totalFiles)
        successful[path] = seDict
      else:
        successful[path] = {}

    return S_OK({'Successful': successful, 'Failed': failed})

  def _getDirectoryPhysicalSizeFromUsage_old(self, lfns, connection):
    """ Get the total size of the requested directories
    """
    successful = {}
    failed = {}
    for path in lfns:

      if path == '/':
        req = "SELECT S.SEName, D.SESize, D.SEFiles FROM FC_DirectoryUsage as D, FC_StorageElements as S"
        req += "  WHERE S.SEID=D.SEID"
      else:
        result = self.findDir(path)
        if not result['OK']:
          failed[path] = "Directory not found"
          continue
        if not result['Value']:
          failed[path] = "Directory not found"
          continue
        dirID = result['Value']
        result = self.getSubdirectoriesByID(dirID, requestString=True, includeParent=True)
        if not result['OK']:
          return result
        subDirString = result['Value']
        req = "SELECT S.SEName, D.SESize, D.SEFiles FROM FC_DirectoryUsage as D, FC_StorageElements as S"
        req += " JOIN (%s) AS F" % subDirString
        req += " WHERE S.SEID=D.SEID AND D.DirID=F.DirID"

      result = self.db._query(req, connection)
      if not result['OK']:
        failed[path] = result['Message']
      elif not result['Value']:
        successful[path] = {}
      elif result['Value'][0][0]:
        seDict = {}
        totalSize = 0
        totalFiles = 0
        for seName, seSize, seFiles in result['Value']:
          sfDict = seDict.get(seName, {'Size': 0, 'Files': 0})
          sfDict['Size'] += seSize
          sfDict['Files'] += seFiles
          seDict[seName] = sfDict
          totalSize += seSize
          totalFiles += seFiles
        seDict['TotalSize'] = int(totalSize)
        seDict['TotalFiles'] = int(totalFiles)
        successful[path] = seDict
      else:
        successful[path] = {}

    return S_OK({'Successful': successful, 'Failed': failed})

  def _getDirectoryPhysicalSize(self, lfns, recursiveSum=True, connection=None):
    """ Get the total size of the requested directories
        :param recursiveSum: If false, don't take subdir into account
    """
    if not recursiveSum:
      return S_ERROR("Not implemented")

    successful = {}
    failed = {}
    for path in lfns:
      if path == '/':
        req = "SELECT SUM(F.Size),COUNT(F.Size),S.SEName from FC_Files as F, FC_Replicas as R, FC_StorageElements as S "
        req += "WHERE R.SEID=S.SEID AND F.FileID=R.FileID "
        req += "GROUP BY S.SEID"
      else:
        result = self.findDir(path)
        if not result['OK']:
          failed[path] = "Directory not found"
          continue
        if not result['Value']:
          failed[path] = "Directory not found"
          continue
        dirID = result['Value']
        result = self.getSubdirectoriesByID(dirID, requestString=True, includeParent=True)
        if not result['OK']:
          failed[path] = result['Message']
          continue
        else:
          dirString = result['Value']

          req = "SELECT SUM(F.Size),COUNT(F.Size),S.SEName from FC_Files as F, FC_Replicas as R, FC_StorageElements as S JOIN (%s) as T " % dirString
          req += "WHERE R.SEID=S.SEID AND F.FileID=R.FileID AND F.DirID=T.DirID "
          req += "GROUP BY S.SEID"

      result = self.db._query(req, connection)
      if not result['OK']:
        failed[path] = result['Message']
      elif not result['Value']:
        successful[path] = {}
      elif result['Value'][0][0]:
        seDict = {}
        totalSize = 0
        totalFiles = 0
        for size, files, seName in result['Value']:
          seDict[seName] = {"Size": int(size), "Files": int(files)}
          totalSize += size
          totalFiles += files
        seDict['TotalSize'] = int(totalSize)
        seDict['TotalFiles'] = int(totalFiles)
        successful[path] = seDict
      else:
        successful[path] = {}

    return S_OK({'Successful': successful, 'Failed': failed})

  def _rebuildDirectoryUsage(self):
    """ Recreate and replenish the Storage Usage tables
    """

    req = "DROP TABLE IF EXISTS FC_DirectoryUsage_backup"
    result = self.db._update(req)
    req = "RENAME TABLE FC_DirectoryUsage TO FC_DirectoryUsage_backup"
    result = self.db._update(req)
    req = "CREATE TABLE `FC_DirectoryUsage` LIKE `FC_DirectoryUsage_backup`"
    result = self.db._update(req)
    if not result['OK']:
      return result

    result = self.__rebuildDirectoryUsageLeaves()
    if not result['OK']:
      return result

    result = self.db.dtree.findDir('/')
    if not result['OK']:
      return result
    if not result['Value']:
      return S_ERROR('Directory / not found')
    dirID = result['Value']
    result = self.__rebuildDirectoryUsage(dirID)
    gLogger.verbose('Finished rebuilding Directory Usage')
    return result

  def __rebuildDirectoryUsageLeaves(self):
    """ Rebuild DirectoryUsage entries for directories having files
    """
    req = 'SELECT DISTINCT(DirID) FROM FC_Files'
    result = self.db._query(req)
    if not result['OK']:
      return result

    dirIDs = [x[0] for x in result['Value']]
    gLogger.verbose('Starting rebuilding Directory Usage, number of visible directories %d' % len(dirIDs))

    insertFields = ['DirID', 'SEID', 'SESize', 'SEFiles', 'LastUpdate']
    insertCount = 0
    insertValues = []

    count = 0
    empty = 0

    for dirID in dirIDs:

      count += 1

      # Get the physical size
      req = "SELECT SUM(F.Size),COUNT(F.Size),R.SEID from FC_Files as F, FC_Replicas as R "
      req += "WHERE F.FileID=R.FileID AND F.DirID=%d GROUP BY R.SEID" % int(dirID)
      result = self.db._query(req)
      if not result['OK']:
        return result
      if not result['Value']:
        empty += 1

      for seSize, seFiles, seID in result['Value']:
        insertValues = [dirID, seID, seSize, seFiles, 'UTC_TIMESTAMP()']
        result = self.db.insertFields('FC_DirectoryUsage', insertFields, insertValues)
        if not result['OK']:
          if "Duplicate" in result['Message']:
            req = "UPDATE FC_DirectoryUsage SET SESize=%d, SEFiles=%d, LastUpdate=UTC_TIMESTAMP()" % (seSize, seFiles)
            req += " WHERE DirID=%s AND SEID=%s" % (dirID, seID)
            result = self.db._update(req)
            if not result['OK']:
              return result
          return result

      # Get the logical size
      req = "SELECT SUM(Size),COUNT(Size) from FC_Files WHERE DirID=%d " % int(dirID)
      result = self.db._query(req)
      if not result['OK']:
        return result
      if not result['Value']:
        return S_ERROR('Empty directory')
      seSize, seFiles = result['Value'][0]
      insertValues = [dirID, 0, seSize, seFiles, 'UTC_TIMESTAMP()']
      result = self.db.insertFields('FC_DirectoryUsage', insertFields, insertValues)
      if not result['OK']:
        if "Duplicate" in result['Message']:
          req = "UPDATE FC_DirectoryUsage SET SESize=%d, SEFiles=%d, LastUpdate=UTC_TIMESTAMP()" % (seSize, seFiles)
          req += " WHERE DirID=%s AND SEID=0" % dirID
          result = self.db._update(req)
          if not result['OK']:
            return result
        else:
          return result

    gLogger.verbose("Processed %d directories, %d empty " % (count, empty))

    return S_OK()

  def __rebuildDirectoryUsage(self, directoryID):
    """ Rebuild DirectoryUsage entries recursively for the given path
    """
    result = self.getChildren(directoryID)
    if not result['OK']:
      return result
    dirIDs = result['Value']
    resultDict = {}
    for dirID in dirIDs:
      result = self.__rebuildDirectoryUsage(dirID)
      if not result['OK']:
        return result
      dirDict = result['Value']
      for seID in dirDict:
        resultDict.setdefault(seID, {'Size': 0, 'Files': 0})
        resultDict[seID]['Size'] += dirDict[seID]['Size']
        resultDict[seID]['Files'] += dirDict[seID]['Files']

    insertFields = ['DirID', 'SEID', 'SESize', 'SEFiles', 'LastUpdate']
    insertValues = []
    for seID in resultDict:
      size = resultDict[seID]['Size']
      files = resultDict[seID]['Files']
      req = "UPDATE FC_DirectoryUsage SET SESize=SESize+%d, SEFiles=SEFiles+%d WHERE DirID=%d AND SEID=%d"
      req = req % (size, files, directoryID, seID)
      result = self.db._update(req)
      if not result['OK']:
        return result
      if not result['Value']:
        insertValues = [directoryID, seID, size, files, 'UTC_TIMESTAMP()']
        result = self.db.insertFields('FC_DirectoryUsage', insertFields, insertValues)
        if not result['OK']:
          return result

    req = "SELECT SEID,SESize,SEFiles from FC_DirectoryUsage WHERE DirID=%d" % directoryID
    result = self.db._query(req)
    if not result['OK']:
      return result

    resultDict = {}
    for seid, size, files in result['Value']:
      resultDict[seid] = {'Size': size, 'Files': files}

    return S_OK(resultDict)

  def getDirectoryCounters(self, connection=False):
    """ Get the total number of directories
    """
    conn = self._getConnection(connection)
    resultDict = {}
    req = "SELECT COUNT(*) from FC_DirectoryInfo"
    res = self.db._query(req, connection)
    if not res['OK']:
      return res
    resultDict['Directories'] = res['Value'][0][0]

    treeTable = self.getTreeTable()

    req = "SELECT COUNT(DirID) FROM %s WHERE Parent NOT IN ( SELECT DirID from %s )" % (treeTable, treeTable)
    req += " AND DirID <> 1"
    res = self.db._query(req, connection)
    if not res['OK']:
      return res
    resultDict['Orphan Directories'] = res['Value'][0][0]

    req = "SELECT COUNT(DirID) FROM %s WHERE DirID NOT IN ( SELECT Parent from %s )" % (treeTable, treeTable)
    req += " AND DirID NOT IN ( SELECT DirID from FC_Files ) "
    res = self.db._query(req, connection)
    if not res['OK']:
      return res
    resultDict['Empty Directories'] = res['Value'][0][0]

    req = "SELECT COUNT(DirID) FROM %s WHERE DirID NOT IN ( SELECT DirID FROM FC_DirectoryInfo )" % treeTable
    res = self.db._query(req, connection)
    if not res['OK']:
      return res
    resultDict['DirTree w/o DirInfo'] = res['Value'][0][0]

    req = "SELECT COUNT(DirID) FROM FC_DirectoryInfo WHERE DirID NOT IN ( SELECT DirID FROM %s )" % treeTable
    res = self.db._query(req, connection)
    if not res['OK']:
      return res
    resultDict['DirInfo w/o DirTree'] = res['Value'][0][0]

    return S_OK(resultDict)
