Imports System.Configuration
Imports System.Globalization
Imports System.Data.SQLite
Imports System.IO
Imports System.Data.SqlClient
Imports Opc.Ua
Imports Opc.Ua.Client
Imports Opc.Ua.Configuration
Imports System
Imports System.Threading
Imports System.Threading.Tasks
Imports System.Collections.Concurrent


Public Class Form1
    Dim AppSettings = ConfigurationManager.AppSettings
    Dim ConnDB As New SqlConnection(ConfigurationManager.ConnectionStrings("DBSQL_Path").ConnectionString)
    Private opcApp As ApplicationInstance
    Private session As Session
    Private endpointUrl As String = "opc.tcp://" & AppSettings("OPCUA_IP") & ":" & AppSettings("OPCUA_PORT")

    'OPC UA NodeIds - Dati ricevuti dal PLC verso l'applicazione
    Private OCV_DB_FromPlc_BitLife As NodeId = New NodeId("MAIN.fbMachine.wfNestStations.fuPolTest.dbapp_plc.OCV_DB_FromPlc_BitLife", 4)
    Private OCV_DB_FromPlc_DataRequest As NodeId = New NodeId("MAIN.fbMachine.wfNestStations.fuPolTest.dbapp_plc.OCV_DB_FromPLC_DataRequest", 4)
    Private OCV_DB_FromPlc_DMData As NodeId = New NodeId("MAIN.fbMachine.wfNestStations.fuPolTest.dbapp_plc.OCV_DB_FromPLC_DMData", 4)
    Private OCV_DB_FromPlc_VoltageMeasured As NodeId = New NodeId("MAIN.fbMachine.wfNestStations.fuPolTest.dbapp_plc.OCV_DB_FromPLC_VoltageMeasured", 4)

    'OPC UA NodeIds - Dati inviati dall'applicazione al PLC
    Private OCV_DB_ToPlc_BitLife As NodeId = New NodeId("MAIN.fbMachine.wfNestStations.fuPolTest.dbapp_plc.OCV_DB_ToPlc_BitLife", 4)
    Private OCV_DB_ToPlc_Busy As NodeId = New NodeId("MAIN.fbMachine.wfNestStations.fuPolTest.dbapp_plc.OCV_DB_ToPlc_Busy", 4)
    Private OCV_DB_ToPlc_DataReady As NodeId = New NodeId("MAIN.fbMachine.wfNestStations.fuPolTest.dbapp_plc.OCV_DB_ToPLC_DataReady", 4)
    Private OCV_DB_ToPlc_Result As NodeId = New NodeId("MAIN.fbMachine.wfNestStations.fuPolTest.dbapp_plc.OCV_DB_ToPLC_RankResult", 4)

    'Variabili aux - Da PLC
    Private BitlifeFromPLC As Boolean = False
    Private FromPlcDataRequest As Boolean = False
    Private FromPlcDMData As String = ""
    Private FromPlcVoltageMeasured As Single

    'Variabili aux - A PLC
    Private BitlifeToPLC As Boolean = False
    Private ToPlcBusy As Boolean = False
    Private ToPlcDataReady As Boolean = False
    Private ToPlcResult As Int16 = 0

    'Variabili aux
    Private DatetimeTrigger As DateTime
    Private DatetimeEndOfProcess As DateTime
    Private LocalDBStatus As String = "No sync yet"
    Private LocalDBVoltMeasure As Single = 0.0
    Private LocalDBDatetime As DateTime
    Private LocalDBDataMatrix As String = ""
    Private LocalDBRank As String = ""
    Private SelfDischargeRateDay As Single = Single.Parse(AppSettings("SELF_DISCHARGE_RATE"), CultureInfo.InvariantCulture)
    Private SelfDischargeRateHour As Single = SelfDischargeRateDay / 24
    Private DeltaHourDiff As Integer = 0
    Private SelfDischargeVoltageDrop As Single = 0.0
    Private OcvDrop As Single = 0.0
    Private CellResult As Int16 = 0

    'Variabili aux per log
    Private logQueue As New ConcurrentQueue(Of String)
    Private logTaskRunning As Boolean = False

    'Monitoraggio connessione
    Private ReadNodeId As NodeId = New NodeId("GVL_Machine.rPercentOfMaxSpeed", 4)
    Private OpcuaConnSt As Boolean = False

    'Monitoraggio PLC
    Private BitLifeMonitorRunning As Boolean = False
    Private PlcInRun As Boolean = False

    'Dichiarazione Thread
    Private connectionThread As Thread
    Private connectionThreadRunning As Boolean = False

    Private ThrendMainProcess As Thread
    Private ThrendMainProcessRunning As Boolean = False

    Private ThrendAsyncDBProcess As Thread
    Private ThrendAsyncDBProcessRunning As Boolean = False


    Delegate Sub UpdateFormDelegate()
    Public UpdateForm As UpdateFormDelegate
    Dim objlockdt As New Object


    ' Restituisce DBNull.Value se il valore non è una data valida o è precedente al limite SqlDateTime (1753-01-01)
    Private Function SafeSqlDate(value As Object) As Object
        If value Is Nothing OrElse IsDBNull(value) Then
            Return DBNull.Value
        End If

        Dim d As DateTime

        ' Se è già un DateTime
        If TypeOf value Is DateTime Then
            d = CType(value, DateTime)
            If d < New DateTime(1753, 1, 1) Then
                Return DBNull.Value
            End If
            Return d
        End If

        ' Prova a convertire da string / numero
        If DateTime.TryParse(value.ToString(), d) Then
            If d < New DateTime(1753, 1, 1) Then
                Return DBNull.Value
            End If
            Return d
        End If

        ' Se non è convertibile -> DBNull
        Return DBNull.Value
    End Function


    Private Async Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        'Controlla e crea il database locale se non esiste
        LocalDbInitializer.InitializeDatabase()

        ConnettiToolStripMenuItem.Enabled = True
        DisconnettiToolStripMenuItem.Enabled = False
        L_Opcua_StConn.Text = "Disconnected"
        L_Opcua_StConn.ForeColor = Color.Red

        If MessageBox.Show("Do you want to connect to the OPC-UA server?", "Server Connection", MessageBoxButtons.YesNo) = DialogResult.Yes Then
            Await ConnectToServer()
        End If

        UpdateConnectionStatus()

        ' --- AVVIO THREAD DB ASINCRONO ---
        ThrendAsyncDBProcessRunning = True
        ThrendAsyncDBProcess = New Thread(AddressOf AsyncDBProcess) With {.IsBackground = True}
        ThrendAsyncDBProcess.Start()
        ' ---------------------------------

    End Sub

    Private Async Sub AsyncDBProcess()
        ThrendAsyncDBProcessRunning = True

        Dim FreqSet As Integer = CInt(AppSettings("FREQ_DB_INSERT_SEC")) * 1000
        Dim RowsSet As Integer = CInt(AppSettings("FREQ_DB_INSERT_N_ROW"))
        Dim FifoSet As Integer = CInt(AppSettings("FIFO_LOCAL_DB_GG"))

        While ThrendAsyncDBProcessRunning
            Try
                ' Mostra che la sincronizzazione è partita
                UpdateLocalDBStatus("Sync in progress...", "progress")

                ' 1. Prelevo i record da MPA_sharing
                Dim remoteRecords As New List(Of Dictionary(Of String, Object))
                Using sqlConn As New SqlConnection(ConnDB.ConnectionString)
                    Await sqlConn.OpenAsync()
                    Dim cmd As New SqlCommand($"SELECT TOP {RowsSet} * FROM cell_from_formation WHERE status_import = 0 ORDER BY ID ASC", sqlConn)

                    Using reader = Await cmd.ExecuteReaderAsync()
                        While Await reader.ReadAsync()
                            Dim record As New Dictionary(Of String, Object)
                            For i As Integer = 0 To reader.FieldCount - 1
                                record.Add(reader.GetName(i), reader.GetValue(i))
                            Next
                            remoteRecords.Add(record)
                        End While
                    End Using
                End Using

                ' 2. Inserisco i record nel DB locale
                If remoteRecords.Count > 0 Then
                    Using localConn As New SQLiteConnection(LocalDbInitializer.connectionString)
                        Await localConn.OpenAsync()
                        Using transaction = localConn.BeginTransaction()
                            For Each rec In remoteRecords
                                Dim cmdLocal As New SQLiteCommand("INSERT INTO cell_from_formation " &
                                "(cell_id, tray_id, position, ocv, cell_rank, datetime_ocv, status, status_import, datetime_import) " &
                                "VALUES (@cell_id, @tray_id, @position, @ocv, @cell_rank, @datetime_ocv, @status, @status_import, @datetime_import)", localConn)

                                cmdLocal.Parameters.AddWithValue("@cell_id", rec("cell_id"))
                                cmdLocal.Parameters.AddWithValue("@tray_id", rec("tray_id"))
                                cmdLocal.Parameters.AddWithValue("@position", rec("position"))
                                cmdLocal.Parameters.AddWithValue("@ocv", rec("ocv"))
                                cmdLocal.Parameters.AddWithValue("@cell_rank", rec("cell_rank"))
                                cmdLocal.Parameters.AddWithValue("@datetime_ocv", rec("datetime_ocv"))
                                cmdLocal.Parameters.AddWithValue("@status", rec("status"))
                                cmdLocal.Parameters.AddWithValue("@status_import", rec("status_import"))
                                cmdLocal.Parameters.AddWithValue("@datetime_import", rec("datetime_import"))
                                Await cmdLocal.ExecuteNonQueryAsync()
                            Next
                            transaction.Commit()
                        End Using
                    End Using

                    ' Aggiorna stato a completato
                    UpdateLocalDBStatus("Last insert: " & DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"), "success")
                Else
                    ' Nessun record nuovo
                    UpdateLocalDBStatus("No new records to sync", "success")
                End If

                ' 3. Aggiorno status_import sui record remoti
                Using sqlConn As New SqlConnection(ConnDB.ConnectionString)
                    Await sqlConn.OpenAsync()
                    Dim idsToUpdate = remoteRecords.Select(Function(r) r("ID")).ToList()
                    If idsToUpdate.Count > 0 Then
                        Dim idList As String = String.Join(",", idsToUpdate)
                        Dim updateCmd As New SqlCommand($"UPDATE cell_from_formation SET status_import = 1 WHERE ID IN ({idList})", sqlConn)
                        Await updateCmd.ExecuteNonQueryAsync()
                    End If
                End Using

                ' 4. Esporta record locali verso il DB remoto
                Dim localrecords As New List(Of Dictionary(Of String, Object))
                Using localconn As New SQLiteConnection(LocalDbInitializer.connectionString)
                    Await localconn.OpenAsync()

                    ' step a: leggi i record da esportare
                    'Dim selectcmd As New SQLiteCommand($"select top {RowsSet} * from cell_consumed where status_export is null or status_export = 0 order by datetime_add asc", localconn)
                    Dim selectcmd As New SQLiteCommand($"select * from cell_consumed where status_export is null or status_export = 0 order by datetime_add asc LIMIT {RowsSet}", localconn)

                    Using reader = Await selectcmd.ExecuteReaderAsync()
                        While Await reader.ReadAsync()
                            Dim rec As New Dictionary(Of String, Object)
                            For i As Integer = 0 To reader.FieldCount - 1
                                rec.Add(reader.GetName(i), reader.GetValue(i))
                            Next
                            localrecords.Add(rec)
                        End While
                    End Using
                End Using

                ' Step B: Inserisci i record nel DB remoto in una transazione separata
                If localrecords.Count > 0 Then
                    Using sqlConn As New SqlConnection(ConnDB.ConnectionString)
                        Await sqlConn.OpenAsync()

                        Using transaction = sqlConn.BeginTransaction()
                            ' Usa un timestamp unico per coerenza
                            Dim exportTime As DateTime = DateTime.Now

                            For Each rec In localrecords
                                Dim cmdRemote As New SqlCommand("INSERT INTO cell_consumed " &
                                "(cell_consumed, datetime_use, status_elab, OCV_ori, OCV_MPA, " &
                                "OCV_ori_datetime, cell_rank_ori, cell_rank_MPA, OCV_MPA_datetime, " &
                                "status_import, status_export, DateTime_add, DateTime_export) " &
                                "VALUES (@cell_consumed, @datetime_use, @status_elab, @OCV_ori, @OCV_MPA, " &
                                "@OCV_ori_datetime, @cell_rank_ori, @cell_rank_MPA, @OCV_MPA_datetime, " &
                                "@status_import, @status_export, @DateTime_add, @DateTime_export)", sqlConn, transaction)

                                ' Parametri con validazione SafeSqlDate per tutte le date
                                cmdRemote.Parameters.AddWithValue("@cell_consumed", If(IsDBNull(rec("cell_consumed")), DBNull.Value, rec("cell_consumed")))
                                cmdRemote.Parameters.AddWithValue("@datetime_use", SafeSqlDate(rec("datetime_use")))
                                cmdRemote.Parameters.AddWithValue("@status_elab", If(IsDBNull(rec("status_elab")), DBNull.Value, rec("status_elab")))
                                cmdRemote.Parameters.AddWithValue("@OCV_ori", If(IsDBNull(rec("OCV_ori")), DBNull.Value, rec("OCV_ori")))
                                cmdRemote.Parameters.AddWithValue("@OCV_MPA", If(IsDBNull(rec("OCV_MPA")), DBNull.Value, rec("OCV_MPA")))
                                cmdRemote.Parameters.AddWithValue("@OCV_ori_datetime", SafeSqlDate(rec("OCV_ori_datetime")))
                                cmdRemote.Parameters.AddWithValue("@cell_rank_ori", If(IsDBNull(rec("cell_rank_ori")), DBNull.Value, rec("cell_rank_ori")))
                                cmdRemote.Parameters.AddWithValue("@cell_rank_MPA", If(IsDBNull(rec("cell_rank_MPA")), DBNull.Value, rec("cell_rank_MPA")))
                                cmdRemote.Parameters.AddWithValue("@OCV_MPA_datetime", SafeSqlDate(rec("OCV_MPA_datetime")))
                                cmdRemote.Parameters.AddWithValue("@status_import", If(IsDBNull(rec("status_import")), DBNull.Value, rec("status_import")))
                                cmdRemote.Parameters.AddWithValue("@status_export", 1)
                                cmdRemote.Parameters.AddWithValue("@DateTime_add", SafeSqlDate(rec("DateTime_add")))
                                cmdRemote.Parameters.AddWithValue("@DateTime_export", exportTime)

                                Await cmdRemote.ExecuteNonQueryAsync()
                            Next

                            transaction.Commit()
                        End Using
                    End Using

                    ' Step C: Aggiorna i record locali con status_export = 1
                    Using localConn As New SQLiteConnection(LocalDbInitializer.connectionString)
                        Await localConn.OpenAsync()

                        Dim updateExportTime As DateTime = DateTime.Now

                        For Each rec In localrecords
                            ' ID locale del record
                            Dim recordId As Object = rec("id")

                            Dim updateLocalCmd As New SQLiteCommand(
                            "UPDATE cell_consumed " &
                            "SET status_export = 1, DateTime_export = @DateTime_export " &
                            "WHERE id = @id", localConn)

                            updateLocalCmd.Parameters.AddWithValue("@DateTime_export", updateExportTime)
                            updateLocalCmd.Parameters.AddWithValue("@id", recordId)

                            Await updateLocalCmd.ExecuteNonQueryAsync()
                        Next
                    End Using
                End If

                ' 5. Elimina dal DB locale i record più vecchi in base alla coda stabilita
                Using localConn As New SQLiteConnection(LocalDbInitializer.connectionString)
                    Await localConn.OpenAsync()
                    Dim cutoffDate As DateTime = DateTime.Now.AddDays(-FifoSet)

                    ' Elimina record vecchi da cell_from_formation
                    Dim delCmd1 As New SQLiteCommand("DELETE FROM cell_from_formation WHERE datetime_ocv < @cutoffDate", localConn)
                    delCmd1.Parameters.AddWithValue("@cutoffDate", cutoffDate)
                    Await delCmd1.ExecuteNonQueryAsync()

                    ' Elimina record vecchi da cell_consumed SOLO se già esportati
                    Dim delCmd2 As New SQLiteCommand("DELETE FROM cell_consumed WHERE OCV_ori_datetime < @cutoffDate AND status_export = 1", localConn)
                    delCmd2.Parameters.AddWithValue("@cutoffDate", cutoffDate)
                    Await delCmd2.ExecuteNonQueryAsync()
                End Using


            Catch ex As Exception
                ' Aggiorna label con errore
                UpdateLocalDBStatus("Error: " & ex.Message, "error")
            End Try

            ' Attesa in base ai secondi settati
            Await Task.Delay(FreqSet)
        End While
    End Sub

    Private Sub UpdateLocalDBStatus(ByVal status As String, ByVal state As String)
        If Me.InvokeRequired Then
            Me.Invoke(Sub()
                          L_LocalDB_St.Text = status
                          Select Case state
                              Case "progress"
                                  L_LocalDB_St.ForeColor = Color.Blue
                              Case "success"
                                  L_LocalDB_St.ForeColor = Color.Green
                              Case "error"
                                  L_LocalDB_St.ForeColor = Color.Red
                          End Select
                      End Sub)
        Else
            L_LocalDB_St.Text = status
            Select Case state
                Case "progress"
                    L_LocalDB_St.ForeColor = Color.Blue
                Case "success"
                    L_LocalDB_St.ForeColor = Color.Green
                Case "error"
                    L_LocalDB_St.ForeColor = Color.Red
            End Select
        End If
    End Sub

    Public Async Function DisconnectFromServer() As Task
        Try
            BitLifeMonitorRunning = False
            connectionThreadRunning = False
            ThrendMainProcessRunning = False

            If connectionThread IsNot Nothing AndAlso connectionThread.IsAlive Then
                connectionThread.Join()
            End If

            If ThrendMainProcess IsNot Nothing AndAlso ThrendMainProcess.IsAlive Then
                ThrendMainProcess.Join()
            End If

            If session IsNot Nothing AndAlso session.Connected Then
                Await session.CloseAsync()
                session.Dispose()
                session = Nothing
            End If

            UpdateConnectionStatus()
        Catch ex As Exception
            ' Log eventuale errore
        End Try
    End Function

    Private Sub UpdateConnectionStatus()
        Try
            If session IsNot Nothing AndAlso session.Connected Then
                ' Prova a leggere per assicurarti che la connessione sia attiva
                Dim val = session.ReadValue(ReadNodeId)
                OpcuaConnSt = True

                ConnettiToolStripMenuItem.Enabled = False
                DisconnettiToolStripMenuItem.Enabled = True
            Else
                OpcuaConnSt = False

                ConnettiToolStripMenuItem.Enabled = True
                DisconnettiToolStripMenuItem.Enabled = False
            End If
        Catch
            OpcuaConnSt = False
            ConnettiToolStripMenuItem.Enabled = True
            DisconnettiToolStripMenuItem.Enabled = False
        End Try
    End Sub

    Public Async Function ConnectToServer() As Task
        Try
            opcApp = New ApplicationInstance() With {
                .ApplicationName = "VB OPC UA Client",
                .ApplicationType = ApplicationType.Client
            }

            Dim config = New ApplicationConfiguration() With {
                .ApplicationName = opcApp.ApplicationName,
                .ApplicationUri = "urn:" & System.Net.Dns.GetHostName() & ":VBOPCUAClient",
                .ApplicationType = ApplicationType.Client,
                .SecurityConfiguration = New SecurityConfiguration() With {
                    .ApplicationCertificate = New CertificateIdentifier(),
                    .AutoAcceptUntrustedCertificates = True,
                    .AddAppCertToTrustedStore = True,
                    .TrustedIssuerCertificates = New CertificateTrustList() With {
                        .StoreType = "Directory",
                        .StorePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "OPC Foundation\CertificateStores\UA Certificate Authorities")
                    },
                    .TrustedPeerCertificates = New CertificateTrustList() With {
                        .StoreType = "Directory",
                        .StorePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "OPC Foundation\CertificateStores\UA Applications")
                    },
                    .RejectedCertificateStore = New CertificateTrustList() With {
                        .StoreType = "Directory",
                        .StorePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "OPC Foundation\CertificateStores\RejectedCertificates")
                    }
                },
                .TransportConfigurations = New TransportConfigurationCollection(),
                .TransportQuotas = New TransportQuotas() With {
                    .OperationTimeout = 15000
                },
                .ClientConfiguration = New ClientConfiguration() With {
                    .DefaultSessionTimeout = 60000
                },
                .TraceConfiguration = New TraceConfiguration()
            }

            Await config.Validate(ApplicationType.Client)
            opcApp.ApplicationConfiguration = config

            opcApp.CheckApplicationInstanceCertificate(False, 0)

            Dim selectedEndpoint = CoreClientUtils.SelectEndpoint(endpointUrl, False, 15000)
            Dim endpointConfiguration As EndpointConfiguration = EndpointConfiguration.Create(config)
            Dim endpoint = New ConfiguredEndpoint(Nothing, selectedEndpoint, endpointConfiguration)

            session = Await Session.Create(config, endpoint, False, "VB.NET OPC UA Client", 60000, Nothing, Nothing)

            ' Ferma eventuali thread già attivi
            connectionThreadRunning = False
            If connectionThread IsNot Nothing AndAlso connectionThread.IsAlive Then
                connectionThread.Join()
            End If

            ThrendMainProcessRunning = False
            If ThrendMainProcess IsNot Nothing AndAlso ThrendMainProcess.IsAlive Then
                ThrendMainProcess.Join()
            End If

            ' Avvia thread
            connectionThreadRunning = True
            connectionThread = New Thread(AddressOf MonitorConnection) With {.IsBackground = True}
            connectionThread.Start()

            ThrendMainProcessRunning = True
            ThrendMainProcess = New Thread(AddressOf MainProcess) With {.IsBackground = True}
            ThrendMainProcess.Start()


            ' Avvia monitor BitLife (task asincrono)
            BitLifeMonitorRunning = True
            Task.Run(Async Function()
                         Await BitLifeMonitorProcess()
                     End Function)

            Me.Invoke(Sub()
                          UpdateConnectionStatus()
                      End Sub)

        Catch ex As Exception
            Me.Invoke(Sub()
                          UpdateConnectionStatus()
                      End Sub)
        End Try
    End Function

    Private Sub MonitorConnection()
        While connectionThreadRunning
            Try
                If session IsNot Nothing AndAlso session.Connected Then
                    Dim val = session.ReadValue(ReadNodeId)
                    Me.Invoke(Sub()
                                  UpdateConnectionStatus()
                              End Sub)
                    Thread.Sleep(5000)
                Else
                    Throw New Exception("Session not connected")
                End If
            Catch ex As Exception
                connectionThreadRunning = False

                ThrendMainProcessRunning = False
                If ThrendMainProcess IsNot Nothing AndAlso ThrendMainProcess.IsAlive Then
                    ThrendMainProcess.Join()
                End If

                If session IsNot Nothing Then
                    Try
                        session.Close()
                        session.Dispose()
                    Catch
                    End Try
                    session = Nothing
                End If

                Me.Invoke(Sub()
                              UpdateConnectionStatus()
                          End Sub)

                ' Tentativo riconnessione asincrono
                Task.Run(Async Function()
                             Dim reconnected As Boolean = False
                             While Not reconnected
                                 Try
                                     Await ConnectToServer()
                                     reconnected = True
                                 Catch
                                     Thread.Sleep(5000)
                                 End Try
                             End While
                         End Function)

                Exit While
            End Try
        End While
    End Sub

    Private Async Function BitLifeMonitorProcess() As Task
        Dim toggleState As Boolean = False
        Dim lastFromPlcBitLife As Boolean = False
        Dim lastChangeTime As DateTime = DateTime.Now

        Do While BitLifeMonitorRunning
            Try
                If session IsNot Nothing AndAlso session.Connected Then
                    Dim writeValue As New WriteValue With {
                        .NodeId = OCV_DB_ToPlc_BitLife,
                        .AttributeId = Attributes.Value,
                        .Value = New DataValue(New Opc.Ua.Variant(toggleState))
                    }
                    Dim writeCollection As New WriteValueCollection From {writeValue}
                    Dim writeResults = Await session.WriteAsync(Nothing, writeCollection, CancellationToken.None)

                    'If writeResults.Results(0) <> StatusCodes.Good Then
                    ' Console.WriteLine("Scrittura BitLife fallita: " & writeResults.Results(0).ToString())
                    'End If

                    toggleState = Not toggleState

                    BitlifeToPLC = toggleState

                    Dim fromPlcValue = session.ReadValue(OCV_DB_FromPlc_BitLife).Value

                    If TypeOf fromPlcValue Is Boolean Then
                        Dim currentValue As Boolean = CBool(fromPlcValue)

                        BitlifeFromPLC = currentValue

                        If currentValue <> lastFromPlcBitLife Then
                            lastFromPlcBitLife = currentValue
                            lastChangeTime = DateTime.Now
                        End If

                        PlcInRun = (DateTime.Now - lastChangeTime).TotalSeconds <= 2
                    Else
                        PlcInRun = False
                    End If


                End If

            Catch ex As Exception
                PlcInRun = False
            End Try

            Await Task.Delay(200) '1000
        Loop
    End Function

    Private Async Sub MainProcess()
        'INIT
        Dim OnsRequest As Boolean = False

        Do While ThrendMainProcessRunning

            ' ------------------------------------------------------
            ' 1. LEGGO I DATI DA PLC

            Dim valueReq = session.ReadValue(OCV_DB_FromPlc_DataRequest).Value
            If TypeOf valueReq Is Boolean Then
                FromPlcDataRequest = CBool(valueReq)
            End If

            Dim valueDM = session.ReadValue(OCV_DB_FromPlc_DMData).Value
            If TypeOf valueDM Is String Then
                FromPlcDMData = CStr(valueDM)
            End If

            Dim valueVolt = session.ReadValue(OCV_DB_FromPlc_VoltageMeasured).Value
            If IsNumeric(valueVolt) Then
                FromPlcVoltageMeasured = Math.Abs(CSng(valueVolt))
            End If
            ' ------------------------------------------------------


            ' ------------------------------------------------------
            ' 2. PROCESSO I DATI SU RICHIESTA

            If FromPlcDataRequest = True And OnsRequest = False Then

                SyncLock objlockdt

                    OnsRequest = True

                    'Presetto
                    ToPlcBusy = True
                    DatetimeTrigger = DateTime.Now
                    ToPlcResult = 0

                    DatetimeEndOfProcess = DateTime.MinValue

                    LocalDBDataMatrix = "- - -"
                    LocalDBDatetime = DateTime.MinValue
                    LocalDBVoltMeasure = 0.0

                    CellResult = 0

                    'Lettura DB locale
                    Try
                        Using conn As New SQLiteConnection(LocalDbInitializer.connectionString)
                            conn.Open() ' <-- sincrono

                            Dim cmd As New SQLiteCommand("
                            SELECT datetime_ocv, ocv, cell_rank 
                            FROM cell_from_formation 
                            WHERE cell_id = @cell_id 
                            LIMIT 1", conn)

                            cmd.Parameters.AddWithValue("@cell_id", FromPlcDMData)

                            Using reader As SQLiteDataReader = cmd.ExecuteReader() ' <-- sincrono
                                If reader.Read() Then
                                    LocalDBDatetime = If(IsDBNull(reader("datetime_ocv")), DateTime.MinValue, CDate(reader("datetime_ocv")))
                                    LocalDBVoltMeasure = If(IsDBNull(reader("ocv")), 0.0F, Single.Parse(CStr(reader("ocv")), Globalization.CultureInfo.InvariantCulture))
                                    LocalDBDataMatrix = FromPlcDMData
                                    LocalDBRank = If(IsDBNull(reader("cell_rank")), "", CStr(reader("cell_rank")))
                                Else
                                    LocalDBDatetime = DateTime.MinValue
                                    LocalDBVoltMeasure = 0.0
                                    LocalDBDataMatrix = "Not Found"
                                    LocalDBRank = ""
                                End If
                            End Using
                        End Using
                    Catch ex As Exception
                        LocalDBDatetime = DateTime.MinValue
                        LocalDBVoltMeasure = 0.0
                        LocalDBDataMatrix = FromPlcDMData
                        LocalDBRank = ""
                    End Try

                    'Elaborazione
                    If LocalDBDataMatrix <> "Not Found" Then
                        OcvDrop = (LocalDBVoltMeasure - FromPlcVoltageMeasured) * 1000

                        DeltaHourDiff = CInt((DatetimeTrigger - LocalDBDatetime).TotalHours)
                        SelfDischargeVoltageDrop = DeltaHourDiff * SelfDischargeRateHour

                        If OcvDrop < SelfDischargeVoltageDrop Then
                            CellResult = 1
                            ToPlcResult = 1
                        Else
                            CellResult = 2
                            ToPlcResult = 2
                        End If

                    Else
                        CellResult = 2
                        ToPlcResult = 2
                    End If

                    'Scrittura DB Locale
                    Try
                        Using conn As New SQLiteConnection(LocalDbInitializer.connectionString)
                            conn.Open()

                            Dim sql As String = "
                                INSERT INTO cell_consumed 
                                (cell_consumed, datetime_use, status_elab, OCV_ori, OCV_MPA, 
                                OCV_ori_datetime, cell_rank_ori, cell_rank_MPA, OCV_MPA_datetime, 
                                status_import, status_export, DateTime_add, DateTime_export)
                                VALUES 
                                (@cell_consumed, @datetime_use, @status_elab, @OCV_ori, @OCV_MPA, 
                                @OCV_ori_datetime, @cell_rank_ori, @cell_rank_MPA, @OCV_MPA_datetime, 
                                @status_import, @status_export, @DateTime_add, @DateTime_export)
                            "

                            Using cmd As New SQLiteCommand(sql, conn)
                                cmd.Parameters.AddWithValue("@cell_consumed", LocalDBDataMatrix)
                                cmd.Parameters.AddWithValue("@datetime_use", DateTime.Now)
                                cmd.Parameters.AddWithValue("@status_elab", If(CellResult = 1, "OK", If(CellResult = 2, "NG", "??")))
                                cmd.Parameters.AddWithValue("@OCV_ori", LocalDBVoltMeasure.ToString(Globalization.CultureInfo.InvariantCulture))
                                cmd.Parameters.AddWithValue("@OCV_MPA", FromPlcVoltageMeasured.ToString(Globalization.CultureInfo.InvariantCulture))
                                cmd.Parameters.AddWithValue("@OCV_ori_datetime", LocalDBDatetime)
                                cmd.Parameters.AddWithValue("@cell_rank_ori", LocalDBRank)
                                cmd.Parameters.AddWithValue("@cell_rank_MPA", DBNull.Value)
                                cmd.Parameters.AddWithValue("@OCV_MPA_datetime", DatetimeTrigger)
                                cmd.Parameters.AddWithValue("@status_import", DBNull.Value)
                                cmd.Parameters.AddWithValue("@status_export", DBNull.Value)
                                cmd.Parameters.AddWithValue("@DateTime_add", DBNull.Value)
                                cmd.Parameters.AddWithValue("@DateTime_export", DBNull.Value)

                                cmd.ExecuteNonQuery()
                            End Using
                        End Using

                    Catch ex As Exception
                        MessageBox.Show("Error during insertion into cell_consumed: " & ex.Message)
                    End Try


                    ToPlcDataReady = True
                    ToPlcBusy = False
                    DatetimeEndOfProcess = DateTime.Now

                    'Scrivo il log
                    If AppSettings("LOG_REC") = "True" Then
                        Dim cellResultText As String = If(CellResult = 1, "OK", If(CellResult = 2, "NG", CellResult.ToString()))

                        Dim logLine As String = $"{DateTime.Now:yyyy-MM-dd HH:mm:ss};" &
                            $"{FromPlcDMData};" &
                            $"{FromPlcVoltageMeasured.ToString("F6", Globalization.CultureInfo.InvariantCulture)};" &
                            $"{DatetimeTrigger:yyyy-MM-dd HH:mm:ss};" &
                            $"{LocalDBDataMatrix};" &
                            $"{LocalDBVoltMeasure.ToString("F6", Globalization.CultureInfo.InvariantCulture)};" &
                            $"{LocalDBDatetime:yyyy-MM-dd HH:mm:ss};" &
                            $"{OcvDrop.ToString("F6", Globalization.CultureInfo.InvariantCulture)};" &
                            $"{SelfDischargeVoltageDrop.ToString("F6", Globalization.CultureInfo.InvariantCulture)};" &
                            $"{DeltaHourDiff};" &
                            $"{cellResultText}"

                        EnqueueLog(logLine)
                    End If

                End SyncLock

            End If

            If FromPlcDataRequest = False Then
                OnsRequest = False
                ToPlcDataReady = False
            End If
            ' ------------------------------------------------------

            ' ------------------------------------------------------
            ' 3. SCRIVO I DATI VERSO IL PLC (batch)

            Dim writeValues As New WriteValueCollection From {
                New WriteValue With {
                    .NodeId = OCV_DB_ToPlc_Busy,
                    .AttributeId = Attributes.Value,
                    .Value = New DataValue(New Opc.Ua.Variant(ToPlcBusy))
                },
                New WriteValue With {
                    .NodeId = OCV_DB_ToPlc_DataReady,
                    .AttributeId = Attributes.Value,
                    .Value = New DataValue(New Opc.Ua.Variant(ToPlcDataReady))
                },
                New WriteValue With {
                    .NodeId = OCV_DB_ToPlc_Result,
                    .AttributeId = Attributes.Value,
                    .Value = New DataValue(New Opc.Ua.Variant(ToPlcResult))
                }
            }

            Dim writeResults = Await session.WriteAsync(Nothing, writeValues, CancellationToken.None)

            For i = 0 To writeResults.Results.Count - 1
                If writeResults.Results(i) <> StatusCodes.Good Then
                    MessageBox.Show($"Node write error {writeValues(i).NodeId}: {writeResults.Results(i)}",
                        "OPC-UA Error",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Error)
                End If
            Next
            ' ------------------------------------------------------

            Await Task.Delay(200) '50
            SubUpdateForm()
        Loop
    End Sub


    Public Sub SubUpdateForm()

        If Me.InvokeRequired Then
            UpdateForm = New UpdateFormDelegate(AddressOf SubUpdateForm)
            Me.BeginInvoke(UpdateForm)
        Else

            SyncLock objlockdt

                '------- From PLC --------
                'Stato Data Request da PLC
                L_fromplc_flag_request.Text = If(FromPlcDataRequest, "1", "0")

                'Data ed ora Trigger request
                L_fromplc_datetime.Text = If(DatetimeTrigger = DateTime.MinValue, "- - -", DatetimeTrigger.ToString("yyyy-MM-dd HH:mm:ss"))

                'Tensione misurata da PLC
                L_fromplc_V_measured.Text = FromPlcVoltageMeasured.ToString("F6", Globalization.CultureInfo.InvariantCulture) & " V"

                'Datamatrix da PLC
                L_fromplc_datamatrix.Text = FromPlcDMData
                '-------------------------

                '---- From Formation -----
                'Datamatrix cella
                L_localdb_datamatrix.Text = LocalDBDataMatrix

                'Tensione misurata (OCV Iniziale)
                L_localdb_V_measured.Text = LocalDBVoltMeasure.ToString("F6", Globalization.CultureInfo.InvariantCulture) & " V"

                'Data ed ora test misurazione tensione
                L_localdb_datetime.Text = If(LocalDBDatetime = DateTime.MinValue, "- - -", LocalDBDatetime.ToString("yyyy-MM-dd HH:mm:ss"))
                '-------------------------

                '------ Calculating ------
                L_InitialOcv.Text = LocalDBVoltMeasure.ToString("F6", Globalization.CultureInfo.InvariantCulture) & " V"
                L_FinalOcv.Text = FromPlcVoltageMeasured.ToString("F6", Globalization.CultureInfo.InvariantCulture) & " V"
                L_OcvDropC.Text = OcvDrop.ToString("F1", Globalization.CultureInfo.InvariantCulture) & " mV"
                L_DeltaHourFormationTest.Text = DeltaHourDiff.ToString
                L_SelfDischargeRateHour.Text = SelfDischargeRateHour.ToString("F6", Globalization.CultureInfo.InvariantCulture) & " mV/h"
                L_SelfDischargeVoltageDropC.Text = SelfDischargeVoltageDrop.ToString("F1", Globalization.CultureInfo.InvariantCulture) & " mV"
                '-------------------------

                '-------- Result ---------
                L_OcvDropR.Text = L_OcvDropC.Text
                L_SelfDischargeVoltageDropR.Text = L_SelfDischargeVoltageDropC.Text
                L_CellResult.Text = If(CellResult = 0, "- - -", If(CellResult = 1, "Good", If(CellResult = 2, "Scrap", "UNKNOWN")))
                L_CellResult.ForeColor = If(CellResult = 0, Color.Yellow, If(CellResult = 1, Color.Green, If(CellResult = 2, Color.Red, Color.Yellow)))
                '-------------------------

                '-------- To PLC ---------
                L_toplc_flag_dataready.Text = If(ToPlcDataReady, "1", "0")
                L_toplc_datetime.Text = If(DatetimeEndOfProcess = DateTime.MinValue, "- - -", DatetimeEndOfProcess.ToString("yyyy-MM-dd HH:mm:ss"))
                L_toplc_result.Text = ToPlcResult.ToString
                '-------------------------

                '-- OPC-UA / PLC Status --
                'Stato connesione
                L_Opcua_StConn.Text = If(OpcuaConnSt, "Connected", "Disconnected")
                L_Opcua_StConn.ForeColor = If(OpcuaConnSt, Color.Green, Color.Red)

                'Stato PLC
                L_StPLC.Text = If(PlcInRun, "Run", "Stop")
                L_StPLC.ForeColor = If(PlcInRun, Color.Green, Color.Red)

                'Bitlife da App a PLC
                L_BitlifeToPLC.Text = If(BitlifeToPLC, "1", "0")


                'Bitlife da PLC a APP
                L_BitlifeFromPLC.Text = If(BitlifeFromPLC, "1", "0")
                '-------------------------

            End SyncLock
        End If


    End Sub

    Private Sub EnqueueLog(ByVal logText As String)
        logQueue.Enqueue(DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss") & " " & logText)

        If Not logTaskRunning Then
            logTaskRunning = True
            Task.Run(AddressOf ProcessLogQueue)
        End If
    End Sub

    Private Sub ProcessLogQueue()
        Dim logDir As String = Path.Combine(My.Application.Info.DirectoryPath, "Log")
        If Not Directory.Exists(logDir) Then Directory.CreateDirectory(logDir)
        Dim logFile As String = Path.Combine(logDir, "Log_" & DateTime.Now.ToString("dd-MM-yyyy") & ".txt")

        ' Scrive intestazione se il file non esiste
        If Not File.Exists(logFile) Then
            Using sw As New StreamWriter(logFile, True)
                sw.WriteLine("Datetime;MpaDMData;MpaVoltageMeasured;MpaDatetime;FormationDataMatrix;FormationVoltMeasure;FormationDatetime;OcvDrop;SelfDischargeVoltageDrop;DeltaHourDiff;CellResult")
            End Using
        End If

        Dim line As String ' <-- dichiarazione corretta della variabile
        While logQueue.TryDequeue(line)
            Try
                Using sw As New StreamWriter(logFile, True)
                    sw.WriteLine(line)
                End Using
            Catch ex As Exception
                ' Opzionale: gestisci errori di scrittura
            End Try
        End While

        logTaskRunning = False
    End Sub

    Private Async Sub ConnettiToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ConnettiToolStripMenuItem.Click
        Await ConnectToServer()
    End Sub

    Private Async Sub DisconnettiToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles DisconnettiToolStripMenuItem.Click
        Await DisconnectFromServer()
        SubUpdateForm()
    End Sub

    Private Async Sub ChiudiToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ChiudiToolStripMenuItem.Click
        Dim res = MessageBox.Show("Are you sure you want to close the application?", "Close Application", MessageBoxButtons.YesNoCancel)
        If res = DialogResult.Yes Then
            BitLifeMonitorRunning = False
            connectionThreadRunning = False
            ThrendMainProcessRunning = False
            ThrendAsyncDBProcessRunning = False

            If connectionThread IsNot Nothing AndAlso connectionThread.IsAlive Then
                connectionThread.Join()
            End If

            If ThrendMainProcess IsNot Nothing AndAlso ThrendMainProcess.IsAlive Then
                ThrendMainProcess.Join()
            End If

            If ThrendAsyncDBProcess IsNot Nothing AndAlso ThrendAsyncDBProcess.IsAlive Then
                ThrendAsyncDBProcess.Join()
            End If

            If session IsNot Nothing AndAlso session.Connected Then
                Await session.CloseAsync()
                session.Dispose()
                session = Nothing
            End If

            Application.Exit()
        End If
    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        ' Assicurati di fermare i thread e chiudere la sessione anche se la finestra viene chiusa con la "X"
        BitLifeMonitorRunning = False
        connectionThreadRunning = False
        ThrendMainProcessRunning = False
        ThrendAsyncDBProcessRunning = False

        If connectionThread IsNot Nothing AndAlso connectionThread.IsAlive Then
            connectionThread.Join()
        End If

        If ThrendMainProcess IsNot Nothing AndAlso ThrendMainProcess.IsAlive Then
            ThrendMainProcess.Join()
        End If

        If ThrendAsyncDBProcess IsNot Nothing AndAlso ThrendAsyncDBProcess.IsAlive Then
            ThrendAsyncDBProcess.Join()
        End If

        If session IsNot Nothing AndAlso session.Connected Then
            session.Close()
            session.Dispose()
            session = Nothing
        End If
    End Sub

    Private Sub SettingToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SettingToolStripMenuItem.Click
        FormSetting.ShowDialog()
    End Sub

    Private Sub ToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles ToolStripMenuItem1.Click
        MessageBox.Show(
                "Application: OCV Data Bridge" & vbCrLf &
                "Version: 1.0" & vbCrLf &
                "Developer: Vincenzo Niespolo" & vbCrLf &
                "Email: niespotech@gmail.com" & vbCrLf &
                "Cell: +39 3349113322"
                )
    End Sub

End Class
