TeamAAS-2.5/TeamAAS.Base/TeamAAS.Database/Services/ProductDatabase.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using TeamAAS.Database.Interfaces;
using TeamAAS.Database.Models;
using TeamAAS.Database.Providers;

namespace TeamAAS.Database.Services
{
    /// <summary>
    /// 产品数据库（生产记录 / 运行日志 / 报警历史 / 设备运行数据）。
    /// 跟着产品配方走，每产品一个独立数据库文件；也支持配置为 MySQL 远程库。
    /// </summary>
    public class ProductDatabase : IDisposable
    {
        private readonly IDatabase _db;
        private readonly DatabaseConfig _config;

        /// <summary>
        /// 数据库实例
        /// </summary>
        public IDatabase Database => _db;

        /// <summary>当前数据库配置（供配置页展示/编辑；改动后由 ProductDatabaseManager 重建实例生效）。</summary>
        public DatabaseConfig Config => _config;

        /// <summary>
        /// 是否连接成功
        /// </summary>
        public bool IsConnected => _db?.IsConnected == true;

        /// <summary>
        /// 当前产品名
        /// </summary>
        public string ProductName { get; private set; }

        /// <summary>
        /// 用配置创建产品数据库（支持 SQLite / MySQL）
        /// </summary>
        public ProductDatabase(DatabaseConfig config)
        {
            _config = config ?? throw new ArgumentNullException(nameof(config));
            ProductName = config.Name;

            // 根据 ProviderType 选择实现
            switch ((config.ProviderType ?? "sqlite").ToLowerInvariant())
            {
                case "mysql":
                    _db = new MySqlDatabase();
                    break;
                case "sqlite":
                default:
                    _db = new SqliteDatabase();
                    break;
            }

            _db.Configure(config);
        }

        /// <summary>
        /// 用默认 SQLite 创建产品数据库（按产品名建文件，放产品目录下）
        /// </summary>
        /// <param name="productDir">产品目录（如 Products\ProductA\）</param>
        /// <param name="productName">产品名</param>
        public static ProductDatabase CreateDefaultSqlite(string productDir, string productName)
        {
            var dbPath = Path.Combine(productDir, "product.db");
            var config = new DatabaseConfig
            {
                Id = Guid.NewGuid(),
                Name = productName,
                ProviderType = "sqlite",
                Server = dbPath
            };
            return new ProductDatabase(config);
        }

        /// <summary>
        /// 初始化：打开连接 + 自动建表
        /// </summary>
        public (bool Success, string Message) Initialize()
        {
            try
            {
                // SQLite 自动建目录
                if ((_config.ProviderType ?? "sqlite").ToLowerInvariant() == "sqlite")
                {
                    var filePath = _config.Server;
                    var dir = Path.GetDirectoryName(filePath);
                    if (!string.IsNullOrWhiteSpace(dir) && !Directory.Exists(dir))
                        Directory.CreateDirectory(dir);
                }

                if (!_db.Open())
                    return (false, "产品数据库打开失败");

                CreateTables();
                return (true, "产品数据库初始化成功");
            }
            catch (Exception ex)
            {
                return (false, $"产品数据库初始化失败：{ex.Message}");
            }
        }

        #region 建表

        private void CreateTables()
        {
            var sqls = new List<string>
            {
                // 生产记录
                @"CREATE TABLE IF NOT EXISTS prod_records (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    recipe_name TEXT,
                    batch_no TEXT,
                    serial_no TEXT,
                    result INTEGER DEFAULT 0,
                    start_time TEXT,
                    end_time TEXT,
                    duration_ms INTEGER,
                    operator_name TEXT,
                    remark TEXT,
                    created_at TEXT DEFAULT (datetime('now','localtime'))
                )",

                // 运行日志
                @"CREATE TABLE IF NOT EXISTS run_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    log_level TEXT DEFAULT 'INFO',
                    module TEXT,
                    message TEXT,
                    detail TEXT,
                    created_at TEXT DEFAULT (datetime('now','localtime'))
                )",

