using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using HMI.Core.Util;
using Plugin.DeviceConfig.Entity;

namespace Plugin.DeviceConfig.Model
{
    /// <summary>
    /// 设备配置数据访问层，基于 SQLite 实现
    /// </summary>
    public class DeviceConfigModel
    {
        private string _dbPath = string.Empty;

        /// <summary>
        /// 初始化数据库，创建所有表
        /// </summary>
        /// <param name="dbPath">数据库文件路径</param>
        public void InitDatabase(string dbPath)
        {
            _dbPath = dbPath;
            try
            {
                // 确保目录存在
                string? dir = System.IO.Path.GetDirectoryName(dbPath);
                if (!string.IsNullOrEmpty(dir) && !System.IO.Directory.Exists(dir))
                {
                    System.IO.Directory.CreateDirectory(dir);
                }

                // 仅在数据库文件不存在时创建（避免重启后抛异常或丢失已有数据）
                if (!System.IO.File.Exists(dbPath))
                {
                    SqliteHelper.CreateDatabase(dbPath);
                }

                using var helper = new SqliteHelper(dbPath);

                var sqls = new List<string>
                {
                    // 设备定义表
                    @"CREATE TABLE IF NOT EXISTS DeviceDefinition (
                        DeviceId TEXT PRIMARY KEY,
                        Kind TEXT NOT NULL,
                        DriverType TEXT NOT NULL,
                        Transport TEXT NOT NULL,
                        Endpoint TEXT NOT NULL,
                        EffectiveVersion TEXT NOT NULL DEFAULT '',
                        CreatedAt TEXT NOT NULL,
                        UpdatedAt TEXT NOT NULL
                    );",

                    // 设备通道表
                    @"CREATE TABLE IF NOT EXISTS DeviceChannel (
                        DeviceId TEXT NOT NULL,
                        ChannelId INTEGER NOT NULL,
                        Capability TEXT NOT NULL DEFAULT '',
                        MaxVoltage REAL NOT NULL DEFAULT 0,
                        MaxCurrent REAL NOT NULL DEFAULT 0,
                        Limits TEXT NOT NULL DEFAULT '{}',
                        SessionKey TEXT NOT NULL DEFAULT '',
                        PRIMARY KEY (DeviceId, ChannelId)
                    );",

                    // Slot映射表
                    @"CREATE TABLE IF NOT EXISTS SlotDeviceMapping (
                        SlotId TEXT NOT NULL,
                        Capability TEXT NOT NULL,
                        Version TEXT NOT NULL,
                        DeviceId TEXT NOT NULL,
                        ChannelId INTEGER NOT NULL DEFAULT 0,
                        MappingVersion TEXT NOT NULL DEFAULT '',
                        PRIMARY KEY (SlotId, Capability, Version)
                    );",

                    // 配置版本表
                    @"CREATE TABLE IF NOT EXISTS DeviceConfigVersion (
                        VersionId TEXT PRIMARY KEY,
                        Status TEXT NOT NULL,
                        Hash TEXT NOT NULL DEFAULT '',
                        Author TEXT NOT NULL DEFAULT '',
                        Timestamp TEXT NOT NULL,
                        DiffDescription TEXT NOT NULL DEFAULT ''
                    );",

