Imports System.Data.SqlClient
Imports ProductiePitching.ServiceAgents
Imports ProductiePitching.ServiceAgents.Implementations

Module EmployeesDatabaseMapper
    Private _sqlConnection As SqlConnection

#Region "Help functions"

    Private ReadOnly _activiteitPerBatchServiceAgent As IActiviteitPerBatchServiceAgent

    Sub New()
        _activiteitPerBatchServiceAgent = New ActiviteitPerBatchServiceAgent
    End Sub

    ''' <summary>
    ''' Execution of a query into a data reader.
    ''' </summary>
    ''' <param name="query">The query</param>
    ''' <returns>The SQL data reader</returns>
    Private Function ExecuteIntoDataSet() As DataSet

        Try
            Return _activiteitPerBatchServiceAgent.GeefWerknemers()
        Catch ex As Exception
            Throw New Exception($"Could not get dataset from the Caché webservice.{vbNewLine}{ex.Message}")
        End Try
    End Function

    ''' <summary>
    ''' Tries to extract a value from a datareader with a key.
    ''' If the value is not present, but not required, a default value van be specified.
    ''' If the value is not present, but required, an exception will be thrown.
    ''' The value will be converted to the specifief type.
    ''' </summary>
    ''' <typeparam name="T">The target type of the value</typeparam>
    ''' <param name="dr">The datareader</param>
    ''' <param name="key">The key, identification of the value</param>
    ''' <param name="isRequired">Whether or not the value is required</param>
    ''' <param name="defaultValue">Optional default value of the value. Standard 'Nothing' or the default value of the type</param>
    ''' <returns>The value converted to the type of <typeparamref name="T"/></returns>
    Private Function ExtractItemFromDataReader(Of T)(ByRef dr As DataTableReader, key As String, isRequired As Boolean, Optional defaultValue As T = Nothing) As T
        Dim value As Object
        Try
            'Extract the value with the key. Column will always be present.
            value = dr.Item(key)
            'Check if the value is DBNull or Nothing.
            If IsDBNull(value) Or value Is Nothing Then
                'If so, check if value was required.
                If isRequired Then
                    'Value was required.
                    Throw New Exception($"A required value with key '{key}' was empty.")
                End If
                'Value was not required, return default value.
                Return defaultValue
            End If
        Catch ex As Exception
            Throw New Exception($"A value for a work item can't be fetched from the data reader. The key is '{key}'.")
        End Try
        Try
            'Do the conversion explicitly, so exceptions can be caught.
            Return CType(value, T)
        Catch ex As Exception
            Throw New Exception($"A value for a work item can't be converted to the specified type. The key is '{key}', the type is {GetType(T).Name}, the value is {value}.")
        End Try
    End Function

#End Region

    ''' <summary>
    ''' Fetches the employees.
    ''' </summary>
    ''' <returns>A collection of employees</returns>
    Public Function FetchEmployees() As ICollection(Of IEmployee)
        'Define the query.

        'Initialise the list with employees.
        Dim employees As New List(Of IEmployee)

        'Declare SQL data reader.
        Dim dataReader As DataTableReader = Nothing

        Try
            'Fetch the data reader with the query.
            Dim CacheDataSet As DataSet = ExecuteIntoDataSet()
            dataReader = CacheDataSet.CreateDataReader()

            While dataReader.Read()
                'Initialise new Employee.
                Dim employee As New Employee
                With employee
                    .PersonelNumber = ExtractItemFromDataReader(Of Integer)(dataReader, "PersNr", True)
                    .Picture = ExtractItemFromDataReader(Of String)(dataReader, "Foto", True, String.Empty)
                    .FirstName = ExtractItemFromDataReader(Of String)(dataReader, "Voornaam", False, String.Empty)
                    .LastName = ExtractItemFromDataReader(Of String)(dataReader, "Achternaam", False, String.Empty)
                    .Initials = ExtractItemFromDataReader(Of String)(dataReader, "Initialen", True)
                End With

                'Add new employee to list.
                employees.Add(employee)
            End While
            'Return the employees.
            Return employees
        Catch ex As Exception
            Throw New Exception($"An error occured while fetching the employees.{vbNewLine}{ex.Message}")
        Finally
            'Close connection if datareader is not nothing.
            If Not IsNothing(dataReader) Then
                dataReader.Close()
            End If
        End Try
    End Function
End Module