using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using HMI.Core.Util;
using log4net;
using Plugin.DeviceDebug.Entity;

namespace Plugin.DeviceDebug.Model
{
    /// <summary>
    /// 数据访问层 - 管理调试会话和操作审计的持久化
    /// </summary>
    public class DeviceDebugModel
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(DeviceDebugModel));
        private SqliteHelper? _sqliteHelper;
        private string _dbPath = string.Empty;

        /// <summary>
        /// 初始化数据库，创建必要的表结构
        /// </summary>
        public void InitDatabase(string dbPath)
        {
            try
            {
                _dbPath = dbPath;

                // 确保目录存在
                string? dir = Path.GetDirectoryName(dbPath);
                if (!string.IsNullOrEmpty(dir) && !Directory.Exists(dir))
                {
                    Directory.CreateDirectory(dir);
                }

                // 如果数据库文件不存在，创建它
                if (!File.Exists(dbPath))
                {
                    SqliteHelper.CreateDatabase(dbPath);
                    log.Info($"创建调试数据库: {dbPath}");
                }

                _sqliteHelper = new SqliteHelper(dbPath);

                // 创建 debug_session 表
                string createSessionTable = @"
                    CREATE TABLE IF NOT EXISTS debug_session (
                        session_id TEXT PRIMARY KEY,
                        device_id TEXT NOT NULL,
                        operator_id TEXT NOT NULL,
                        operator_name TEXT,
                        effective_version TEXT,
                        state INTEGER NOT NULL DEFAULT 0,
                        reservation_ref TEXT,
                        created_at TEXT NOT NULL,
                        closed_at TEXT
                    )";

                // 创建 debug_operation 表
                string createOperationTable = @"
                    CREATE TABLE IF NOT EXISTS debug_operation (
                        operation_id TEXT PRIMARY KEY,
                        session_id TEXT NOT NULL,
                        command_type TEXT NOT NULL,
                        parameters TEXT,
                        outcome TEXT NOT NULL,
                        device_readback TEXT,
                        error_code TEXT,
                        error_message TEXT,
                        timestamp TEXT NOT NULL,
                        FOREIGN KEY (session_id) REFERENCES debug_session(session_id)
                    )";

                _sqliteHelper.ExecuteSql(createSessionTable);
                _sqliteHelper.ExecuteSql(createOperationTable);

                log.Info("调试数据库表初始化完成");
            }
            catch (Exception ex)
            {
                log.Error($"初始化调试数据库失败: {ex.Message}", ex);
                throw;
            }
        }

        #region 会话 CRUD

        /// <summary>
        /// 创建会话
        /// </summary>
        public void CreateSession(DebugSession session)
        {
            try
            {
                if (_sqliteHelper == null)
                    throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");

                string sql = $@"
                    INSERT INTO debug_session (
                        session_id, device_id, operator_id, operator_name, 
                        effective_version, state, reservation_ref, created_at, closed_at
                    ) VALUES (
                        '{EscapeSql(session.SessionId)}',
                        '{EscapeSql(session.DeviceId)}',
                        '{EscapeSql(session.OperatorId)}',
                        '{EscapeSql(session.OperatorName)}',
                        '{EscapeSql(session.EffectiveVersion)}',
                        {(int)session.State},
                        '{EscapeSql(session.ReservationRef)}',
                        '{session.CreatedAt:yyyy-MM-dd HH:mm:ss}',
                        {(session.ClosedAt.HasValue ? $"'{session.ClosedAt.Value:yyyy-MM-dd HH:mm:ss}'" : "NULL")}
                    )";

                _sqliteHelper.ExecuteSql(sql);
                log.Debug($"创建会话: {session.SessionId}, 设备: {session.DeviceId}");
            }
            catch (Exception ex)
            {
                log.Error($"创建会话失败: {ex.Message}", ex);
                throw;
            }
        }

        /// <summary>
        /// 获取会话
        /// </summary>
        public DebugSession? GetSession(string sessionId)
        {
            try
            {
                if (_sqliteHelper == null)
                    throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");

                string sql = $"SELECT * FROM debug_session WHERE session_id = '{EscapeSql(sessionId)}'";
                DataTable dt = _sqliteHelper.GetDataTable(sql);

                if (dt.Rows.Count == 0)
                    return null;

                return MapToDebugSession(dt.Rows[0]);
            }
            catch (Exception ex)
            {
                log.Error($"获取会话失败: {ex.Message}", ex);
                return null;
            }
        }

        /// <summary>
        /// 更新会话
        /// </summary>
        public void UpdateSession(DebugSession session)
        {
            try
            {
                if (_sqliteHelper == null)
                    throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");

                string sql = $@"
                    UPDATE debug_session SET
                        device_id = '{EscapeSql(session.DeviceId)}',
                        operator_id = '{EscapeSql(session.OperatorId)}',
                        operator_name = '{EscapeSql(session.OperatorName)}',
                        effective_version = '{EscapeSql(session.EffectiveVersion)}',
                        state = {(int)session.State},
                        reservation_ref = '{EscapeSql(session.ReservationRef)}',
                        closed_at = {(session.ClosedAt.HasValue ? $"'{session.ClosedAt.Value:yyyy-MM-dd HH:mm:ss}'" : "NULL")}
                    WHERE session_id = '{EscapeSql(session.SessionId)}'";

                _sqliteHelper.ExecuteSql(sql);
                log.Debug($"更新会话: {session.SessionId}, 状态: {session.State}");
            }
            catch (Exception ex)
            {
                log.Error($"更新会话失败: {ex.Message}", ex);
                throw;
            }
        }

        /// <summary>
        /// 获取设备的所有活跃会话（未关闭的）
        /// </summary>
        public List<DebugSession> GetActiveSessionsByDevice(string deviceId)
        {
            try
            {
                if (_sqliteHelper == null)
                    throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");

                string sql = $@"
                    SELECT * FROM debug_session 
                    WHERE device_id = '{EscapeSql(deviceId)}' 
                    AND state != {(int)DebugSessionState.Closed}
                    ORDER BY created_at DESC";

                DataTable dt = _sqliteHelper.GetDataTable(sql);
                return dt.Rows.Cast<DataRow>().Select(MapToDebugSession).ToList();
            }
            catch (Exception ex)
            {
                log.Error($"获取设备活跃会话失败: {ex.Message}", ex);
                return new List<DebugSession>();
            }
        }

        /// <summary>
        /// 获取所有会话
        /// </summary>
        public List<DebugSession> GetAllSessions()
        {
            try
            {
                if (_sqliteHelper == null)
                    throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");

                string sql = "SELECT * FROM debug_session ORDER BY created_at DESC";
                DataTable dt = _sqliteHelper.GetDataTable(sql);
                return dt.Rows.Cast<DataRow>().Select(MapToDebugSession).ToList();
            }
            catch (Exception ex)
            {
                log.Error($"获取所有会话失败: {ex.Message}", ex);
                return new List<DebugSession>();
            }
        }

        #endregion

        #region 审计 CRUD

        /// <summary>
        /// 记录操作
        /// </summary>
        public void RecordOperation(DebugOperation operation)
        {
            try
            {
                if (_sqliteHelper == null)
                    throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");

                string sql = $@"
                    INSERT INTO debug_operation (
                        operation_id, session_id, command_type, parameters,
                        outcome, device_readback, error_code, error_message, timestamp
                    ) VALUES (
                        '{EscapeSql(operation.OperationId)}',
                        '{EscapeSql(operation.SessionId)}',
                        '{EscapeSql(operation.CommandType)}',
                        '{EscapeSql(operation.Parameters)}',
                        '{EscapeSql(operation.Outcome)}',
                        '{EscapeSql(operation.DeviceReadback)}',
                        '{EscapeSql(operation.ErrorCode)}',
                        '{EscapeSql(operation.ErrorMessage)}',
                        '{operation.Timestamp:yyyy-MM-dd HH:mm:ss}'
                    )";

                _sqliteHelper.ExecuteSql(sql);
                log.Debug($"记录操作: {operation.OperationId}, 类型: {operation.CommandType}, 结果: {operation.Outcome}");
            }
            catch (Exception ex)
            {
                log.Error($"记录操作失败: {ex.Message}", ex);
                throw;
            }
        }

        /// <summary>
        /// 获取会话的所有操作
        /// </summary>
        public List<DebugOperation> GetOperationsBySession(string sessionId)
        {
            try
            {
                if (_sqliteHelper == null)
                    throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");

                string sql = $@"
                    SELECT * FROM debug_operation 
                    WHERE session_id = '{EscapeSql(sessionId)}'
                    ORDER BY timestamp DESC";

                DataTable dt = _sqliteHelper.GetDataTable(sql);
                return dt.Rows.Cast<DataRow>().Select(MapToDebugOperation).ToList();
            }
            catch (Exception ex)
            {
                log.Error($"获取会话操作失败: {ex.Message}", ex);
                return new List<DebugOperation>();
            }
        }

        /// <summary>
        /// 获取设备最近的操作
        /// </summary>
        public List<DebugOperation> GetRecentOperations(string deviceId, int count)
        {
            try
            {
                if (_sqliteHelper == null)
                    throw new InvalidOperationException("数据库未初始化，请先调用 InitDatabase");

                string sql = $@"
                    SELECT o.* FROM debug_operation o
                    INNER JOIN debug_session s ON o.session_id = s.session_id
                    WHERE s.device_id = '{EscapeSql(deviceId)}'
                    ORDER BY o.timestamp DESC
                    LIMIT {count}";

                DataTable dt = _sqliteHelper.GetDataTable(sql);
                return dt.Rows.Cast<DataRow>().Select(MapToDebugOperation).ToList();
            }
            catch (Exception ex)
            {
                log.Error($"获取最近操作失败: {ex.Message}", ex);
                return new List<DebugOperation>();
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        /// 将 DataRow 映射为 DebugSession
        /// </summary>
        private DebugSession MapToDebugSession(DataRow row)
        {
            return new DebugSession
            {
                SessionId = row["session_id"].ToString() ?? string.Empty,
                DeviceId = row["device_id"].ToString() ?? string.Empty,
                OperatorId = row["operator_id"].ToString() ?? string.Empty,
                OperatorName = row["operator_name"]?.ToString() ?? string.Empty,
                EffectiveVersion = row["effective_version"]?.ToString() ?? string.Empty,
                State = (DebugSessionState)Convert.ToInt32(row["state"]),
                ReservationRef = row["reservation_ref"]?.ToString() ?? string.Empty,
                CreatedAt = DateTime.Parse(row["created_at"].ToString() ?? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                ClosedAt = row["closed_at"] != DBNull.Value && !string.IsNullOrEmpty(row["closed_at"].ToString())
                    ? DateTime.Parse(row["closed_at"].ToString()!)
                    : null
            };
        }

        /// <summary>
        /// 将 DataRow 映射为 DebugOperation
        /// </summary>
        private DebugOperation MapToDebugOperation(DataRow row)
        {
            return new DebugOperation
            {
                OperationId = row["operation_id"].ToString() ?? string.Empty,
                SessionId = row["session_id"].ToString() ?? string.Empty,
                CommandType = row["command_type"].ToString() ?? string.Empty,
                Parameters = row["parameters"]?.ToString() ?? string.Empty,
                Outcome = row["outcome"].ToString() ?? string.Empty,
                DeviceReadback = row["device_readback"]?.ToString() ?? string.Empty,
                ErrorCode = row["error_code"]?.ToString() ?? string.Empty,
                ErrorMessage = row["error_message"]?.ToString() ?? string.Empty,
                Timestamp = DateTime.Parse(row["timestamp"].ToString() ?? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
            };
        }

        /// <summary>
        /// SQL 字符串转义（防止注入）
        /// </summary>
        private string EscapeSql(string value)
        {
            return value.Replace("'", "''");
        }

        #endregion
    }
}