Imports System.Data.SQLite
Imports System.IO

Module LocalDbInitializer

    Public dbPath As String = Path.Combine(Application.StartupPath, "localdb.db")
    Public connectionString As String = $"Data Source={dbPath};Version=3;"

    Public Sub InitializeDatabase()
        ' Se il DB non esiste, lo crea
        If Not File.Exists(dbPath) Then
            SQLiteConnection.CreateFile(dbPath)
        End If

        Using conn As New SQLiteConnection(connectionString)
            conn.Open()

            ' -----------------------------
            ' Tabella 1: cell_from_formation
            ' -----------------------------
            Dim createTable1 As String = "
            CREATE TABLE IF NOT EXISTS cell_from_formation (
                ID INTEGER PRIMARY KEY AUTOINCREMENT,
                cell_id NVARCHAR(50),
                tray_id NVARCHAR(50),
                position NVARCHAR(5),
                ocv NVARCHAR(50),
                cell_rank NVARCHAR(50),
                datetime_ocv DATETIME,
                status NVARCHAR(50),
                status_import NVARCHAR(3) DEFAULT '0',
                datetime_import DATETIME
            )"
            Using cmd As New SQLiteCommand(createTable1, conn)
                cmd.ExecuteNonQuery()
            End Using

            ' -----------------------------
            ' Tabella 2: cell_consumed
            ' -----------------------------
            Dim createTable2 As String = "
            CREATE TABLE IF NOT EXISTS cell_consumed (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                cell_consumed NVARCHAR(50),
                datetime_use DATETIME,
                status_elab NVARCHAR(5),
                OCV_ori NVARCHAR(50),
                OCV_MPA NVARCHAR(50),
                OCV_ori_datetime DATETIME,
                cell_rank_ori NVARCHAR(50),
                cell_rank_MPA NVARCHAR(50),
                OCV_MPA_datetime DATETIME,
                status_import NVARCHAR(3) DEFAULT '0',
                status_export NVARCHAR(3) DEFAULT '0',
                DateTime_add DATETIME,
                DateTime_export DATETIME
            )"
            Using cmd As New SQLiteCommand(createTable2, conn)
                cmd.ExecuteNonQuery()
            End Using

            conn.Close()
        End Using
    End Sub

End Module
