using DeviceService.Core;
using DeviceService.Logging;
using System.Data.SQLite;
using System.IO;

namespace DeviceService.Device
{
    /// <summary>设备记录（Device 表的一行）。</summary>
    public sealed class DeviceRecord
    {
        /// <summary>自增主键，对应设备实例的 Id；0 表示尚未入库。</summary>
        public long Id { get; set; }

        /// <summary>设备业务标识（唯一），对应设备实例的 DeviceId。</summary>
        public string DeviceId { get; set; } = string.Empty;

        public string Type { get; set; } = string.Empty;

        public string Brand { get; set; } = string.Empty;

        public string ConnectStr { get; set; } = string.Empty;

        public AccessType AccessType { get; set; }
    }

    /// <summary>
    /// 设备信息 SQLite 持久化：数据库文件位于 应用根目录\Config\Devices.db。
    ///
    /// 表结构：
    /// - Device：Id 为自增主键，DeviceId 为唯一业务标识，Type 用于定位设备实现类
    ///
    /// 所有操作失败仅通过 log 上报，不会中断设备加载流程。
    /// </summary>
    public sealed class DeviceDatabase
    {
        private const string CreateTableSql = @"
CREATE TABLE IF NOT EXISTS Device (
    Id         INTEGER PRIMARY KEY AUTOINCREMENT,
    DeviceId   TEXT NOT NULL,
    Type       TEXT NOT NULL,
    Brand      TEXT,
    ConnectStr TEXT,
    AccessType INTEGER NOT NULL DEFAULT 0
);";

        private static readonly object _staticSync = new object();
        private static DeviceDatabase? _instance;

        public static DeviceDatabase Instance
        {
            get
            {
                lock (_staticSync)
                {
                    return _instance ??= new DeviceDatabase();
                }
            }
        }

        /// <summary>配置目录：应用根目录\Config。</summary>
        public static string ConfigDirectory =>
            Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Config");

        /// <summary>数据库文件路径。</summary>
        public static string DatabasePath =>
            Path.Combine(ConfigDirectory, "Devices.db");

        private readonly object _sync = new object();
        private bool _initialized;

        private DeviceDatabase()
        {
        }

        /// <summary>确保 Config 目录与库表结构存在。失败返回 false 并通过 log 上报。</summary>
        private bool EnsureReady()
        {
            if (_initialized)
            {
                return true;
            }

            try
            {
                if (!Directory.Exists(ConfigDirectory))
                {
                    Directory.CreateDirectory(ConfigDirectory);
                }

                using (var conn = Open())
                {
                    ExecuteNonQuery(conn, CreateTableSql, null);

                    // 旧库的结构是 DeviceId 主键，升级为 Id 自增主键
                    MigrateToAutoIncrementId(conn);

                    ExecuteNonQuery(conn,
                        "CREATE UNIQUE INDEX IF NOT EXISTS IX_Device_DeviceId ON Device(DeviceId);",
                        null);
                }

                _initialized = true;
                return true;
            }
            catch (Exception ex)
            {
                GlobalLogger.Logger.Error("DeviceManager", $"[设备库] 初始化失败（{DatabasePath}）: {ex.Message}");
                return false;
            }
        }

        private SQLiteConnection Open()
        {
            var conn = new SQLiteConnection($"Data Source={DatabasePath};Version=3;");
            conn.Open();
            return conn;
        }

