TMTT/DataService/DatabaseHelper.cs

using System.Data.SQLite;
using System;

// 并且可能需要处理 SQLite 的原生库（x86/x64）。

public class DatabaseHelper
{
    // C# 7.3 完全支持字符串字面量
    public static string ConnectionString = "Data Source=app.db;Version=3; Pooling=True;";

    public static void InitializeDatabase()
    {
        //Batteries_V2.Init();
        //SQLitePCL.Batteries.Init();
        using (var connection = new SQLiteConnection(ConnectionString))
        {
            
            connection.Open();
            using (var cmd = connection.CreateCommand())
            {
                // 启用 WAL 模式（只需一次）
                cmd.CommandText = "PRAGMA journal_mode=WAL;";
                cmd.ExecuteNonQuery();

                // 降低同步级别（安全可接受）
                cmd.CommandText = "PRAGMA synchronous=NORMAL;";
                cmd.ExecuteNonQuery();
            }
            CreateTablesIfNotExists(connection);
            InsertDefaultAdminIfNotExists(connection);

            EnsureUserTableColumns(connection);
        } // connection 在这里被自动关闭和释放
    }
    private static void CreateTablesIfNotExists(SQLiteConnection connection)
    {
        string createUsersTable = @"
        CREATE TABLE IF NOT EXISTS Users (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Username TEXT NOT NULL UNIQUE,
            PasswordHash TEXT NOT NULL,
            PermissionLevel INTEGER NOT NULL,
            CanCloseSoftware INTEGER NOT NULL
        );";

        string createLogsTable = @"
        CREATE TABLE IF NOT EXISTS OperationLogs (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Timestamp DATETIME NOT NULL DEFAULT (datetime('now','localtime')),
            UserId INTEGER NOT NULL,
            Username TEXT NOT NULL,
            ActionType INTEGER NOT NULL,
            ControlName TEXT NOT NULL,
            OldValue TEXT,
            NewValue TEXT,
            Details TEXT,
            FOREIGN KEY (UserId) REFERENCES Users(Id)
        );";

        string createAlarmsTable = @"
        CREATE TABLE IF NOT EXISTS AlarmLogs (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Timestamp DATETIME NOT NULL DEFAULT (datetime('now','localtime')),
            UserId INTEGER NOT NULL,
            Username TEXT NOT NULL,
            AlarmMessage TEXT,
            AlarmType INTEGER NOT NULL,
            FOREIGN KEY (UserId) REFERENCES Users(Id)
        );";

        string createTipsTable = @"
        CREATE TABLE IF NOT EXISTS TipsLogs (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Timestamp DATETIME NOT NULL DEFAULT (datetime('now','localtime')),
            UserId INTEGER NOT NULL,
            Username TEXT NOT NULL,
            Message TEXT,
            Type INTEGER NOT NULL,
            FOREIGN KEY (UserId) REFERENCES Users(Id)
        );";

        string createBathcesTable = @"
        CREATE TABLE IF NOT EXISTS BatchLogs (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            BatchID TEXT NOT NULL UNIQUE,  --加 UNIQUE 约束
            StartTimestamp DATETIME NOT NULL DEFAULT (datetime('now','localtime')),
            StartUserId INTEGER NOT NULL,
            StartUsername TEXT NOT NULL,
            EndTimestamp DATETIME,
            EndUserId INTEGER,
            EndUsername TEXT,
            RecipeName TEXT,
            Details TEXT,
            FOREIGN KEY (StartUserId) REFERENCES Users(Id),
            FOREIGN KEY (EndUserId) REFERENCES Users(Id)
        );";

        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = createUsersTable;
            cmd.ExecuteNonQuery();

            cmd.CommandText = createLogsTable;
            cmd.ExecuteNonQuery();

            cmd.CommandText = createAlarmsTable;
            cmd.ExecuteNonQuery();

            cmd.CommandText = createTipsTable;
            cmd.ExecuteNonQuery();

            cmd.CommandText = createBathcesTable;
            cmd.ExecuteNonQuery();
        }
    }

    private static void InsertDefaultAdminIfNotExists(SQLiteConnection connection)
    {
        // 假设最高权限为 4（你可以根据自己的定义调整）
        const string defaultUsername = "developer";
        const int developerPermissionLevel = 4; // 最高权限
        const string defaultPassword = "developer123"; // 初始密码建议首次登录后强制修改

        // 先检查是否有任何用户存在（或更严格地检查 developer 是否存在）
        var checkCmd = new SQLiteCommand("SELECT 1 FROM Users LIMIT 1", connection);
        var exists = checkCmd.ExecuteScalar() != null;

        if (!exists)
        {
            // 插入默认开发者账户
            string passwordHash = BCrypt.Net.BCrypt.HashPassword(defaultPassword);

            using (var insertCmd = new SQLiteCommand(@"
            INSERT INTO Users (Username, PasswordHash, PermissionLevel,CanCloseSoftware)
            VALUES (@username, @hash, @level,@canClose)", connection))
            {
                insertCmd.Parameters.AddWithValue("@username", defaultUsername);
                insertCmd.Parameters.AddWithValue("@hash", passwordHash);
                insertCmd.Parameters.AddWithValue("@level", developerPermissionLevel);
                insertCmd.Parameters.AddWithValue("@canClose", 1);
                insertCmd.ExecuteNonQuery();
                // 可选：记录日志（此时可能还不能写日志表）
            }
        }
    }
    private static void EnsureUserTableColumns(SQLiteConnection connection)
    {
        return;
        // 检查 RegistrationDate 是否已存在
        if (!ColumnExists(connection, "Users", "RegistrationDate"))
        {
            string addColumnSql = @"
            ALTER TABLE Users 
            ADD COLUMN RegistrationDate DATETIME NOT NULL DEFAULT (datetime('now'));";

            using (var cmd = new SQLiteCommand(addColumnSql, connection))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }

    // 辅助方法：检查某表是否包含某列
    private static bool ColumnExists(SQLiteConnection connection, string tableName, string columnName)
    {
        using (var cmd = new SQLiteCommand(@"
        SELECT COUNT(*) 
        FROM pragma_table_info(@tableName) 
        WHERE name = @columnName", connection))
        {
            cmd.Parameters.AddWithValue("@tableName", tableName);
            cmd.Parameters.AddWithValue("@columnName", columnName);

            long count = (long)cmd.ExecuteScalar();
            return count > 0;
        }
    }
}