""" UserProfileDB class is a front-end to the User Profile Database
"""

__RCSID__ = "$Id$"

import types
import os
import sys
import hashlib

from DIRAC  import S_OK, S_ERROR, gLogger, gConfig
from DIRAC.Core.Utilities import Time
from DIRAC.ConfigurationSystem.Client.Helpers import Registry
from DIRAC.Core.Base.DB import DB

class UserProfileDB( DB ):
  """ UserProfileDB class is a front-end to the User Profile Database
  """

  tableDict = { 'up_Users' : { 'Fields' : { 'Id' : 'INTEGER AUTO_INCREMENT NOT NULL',
                                            'UserName' : 'VARCHAR(32) NOT NULL',
                                            'LastAccess' : 'DATETIME',
                                            },
                               'PrimaryKey' : 'Id',
                               'UniqueIndexes' : { 'U' : [ 'UserName' ] },
                               'Engine': 'InnoDB',
                               },
                'up_Groups': { 'Fields' : { 'Id' : 'INTEGER AUTO_INCREMENT NOT NULL',
                                            'UserGroup' : 'VARCHAR(32) NOT NULL',
                                            'LastAccess' : 'DATETIME',
                                            },
                               'PrimaryKey' : 'Id',
                               'UniqueIndexes' : { 'G' : [ 'UserGroup' ] },
                               'Engine': 'InnoDB',
                               },
                'up_VOs': { 'Fields' : { 'Id' : 'INTEGER AUTO_INCREMENT NOT NULL',
                                         'VO' : 'VARCHAR(32) NOT NULL',
                                         'LastAccess' : 'DATETIME',
                                         },
                            'PrimaryKey' : 'Id',
                            'UniqueIndexes' : { 'VO' : [ 'VO' ] },
                            'Engine': 'InnoDB',
                            },
                'up_ProfilesData': { 'Fields' : { 'UserId' : 'INTEGER',
                                                  'GroupId' : 'INTEGER',
                                                  'VOId' : 'INTEGER',
                                                  'Profile' : 'VARCHAR(255) NOT NULL',
                                                  'VarName' : 'VARCHAR(255) NOT NULL',
                                                  'Data' : 'BLOB',
                                                  'ReadAccess' : 'VARCHAR(10) DEFAULT "USER"',
                                                  'PublishAccess' : 'VARCHAR(10) DEFAULT "USER"',
                                                  },
                                     'PrimaryKey' : [ 'UserId', 'GroupId', 'Profile', 'VarName' ],
                                     'Indexes' : { 'ProfileKey' : [ 'UserId', 'GroupId', 'Profile' ],
                                                   'UserKey' : [ 'UserId' ] ,
                                                   },
                                     'Engine': 'InnoDB',
                                     },
                'up_HashTags': { 'Fields' : { 'UserId' : 'INTEGER',
                                              'GroupId' : 'INTEGER',
                                              'VOId' : 'INTEGER',
                                              'HashTag' : 'VARCHAR(32) NOT NULL',
                                              'TagName' : 'VARCHAR(255) NOT NULL',
                                              'LastAccess' : 'DATETIME',
                                              },
                                 'PrimaryKey' : [ 'UserId', 'GroupId', 'TagName' ],
                                 'Indexes' : { 'HashKey' : [ 'UserId', 'HashTag' ] },
                                 'Engine': 'InnoDB',
                                 },
               }


  def __init__( self ):
    """ Cons
tructor
    """
    self.__permValues = [ 'USER', 'GROUP', 'VO', 'ALL' ]
    self.__permAttrs = [ 'ReadA
ccess', 'PublishAccess' ]
    DB.__init__( self, 'UserProfileDB', 'Framework/UserProfileDB', 10 )
    retVal = self.__initializeDB()
    if not retVal[ 'OK' ]:
      raise Exception( "Can't create tables: %s" % retVal[ 'Message' ] )

  def _checkTable( self ):
    """ Make sure the tables are created
    """
    return self.__initializeDB()

  def __initializeDB( self ):
    """
    Create the tables
    """
    retVal = self._query( "show tables" )
    if not retVal[ 'OK' ]:
      return retVal

    tablesInDB = [ t[0] for t in retVal[ 'Value' ] ]
    tablesD = {}

    if 'up_Users' not in tablesInDB:
      tablesD[ 'up_Users' ] = self.tableDict['up_Users']

    if 'up_Groups' not in tablesInDB:
      tablesD[ 'up_Groups' ] = self.tableDict[ 'up_Groups']

    if 'up_VOs' not in tablesInDB:
      tablesD[ 'up_VOs' ] = self.tableDict['up_VOs']

    if 'up_ProfilesData' not in tablesInDB:
      tablesD[ 'up_ProfilesData' ] = self.tableDict['up_ProfilesData']

    if 'up_HashTags' not in tablesInDB:
      tablesD[ 'up_HashTags' ] = self.tableDict['up_HashTags']

    return self._createTables( tablesD )

  def __getUserId( self, userName, insertIfMissing = True ):
    return self.__getObjId( userName, 'UserName', 'up_Users', insertIfMissing )

  def __getGroupId( self, groupName, insertIfMissing = True ):
    return self.__getObjId( groupName, 'UserGroup', 'up_Groups', insertIfMissing )

  def __getVOId( self, voName, insertIfMissing = True ):
    return self.__getObjId( voName, 'VO', 'up_VOs', insertIfMissing )

  def __getObjId( self, objValue, varName, tableName, insertIfMissing = True ):
    result = self.getFields( tableName, ['Id'], { varName: objValue } )
    if not result[ 'OK' ]:
      return result
    data = result[ 'Value' ]
    if len( data ) > 0:
      objId = data[0][0]
      self.updateFields( tableName, ['LastAccess'], ['UTC_TIMESTAMP()'], { 'Id': objId } )
      return S_OK( objId )
    if not insertIfMissing:
      return S_ERROR( "No entry %s for %s defined in the DB" % ( objValue, varName ) )
    result = self.insertFields( tableName, [ varName, 'LastAccess' ], [ objValue, 'UTC_TIMESTAMP()' ] )
    if not result[ 'OK' ]:
      return result
    return S_OK( result[ 'lastRowId' ] )

  def getUserGroupIds( self, userName, userGroup, insertIfMissing = True ):
    result = self.__getUserId( userName, insertIfMissing )
    if not result[ 'OK' ]:
      return result
    userId = result[ 'Value' ]
    result = self.__getGroupId( userGroup, insertIfMissing )
    if not result[ 'OK' ]:
      return result
    groupId = result[ 'Value' ]
    userVO = Registry.getVOForGroup( userGroup )
    if not userVO:
      userVO = "undefined"
    result = self.__getVOId( userVO, insertIfMissing )
    if not result[ 'OK' ]:
      return result
    voId = result[ 'Value' ]
    return S_OK( ( userId, groupId, voId ) )

  def deleteUserProfile( self, userName, userGroup = False ):
    """
    Delete the profiles for a user
    """
    result = self.__getUserId( userName )
    if not result[ 'OK' ]:
      return result
    userId = result[ 'Value' ]
    condDict = { 'UserId': userId }
    if userGroup:
      result = self.__getGroupId( userGroup )
      if not result[ 'OK' ]:
        return result
      groupId = result[ 'Value' ]
      condDict['GroupId'] = groupId
    result = self.deleteEntries( 'up_ProfilesData', condDict )
    if not result[ 'OK' ] or not userGroup:
      return result
    return self.deleteEntries( 'up_Users', { 'Id': userId } )

  def __webProfileUserDataCond( self, userIds, sqlProfileName = False, sqlVarName = False ):
    condSQL = [ '`up_ProfilesData`.UserId=%s' % userIds[0],
                '`up_ProfilesData`.GroupId=%s' % userIds[1],
                '`up_ProfilesData`.VOId=%s' % userIds[2] ]
    if sqlProfileName:
      condSQL.append( '`up_ProfilesData`.Profile=%s' % sqlProfileName )
    if sqlVarName:
      condSQL.append( '`up_ProfilesData`.VarName=%s' % sqlVarName )
    return " AND ".join( condSQL )

  def __webProfileReadAccessDataCond( self, userIds, ownerIds, sqlProfileName, sqlVarName = False, match = False ):
    permCondSQL = []
    sqlCond = []
    
    if match:
      sqlCond.append( '`up_ProfilesData`.UserId = %s AND `up_ProfilesData`.Gro