using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using Autodesk.Navisworks.Api;
using NavisworksTransport.Core.Config;

namespace NavisworksTransport
{
    /// <summary>
    /// 路径数据库管理器 - 简化版
    /// 负责路径和碰撞数据的持久化存储
    /// </summary>
    public class PathDatabase : IDisposable
    {
        private SQLiteConnection _connection;
        private readonly string _dbPath;

        /// <summary>
        /// 初始化路径数据库
        /// </summary>
        /// <param name="documentPath">Navisworks文档路径</param>
        public PathDatabase(string documentPath)
        {
            // 数据库文件与nwd文件同名，扩展名为.db
            _dbPath = Path.ChangeExtension(documentPath, ".db");

            InitializeDatabase();
        }

        /// <summary>
        /// 初始化数据库连接和表结构
        /// </summary>
        private void InitializeDatabase()
        {
            bool isNewDatabase = !File.Exists(_dbPath);

            _connection = new SQLiteConnection($"Data Source={_dbPath};Version=3;");
            _connection.Open();

            // 启用WAL模式（Write-Ahead Logging，支持并发读）
            ExecuteNonQuery("PRAGMA journal_mode=WAL");
            ExecuteNonQuery("PRAGMA foreign_keys=ON");

            // 无论数据库是否为新数据库，都调用CreateTables()
            // 因为使用了CREATE TABLE IF NOT EXISTS语法，所以是安全的
            // 这样可以确保所有表都存在，避免在旧数据库中缺少新表的问题
            CreateTables();

            if (isNewDatabase)
            {
                LogManager.Info($"创建新数据库: {_dbPath}");
            }
            else
            {
                LogManager.Info($"打开现有数据库: {_dbPath}");
            }
        }

