Imports DigitalData.Modules.Database
Imports DigitalData.Modules.Database.MSSQLServer
Imports DigitalData.Modules.Language
Imports DigitalData.Modules.Logging
Imports System.Data.SqlClient
Imports DigitalData.Modules.ZooFlow.State
Imports DigitalData.Modules.Patterns
Imports DigitalData.Services.EDMIService.Methods.IDB
Imports DigitalData.Modules.Filesystem

Namespace IDB
    Public Class Helpers
        Inherits BaseClass

        Private ReadOnly Patterns As Patterns2
        Private ReadOnly Database As MSSQLServer
        Private ReadOnly FileEx As File

        Public Sub New(pLogConfig As LogConfig, pMSSQLServer As MSSQLServer)
            MyBase.New(pLogConfig)
            Patterns = New Patterns2(pLogConfig)
            Database = pMSSQLServer
            FileEx = New File(pLogConfig)
        End Sub

        Public Function GetPlaceholderValue(pValue As String,
                                            pFileInfo As IO.FileInfo,
                                            pUserState As UserState,
                                            pUserAttributes As Dictionary(Of String, List(Of String)),
                                            pAutoAttributes As Dictionary(Of String, List(Of String))) As String
            Dim oResult As String = pValue

            oResult = Patterns.ReplaceInternalValues(oResult)
            oResult = Patterns.ReplaceFileValues(oResult, pFileInfo)
            oResult = Patterns.ReplaceUserValues(oResult, pUserState)
            oResult = Patterns.ReplaceGlobixValues(oResult, pUserAttributes, pAutoAttributes)

            Return oResult
        End Function

        Public Function UserAttributesToDictionary(pUserAttributes As List(Of UserAttributeValue)) As Dictionary(Of String, List(Of String))
            If pUserAttributes Is Nothing OrElse pUserAttributes.Count = 0 Then
                Return New Dictionary(Of String, List(Of String))
            End If

            Return pUserAttributes.ToDictionary(
               Function(attr) attr.Name,
               Function(attr) attr.Values)
        End Function

        Public Function TestObjectIdExists(pObjectId As Long, Optional ByRef IsDeleted As Boolean = False, Optional ByRef IsActive As Boolean = False) As Boolean
            Try
                Dim oSQL As String = $"SELECT IDB_OBJ_ID, ACTIVE, DELETED FROM TBIDB_OBJECT WHERE IDB_OBJ_ID = {pObjectId}"
                Dim oTable As DataTable = Database.GetDatatable(oSQL)

                If IsNothing(oTable) OrElse oTable.Rows.Count = 0 Then
                    Logger.Warn("ObjectId {0} does not exist")
                    Return False
                End If

                Dim oRow As DataRow = oTable.Rows.Item(0)
                Dim oActive = Utils.NotNull(oRow.Item("ACTIVE"), False)
                Dim oDeleted = Utils.NotNull(oRow.Item("DELETED"), False)

                IsDeleted = oDeleted
                IsActive = oActive

                Return True

            Catch ex As Exception
                Logger.Error(ex)
                Return False

            End Try
        End Function

        Public Function TestFileChecksumExists(pChecksum As String) As Long
            Try
                Logger.Debug("Checking for Checksum [{0}]..", pChecksum)

                Dim oChecksumSQL = $"SELECT IDB_OBJ_ID FROM TBIDB_FILE_OBJECT WHERE FILE_HASH = '{pChecksum}'"
                Dim oExistingObjectId As Long = Database.GetScalarValue(oChecksumSQL)
                Dim oChecksumExists = (oExistingObjectId > 0)

                If oChecksumExists AndAlso TestFileDeleted(oExistingObjectId) Then
                    Logger.Info("Checksum exists on Object [{0}] but the Object was deleted", oExistingObjectId)
                    Return 0
                End If

                If Not oChecksumExists Then
                    Logger.Debug("Checksum does not exist.")
                    Return 0
                End If

                Logger.Info("Returning with ObjectId [{0}] because Checksum [{1}] already exists.", oExistingObjectId, pChecksum)
                Return oExistingObjectId

            Catch ex As Exception
                Logger.Error(ex)
                Return 0

            End Try
        End Function

        Public Function TestFileDeleted(pObjectId As Long) As Boolean
            Try
                Logger.Debug("Checking if Object [{0}] has been deleted", pObjectId)

                Dim oObjectSQL = $"SELECT IDB_OBJ_ID FROM TBIDB_OBJECT WHERE IDB_OBJ_ID = {pObjectId} AND DELETED = 1"
                Dim oDeletedObjectId As Long = Database.GetScalarValue(oObjectSQL)

                Return (oDeletedObjectId > 0)
            Catch ex As Exception
                Logger.Error(ex)
                Return False
            End Try
        End Function

        Public Function NewObjectId(pKindType As String, pBusinessEntity As String, pWho As String) As Long
            Try
                Dim oNewObjectIdSQL = $"DECLARE @NEW_IDB_OBJ_ID BIGINT
                            EXEC PRIDB_NEW_OBJECT '{pKindType}','{pWho}','{pBusinessEntity}',0, @IDB_OBJ_ID = @NEW_IDB_OBJ_ID OUTPUT;
                            SELECT @NEW_IDB_OBJ_ID"
                Dim oObjectId As Long = Database.GetScalarValue(oNewObjectIdSQL)

                Logger.Info("New Object with Id [{0}] created!", oObjectId)

                If IsNothing(oObjectId) Then
                    LogAndThrow("Could not create new ObjectId!")
                End If

                Return oObjectId
            Catch ex As Exception
                Logger.Error(ex)
                Return Nothing
            End Try
        End Function

        Public Function NewObjectIdWithTransaction(pKindType As String, pWho As String, pConnection As SqlConnection, pTransaction As SqlTransaction) As Long
            Try
                Dim oNewObjectIdSQL = $"DECLARE @NEW_IDB_OBJ_ID BIGINT
                            EXEC PRIDB_NEW_OBJECT '{pKindType}','{pWho}',0, 0, @IDB_OBJ_ID = @NEW_IDB_OBJ_ID OUTPUT;
                            SELECT @NEW_IDB_OBJ_ID"
                Dim oObjectId As Long = Database.GetScalarValueWithConnectionObject(oNewObjectIdSQL,
                                                                                    pConnection,
                                                                                    TransactionMode.ExternalTransaction,
                                                                                    pTransaction)

                Logger.Info("New Object with Id [{0}] created!", oObjectId)

                If IsNothing(oObjectId) Then
                    LogAndThrow("Could not create new ObjectId!")
                End If

                Return oObjectId
            Catch ex As Exception
                Logger.Error(ex)
                Return Nothing
            End Try
        End Function

        Public Function GetFileObjectPath(pStore As GlobalState.ObjectStore, pDateImported As Date) As String
            ' Get directory by DateImported or, if not supplied, by current date
            Dim oFinalPath As String
            If IsNothing(pDateImported) Then
                oFinalPath = FileEx.CreateDateDirectory(pStore.Path, Now)
            Else
                oFinalPath = FileEx.CreateDateDirectory(pStore.Path, pDateImported)
            End If

            Return oFinalPath
        End Function

        Public Function SetAttributeValueWithTransaction(pConnection As SqlConnection, pTransaction As SqlTransaction, pObjectId As Long, pAttributeName As String, pValue As String, pLanguage As String, pWho As String) As Boolean
            Logger.Info("Setting value of Attribute [{0}]", pAttributeName)
            Dim oSql = $"
                DECLARE @NEW_OBJ_MD_ID BIGINT
                EXEC PRIDB_NEW_OBJ_DATA {pObjectId}, '{pAttributeName}', '{pWho}', '{pValue}', '{pLanguage}', 0, @OMD_ID = @NEW_OBJ_MD_ID OUTPUT"

            If Database.ExecuteNonQueryWithConnectionObject(oSql, pConnection, MSSQLServer.TransactionMode.ExternalTransaction, pTransaction) = False Then
                Logger.Warn("Error while setting attribute value.")
                Return False
            End If

            Return True
        End Function

        Public Sub SetAttributeValuesWithTransaction(pConnection As SqlConnection, pTransaction As SqlTransaction, pObjectId As Long, pAttributes As Dictionary(Of String, List(Of String)), pLanguage As String, pWho As String)
            For Each oAttribute As KeyValuePair(Of String, List(Of String)) In pAttributes
                Try
                    ' TODO: This works only for simple attributes for now!!!
                    Dim oValue = oAttribute.Value.FirstOrDefault()

                    ' Dont write empty attributes
                    If oValue Is Nothing Then
                        Continue For
                    End If

                    ' Now make the call to the database
                    Dim oSuccess = SetAttributeValueWithTransaction(pConnection, pTransaction, pObjectId, oAttribute.Key, oValue, pLanguage, pWho)
                    If oSuccess Then
                        Logger.Info("Attribute written [{0}] => [{1}]", oAttribute.Key, oAttribute.Value.First())
                    Else
                        Logger.Warn("Attribute value could not be written")
                    End If
                Catch ex As Exception
                    LogAndThrow(ex, $"Attribute [{oAttribute.Key}] could not be written!")
                End Try
            Next
        End Sub

        Public Function NewDynamicFolderForObject(pObjectId As Long, pFolder As String, pWho As String, pLangID As Integer, pLangCode As String) As Boolean

            Dim oSql = $"
                DECLARE @NEW_OBJ_MD_ID BIGINT
                EXEC PRIDB_NEW_OBJ_DATA {pObjectId}, 'VIRTUAL_FOLDER', '{pWho}', '{pFolder}', '{pLangCode}', 0, @OMD_ID = @NEW_OBJ_MD_ID OUTPUT"
            If Database.ExecuteNonQuery(oSql) = True Then
                Logger.Debug("...Dynamic Folder [{0}] successfully saved!", pFolder)
            End If
            oSql = $"
                DECLARE @LAST_SN_ID BIGINT
                EXEC PRIDB_NEW_DYNAMIC_FOLDER '{pFolder}','{pWho}',1,@LAST_SN_ID OUTPUT
                SELECT @LAST_SN_ID"
            Dim oNodeID As Integer
            oNodeID = Database.GetScalarValue(oSql)
            If IsNothing(oNodeID) = False Then
                Logger.Debug("...Nodes successffully created!")
                oSql = $"DECLARE @LAST_SN_ID BIGINT
                        EXEC PRIDB_NEW_STUCTURE_NODE_FOR_OBJECT {pObjectId},{oNodeID},'{pWho}',{pLangID}, @LAST_SN_ID OUTPUT"
                If Database.ExecuteNonQuery(oSql) = False Then
                    Logger.Warn("Error while setting Dynamic Folder.")
                    Return False
                Else
                    Logger.Info("DynamicFolder and Nodes successffully created!")
                End If
            End If
            Return True
        End Function

        Public Function SetObjectState(pObjectId As Long, pState As String, pWho As String) As Boolean
            Try
                Logger.Debug("Setting object state for [{0}]: [{1}]")

                Dim oSql As String = $"EXEC PRIDB_OBJECT_SET_STATE {pObjectId}, '{pState}', '{pWho}'"
                Dim oResult = Database.ExecuteNonQuery(oSql)
                If oResult = False Then
                    Return False
                End If

                Logger.Info("Object state for [{0}] set: [{1}]", pObjectId, pState)
                Return True

            Catch ex As Exception
                Logger.Warn("Error while setting Object State")
                Logger.Error(ex)
                Return False

            End Try
        End Function
    End Class

End Namespace