YZWater3/src/YZWater.Core/Services/DatabaseService.cs

using Serilog;
using SqlSugar;
using YZWater.Core.Models;

namespace YZWater.Core.Services;

/// <summary>
/// 数据库服务 - 使用 SqlSugar + SQLite
/// </summary>
public static class DatabaseService
{
    private static SqlSugarClient? _db;
    private static readonly string DbPath = "yzwater.db";

    /// <summary>
    /// 数据库实例
    /// </summary>
    public static ISqlSugarClient Db => _db ?? throw new InvalidOperationException("数据库未初始化，请先调用 Initialize()");

    /// <summary>
    /// 初始化数据库（需先调用 LogService.Initialize()）
    /// </summary>
    public static void Initialize()
    {
        InitDatabase();
    }

    private static void InitDatabase()
    {
        _db = new SqlSugarClient(new ConnectionConfig
        {
            ConnectionString = $"Data Source={DbPath}",
            DbType = DbType.Sqlite,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute
        });

        _db.Aop.OnLogExecuting = (sql, pars) =>
        {
            Log.Debug("SQL: {Sql}", sql);
        };

        _db.DbMaintenance.CreateDatabase();

        // 启用 SQLite WAL 模式（提升并发读写性能）
        try
        {
            _db.Ado.ExecuteCommand("PRAGMA journal_mode=WAL");
            _db.Ado.ExecuteCommand("PRAGMA synchronous=NORMAL");  // WAL 模式下推荐
            _db.Ado.ExecuteCommand("PRAGMA cache_size=-64000");   // 64MB 缓存
            _db.Ado.ExecuteCommand("PRAGMA temp_store=MEMORY");   // 临时表使用内存
            Log.Information("SQLite WAL 模式已启用");
        }
        catch (Exception ex)
        {
            Log.Warning(ex, "启用 WAL 模式失败（继续使用默认模式）");
        }

        // 逐个初始化表，单个失败不删除数据也不影响其他表
        var tables = new[] { typeof(Person), typeof(AlarmRecord), typeof(FlowRecord),
                             typeof(EquipmentStatus), typeof(AuditLog), typeof(User),
                             typeof(AnalogRecord) };

        foreach (var tableType in tables)
        {
            try
            {
                _db.CodeFirst.InitTables(tableType);
            }
            catch (Exception ex)
            {
                // 仅记录错误，继续初始化其他表，不删除数据库
                Log.Error(ex, "初始化表 {Table} 失败（保留已有数据，跳过此表）", tableType.Name);
            }
        }

        try
        {
            AuthService.Initialize();
        }
        catch (Exception ex)
        {
            Log.Error(ex, "AuthService 初始化失败（默认管理员可能未创建）");
        }

        // 创建索引以提升查询性能（忽略已存在的索引）
        try
        {
            CreateIndexes();
        }
        catch (Exception ex)
        {
            Log.Warning(ex, "创建索引失败（不影响功能）");
        }

        Log.Information("数据库初始化完成");
    }

    /// <summary>
    /// 创建常用查询索引
    /// </summary>
    private static void CreateIndexes()
    {
        var indexSqls = new[]
        {
            "CREATE INDEX IF NOT EXISTS idx_flow_record_time ON flow_records(RecordTime)",
            "CREATE INDEX IF NOT EXISTS idx_alarm_time ON alarm_records(AlarmTime)",
            "CREATE INDEX IF NOT EXISTS idx_equip_device_id ON equipment_status(DeviceId)",
            "CREATE INDEX IF NOT EXISTS idx_equip_update_time ON equipment_status(LastUpdateTime)",
            "CREATE INDEX IF NOT EXISTS idx_analog_record_time ON analog_records(RecordTime)",
            "CREATE INDEX IF NOT EXISTS idx_analog_tag_name ON analog_records(TagName)"
        };

        foreach (var sql in indexSqls)
        {
            try
            {
                _db!.Ado.ExecuteCommand(sql);
            }
            catch (Exception ex)
            {
                Log.Debug(ex, "创建索引失败: {Sql}", sql);
            }
        }
    }

    public static void Dispose()
    {
        _db?.Dispose();
    }
}