                // 报警历史
                @"CREATE TABLE IF NOT EXISTS alarm_history (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    alarm_code TEXT,
                    alarm_name TEXT,
                    alarm_level INTEGER DEFAULT 2,
                    description TEXT,
                    source TEXT,
                    start_time TEXT,
                    end_time TEXT,
                    ack_by TEXT,
                    ack_time TEXT,
                    is_acknowledged INTEGER DEFAULT 0,
                    is_resolved INTEGER DEFAULT 0,
                    created_at TEXT DEFAULT (datetime('now','localtime'))
                )",

                // 设备运行数据（时序型）
                @"CREATE TABLE IF NOT EXISTS device_data (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    device_name TEXT NOT NULL,
                    data_key TEXT NOT NULL,
                    data_value REAL,
                    value_text TEXT,
                    quality INTEGER DEFAULT 1,
                    created_at TEXT DEFAULT (datetime('now','localtime'))
                )",

                // 产品信息表
                @"CREATE TABLE IF NOT EXISTS product_info (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    product_name TEXT NOT NULL UNIQUE,
                    version TEXT,
                    description TEXT,
                    created_at TEXT DEFAULT (datetime('now','localtime')),
                    updated_at TEXT DEFAULT (datetime('now','localtime'))
                )"
            };

            // 索引
            sqls.Add("CREATE INDEX IF NOT EXISTS idx_prod_result ON prod_records(result)");
            sqls.Add("CREATE INDEX IF NOT EXISTS idx_prod_time ON prod_records(start_time)");
            sqls.Add("CREATE INDEX IF NOT EXISTS idx_prod_serial ON prod_records(serial_no)");

            sqls.Add("CREATE INDEX IF NOT EXISTS idx_log_level ON run_logs(log_level)");
            sqls.Add("CREATE INDEX IF NOT EXISTS idx_log_time ON run_logs(created_at)");
            sqls.Add("CREATE INDEX IF NOT EXISTS idx_log_module ON run_logs(module)");

            sqls.Add("CREATE INDEX IF NOT EXISTS idx_alarm_level ON alarm_history(alarm_level)");
            sqls.Add("CREATE INDEX IF NOT EXISTS idx_alarm_time ON alarm_history(start_time)");
            sqls.Add("CREATE INDEX IF NOT EXISTS idx_alarm_resolved ON alarm_history(is_resolved)");

            sqls.Add("CREATE INDEX IF NOT EXISTS idx_device_name ON device_data(device_name)");
            sqls.Add("CREATE INDEX IF NOT EXISTS idx_device_time ON device_data(created_at)");
            sqls.Add("CREATE INDEX IF NOT EXISTS idx_device_key ON device_data(data_key)");

            _db.ExecuteTransaction(sqls);

            // 写入产品信息
            var productInfo = _db.ExecuteScalar(
                "SELECT COUNT(*) FROM product_info WHERE product_name = @n",
                new Dictionary<string, object> {{"@n", ProductName}});
            if (Convert.ToInt32(productInfo) == 0)
            {
                _db.ExecuteNonQuery(
                    "INSERT INTO product_info (product_name, description) VALUES (@n, @d)",
                    new Dictionary<string, object>
                    {
                        {"@n", ProductName},
                        {"@d", $"产品[{ProductName}]的运行数据库"}
                    });
            }
        }

        #endregion

        #region 生产记录

        /// <summary>
        /// 写入一条生产记录（开始）
        /// </summary>
        /// <returns>记录ID</returns>
        public int StartProduction(string recipeName, string batchNo, string serialNo, string operatorName = null)
        {
            var sql = @"INSERT INTO prod_records
                (recipe_name, batch_no, serial_no, start_time, operator_name)
                VALUES (@r, @b, @s, datetime('now','localtime'), @o)";

            var p = new Dictionary<string, object>
            {
                {"@r", (object)recipeName ?? DBNull.Value},
                {"@b", (object)batchNo ?? DBNull.Value},
                {"@s", (object)serialNo ?? DBNull.Value},
                {"@o", (object)operatorName ?? DBNull.Value}
            };

            _db.ExecuteNonQuery(sql, p);
            return Convert.ToInt32(_db.ExecuteScalar("SELECT last_insert_rowid()"));
        }

        /// <summary>
        /// 完成生产记录
        /// </summary>
        public void FinishProduction(int recordId, bool success, string remark = null)
        {
            var sql = @"UPDATE prod_records
                SET result = @r, end_time = datetime('now','localtime'),
                    duration_ms = CAST((julianday('now','localtime') - julianday(start_time)) * 86400000 AS INTEGER),
                    remark = @rk
                WHERE id = @id";

            var p = new Dictionary<string, object>
            {
                {"@id", recordId},
                {"@r", success ? 1 : 0},
                {"@rk", (object)remark ?? DBNull.Value}
            };

            _db.ExecuteNonQuery(sql, p);
        }

        /// <summary>
        /// 查询生产记录统计
        /// </summary>
        public (int Total, int Ok, int Ng, double Yield) GetProductionStats(DateTime? startTime = null, DateTime? endTime = null)
        {
            var conditions = new List<string>();
            var parameters = new Dictionary<string, object>();

            if (startTime.HasValue)
            {
                conditions.Add("start_time >= @st");
                parameters["@st"] = startTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            }
            if (endTime.HasValue)
            {
                conditions.Add("start_time <= @et");
                parameters["@et"] = endTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            }

            var where = conditions.Count > 0 ? "WHERE " + string.Join(" AND ", conditions) : "";

            var total = Convert.ToInt32(
                _db.ExecuteScalar($"SELECT COUNT(*) FROM prod_records {where}", parameters));
            var ok = Convert.ToInt32(
                _db.ExecuteScalar($"SELECT COUNT(*) FROM prod_records {where} AND result = 1",
                    parameters));
            var ng = total - ok;
            var yield = total > 0 ? (double)ok / total * 100 : 0;

            return (total, ok, ng, Math.Round(yield, 2));
        }

        #endregion

        #region 运行日志

        /// <summary>
        /// 写入运行日志
        /// </summary>
        public void WriteLog(string level, string module, string message, string detail = null)
        {
            try
            {
                var sql = @"INSERT INTO run_logs (log_level, module, message, detail)
                    VALUES (@l, @m, @msg, @d)";

                var p = new Dictionary<string, object>
                {
                    {"@l", level ?? "INFO"},
                    {"@m", (object)module ?? DBNull.Value},
                    {"@msg", message ?? ""},
                    {"@d", (object)detail ?? DBNull.Value}
                };

                _db.ExecuteNonQuery(sql, p);
            }
            catch { /* 日志写入失败不抛 */ }
        }

        /// <summary>
        /// 便捷方法：INFO
        /// </summary>
        public void LogInfo(string module, string message, string detail = null)
            => WriteLog("INFO", module, message, detail);

        /// <summary>
        /// 便捷方法：WARN
        /// </summary>
        public void LogWarn(string module, string message, string detail = null)
            => WriteLog("WARN", module, message, detail);

        /// <summary>
        /// 便捷方法：ERROR
        /// </summary>
        public void LogError(string module, string message, string detail = null)
            => WriteLog("ERROR", module, message, detail);

        #endregion

        #region 报警

        /// <summary>
        /// 触发报警
        /// </summary>
        /// <returns>报警记录ID</returns>
        public int RaiseAlarm(string code, string name, int level, string description = null, string source = null)
        {
            var sql = @"INSERT INTO alarm_history
                (alarm_code, alarm_name, alarm_level, description, source, start_time)
                VALUES (@c, @n, @l, @d, @s, datetime('now','localtime'))";

            var p = new Dictionary<string, object>
            {
                {"@c", code},
                {"@n", name},
                {"@l", level},
                {"@d", (object)description ?? DBNull.Value},
                {"@s", (object)source ?? DBNull.Value}
            };

            _db.ExecuteNonQuery(sql, p);
            return Convert.ToInt32(_db.ExecuteScalar("SELECT last_insert_rowid()"));
        }

        /// <summary>
        /// 确认报警
        /// </summary>
        public void AcknowledgeAlarm(int alarmId, string ackBy = null)
        {
            var sql = @"UPDATE alarm_history
                SET is_acknowledged = 1, ack_by = @a, ack_time = datetime('now','localtime')
                WHERE id = @id";

            var p = new Dictionary<string, object>
            {
                {"@id", alarmId},
                {"@a", (object)ackBy ?? DBNull.Value}
            };

            _db.ExecuteNonQuery(sql, p);
        }

        /// <summary>
        /// 消除报警
        /// </summary>
        public void ResolveAlarm(int alarmId)
        {
            var sql = @"UPDATE alarm_history
                SET is_resolved = 1, end_time = datetime('now','localtime')
                WHERE id = @id";

            _db.ExecuteNonQuery(sql, new Dictionary<string, object> {{"@id", alarmId}});
        }

        /// <summary>
        /// 获取未消除报警数
        /// </summary>
        public int GetActiveAlarmCount()
        {
            return Convert.ToInt32(
                _db.ExecuteScalar("SELECT COUNT(*) FROM alarm_history WHERE is_resolved = 0"));
        }

        #endregion

        #region 设备数据

        /// <summary>
        /// 写入设备运行数据
        /// </summary>
        public void WriteDeviceData(string deviceName, string dataKey, double? value = null, string valueText = null, int quality = 1)
        {
            try
            {
                var sql = @"INSERT INTO device_data (device_name, data_key, data_value, value_text, quality)
                    VALUES (@d, @k, @v, @vt, @q)";

                var p = new Dictionary<string, object>
                {
                    {"@d", deviceName},
                    {"@k", dataKey},
                    {"@v", (object)value ?? DBNull.Value},
                    {"@vt", (object)valueText ?? DBNull.Value},
                    {"@q", quality}
                };

                _db.ExecuteNonQuery(sql, p);
            }
            catch { /* 数据写入失败不抛 */ }
        }

        /// <summary>
        /// 批量写入设备数据
        /// </summary>
        public void WriteDeviceDataBatch(IEnumerable<(string deviceName, string key, double? value, string valueText)> dataList)
        {
            if (dataList == null) return;

            var sqls = new List<string>();
            // 简单拼装（内部数据，安全风险低）
            foreach (var d in dataList)
            {
                var v = d.value.HasValue
                    ? d.value.Value.ToString(System.Globalization.CultureInfo.InvariantCulture)
                    : "NULL";
                var vt = d.valueText != null ? $"'{d.valueText.Replace("'", "''")}'" : "NULL";

                sqls.Add($@"INSERT INTO device_data (device_name, data_key, data_value, value_text)
                    VALUES ('{d.deviceName.Replace("'", "''")}', '{d.key.Replace("'", "''")}', {v}, {vt})");
            }

            if (sqls.Count > 0)
                _db.ExecuteTransaction(sqls);
        }

        /// <summary>
        /// 查询设备某数据点的历史
        /// </summary>
        public DataTable QueryDeviceHistory(string deviceName, string dataKey,
            DateTime? startTime = null, DateTime? endTime = null, int limit = 1000)
        {
            var conditions = new List<string> {"device_name = @d", "data_key = @k"};
            var parameters = new Dictionary<string, object>
            {
                {"@d", deviceName},
                {"@k", dataKey}
            };

            if (startTime.HasValue)
            {
                conditions.Add("created_at >= @st");
                parameters["@st"] = startTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            }
            if (endTime.HasValue)
            {
                conditions.Add("created_at <= @et");
                parameters["@et"] = endTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
            }

            var where = "WHERE " + string.Join(" AND ", conditions);
            var sql = $@"
                SELECT * FROM device_data {where}
                ORDER BY id DESC
                LIMIT @lim";

            parameters["@lim"] = limit;

            var dt = _db.ExecuteQuery(sql, parameters);
            // 反序变成时间正序
            var result = dt.Clone();
            for (int i = dt.Rows.Count - 1; i >= 0; i--)
            {
                result.ImportRow(dt.Rows[i]);
            }
            return result;
        }

        #endregion

        #region IDisposable

        private bool _disposed;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (_disposed) return;
            if (disposing)
            {
                try { _db?.Dispose(); } catch { }
            }
            _disposed = true;
        }

        ~ProductDatabase()
        {
            Dispose(false);
        }

        #endregion
    }
}