Imports System.Configuration
Imports GPSConnector.Common
Imports System.Data.SqlClient
Imports System.Linq

Public Class Database
    Implements IDatabase
    Private ReadOnly _connectionString As String
    Private _deviceList As List(Of GPSDevice)
    Private _ownerList As Dictionary(Of String, Owner)
    Private ReadOnly _allowAllDevices As Boolean

    Public Sub New(connectionStringName As String)
        _connectionString = ConfigurationManager.ConnectionStrings(connectionStringName).ConnectionString
        _allowAllDevices = ConfigurationManager.AppSettings("AllowAllDevices") = "true"
    End Sub
    Public Function GetDeviceList(ByVal ownerKey As String) As List(Of GPSDevice) Implements IDatabase.GetDeviceList
        If _deviceList Is Nothing Then GetDeviceListInternal()
        If _ownerList Is Nothing Then GetOwners()
        If Not _ownerList.ContainsKey(ownerKey) Then Return Nothing
        Dim owner As Owner = _ownerList(ownerKey)
        Return If(owner.IsAdmin, _deviceList, _deviceList.Where(Function(d) d.OwnerId = owner.ID).ToList())
    End Function

    Public Function IsDeviceRegistered(imei As String, deviceType As String) As Boolean Implements IDatabase.IsDeviceRegistered
        If _allowAllDevices Then Return True
        Return GetDevice(imei, deviceType) IsNot Nothing
    End Function

    Public Sub SetGPSDeviceDataId(ByVal gpsDeviceData As GPSDeviceData, deviceType As String) Implements IDatabase.SetGPSDeviceDataId
        Dim gpsDevice As GPSDevice = GetDevice(gpsDeviceData.Imei, deviceType)
        gpsDeviceData.Id = gpsDevice.Id
    End Sub

    Public Function GetOwner(ByVal ownerKey As String) As Owner Implements IDatabase.GetOwner
        If _ownerList Is Nothing Then GetOwners()
        If Not _ownerList.ContainsKey(ownerKey) Then
            Return Nothing
        End If
        Return _ownerList(ownerKey)
    End Function

    Public Function GetDeviceCount(deviceType As String) As Integer Implements IDatabase.GetDeviceCount
        If _deviceList Is Nothing Then GetDeviceListInternal()
        Return _deviceList.Where(Function(d) d.DeviceType.ToLower() = deviceType.ToLower()).Count()
    End Function

    Public Sub GetOwnerCalcDistributors(owner As Owner) Implements IDatabase.GetOwnerCalcDistributors
        owner.CalcDistributors = New List(Of Distributor)()

        If Not owner.DistributorId.HasValue AndAlso Not owner.IsAdmin Then
            Return
        End If
        Dim sqlConnection As New SqlConnection(_connectionString)

        Try
            sqlConnection.Open()
            Using cmd As New SqlCommand()
                With cmd
                    .Connection = sqlConnection
                    .CommandText = "dbo.sp_distributor_get_calc_distributors"
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.AddWithValue("@distributor_id", If(owner.DistributorId.HasValue, owner.DistributorId.Value, DBNull.Value))
                End With
                Using reader = cmd.ExecuteReader()
                    If Not reader Is Nothing AndAlso reader.HasRows Then
                        Dim indId = reader.GetOrdinal("Id")
                        Dim indName = reader.GetOrdinal("Name")
                        While reader.Read()
                            Dim distributor As New Distributor
                            With distributor
                                .Id = reader.GetInt32(indId)
                                .Name = reader.GetString(indName)
                            End With
                            owner.CalcDistributors.Add(distributor)
                        End While
                    End If
                End Using
            End Using
        Finally
            sqlConnection.Close()
            sqlConnection.Dispose()

        End Try
    End Sub

    Public Function GetPicoTrackDeployments(ByVal imei As String) As List(Of PicoTrackConfigDeployment) Implements IDatabase.GetPicoTrackDeployments
        Dim deployments As New List(Of PicoTrackConfigDeployment)
        Dim sqlConnection As New SqlConnection(_connectionString)

        Try
            sqlConnection.Open()
                Using cmd As New SqlCommand()
                    With cmd
                        .Connection = sqlConnection
                        .CommandText = "dbo.sp_gps_device_configuration_deployment_sel"
                        .CommandType = CommandType.StoredProcedure
                        .Parameters.AddWithValue("@imei", imei)
                    End With
                    Using reader = cmd.ExecuteReader()
                        If Not reader Is Nothing AndAlso reader.HasRows Then
                            Dim indId As Integer = reader.GetOrdinal("id")
                            Dim indInput As Integer = reader.GetOrdinal("input")
                            Dim i As Integer = 0
                            While reader.Read()
                                Dim deployment As New PicoTrackConfigDeployment
                                i += 1
                                With deployment
                                    .Id = reader.GetInt32(indId)
                                    .RawInput = reader.GetString(indInput)
                                    .MgsId = i
                                End With
                                deployments.Add(deployment)
                            End While
                        End If
                    End Using
                End Using
        Catch ex As Exception
            deployments.Clear()
        Finally
            sqlConnection.Close()
            sqlConnection.Dispose()
        End Try

        Return deployments
    End Function

    Public Sub ChangeDeploymentState(ByVal deploymentId As Integer, ByVal status As Integer, ByVal response As String) Implements IDatabase.ChangeDeploymentState
        Dim sqlConnection As New SqlConnection(_connectionString)
        Try
            sqlConnection.Open()
            Using cmd As New SqlCommand()
                With cmd
                    .Connection = sqlConnection
                    .CommandText = "dbo.sp_gps_device_configuration_deployment_change_state"
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.AddWithValue("@id", deploymentId)
                    .Parameters.AddWithValue("@status", status)
                    .Parameters.AddWithValue("@device_response", response)
                    .ExecuteNonQuery()
                End With
            End Using
        Finally
            sqlConnection.Close()
            sqlConnection.Dispose()
        End Try
    End Sub

    Public Sub ResetDeploymentState(ByVal imei As String) Implements IDatabase.ResetDeploymentState
        Dim sqlConnection As New SqlConnection(_connectionString)
        Try
            sqlConnection.Open()
            Using cmd As New SqlCommand()
                With cmd
                    .Connection = sqlConnection
                    .CommandText = "dbo.sp_gps_device_configuration_deployment_reset_state"
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.AddWithValue("@imei", imei)
                    .ExecuteNonQuery()
                End With
            End Using
        Finally
            sqlConnection.Close()
            sqlConnection.Dispose()
        End Try

    End Sub

    Public Sub ResetAllDeploymentState() Implements IDatabase.ResetAllDeploymentState
        Dim sqlConnection As New SqlConnection(_connectionString)

        Try
            sqlConnection.Open()
            Using cmd As New SqlCommand()
                With cmd
                    .Connection = sqlConnection
                    .CommandText = "dbo.sp_gps_device_configuration_deployment_reset_state"
                    .CommandType = CommandType.StoredProcedure
                    .ExecuteNonQuery()
                End With
            End Using
        Finally
            sqlConnection.Close()
            sqlConnection.Dispose()

        End Try

    End Sub

    Public Function IsDeviceOwnedBy(ByVal id As Integer, ByVal ownerId As Integer) As Boolean Implements IDatabase.IsDeviceOwnedBy
        If _deviceList Is Nothing Then GetDeviceListInternal()
        Return _deviceList.Exists(Function(d) d.OwnerId = ownerId)
    End Function

    Private Function GetDevice(imei As String, deviceType As String) As GPSDevice
        If _deviceList Is Nothing Then GetDeviceListInternal()
        Return _deviceList.FirstOrDefault(Function(gd) (gd.Imei.Length > 6 AndAlso gd.Imei = imei) OrElse (deviceType.ToLower() = gd.DeviceType.ToLower() AndAlso Right(gd.Imei, 6) = imei))
    End Function

    Private Sub GetOwners()
        _ownerList = New Dictionary(Of String, Owner)()
        Using sqlConnection As New SqlConnection(_connectionString)
            sqlConnection.Open()
            Using cmd As New SqlCommand()
                With cmd
                    .Connection = sqlConnection
                    .CommandText = "dbo.sp_gps_device_owner_get_all"
                    .CommandType = CommandType.StoredProcedure
                End With
                Using reader = cmd.ExecuteReader()
                    If Not reader Is Nothing AndAlso reader.HasRows Then
                        Dim indId = reader.GetOrdinal("Id")
                        Dim indOwnerName = reader.GetOrdinal("owner_name")
                        Dim indOwnerKey = reader.GetOrdinal("owner_key")
                        Dim indIsAdmin = reader.GetOrdinal("is_admin")
                        Dim indIsClient = reader.GetOrdinal("is_client")
                        Dim indDistributorId = reader.GetOrdinal("distributor_id")
                        While reader.Read()
                            Dim owner As New Owner
                            With owner
                                .ID = reader.GetInt32(indId)
                                .Name = reader.GetString(indOwnerName)
                                .IsAdmin = Not reader.IsDBNull(indIsAdmin) AndAlso reader.GetBoolean(indIsAdmin)
                                .IsClient = Not reader.IsDBNull(indIsClient) AndAlso reader.GetBoolean(indIsClient)
                                .Key = reader.GetString(indOwnerKey)
                            End With
                            If Not reader.IsDBNull(indDistributorId) Then
                                owner.DistributorId = reader.GetInt32(indDistributorId)
                            End If
                            _ownerList.Add(owner.Key, owner)
                        End While
                    End If
                End Using

            End Using
        End Using
    End Sub

    Private Sub GetDeviceListInternal()
        _deviceList = New List(Of GPSDevice)()
        Using conn As New SqlConnection(_connectionString)
            conn.Open()
            Using cmd As New SqlCommand()
                With cmd
                    .Connection = conn
                    .CommandText = "dbo.sp_gps_device_get_all"
                    .CommandType = CommandType.StoredProcedure
                End With
                Using reader = cmd.ExecuteReader()
                    If Not reader Is Nothing AndAlso reader.HasRows Then
                        Dim indId = reader.GetOrdinal("Id")
                        Dim indOwner = reader.GetOrdinal("owner_name")
                        Dim indModel = reader.GetOrdinal("device_model")
                        Dim indDeviceType = reader.GetOrdinal("device_type")
                        Dim indImei As Integer = reader.GetOrdinal("imei")
                        Dim indSerialNumber As Integer = reader.GetOrdinal("serial_number")
                        Dim indDeviceName As Integer = reader.GetOrdinal("name")
                        Dim indOwnerId As Integer = reader.GetOrdinal("gps_owner_id")
                        While reader.Read()
                            Dim gpsDevice As New GPSDevice
                            With gpsDevice
                                .Active = True
                                .DeviceType = If(reader.IsDBNull(indDeviceType), String.Empty, reader.GetString(indDeviceType))
                                .Id = reader.GetInt32(indId)
                                .Owner = If(reader.IsDBNull(indOwner), String.Empty, reader.GetString(indOwner))
                                .Model = If(reader.IsDBNull(indModel), String.Empty, reader.GetString(indModel))
                                .Imei = If(reader.IsDBNull(indImei), String.Empty, reader.GetString(indImei))
                                .SerialNumber = If(reader.IsDBNull(indSerialNumber), String.Empty, reader.GetString(indSerialNumber))
                                .DeviceName = If(reader.IsDBNull(indDeviceName), String.Empty, reader.GetString(indDeviceName))
                                .OwnerId = reader.GetInt32(indOwnerId)
                            End With
                            _deviceList.Add(gpsDevice)
                        End While
                    End If
                End Using
            End Using
        End Using
    End Sub
End Class
