Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports DigitalData.Modules.Base
Imports DigitalData.Modules.Database
Imports DigitalData.Modules.Interfaces.PropertyValues
Imports DigitalData.Modules.Logging

Namespace ZUGFeRD
    Public Class EmailFunctions
        Private ReadOnly _logConfig As LogConfig
        Private ReadOnly _logger As Logger
        Private ReadOnly _mssql As MSSQLServer

        Public Sub New(LogConfig As LogConfig, MSSQL As MSSQLServer)
            _logConfig = LogConfig
            _logger = _logConfig.GetLogger()
            _mssql = MSSQL
        End Sub

        ''' <summary>
        ''' Method to decide wether we use the old or the new
        ''' Rejection E-mail method.
        ''' 
        ''' TODO we have no information about the language of the receiver at the moment
        ''' </summary>
        ''' <param name="pMessageId">E-Mail Message ID</param>
        ''' <param name="pTransaction">DB Transaction</param>
        ''' <param name="pBodyText">Body Text</param>
        ''' <param name="pEmailData">Email Data object</param>
        ''' <param name="pSourceProcedure">Exception Title</param>
        ''' <param name="pEmailAccountId">Sending Profile from config</param>
        ''' <param name="pNamePortal">Name of the Portal from config</param>
        ''' <param name="pTemplateId">ID for E-Mail-Template from config</param>
        ''' <param name="pErrorCode">Error Code</param>
        ''' <param name="pParameter1">Zusätzlicher Parameter 1</param>
        ''' <param name="pParameter2">Zusätzlicher Parameter 2</param>
        Public Sub AddToEmailQueueMSSQL(pMessageId As String, pTransaction As SqlTransaction, pBodyText As String, pEmailData As EmailData, pSourceProcedure As String,
                                        pEmailAccountId As Integer, pNamePortal As String, pTemplateId As Integer, pErrorCode As ErrorCode,
                                        pParameter1 As String, pParameter2 As String)

            Dim useLegacyMethod = True
            Dim oErrorCode As String = String.Empty

            ' ErrorCode valid?
            If pErrorCode <> ErrorCode.Unknown Then
                Dim intCode As Integer = DirectCast(pErrorCode, Integer)
                oErrorCode = $"{EmailStrings.ErrorCodePraefix}{intCode}"

                Dim oSQL = $"SELECT COUNT(*) FROM TBDD_GUI_LANGUAGE_PHRASE WHERE TITLE = '{oErrorCode}'"
                If _mssql.GetScalarValue(oSQL) > 0 Then
                    useLegacyMethod = False
                Else
                    _logger.Warn($"Rejection reason [{oErrorCode}] not found in TBDD_GUI_LANGUAGE_PHRASE!")
                End If
            End If

            ' Gibt es das Template in TBDD_EMAIL_TEMPLATE?
            If useLegacyMethod = False AndAlso pTemplateId > 0 Then
                Try
                    Dim oSQL = $"SELECT COUNT(*) FROM TBDD_EMAIL_TEMPLATE WHERE GUID = {pTemplateId}"
                    If _mssql.GetScalarValue(oSQL) <= 0 Then
                        _logger.Warn($"EMAIL_TEMPLATE [{pTemplateId}] not found in TBDD_EMAIL_TEMPLATE!")
                        useLegacyMethod = True
                    End If
                Catch ex As Exception
                    _logger.Error(ex)
                    useLegacyMethod = True
                End Try

            Else
                _logger.Debug($"RejectionTemplateId not configured!")
                useLegacyMethod = True
            End If

            ' Check if Stored Procedure PRDD_SEND_REJECTION_MAIL exists
            If useLegacyMethod = False Then
                Try
                    Dim oSQL = $"SELECT COUNT(*) FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.PRDD_SEND_REJECTION_MAIL')"
                    If _mssql.GetScalarValue(oSQL) <= 0 Then
                        _logger.Warn($"Procedure ['PRDD_SEND_REJECTION_MAIL'] not found in Database!")
                        useLegacyMethod = True
                    End If
                Catch ex As Exception
                    _logger.Error(ex)
                    useLegacyMethod = True
                End Try
            End If

            If useLegacyMethod = True Then
                _logger.Warn("New rejection mail logic is not configured correctly, use legacy logic instead!")
                AddToEmailQueueMSSQL(pMessageId, pBodyText, pEmailData, pSourceProcedure, pEmailAccountId, pNamePortal)
            Else
                _logger.Debug("New rejection mail logic is configured!")
                AddToEmailQueueMSSQL(pMessageId, pTransaction, pTemplateId, oErrorCode, pEmailAccountId, pParameter1, pParameter2)
            End If

        End Sub


        ''' <summary>
        ''' Function calls SP PRDD_SEND_REJECTION_MAIL
        ''' for sending rejection mail.
        ''' </summary>
        ''' <param name="pMessageId">E-Mail Message ID</param>
        ''' <param name="pTransaction">DB Transaction</param>
        ''' <param name="pTemplateId">GUID for TBDD_EMAIL_TEMPLATE from config</param>
        ''' <param name="pErrorCode">ErrorID (TBDD_GUI_LANGUAGE_PHRASE)</param>
        ''' <param name="pEmailAccountId">Sending profile from config</param>
        ''' <param name="pParameter1">Zusätzlicher Parameter 1</param>
        ''' <param name="pParameter2">Zusätzlicher Parameter 2</param>
        Private Sub AddToEmailQueueMSSQL(pMessageId As String, pTransaction As SqlTransaction, pTemplateId As Integer, pErrorCode As String, pEmailAccountId As Integer,
                                         pParameter1 As String, pParameter2 As String)

            If pParameter1.IsNullOrEmpty Then
                pParameter1 = ""
            Else
                pParameter1 = pParameter1.Replace("'", "''")
            End If

            If pParameter2.IsNullOrEmpty Then
                pParameter2 = ""
            Else
                pParameter2 = pParameter2.Replace("'", "''")
            End If

            Try
                Dim oExecute = $"EXECUTE dbo.PRDD_SEND_REJECTION_MAIL
                                    '{pMessageId}'
                                    , 0
                                    , {pEmailAccountId}
                                    , 'ZUGFeRD Service'
                                    , {pTemplateId}
                                    , '{pErrorCode}'
                                    , '{pParameter1}'
                                    , '{pParameter2}'
                                    , 77"

                _mssql.ExecuteNonQuery(oExecute, pTransaction)

            Catch ex As Exception
                _logger.Error(ex)
            End Try

        End Sub

        Private Sub AddToEmailQueueMSSQL(MessageId As String, BodyText As String, pEmailData As EmailData, SourceProcedure As String, pEmailAccountId As Integer, NamePortal As String)
            If pEmailData Is Nothing Then
                _logger.Warn("EmailData is empty. Email will not be sent!")
                Exit Sub
            End If

            Try
                Dim oJobId = RandomValue(1, 10000)
                Dim oReference = MessageId
                Dim oEmailTo = ""
                Dim oSubject = EmailStrings.EMAIL_SUBJECT_REJECTED.Replace(EmailStrings.constNAME_ZUGFERD_PORTAL, NamePortal)
                Dim oCreatedWho = "ZUGFeRD Service"

                Dim oMaskedBodyText = BodyText.Replace("'", "''")
                Dim oSubjectBodyText = String.Format(EmailStrings.EMAIL_SUBJECT_TEXT.Replace(EmailStrings.constNAME_ZUGFERD_PORTAL, NamePortal), pEmailData.Subject).Replace("'", "''")
                Dim oCompleteBodyText = oMaskedBodyText & oSubjectBodyText

                Dim oFinalBodyText = String.Format(EmailStrings.EMAIL_WRAPPING_TEXT.Replace(EmailStrings.constNAME_ZUGFERD_PORTAL, NamePortal), oCompleteBodyText)

                Dim oEmailAddress = pEmailData.From
                Dim oAttachmentPath = pEmailData.Attachment
                If oAttachmentPath <> String.Empty Then
                    _logger.Debug($"Attachment_String [{oAttachmentPath}]!")
                    If IO.File.Exists(oAttachmentPath) = False Then
                        _logger.Info($"Attachment.File [{oAttachmentPath}] is not existing!!!")
                    End If
                End If
                Dim oAttachmentPathEscaped = oAttachmentPath.Replace("'", "''")

                If IsNothing(oEmailAddress) OrElse String.IsNullOrWhiteSpace(oEmailAddress) Then
                    _logger.Warn("Could not find email-address for MessageId {0}", MessageId)
                    oEmailTo = String.Empty
                Else
                    oEmailTo = oEmailAddress
                End If

                _logger.Debug("Generated Email:")
                _logger.Debug("To: {0}", oEmailTo)
                _logger.Debug("Subject: {0}", oSubject)
                _logger.Debug("Body {0}", oFinalBodyText)
                Dim osql = $"Select MAX(GUID) FROM TBEMLP_HISTORY WHERE EMAIL_MSGID = '{MessageId}'"
                Dim oHistoryID = _mssql.GetScalarValue(osql)

                If IsNumeric(oHistoryID) Then
                    Dim oInsert = $"INSERT INTO [dbo].[TBEMLP_EMAIL_OUT] 
                       ([REMINDER_TYPE_ID]
                       ,[SENDING_PROFILE]
                       ,[REFERENCE_ID]
                       ,[REFERENCE_STRING]
                       ,[WF_ID]
                       ,[EMAIL_ADRESS]
                       ,[EMAIL_SUBJ]
                       ,[EMAIL_BODY]
                       ,[COMMENT]
                       ,[ADDED_WHO]
                        ,EMAIL_ATTMT1)
                 VALUES
                       (77
                       ,{pEmailAccountId}
                       ,{oHistoryID}
                       ,'{MessageId}'
                       ,77
                       ,'{oEmailTo}'
                       ,'{oSubject}'
                       ,'{oFinalBodyText}'
                       ,'{SourceProcedure}'
                       ,'{oCreatedWho}'
                       ,'{oAttachmentPathEscaped}')"
                    _mssql.ExecuteNonQuery(oInsert)
                Else
                    _logger.Warn("Could not get oHistoryID in AddToEmailQueueMSSQL!!")
                End If
            Catch ex As Exception
                _logger.Error(ex)
            End Try
        End Sub

        Public Function GetEmailDataForMessageId(MessageId As String) As EmailData
            Dim oSQL = $"SELECT EMAIL_FROM, EMAIL_SUBJECT FROM TBEMLP_HISTORY WHERE EMAIL_MSGID = '{MessageId}'"
            Try
                Dim oDatatable = _mssql.GetDatatable(oSQL)
                Dim oRow As DataRow

                If oDatatable.Rows.Count = 0 Then
                    _logger.Warn("Got no results for MessageId {0}", MessageId)
                    Return Nothing
                ElseIf oDatatable.Rows.Count > 1 Then
                    _logger.Warn("Got too many results for MessageId {0}. Using last row.", MessageId)
                End If

                _logger.Debug("Got Email Data for FileId {0}", MessageId)
                oRow = oDatatable.Rows.Item(oDatatable.Rows.Count - 1)

                Dim oFromDefault = String.Format("No Sender found for ({0})", MessageId)
                Dim oFrom = oRow.ItemEx("EMAIL_FROM", oFromDefault)

                Dim oSubjectDefault = String.Format("No Subject found for ({0})", MessageId)
                Dim oSubject = oRow.ItemEx("EMAIL_SUBJECT", oSubjectDefault)

                _logger.Info("Email Data for [{0}] fetched!", MessageId)
                Return New EmailData() With {
                    .From = oFrom,
                    .Subject = oSubject
                }

            Catch ex As Exception
                _logger.Warn("Could not fetch Email Data for FileId {0}", MessageId)
                Return Nothing
            End Try
        End Function

        Public Function GetOriginalEmailPath(OriginalEmailDirectory As String, MessageId As String) As String
            Dim oAttachmentFile = MessageId & ".eml"
            Dim oAttachmentPath = Path.Combine(OriginalEmailDirectory, oAttachmentFile)

            If File.Exists(oAttachmentPath) Then
                Return oAttachmentPath
            Else
                _logger.Warn("Email File {0} does not exist. Empty String will be returned.", oAttachmentPath)
                Return String.Empty
            End If
        End Function

        Private Function RandomValue(lowerBound As Integer, upperBound As Integer) As Integer
            Dim oRandomValue = CInt(Math.Floor((upperBound - lowerBound + 1) * Rnd())) + lowerBound
            Return oRandomValue
        End Function

        Public Function CreateBodyForMissingProperties(OriginalFilename As String, MissingProperties As List(Of MissingProperty)) As String
            Dim oBody = String.Format(EmailStrings.EMAIL_MISSINGPROPERTIES_1, OriginalFilename)

            If MissingProperties.Count > 0 Then
                oBody &= $"{vbNewLine}{vbNewLine}"
                oBody &= EmailStrings.EMAIL_MISSINGPROPERTIES_2
                oBody &= $"{vbNewLine}{vbNewLine}"

                For Each prop In MissingProperties
                    oBody &= $"- {prop.Description}"
                Next
            End If

            Return oBody
        End Function

        Public Function CreateBodyForUnhandledException(MessageId As String, Exception As Exception) As String
            Dim oBody = String.Format(EmailStrings.EMAIL_UNHANDLED_EXCEPTION, MessageId, Exception.Message, Exception.StackTrace)

            Return oBody
        End Function
    End Class
End Namespace