        /// <summary>读取库中全部设备记录（按 Type、DeviceId 排序）。</summary>
        public IReadOnlyList<DeviceRecord> LoadAll()
        {
            var result = new List<DeviceRecord>();
            if (!EnsureReady())
            {
                return result;
            }

            try
            {
                lock (_sync)
                {
                    using (var conn = Open())
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText =
                            "SELECT * FROM Device ORDER BY Type, DeviceId";
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                result.Add(new DeviceRecord
                                {
                                    Id = GetInt64(reader, "Id"),
                                    DeviceId = GetString(reader, "DeviceId"),
                                    Type = GetString(reader, "Type"),
                                    Brand = GetString(reader, "Brand"),
                                    ConnectStr = GetString(reader, "ConnectStr"),
                                    AccessType = (AccessType)GetInt32(reader, "AccessType")
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                GlobalLogger.Logger.Error("DeviceManager", $"[设备库] 读取设备列表失败: {ex.Message}");
            }

            return result;
        }

        /// <summary>
        /// 写入（覆盖）一台设备的记录，返回其自增主键 Id；失败返回 0。
        /// Id 为 0 时新增，否则按 Id 更新。
        /// </summary>
        public long Save(DeviceRecord record, Action<string>? log = null)
        {
            if (record == null || string.IsNullOrWhiteSpace(record.DeviceId))
            {
                return 0;
            }

            if (!EnsureReady())
            {
                return 0;
            }

            try
            {
                lock (_sync)
                {
                    using (var conn = Open())
                    {
                        if (record.Id > 0)
                        {
                            int affected;
                            using (var cmd = conn.CreateCommand())
                            {
                                cmd.CommandText = @"
UPDATE Device SET DeviceId = @id, Type = @t, Brand = @b, ConnectStr = @c, AccessType = @a
WHERE Id = @pk";
                                cmd.Parameters.AddWithValue("@id", record.DeviceId);
                                cmd.Parameters.AddWithValue("@t", record.Type ?? string.Empty);
                                cmd.Parameters.AddWithValue("@b", record.Brand ?? string.Empty);
                                cmd.Parameters.AddWithValue("@c", record.ConnectStr ?? string.Empty);
                                cmd.Parameters.AddWithValue("@a", (int)record.AccessType);
                                cmd.Parameters.AddWithValue("@pk", record.Id);
                                affected = cmd.ExecuteNonQuery();
                            }

                            if (affected > 0)
                            {
                                return record.Id;
                            }
                        }

                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = @"
INSERT INTO Device(DeviceId, Type, Brand, ConnectStr, AccessType)
VALUES(@id, @t, @b, @c, @a)";
                            cmd.Parameters.AddWithValue("@id", record.DeviceId);
                            cmd.Parameters.AddWithValue("@t", record.Type ?? string.Empty);
                            cmd.Parameters.AddWithValue("@b", record.Brand ?? string.Empty);
                            cmd.Parameters.AddWithValue("@c", record.ConnectStr ?? string.Empty);
                            cmd.Parameters.AddWithValue("@a", (int)record.AccessType);
                            cmd.ExecuteNonQuery();
                        }

                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "SELECT last_insert_rowid()";
                            return Convert.ToInt64(cmd.ExecuteScalar());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                log?.Invoke($"[设备库] 保存 {record.DeviceId} 失败: {ex.Message}");
                return 0;
            }
        }

        /// <summary>删除一台设备的记录。</summary>
        public void Remove(string deviceId)
        {
            if (string.IsNullOrEmpty(deviceId) || !EnsureReady())
            {
                return;
            }

            try
            {
                lock (_sync)
                {
                    using (var conn = Open())
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "DELETE FROM Device WHERE DeviceId = @id";
                        cmd.Parameters.AddWithValue("@id", deviceId);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                GlobalLogger.Logger.Error("DeviceManager", $"[设备库] 移除 {deviceId} 失败: {ex.Message}");
            }
        }

        /// <summary>旧库（DeviceId 作主键）升级为 Id 自增主键。</summary>
        private static void MigrateToAutoIncrementId(SQLiteConnection conn)
        {
            var columns = new List<string>();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "PRAGMA table_info(Device)";
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        columns.Add(GetString(reader, "name"));
                    }
                }
            }

            if (columns.Count == 0 || columns.Contains("Id"))
            {
                return;
            }

            lock (_staticSync)
            {
                using (var tx = conn.BeginTransaction())
                {
                    ExecuteNonQuery(conn, "DROP TABLE IF EXISTS Device_old;", tx);
                    ExecuteNonQuery(conn, "ALTER TABLE Device RENAME TO Device_old;", tx);
                    ExecuteNonQuery(conn, CreateTableSql, tx);
                    ExecuteNonQuery(conn, @"
INSERT INTO Device(DeviceId, Type, Brand, ConnectStr, AccessType)
SELECT DeviceId, Type, Brand, ConnectStr, AccessType FROM Device_old;", tx);
                    ExecuteNonQuery(conn, "DROP TABLE Device_old;", tx);
                    tx.Commit();
                }
            }
        }

        private static void ExecuteNonQuery(SQLiteConnection conn, string sql, SQLiteTransaction? tx)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tx;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }

        private static string GetString(SQLiteDataReader reader, string column)
        {
            object value = reader[column];
            return value == DBNull.Value ? string.Empty : value.ToString() ?? string.Empty;
        }

        private static int GetInt32(SQLiteDataReader reader, string column)
        {
            object value = reader[column];
            return value == DBNull.Value ? 0 : Convert.ToInt32(value);
        }

        private static long GetInt64(SQLiteDataReader reader, string column)
        {
            object value = reader[column];
            return value == DBNull.Value ? 0L : Convert.ToInt64(value);
        }
    }
}