        /// <summary>
        /// 创建数据库表结构
        /// </summary>
        private void CreateTables()
        {
            // 1. 路径基本信息表
            ExecuteNonQuery(@"
                CREATE TABLE IF NOT EXISTS PathRoutes (
                    Id TEXT PRIMARY KEY,
                    Name TEXT NOT NULL,
                    TotalLength REAL,
                    EstimatedTime REAL,
                    TurnRadius REAL DEFAULT 0.0,  -- 实际使用时从配置文件获取默认值
                    IsCurved INTEGER DEFAULT 0,
                    MaxVehicleLength REAL,
                    MaxVehicleWidth REAL,
                    MaxVehicleHeight REAL,
                    SafetyMargin REAL,
                    GridSize REAL,
                    CreatedTime DATETIME,
                    LastModified DATETIME
                )
            ");

            // 2. 碰撞报告表（替换原有的CollisionResults表）
            ExecuteNonQuery(@"
                CREATE TABLE IF NOT EXISTS CollisionReports (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    RouteId TEXT,
                    PathName TEXT,
                    AnimatedObjectName TEXT,
                    UniqueCollidedObjectsCount INTEGER,
                    FrameRate INTEGER,
                    Duration REAL,
                    DetectionGap REAL,
                    AnimationCollisionCount INTEGER,
                    ClashDetectiveCollisionCount INTEGER,
                    ReportTime DATETIME
                )
            ");

            // 3. 分析结果表
            ExecuteNonQuery(@"
                CREATE TABLE IF NOT EXISTS AnalysisResults (
                    RouteId TEXT PRIMARY KEY,
                    CollisionCount INTEGER,
                    SafetyScore REAL,
                    EfficiencyScore REAL,
                    OverallScore REAL,
                    AnalysisTime DATETIME,
                    Strategy TEXT,
                    FOREIGN KEY(RouteId) REFERENCES PathRoutes(Id) ON DELETE CASCADE
                )
            ");

            // 4. 路径点表（新增 CustomTurnRadius）
            ExecuteNonQuery(@"
                CREATE TABLE IF NOT EXISTS PathPoints (
                    Id TEXT PRIMARY KEY,
                    RouteId TEXT NOT NULL,
                    SequenceNumber INTEGER NOT NULL,
                    Name TEXT,
                    X REAL,
                    Y REAL,
                    Z REAL,
                    Type INTEGER,
                    CustomTurnRadius REAL,
                    FOREIGN KEY(RouteId) REFERENCES PathRoutes(Id) ON DELETE CASCADE
                )
            ");

            // 5. 路径边表（新增SequenceNumber字段）
            ExecuteNonQuery(@"
                CREATE TABLE IF NOT EXISTS PathEdges (
                    Id TEXT PRIMARY KEY,
                    RouteId TEXT NOT NULL,
                    StartPointId TEXT NOT NULL,
                    EndPointId TEXT NOT NULL,
                    SegmentType INTEGER NOT NULL,
                    PhysicalLength REAL NOT NULL,
                    SequenceNumber INTEGER NOT NULL,
                    Ts_X REAL,
                    Ts_Y REAL,
                    Ts_Z REAL,
                    Te_X REAL,
                    Te_Y REAL,
                    Te_Z REAL,
                    ArcCenter_X REAL,
                    ArcCenter_Y REAL,
                    ArcCenter_Z REAL,
                    RequestedRadius REAL,
                    ActualRadius REAL,
                    DeflectionAngle REAL,
                    ArcLength REAL,
                    FOREIGN KEY(RouteId) REFERENCES PathRoutes(Id) ON DELETE CASCADE
                )
            ");

            // 6. ClashDetective结果表
            ExecuteNonQuery(@"
                CREATE TABLE IF NOT EXISTS ClashDetectiveResults (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    TestName TEXT NOT NULL UNIQUE,
                    PathName TEXT NOT NULL,
                    RouteId TEXT NOT NULL,
                    TestTime DATETIME NOT NULL,
                    CollisionCount INTEGER NOT NULL,
                    AnimationCollisionCount INTEGER NOT NULL,
                    FrameRate INTEGER,
                    Duration REAL,
                    DetectionGap REAL,
                    AnimatedObjectName TEXT,
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            ");

            // 创建索引
            ExecuteNonQuery("CREATE INDEX IF NOT EXISTS idx_reports_route ON CollisionReports(RouteId)");
            ExecuteNonQuery("CREATE INDEX IF NOT EXISTS idx_pathpoints_route ON PathPoints(RouteId)");
            ExecuteNonQuery("CREATE INDEX IF NOT EXISTS idx_pathedges_route ON PathEdges(RouteId)");
            ExecuteNonQuery("CREATE INDEX IF NOT EXISTS idx_clash_path_name ON ClashDetectiveResults(PathName)");
            ExecuteNonQuery("CREATE INDEX IF NOT EXISTS idx_clash_test_time ON ClashDetectiveResults(TestTime DESC)");
        }

        /// <summary>
        /// 保存路径基本信息
        /// </summary>
        public void SavePathRoute(PathRoute route)
        {
            // 使用事务确保路径和路径点一起保存
            using (var transaction = _connection.BeginTransaction())
            {
                try
                {
                    // 保存路径基本信息
                    var sql = @"
                        INSERT OR REPLACE INTO PathRoutes
                        (Id, Name, TotalLength, EstimatedTime, TurnRadius, IsCurved, MaxVehicleLength, MaxVehicleWidth, MaxVehicleHeight, SafetyMargin, GridSize, CreatedTime, LastModified)
                        VALUES (@id, @name, @length, @time, @turnRadius, @isCurved, @maxLength, @maxWidth, @maxHeight, @safetyMargin, @gridSize, @created, @modified)
                    ";

                    using (var cmd = new SQLiteCommand(sql, _connection))
                    {
                        cmd.Parameters.AddWithValue("@id", route.Id);
                        cmd.Parameters.AddWithValue("@name", route.Name);
                        cmd.Parameters.AddWithValue("@length", route.TotalLength);
                        cmd.Parameters.AddWithValue("@time", route.EstimatedTime);
                        cmd.Parameters.AddWithValue("@turnRadius", route.TurnRadius);
                        cmd.Parameters.AddWithValue("@isCurved", route.IsCurved ? 1 : 0);
                        cmd.Parameters.AddWithValue("@maxLength", route.MaxVehicleLength);
                        cmd.Parameters.AddWithValue("@maxWidth", route.MaxVehicleWidth);
                        cmd.Parameters.AddWithValue("@maxHeight", route.MaxVehicleHeight);
                        cmd.Parameters.AddWithValue("@safetyMargin", route.SafetyMargin);
                        cmd.Parameters.AddWithValue("@gridSize", route.GridSize);
                        cmd.Parameters.AddWithValue("@created", route.CreatedTime);
                        cmd.Parameters.AddWithValue("@modified", DateTime.Now);
                        cmd.ExecuteNonQuery();
                    }

                    // 删除旧的路径点和边
                    DeletePathPoints(route.Id);
                    DeletePathEdges(route.Id);

                    // 保存新的路径点
                    if (route.Points != null && route.Points.Count > 0)
                    {
                        var pointSql = @"
                            INSERT INTO PathPoints
                            (Id, RouteId, SequenceNumber, Name, X, Y, Z, Type, CustomTurnRadius)
                            VALUES (@id, @routeId, @seq, @name, @x, @y, @z, @type, @customRadius)
                        ";

                        for (int i = 0; i < route.Points.Count; i++)
                        {
                            var point = route.Points[i];
                            using (var cmd = new SQLiteCommand(pointSql, _connection))
                            {
                                cmd.Parameters.AddWithValue("@id", point.Id);
                                cmd.Parameters.AddWithValue("@routeId", route.Id);
                                cmd.Parameters.AddWithValue("@seq", i);
                                cmd.Parameters.AddWithValue("@name", point.Name ?? "");
                                cmd.Parameters.AddWithValue("@x", point.Position.X);
                                cmd.Parameters.AddWithValue("@y", point.Position.Y);
                                cmd.Parameters.AddWithValue("@z", point.Position.Z);
                                cmd.Parameters.AddWithValue("@type", (int)point.Type);
                                cmd.Parameters.AddWithValue("@customRadius", point.CustomTurnRadius.HasValue ?
                                    (object)point.CustomTurnRadius.Value : DBNull.Value);
                                cmd.ExecuteNonQuery();
                            }
                        }

                        LogManager.Info($"保存路径: {route.Name}，包含 {route.Points.Count} 个路径点");
                    }

                    // 保存路径边
                    if (route.Edges != null && route.Edges.Count > 0)
                    {
                        for (int i = 0; i < route.Edges.Count; i++)
                        {
                            SavePathEdge(route.Id, route.Edges[i], i);
                        }

                        LogManager.Info($"保存路径: {route.Name}，包含 {route.Edges.Count} 个路径边");
                    }

                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    LogManager.Error($"保存路径失败: {ex.Message}", ex);
                    throw;
                }
            }
        }

        /// <summary>
        /// 保存碰撞报告
        /// </summary>
        public void SaveCollisionReport(string routeId, string pathName, string animatedObjectName,
                                       int uniqueCollidedObjectsCount, int frameRate, double duration, double detectionGap,
                                       int animationCollisionCount, int clashDetectiveCollisionCount)
        {
            try
            {
                var sql = @"
                    INSERT INTO CollisionReports
                    (RouteId, PathName, AnimatedObjectName, UniqueCollidedObjectsCount, FrameRate, Duration,
                     DetectionGap, AnimationCollisionCount, ClashDetectiveCollisionCount, ReportTime)
                    VALUES (@routeId, @pathName, @animatedObjectName, @uniqueCollidedObjectsCount, @frameRate, @duration,
                            @detectionGap, @animationCount, @clashCount, @reportTime)
                ";

                using (var cmd = new SQLiteCommand(sql, _connection))
                {
                    cmd.Parameters.AddWithValue("@routeId", routeId ?? "");
                    cmd.Parameters.AddWithValue("@pathName", pathName ?? "未知路径");
                    cmd.Parameters.AddWithValue("@animatedObjectName", animatedObjectName ?? "未知对象");
                    cmd.Parameters.AddWithValue("@uniqueCollidedObjectsCount", uniqueCollidedObjectsCount);
                    cmd.Parameters.AddWithValue("@frameRate", frameRate);
                    cmd.Parameters.AddWithValue("@duration", duration);
                    cmd.Parameters.AddWithValue("@detectionGap", detectionGap);
                    cmd.Parameters.AddWithValue("@animationCount", animationCollisionCount);
                    cmd.Parameters.AddWithValue("@clashCount", clashDetectiveCollisionCount);
                    cmd.Parameters.AddWithValue("@reportTime", DateTime.Now);
                    cmd.ExecuteNonQuery();
                }

                LogManager.Info($"碰撞报告已保存: 路径={pathName}, 碰撞构件={uniqueCollidedObjectsCount}, 动画碰撞={animationCollisionCount}, ClashDetective碰撞={clashDetectiveCollisionCount}");
            }
            catch (Exception ex)
            {
                LogManager.Error($"保存碰撞报告失败: {ex.Message}", ex);
                throw;
            }
        }

        /// <summary>
        /// 保存路径的碰撞结果（已废弃，保留以兼容旧代码）
        /// </summary>
        public void SaveCollisions(string routeId, List<CollisionResult> collisions)
        {
            // 先删除旧的碰撞数据
            ExecuteNonQuery("DELETE FROM CollisionResults WHERE RouteId=@id",
                          new { id = routeId });

            if (collisions == null || collisions.Count == 0)
                return;

            // 批量插入新数据
            using (var transaction = _connection.BeginTransaction())
            {
                try
                {
                    var sql = @"
                        INSERT INTO CollisionResults
                        (RouteId, Item1Name, Item2Name, CenterX, CenterY, CenterZ, Distance, DetectedTime)
                        VALUES (@rid, @i1, @i2, @x, @y, @z, @dist, @time)
                    ";

                    foreach (var collision in collisions)
                    {
                        using (var cmd = new SQLiteCommand(sql, _connection))
                        {
                            cmd.Parameters.AddWithValue("@rid", routeId);
                            cmd.Parameters.AddWithValue("@i1", GetItemDisplayName(collision.Item1));
                            cmd.Parameters.AddWithValue("@i2", GetItemDisplayName(collision.Item2));
                            cmd.Parameters.AddWithValue("@x", collision.Center.X);
                            cmd.Parameters.AddWithValue("@y", collision.Center.Y);
                            cmd.Parameters.AddWithValue("@z", collision.Center.Z);
                            cmd.Parameters.AddWithValue("@dist", collision.Distance);
                            cmd.Parameters.AddWithValue("@time", DateTime.Now);
                            cmd.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                    LogManager.Info($"保存碰撞结果: RouteId={routeId}, Count={collisions.Count}");
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    LogManager.Error($"保存碰撞结果失败: {ex.Message}", ex);
                    throw;
                }
            }
        }

        #region ClashDetective结果管理

        /// <summary>
        /// 保存ClashDetective碰撞检测结果
        /// </summary>
        public void SaveClashDetectiveResult(ClashDetectiveResultRecord record)
        {
            try
            {
                // 检查TestName是否已存在
                var checkSql = "SELECT COUNT(*) FROM ClashDetectiveResults WHERE TestName = @testName";
                using (var checkCmd = new SQLiteCommand(checkSql, _connection))
                {
                    checkCmd.Parameters.AddWithValue("@testName", record.TestName);
                    var count = Convert.ToInt32(checkCmd.ExecuteScalar());
                    if (count > 0)
                    {
                        throw new InvalidOperationException($"测试名称 '{record.TestName}' 已存在，无法重复保存");
                    }
                }

                var sql = @"
                    INSERT INTO ClashDetectiveResults
                    (TestName, PathName, RouteId, TestTime, CollisionCount, AnimationCollisionCount,
                     FrameRate, Duration, DetectionGap, AnimatedObjectName, CreatedAt)
                    VALUES (@testName, @pathName, @routeId, @testTime, @collisionCount, @animationCollisionCount,
                            @frameRate, @duration, @detectionGap, @animatedObjectName, @createdAt)
                ";

                using (var cmd = new SQLiteCommand(sql, _connection))
                {
                    cmd.Parameters.AddWithValue("@testName", record.TestName);
                    cmd.Parameters.AddWithValue("@pathName", record.PathName);
                    cmd.Parameters.AddWithValue("@routeId", record.RouteId);
                    cmd.Parameters.AddWithValue("@testTime", record.TestTime);
                    cmd.Parameters.AddWithValue("@collisionCount", record.CollisionCount);
                    cmd.Parameters.AddWithValue("@animationCollisionCount", record.AnimationCollisionCount);
                    cmd.Parameters.AddWithValue("@frameRate", record.FrameRate);
                    cmd.Parameters.AddWithValue("@duration", record.Duration);
                    cmd.Parameters.AddWithValue("@detectionGap", record.DetectionGap);
                    cmd.Parameters.AddWithValue("@animatedObjectName", record.AnimatedObjectName ?? "");
                    cmd.Parameters.AddWithValue("@createdAt", record.CreatedAt);
                    cmd.ExecuteNonQuery();
                }

                LogManager.Info($"ClashDetective结果已保存: 测试={record.TestName}, 碰撞数={record.CollisionCount}");
            }
            catch (Exception ex)
            {
                LogManager.Error($"保存ClashDetective结果失败: {ex.Message}", ex);
                throw;
            }
        }

        /// <summary>
        /// 获取路径的所有ClashDetective检测结果
        /// </summary>
        public List<ClashDetectiveResultRecord> GetClashDetectiveResultsByPath(string pathName)
        {
            var results = new List<ClashDetectiveResultRecord>();

            try
            {
                var sql = @"
                    SELECT Id, TestName, PathName, RouteId, TestTime, CollisionCount, AnimationCollisionCount,
                           FrameRate, Duration, DetectionGap, AnimatedObjectName, CreatedAt
                    FROM ClashDetectiveResults
                    WHERE PathName = @pathName
                    ORDER BY TestTime DESC
                ";

                using (var cmd = new SQLiteCommand(sql, _connection))
                {
                    cmd.Parameters.AddWithValue("@pathName", pathName);
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            results.Add(new ClashDetectiveResultRecord
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                TestName = reader["TestName"].ToString(),
                                PathName = reader["PathName"].ToString(),
                                RouteId = reader["RouteId"]?.ToString(),
                                TestTime = Convert.ToDateTime(reader["TestTime"]),
                                CollisionCount = Convert.ToInt32(reader["CollisionCount"]),
                                AnimationCollisionCount = Convert.ToInt32(reader["AnimationCollisionCount"]),
                                FrameRate = reader["FrameRate"] != DBNull.Value ? Convert.ToInt32(reader["FrameRate"]) : 0,
                                Duration = reader["Duration"] != DBNull.Value ? Convert.ToDouble(reader["Duration"]) : 0.0,
                                DetectionGap = reader["DetectionGap"] != DBNull.Value ? Convert.ToDouble(reader["DetectionGap"]) : 0.0,
                                AnimatedObjectName = reader["AnimatedObjectName"]?.ToString(),
                                CreatedAt = Convert.ToDateTime(reader["CreatedAt"])
                            });
                        }
                    }
                }

                LogManager.Debug($"获取到 {results.Count} 条ClashDetective结果记录");
            }
            catch (Exception ex)
            {
                LogManager.Error($"获取ClashDetective结果失败: {ex.Message}", ex);
            }

            return results;
        }

        /// <summary>
        /// 获取路径最新的ClashDetective检测结果
        /// </summary>
        public ClashDetectiveResultRecord GetLatestClashDetectiveResult(string pathName)
        {
            try
            {
                var sql = @"
                    SELECT Id, TestName, PathName, RouteId, TestTime, CollisionCount, AnimationCollisionCount,
                           FrameRate, Duration, DetectionGap, AnimatedObjectName, CreatedAt
                    FROM ClashDetectiveResults
                    WHERE PathName = @pathName
                    ORDER BY TestTime DESC
                    LIMIT 1
                ";

                using (var cmd = new SQLiteCommand(sql, _connection))
                {
                    cmd.Parameters.AddWithValue("@pathName", pathName);
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new ClashDetectiveResultRecord
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                TestName = reader["TestName"].ToString(),
                                PathName = reader["PathName"].ToString(),
                                RouteId = reader["RouteId"]?.ToString(),
                                TestTime = Convert.ToDateTime(reader["TestTime"]),
                                CollisionCount = Convert.ToInt32(reader["CollisionCount"]),
                                AnimationCollisionCount = Convert.ToInt32(reader["AnimationCollisionCount"]),
                                FrameRate = reader["FrameRate"] != DBNull.Value ? Convert.ToInt32(reader["FrameRate"]) : 0,
                                Duration = reader["Duration"] != DBNull.Value ? Convert.ToDouble(reader["Duration"]) : 0.0,
                                DetectionGap = reader["DetectionGap"] != DBNull.Value ? Convert.ToDouble(reader["DetectionGap"]) : 0.0,
                                AnimatedObjectName = reader["AnimatedObjectName"]?.ToString(),
                                CreatedAt = Convert.ToDateTime(reader["CreatedAt"])
                            };
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Error($"获取最新ClashDetective结果失败: {ex.Message}", ex);
            }

            return null;
        }

        /// <summary>
        /// 删除ClashDetective检测结果
        /// </summary>
        public void DeleteClashDetectiveResult(int id)
        {
            try
            {
                ExecuteNonQuery("DELETE FROM ClashDetectiveResults WHERE Id=@id",
                              new { id = id });
                LogManager.Info($"已删除ClashDetective结果记录: Id={id}");
            }
            catch (Exception ex)
            {
                LogManager.Error($"删除ClashDetective结果失败: {ex.Message}", ex);
                throw;
            }
        }

        #endregion

        /// <summary>
        /// 保存分析评分结果
        /// </summary>
        public void SaveAnalysisScore(string routeId, int collisionCount, double safetyScore,
                                       double efficiencyScore, string strategy)
        {
            var overallScore = (safetyScore + efficiencyScore) / 2.0;

            var sql = @"
                INSERT OR REPLACE INTO AnalysisResults
                (RouteId, CollisionCount, SafetyScore, EfficiencyScore, OverallScore, AnalysisTime, Strategy)
                VALUES (@id, @count, @safety, @eff, @overall, @time, @strategy)
            ";

            using (var cmd = new SQLiteCommand(sql, _connection))
            {
                cmd.Parameters.AddWithValue("@id", routeId);
                cmd.Parameters.AddWithValue("@count", collisionCount);
                cmd.Parameters.AddWithValue("@safety", safetyScore);
                cmd.Parameters.AddWithValue("@eff", efficiencyScore);
                cmd.Parameters.AddWithValue("@overall", overallScore);
                cmd.Parameters.AddWithValue("@time", DateTime.Now);
                cmd.Parameters.AddWithValue("@strategy", strategy ?? "默认");
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 获取路径的碰撞数量
        /// </summary>
        public int GetCollisionCount(string routeId)
        {
            var sql = "SELECT COUNT(*) FROM CollisionResults WHERE RouteId=@id";

            using (var cmd = new SQLiteCommand(sql, _connection))
            {
                cmd.Parameters.AddWithValue("@id", routeId);
                var result = cmd.ExecuteScalar();
                return Convert.ToInt32(result);
            }
        }

        /// <summary>
        /// 获取路径的分析结果
        /// </summary>
        public PathAnalysisResult GetAnalysisResult(string routeId)
        {
            var sql = "SELECT * FROM AnalysisResults WHERE RouteId=@id";

            using (var cmd = new SQLiteCommand(sql, _connection))
            {
                cmd.Parameters.AddWithValue("@id", routeId);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new PathAnalysisResult
                        {
                            RouteId = reader["RouteId"].ToString(),
                            CollisionCount = Convert.ToInt32(reader["CollisionCount"]),
                            SafetyScore = Convert.ToDouble(reader["SafetyScore"]),
                            EfficiencyScore = Convert.ToDouble(reader["EfficiencyScore"]),
                            OverallScore = Convert.ToDouble(reader["OverallScore"]),
                            AnalysisTime = Convert.ToDateTime(reader["AnalysisTime"]),
                            Strategy = reader["Strategy"].ToString()
                        };
                    }
                }
            }

            return null;
        }

        /// <summary>
        /// 获取所有路径的分析结果
        /// </summary>
        public List<PathAnalysisResult> GetAllAnalysisResults()
        {
            var results = new List<PathAnalysisResult>();
            var sql = "SELECT * FROM AnalysisResults ORDER BY OverallScore DESC";

            using (var cmd = new SQLiteCommand(sql, _connection))
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    results.Add(new PathAnalysisResult
                    {
                        RouteId = reader["RouteId"].ToString(),
                        CollisionCount = Convert.ToInt32(reader["CollisionCount"]),
                        SafetyScore = Convert.ToDouble(reader["SafetyScore"]),
                        EfficiencyScore = Convert.ToDouble(reader["EfficiencyScore"]),
                        OverallScore = Convert.ToDouble(reader["OverallScore"]),
                        AnalysisTime = Convert.ToDateTime(reader["AnalysisTime"]),
                        Strategy = reader["Strategy"].ToString()
                    });
                }
            }

            return results;
        }

        /// <summary>
        /// 删除路径及其相关数据
        /// </summary>
        /// <param name="routeId">路径ID</param>
        public void DeletePathRoute(string routeId)
        {
            try
            {
                // 由于设置了外键级联删除，删除路径时会自动删除相关的碰撞结果和分析结果
                var sql = "DELETE FROM PathRoutes WHERE Id = @id";
                using (var cmd = new SQLiteCommand(sql, _connection))
                {
                    cmd.Parameters.AddWithValue("@id", routeId);
                    int affected = cmd.ExecuteNonQuery();
                    LogManager.Info($"删除路径记录: ID={routeId}, 影响行数={affected}");
                }
            }
            catch (Exception ex)
            {
                LogManager.Error($"删除路径失败: ID={routeId}, {ex.Message}", ex);
                throw;
            }
        }

        /// <summary>
        /// 获取所有路径基本信息
        /// </summary>
        public List<PathRoute> GetAllPathRoutes()
        {
            var routes = new List<PathRoute>();
            var sql = @"SELECT pr.*, ar.CollisionCount, ar.SafetyScore, ar.EfficiencyScore, ar.OverallScore
                        FROM PathRoutes pr
                        LEFT JOIN AnalysisResults ar ON pr.Id = ar.RouteId
                        ORDER BY pr.LastModified DESC";

            using (var cmd = new SQLiteCommand(sql, _connection))
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var route = new PathRoute
                    {
                        Id = reader["Id"].ToString(),
                        Name = reader["Name"].ToString(),
                        TotalLength = Convert.ToDouble(reader["TotalLength"]),
                        EstimatedTime = Convert.ToDouble(reader["EstimatedTime"]),
                        TurnRadius = reader.IsDBNull(reader.GetOrdinal("TurnRadius")) ? 0.0 : Convert.ToDouble(reader["TurnRadius"]),  // 0.0 表示未设置，实际使用时从配置获取
                        IsCurved = reader.IsDBNull(reader.GetOrdinal("IsCurved")) ? false : Convert.ToInt32(reader["IsCurved"]) == 1,
                        MaxVehicleLength = reader.IsDBNull(reader.GetOrdinal("MaxVehicleLength")) ? 1.0 : Convert.ToDouble(reader["MaxVehicleLength"]),
                        MaxVehicleWidth = reader.IsDBNull(reader.GetOrdinal("MaxVehicleWidth")) ? 1.0 : Convert.ToDouble(reader["MaxVehicleWidth"]),
                        MaxVehicleHeight = reader.IsDBNull(reader.GetOrdinal("MaxVehicleHeight")) ? 2.0 : Convert.ToDouble(reader["MaxVehicleHeight"]),
                        SafetyMargin = reader.IsDBNull(reader.GetOrdinal("SafetyMargin")) ? 0.5 : Convert.ToDouble(reader["SafetyMargin"]),
                        GridSize = reader.IsDBNull(reader.GetOrdinal("GridSize")) ? 0.5 : Convert.ToDouble(reader["GridSize"]),
                        CreatedTime = Convert.ToDateTime(reader["CreatedTime"]),
                        LastModified = Convert.ToDateTime(reader["LastModified"])
                    };

                    // 添加分析结果（如果存在）
                    if (!reader.IsDBNull(reader.GetOrdinal("CollisionCount")))
                    {
                        route.CollisionCount = Convert.ToInt32(reader["CollisionCount"]);
                        route.SafetyScore = Convert.ToDouble(reader["SafetyScore"]);
                        route.EfficiencyScore = Convert.ToDouble(reader["EfficiencyScore"]);
                        route.OverallScore = Convert.ToDouble(reader["OverallScore"]);
                    }

                    routes.Add(route);
                }
            }

            // 为每个路径加载路径点和路径边
            foreach (var route in routes)
            {
                LoadPathPoints(route);
                LoadPathEdges(route);
            }

            LogManager.Info($"从数据库加载了 {routes.Count} 条路径记录");
            return routes;
        }

        /// <summary>
        /// 加载指定路径的路径点
        /// </summary>
        private void LoadPathPoints(PathRoute route)
        {
            var sql = @"SELECT * FROM PathPoints
                        WHERE RouteId=@routeId
                        ORDER BY SequenceNumber";

            using (var cmd = new SQLiteCommand(sql, _connection))
            {
                cmd.Parameters.AddWithValue("@routeId", route.Id);
                using (var reader = cmd.ExecuteReader())
                {
                    int index = 0;
                    while (reader.Read())
                    {
                        var point = new PathPoint
                        {
                            Id = reader["Id"].ToString(),
                            Name = reader["Name"].ToString(),
                            Position = new Autodesk.Navisworks.Api.Point3D(
                                Convert.ToDouble(reader["X"]),
                                Convert.ToDouble(reader["Y"]),
                                Convert.ToDouble(reader["Z"])
                            ),
                            Type = (PathPointType)Convert.ToInt32(reader["Type"]),
                            Index = index++
                        };

                        // 加载自定义转向半径
                        if (!reader.IsDBNull(reader.GetOrdinal("CustomTurnRadius")))
                        {
                            point.CustomTurnRadius = Convert.ToDouble(reader["CustomTurnRadius"]);
                        }

                        route.Points.Add(point);
                    }
                }
            }
        }

        /// <summary>
        /// 保存路径边
        /// </summary>
        private void SavePathEdge(string routeId, PathEdge edge, int sequenceNumber)
        {
            var sql = @"
                INSERT INTO PathEdges
                (Id, RouteId, StartPointId, EndPointId, SegmentType, PhysicalLength, SequenceNumber,
                 Ts_X, Ts_Y, Ts_Z, Te_X, Te_Y, Te_Z,
                 ArcCenter_X, ArcCenter_Y, ArcCenter_Z,
                 RequestedRadius, ActualRadius, DeflectionAngle, ArcLength)
                VALUES (@id, @routeId, @startId, @endId, @segType, @length, @seqNum,
                        @tsx, @tsy, @tsz, @tex, @tey, @tez,
                        @acx, @acy, @acz,
                        @reqR, @actR, @angle, @arcLen)
            ";

            using (var cmd = new SQLiteCommand(sql, _connection))
            {
                cmd.Parameters.AddWithValue("@id", edge.Id);
                cmd.Parameters.AddWithValue("@routeId", routeId);
                cmd.Parameters.AddWithValue("@startId", edge.StartPointId ?? "");
                cmd.Parameters.AddWithValue("@endId", edge.EndPointId ?? "");
                cmd.Parameters.AddWithValue("@segType", (int)edge.SegmentType);
                cmd.Parameters.AddWithValue("@length", edge.PhysicalLength);
                cmd.Parameters.AddWithValue("@seqNum", sequenceNumber);

                // 直线段：保存起止点坐标
                if (edge.SegmentType == PathSegmentType.Straight && edge.SampledPoints != null && edge.SampledPoints.Count >= 2)
                {
                    cmd.Parameters.AddWithValue("@tsx", edge.SampledPoints.First().X);
                    cmd.Parameters.AddWithValue("@tsy", edge.SampledPoints.First().Y);
                    cmd.Parameters.AddWithValue("@tsz", edge.SampledPoints.First().Z);
                    cmd.Parameters.AddWithValue("@tex", edge.SampledPoints.Last().X);
                    cmd.Parameters.AddWithValue("@tey", edge.SampledPoints.Last().Y);
                    cmd.Parameters.AddWithValue("@tez", edge.SampledPoints.Last().Z);

                    // 圆弧参数为NULL
                    cmd.Parameters.AddWithValue("@acx", DBNull.Value);
                    cmd.Parameters.AddWithValue("@acy", DBNull.Value);
                    cmd.Parameters.AddWithValue("@acz", DBNull.Value);
                    cmd.Parameters.AddWithValue("@reqR", DBNull.Value);
                    cmd.Parameters.AddWithValue("@actR", DBNull.Value);
                    cmd.Parameters.AddWithValue("@angle", DBNull.Value);
                    cmd.Parameters.AddWithValue("@arcLen", DBNull.Value);
                }
                else if (edge.SegmentType == PathSegmentType.Arc && edge.Trajectory != null)
                {
                    // 圆弧段：保存圆弧轨迹数据
                    cmd.Parameters.AddWithValue("@tsx", edge.Trajectory.Ts.X);
                    cmd.Parameters.AddWithValue("@tsy", edge.Trajectory.Ts.Y);
                    cmd.Parameters.AddWithValue("@tsz", edge.Trajectory.Ts.Z);
                    cmd.Parameters.AddWithValue("@tex", edge.Trajectory.Te.X);
                    cmd.Parameters.AddWithValue("@tey", edge.Trajectory.Te.Y);
                    cmd.Parameters.AddWithValue("@tez", edge.Trajectory.Te.Z);
                    cmd.Parameters.AddWithValue("@acx", edge.Trajectory.ArcCenter.X);
                    cmd.Parameters.AddWithValue("@acy", edge.Trajectory.ArcCenter.Y);
                    cmd.Parameters.AddWithValue("@acz", edge.Trajectory.ArcCenter.Z);
                    cmd.Parameters.AddWithValue("@reqR", edge.Trajectory.RequestedRadius);
                    cmd.Parameters.AddWithValue("@actR", edge.Trajectory.ActualRadius);
                    cmd.Parameters.AddWithValue("@angle", edge.Trajectory.DeflectionAngle);
                    cmd.Parameters.AddWithValue("@arcLen", edge.Trajectory.ArcLength);
                }
                else
                {
                    // 其他情况，所有参数为NULL
                    cmd.Parameters.AddWithValue("@tsx", DBNull.Value);
                    cmd.Parameters.AddWithValue("@tsy", DBNull.Value);
                    cmd.Parameters.AddWithValue("@tsz", DBNull.Value);
                    cmd.Parameters.AddWithValue("@tex", DBNull.Value);
                    cmd.Parameters.AddWithValue("@tey", DBNull.Value);
                    cmd.Parameters.AddWithValue("@tez", DBNull.Value);
                    cmd.Parameters.AddWithValue("@acx", DBNull.Value);
                    cmd.Parameters.AddWithValue("@acy", DBNull.Value);
                    cmd.Parameters.AddWithValue("@acz", DBNull.Value);
                    cmd.Parameters.AddWithValue("@reqR", DBNull.Value);
                    cmd.Parameters.AddWithValue("@actR", DBNull.Value);
                    cmd.Parameters.AddWithValue("@angle", DBNull.Value);
                    cmd.Parameters.AddWithValue("@arcLen", DBNull.Value);
                }

                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 删除路径点
        /// </summary>
        private void DeletePathPoints(string routeId)
        {
            using (var cmd = new SQLiteCommand("DELETE FROM PathPoints WHERE RouteId = @routeId", _connection))
            {
                cmd.Parameters.AddWithValue("@routeId", routeId);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 删除路径边
        /// </summary>
        private void DeletePathEdges(string routeId)
        {
            using (var cmd = new SQLiteCommand("DELETE FROM PathEdges WHERE RouteId = @routeId", _connection))
            {
                cmd.Parameters.AddWithValue("@routeId", routeId);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 加载路径边
        /// </summary>
        private void LoadPathEdges(PathRoute route)
        {
            var sql = "SELECT * FROM PathEdges WHERE RouteId = @routeId ORDER BY SequenceNumber";

            using (var cmd = new SQLiteCommand(sql, _connection))
            {
                cmd.Parameters.AddWithValue("@routeId", route.Id);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var edge = new PathEdge
                        {
                            Id = reader["Id"].ToString(),
                            StartPointId = reader["StartPointId"].ToString(),
                            EndPointId = reader["EndPointId"].ToString(),
                            SegmentType = (PathSegmentType)Convert.ToInt32(reader["SegmentType"]),
                            PhysicalLength = Convert.ToDouble(reader["PhysicalLength"])
                        };

                        // 加载圆弧轨迹数据
                        if (edge.SegmentType == PathSegmentType.Arc)
                        {
                            edge.Trajectory = new ArcTrajectory
                            {
                                Ts = new Point3D(
                                    Convert.ToDouble(reader["Ts_X"]),
                                    Convert.ToDouble(reader["Ts_Y"]),
                                    Convert.ToDouble(reader["Ts_Z"])),
                                Te = new Point3D(
                                    Convert.ToDouble(reader["Te_X"]),
                                    Convert.ToDouble(reader["Te_Y"]),
                                    Convert.ToDouble(reader["Te_Z"])),
                                ArcCenter = new Point3D(
                                    Convert.ToDouble(reader["ArcCenter_X"]),
                                    Convert.ToDouble(reader["ArcCenter_Y"]),
                                    Convert.ToDouble(reader["ArcCenter_Z"])),
                                RequestedRadius = Convert.ToDouble(reader["RequestedRadius"]),
                                ActualRadius = Convert.ToDouble(reader["ActualRadius"]),
                                DeflectionAngle = Convert.ToDouble(reader["DeflectionAngle"]),
                                ArcLength = Convert.ToDouble(reader["ArcLength"])
                            };
                        }
                        else if (edge.SegmentType == PathSegmentType.Straight)
                        {
                            // 直线段也加载Ts和Te（用于重新计算SampledPoints）
                            if (!reader.IsDBNull(reader.GetOrdinal("Ts_X")) &&
                                !reader.IsDBNull(reader.GetOrdinal("Te_X")))
                            {
                                edge.Trajectory = new ArcTrajectory
                                {
                                    Ts = new Point3D(
                                        Convert.ToDouble(reader["Ts_X"]),
                                        Convert.ToDouble(reader["Ts_Y"]),
                                        Convert.ToDouble(reader["Ts_Z"])),
                                    Te = new Point3D(
                                        Convert.ToDouble(reader["Te_X"]),
                                        Convert.ToDouble(reader["Te_Y"]),
                                        Convert.ToDouble(reader["Te_Z"])),
                                    ArcCenter = new Point3D(0, 0, 0),  // 直线段没有圆心
                                    RequestedRadius = 0,
                                    ActualRadius = 0,
                                    DeflectionAngle = 0,
                                    ArcLength = 0
                                };
                            }
                        }

                        route.Edges.Add(edge);
                    }
                }
            }

            // 重新计算SampledPoints（因为数据库中没有保存采样点）
            if (route.Edges.Count > 0)
            {
                double samplingStep = ConfigManager.Instance.Current.PathEditing.ArcSamplingStep;
                foreach (var edge in route.Edges)
                {
                    if (edge.SegmentType == PathSegmentType.Arc && edge.Trajectory != null)
                    {
                        // 圆弧段：使用PathCurveEngine重新采样
                        edge.SampledPoints = PathCurveEngine.SampleArc(edge.Trajectory, samplingStep);
                    }
                    else if (edge.SegmentType == PathSegmentType.Straight)
                    {
                        // 直线段：从数据库加载的Ts和Te重新采样
                        Point3D startPoint = null;
                        Point3D endPoint = null;

                        // 尝试从Trajectory中获取（直线段也可能用Trajectory存储起止点）
                        if (edge.Trajectory != null)
                        {
                            startPoint = edge.Trajectory.Ts;
                            endPoint = edge.Trajectory.Te;
                        }

                        // 如果找到了起止点，重新采样
                        if (startPoint != null && endPoint != null)
                        {
                            int sampleCount = Math.Max(2, (int)Math.Ceiling(edge.PhysicalLength / samplingStep));
                            edge.SampledPoints = new List<Point3D>();
                            for (int i = 0; i <= sampleCount; i++)
                            {
                                double t = i / (double)sampleCount;
                                Point3D sampledPoint = startPoint + (endPoint - startPoint) * t;
                                edge.SampledPoints.Add(sampledPoint);
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行非查询SQL语句
        /// </summary>
        private void ExecuteNonQuery(string sql, object parameters = null)
        {
            using (var cmd = new SQLiteCommand(sql, _connection))
            {
                if (parameters != null)
                {
                    var props = parameters.GetType().GetProperties();
                    foreach (var prop in props)
                    {
                        cmd.Parameters.AddWithValue($"@{prop.Name}", prop.GetValue(parameters));
                    }
                }
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 获取ModelItem的显示名称
        /// </summary>
        private string GetItemDisplayName(ModelItem item)
        {
            if (item == null)
                return "未知对象";

            try
            {
                return item.DisplayName ?? item.ToString();
            }
            catch
            {
                return "未知对象";
            }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                _connection?.Close();
                _connection?.Dispose();
            }
        }
    }

    /// <summary>
    /// ClashDetective碰撞检测结果记录
    /// </summary>
    public class ClashDetectiveResultRecord
    {
        public int Id { get; set; }
        public string TestName { get; set; }
        public string PathName { get; set; }
        public string RouteId { get; set; }
        public DateTime TestTime { get; set; }
        public int CollisionCount { get; set; }
        public int AnimationCollisionCount { get; set; }
        public int FrameRate { get; set; }
        public double Duration { get; set; }
        public double DetectionGap { get; set; }
        public string AnimatedObjectName { get; set; }
        public DateTime CreatedAt { get; set; }
    }

    /// <summary>
    /// 路径分析结果数据模型
    /// </summary>
    public class PathAnalysisResult
    {
        public string RouteId { get; set; }
        public int CollisionCount { get; set; }
        public double SafetyScore { get; set; }
        public double EfficiencyScore { get; set; }
        public double OverallScore { get; set; }
        public DateTime AnalysisTime { get; set; }
        public string Strategy { get; set; }
    }
}