                    // 设备组模板表
                    @"CREATE TABLE IF NOT EXISTS DeviceGroupTemplate (
                        TemplateId TEXT PRIMARY KEY,
                        DeviceType TEXT NOT NULL DEFAULT '',
                        DriverType TEXT NOT NULL DEFAULT '',
                        Transport TEXT NOT NULL DEFAULT '',
                        DeviceCount INTEGER NOT NULL DEFAULT 0,
                        ChannelsPerDevice INTEGER NOT NULL DEFAULT 0,
                        AddressRule TEXT NOT NULL DEFAULT '',
                        SlotStart INTEGER NOT NULL DEFAULT 0
                    );"
                };

                helper.ExecuteBatchSql(sqls);
            }
            catch (Exception ex)
            {
                throw new Exception($"初始化设备配置数据库失败: {ex.Message}", ex);
            }
        }

        #region DeviceDefinition CRUD

        /// <summary>
        /// 获取所有设备定义
        /// </summary>
        public List<DeviceDefinition> GetAllDevices()
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var dt = helper.GetDataTable("SELECT * FROM DeviceDefinition ORDER BY CreatedAt DESC;");
                return MapToDeviceList(dt);
            }
            catch (Exception ex)
            {
                throw new Exception($"获取所有设备定义失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据ID获取设备定义
        /// </summary>
        public DeviceDefinition? GetDeviceById(string deviceId)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var dt = helper.GetDataTable($"SELECT * FROM DeviceDefinition WHERE DeviceId = '{Escape(deviceId)}';");
                var list = MapToDeviceList(dt);
                return list.Count > 0 ? list[0] : null;
            }
            catch (Exception ex)
            {
                throw new Exception($"获取设备定义[{deviceId}]失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 保存设备定义（新增或更新）
        /// </summary>
        public void SaveDevice(DeviceDefinition device)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                device.UpdatedAt = DateTime.Now;

                var sql = $@"INSERT OR REPLACE INTO DeviceDefinition 
                    (DeviceId, Kind, DriverType, Transport, Endpoint, EffectiveVersion, CreatedAt, UpdatedAt) 
                    VALUES (
                        '{Escape(device.DeviceId)}',
                        '{device.Kind}',
                        '{Escape(device.DriverType)}',
                        '{device.Transport}',
                        '{Escape(device.Endpoint)}',
                        '{Escape(device.EffectiveVersion)}',
                        '{device.CreatedAt:yyyy-MM-dd HH:mm:ss}',
                        '{device.UpdatedAt:yyyy-MM-dd HH:mm:ss}'
                    );";
                helper.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                throw new Exception($"保存设备定义[{device.DeviceId}]失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除设备定义
        /// </summary>
        public void DeleteDevice(string deviceId)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                helper.ExecuteSql($"DELETE FROM DeviceDefinition WHERE DeviceId = '{Escape(deviceId)}';");
            }
            catch (Exception ex)
            {
                throw new Exception($"删除设备定义[{deviceId}]失败: {ex.Message}", ex);
            }
        }

        private List<DeviceDefinition> MapToDeviceList(DataTable dt)
        {
            var list = new List<DeviceDefinition>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new DeviceDefinition
                {
                    DeviceId = row["DeviceId"].ToString() ?? string.Empty,
                    Kind = Enum.TryParse<DeviceKind>(row["Kind"].ToString(), out var kind) ? kind : DeviceKind.PowerSupply,
                    DriverType = row["DriverType"].ToString() ?? string.Empty,
                    Transport = Enum.TryParse<TransportType>(row["Transport"].ToString(), out var tp) ? tp : TransportType.Tcp,
                    Endpoint = row["Endpoint"].ToString() ?? string.Empty,
                    EffectiveVersion = row["EffectiveVersion"].ToString() ?? string.Empty,
                    CreatedAt = DateTime.TryParse(row["CreatedAt"].ToString(), out var ca) ? ca : DateTime.Now,
                    UpdatedAt = DateTime.TryParse(row["UpdatedAt"].ToString(), out var ua) ? ua : DateTime.Now,
                });
            }
            return list;
        }

        #endregion

        #region DeviceChannel CRUD

        /// <summary>
        /// 获取指定设备的所有通道
        /// </summary>
        public List<DeviceChannel> GetChannelsByDevice(string deviceId)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var dt = helper.GetDataTable($"SELECT * FROM DeviceChannel WHERE DeviceId = '{Escape(deviceId)}' ORDER BY ChannelId;");
                return MapToChannelList(dt);
            }
            catch (Exception ex)
            {
                throw new Exception($"获取设备[{deviceId}]通道失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 保存设备通道（新增或更新）
        /// </summary>
        public void SaveChannel(DeviceChannel channel)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var sql = $@"INSERT OR REPLACE INTO DeviceChannel 
                    (DeviceId, ChannelId, Capability, MaxVoltage, MaxCurrent, Limits, SessionKey) 
                    VALUES (
                        '{Escape(channel.DeviceId)}',
                        {channel.ChannelId},
                        '{Escape(channel.Capability)}',
                        {channel.MaxVoltage},
                        {channel.MaxCurrent},
                        '{Escape(channel.Limits)}',
                        '{Escape(channel.SessionKey)}'
                    );";
                helper.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                throw new Exception($"保存通道[{channel.DeviceId}:{channel.ChannelId}]失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除设备通道
        /// </summary>
        public void DeleteChannel(string deviceId, int channelId)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                helper.ExecuteSql($"DELETE FROM DeviceChannel WHERE DeviceId = '{Escape(deviceId)}' AND ChannelId = {channelId};");
            }
            catch (Exception ex)
            {
                throw new Exception($"删除通道[{deviceId}:{channelId}]失败: {ex.Message}", ex);
            }
        }

        private List<DeviceChannel> MapToChannelList(DataTable dt)
        {
            var list = new List<DeviceChannel>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new DeviceChannel
                {
                    DeviceId = row["DeviceId"].ToString() ?? string.Empty,
                    ChannelId = Convert.ToInt32(row["ChannelId"]),
                    Capability = row["Capability"].ToString() ?? string.Empty,
                    MaxVoltage = Convert.ToDouble(row["MaxVoltage"]),
                    MaxCurrent = Convert.ToDouble(row["MaxCurrent"]),
                    Limits = row["Limits"].ToString() ?? "{}",
                    SessionKey = row["SessionKey"].ToString() ?? string.Empty,
                });
            }
            return list;
        }

        #endregion

        #region SlotDeviceMapping CRUD

        /// <summary>
        /// 获取所有Slot映射
        /// </summary>
        public List<SlotDeviceMapping> GetSlotMappings()
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var dt = helper.GetDataTable("SELECT * FROM SlotDeviceMapping ORDER BY SlotId;");
                return MapToSlotMappingList(dt);
            }
            catch (Exception ex)
            {
                throw new Exception($"获取Slot映射失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 保存Slot映射（新增或更新）
        /// </summary>
        public void SaveSlotMapping(SlotDeviceMapping mapping)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var sql = $@"INSERT OR REPLACE INTO SlotDeviceMapping 
                    (SlotId, Capability, Version, DeviceId, ChannelId, MappingVersion) 
                    VALUES (
                        '{Escape(mapping.SlotId)}',
                        '{Escape(mapping.Capability)}',
                        '{Escape(mapping.Version)}',
                        '{Escape(mapping.DeviceId)}',
                        {mapping.ChannelId},
                        '{Escape(mapping.MappingVersion)}'
                    );";
                helper.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                throw new Exception($"保存Slot映射[{mapping.SlotId}]失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除Slot映射
        /// </summary>
        public void DeleteSlotMapping(string slotId, string capability, string version)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                helper.ExecuteSql($@"DELETE FROM SlotDeviceMapping 
                    WHERE SlotId = '{Escape(slotId)}' 
                    AND Capability = '{Escape(capability)}' 
                    AND Version = '{Escape(version)}';");
            }
            catch (Exception ex)
            {
                throw new Exception($"删除Slot映射[{slotId}:{capability}:{version}]失败: {ex.Message}", ex);
            }
        }

        private List<SlotDeviceMapping> MapToSlotMappingList(DataTable dt)
        {
            var list = new List<SlotDeviceMapping>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new SlotDeviceMapping
                {
                    SlotId = row["SlotId"].ToString() ?? string.Empty,
                    Capability = row["Capability"].ToString() ?? string.Empty,
                    Version = row["Version"].ToString() ?? string.Empty,
                    DeviceId = row["DeviceId"].ToString() ?? string.Empty,
                    ChannelId = Convert.ToInt32(row["ChannelId"]),
                    MappingVersion = row["MappingVersion"].ToString() ?? string.Empty,
                });
            }
            return list;
        }

        #endregion

        #region DeviceConfigVersion CRUD

        /// <summary>
        /// 获取所有配置版本
        /// </summary>
        public List<DeviceConfigVersion> GetConfigVersions()
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var dt = helper.GetDataTable("SELECT * FROM DeviceConfigVersion ORDER BY Timestamp DESC;");
                return MapToVersionList(dt);
            }
            catch (Exception ex)
            {
                throw new Exception($"获取配置版本失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 保存配置版本（新增或更新）
        /// </summary>
        public void SaveConfigVersion(DeviceConfigVersion version)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var sql = $@"INSERT OR REPLACE INTO DeviceConfigVersion 
                    (VersionId, Status, Hash, Author, Timestamp, DiffDescription) 
                    VALUES (
                        '{Escape(version.VersionId)}',
                        '{version.Status}',
                        '{Escape(version.Hash)}',
                        '{Escape(version.Author)}',
                        '{version.Timestamp:yyyy-MM-dd HH:mm:ss}',
                        '{Escape(version.DiffDescription)}'
                    );";
                helper.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                throw new Exception($"保存配置版本[{version.VersionId}]失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 更新版本状态
        /// </summary>
        public void UpdateVersionStatus(string versionId, string newStatus)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                helper.ExecuteSql($@"UPDATE DeviceConfigVersion 
                    SET Status = '{Escape(newStatus)}' 
                    WHERE VersionId = '{Escape(versionId)}';");
            }
            catch (Exception ex)
            {
                throw new Exception($"更新版本状态[{versionId}]失败: {ex.Message}", ex);
            }
        }

        private List<DeviceConfigVersion> MapToVersionList(DataTable dt)
        {
            var list = new List<DeviceConfigVersion>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new DeviceConfigVersion
                {
                    VersionId = row["VersionId"].ToString() ?? string.Empty,
                    Status = Enum.TryParse<VersionStatus>(row["Status"].ToString(), out var st) ? st : VersionStatus.Draft,
                    Hash = row["Hash"].ToString() ?? string.Empty,
                    Author = row["Author"].ToString() ?? string.Empty,
                    Timestamp = DateTime.TryParse(row["Timestamp"].ToString(), out var ts) ? ts : DateTime.Now,
                    DiffDescription = row["DiffDescription"].ToString() ?? string.Empty,
                });
            }
            return list;
        }

        #endregion

        #region DeviceGroupTemplate CRUD

        /// <summary>
        /// 获取所有设备组模板
        /// </summary>
        public List<DeviceGroupTemplate> GetGroupTemplates()
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var dt = helper.GetDataTable("SELECT * FROM DeviceGroupTemplate ORDER BY DeviceType;");
                return MapToTemplateList(dt);
            }
            catch (Exception ex)
            {
                throw new Exception($"获取设备组模板失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 保存设备组模板（新增或更新）
        /// </summary>
        public void SaveGroupTemplate(DeviceGroupTemplate template)
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);
                var sql = $@"INSERT OR REPLACE INTO DeviceGroupTemplate 
                    (TemplateId, DeviceType, DriverType, Transport, DeviceCount, ChannelsPerDevice, AddressRule, SlotStart) 
                    VALUES (
                        '{Escape(template.TemplateId)}',
                        '{Escape(template.DeviceType)}',
                        '{Escape(template.DriverType)}',
                        '{Escape(template.Transport)}',
                        {template.DeviceCount},
                        {template.ChannelsPerDevice},
                        '{Escape(template.AddressRule)}',
                        {template.SlotStart}
                    );";
                helper.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                throw new Exception($"保存设备组模板[{template.TemplateId}]失败: {ex.Message}", ex);
            }
        }

        private List<DeviceGroupTemplate> MapToTemplateList(DataTable dt)
        {
            var list = new List<DeviceGroupTemplate>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new DeviceGroupTemplate
                {
                    TemplateId = row["TemplateId"].ToString() ?? string.Empty,
                    DeviceType = row["DeviceType"].ToString() ?? string.Empty,
                    DriverType = row["DriverType"].ToString() ?? string.Empty,
                    Transport = row["Transport"].ToString() ?? string.Empty,
                    DeviceCount = Convert.ToInt32(row["DeviceCount"]),
                    ChannelsPerDevice = Convert.ToInt32(row["ChannelsPerDevice"]),
                    AddressRule = row["AddressRule"].ToString() ?? string.Empty,
                    SlotStart = Convert.ToInt32(row["SlotStart"]),
                });
            }
            return list;
        }

        #endregion

        #region 冲突检测与哈希计算

        /// <summary>
        /// 检测配置冲突：IP冲突、Slot映射重复、通道越界等
        /// </summary>
        /// <returns>冲突描述列表，为空则表示无冲突</returns>
        public List<string> DetectConflicts()
        {
            var conflicts = new List<string>();
            try
            {
                using var helper = new SqliteHelper(_dbPath);

                // 1. 检测IP/Endpoint冲突（相同Endpoint的不同设备）
                var ipSql = @"SELECT Endpoint, COUNT(*) as Cnt 
                    FROM DeviceDefinition 
                    WHERE Transport = 'Tcp' 
                    GROUP BY Endpoint 
                    HAVING Cnt > 1;";
                var ipDt = helper.GetDataTable(ipSql);
                foreach (DataRow row in ipDt.Rows)
                {
                    conflicts.Add($"IP/Endpoint冲突: {row["Endpoint"]} 被 {row["Cnt"]} 个设备使用");
                }

                // 2. 检测Slot映射重复（相同Slot+Capability+Version映射到不同设备）
                var slotSql = @"SELECT SlotId, Capability, Version, COUNT(*) as Cnt 
                    FROM SlotDeviceMapping 
                    GROUP BY SlotId, Capability, Version 
                    HAVING Cnt > 1;";
                var slotDt = helper.GetDataTable(slotSql);
                foreach (DataRow row in slotDt.Rows)
                {
                    conflicts.Add($"Slot映射重复: Slot={row["SlotId"]}, Capability={row["Capability"]}, Version={row["Version"]}");
                }

                // 3. 检测通道越界（MaxVoltage或MaxCurrent为负数）
                var channelSql = @"SELECT DeviceId, ChannelId, MaxVoltage, MaxCurrent 
                    FROM DeviceChannel 
                    WHERE MaxVoltage < 0 OR MaxCurrent < 0;";
                var channelDt = helper.GetDataTable(channelSql);
                foreach (DataRow row in channelDt.Rows)
                {
                    conflicts.Add($"通道参数越界: 设备={row["DeviceId"]}, 通道={row["ChannelId"]}, " +
                                  $"电压={row["MaxVoltage"]}, 电流={row["MaxCurrent"]}");
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"冲突检测失败: {ex.Message}", ex);
            }

            return conflicts;
        }

        /// <summary>
        /// 计算当前配置的SHA256哈希
        /// </summary>
        /// <returns>SHA256哈希字符串（小写十六进制）</returns>
        public string ComputeConfigHash()
        {
            try
            {
                using var helper = new SqliteHelper(_dbPath);

                // 拼接所有设备定义的关键信息作为哈希输入
                var sb = new StringBuilder();

                var deviceDt = helper.GetDataTable("SELECT * FROM DeviceDefinition ORDER BY DeviceId;");
                foreach (DataRow row in deviceDt.Rows)
                {
                    sb.Append($"DEVICE:{row["DeviceId"]}|{row["Kind"]}|{row["DriverType"]}|{row["Transport"]}|{row["Endpoint"]};");
                }

                var channelDt = helper.GetDataTable("SELECT * FROM DeviceChannel ORDER BY DeviceId, ChannelId;");
                foreach (DataRow row in channelDt.Rows)
                {
                    sb.Append($"CHANNEL:{row["DeviceId"]}|{row["ChannelId"]}|{row["Capability"]}|{row["MaxVoltage"]}|{row["MaxCurrent"]};");
                }

                var slotDt = helper.GetDataTable("SELECT * FROM SlotDeviceMapping ORDER BY SlotId, Capability, Version;");
                foreach (DataRow row in slotDt.Rows)
                {
                    sb.Append($"SLOT:{row["SlotId"]}|{row["Capability"]}|{row["Version"]}|{row["DeviceId"]}|{row["ChannelId"]};");
                }

                using var sha256 = SHA256.Create();
                var hashBytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(sb.ToString()));
                return BitConverter.ToString(hashBytes).Replace("-", "").ToLowerInvariant();
            }
            catch (Exception ex)
            {
                throw new Exception($"计算配置哈希失败: {ex.Message}", ex);
            }
        }

        #endregion

        #region 辅助方法

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

        #endregion
    }
}