using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.Json;
using CounterDrone.Core.Models;
using CounterDrone.Core.Repository;
using CounterDrone.Core.Services;
using SQLite;

namespace CounterDrone.Core
{
    /// <summary>数据库管理器 — 建表 / 连接管理</summary>
    public class DatabaseManager
    {
        private readonly IPathProvider _paths;

        public DatabaseManager(IPathProvider paths)
        {
            _paths = paths;
        }

        /// <summary>初始化主数据库（建表 + 种子数据）</summary>
        public SQLiteConnection OpenMainDb()
        {
            var dbPath = _paths.GetMainDbPath();
            var db = new SQLiteConnection(dbPath);
            CreateMainTables(db);
            SeedDefaultData(db);
            SeedDefaultScenarios(db);
            return db;
        }

        /// <summary>创建/打开任务帧数据库</summary>
        public SQLiteConnection OpenFrameDb(string scenarioId)
        {
            var dbPath = Path.Combine(_paths.GetFramesDir(), $"{scenarioId}.db");
            var db = new SQLiteConnection(dbPath);
            db.CreateTable<SimFrameRecord>();
            db.CreateIndex("SimFrameRecord", new[] { "ScenarioId", "FrameIndex" });
            return db;
        }

        /// <summary>删除任务帧数据库文件</summary>
        public void DeleteFrameDb(string scenarioId)
        {
            var dbPath = Path.Combine(_paths.GetFramesDir(), $"{scenarioId}.db");
            if (File.Exists(dbPath))
                File.Delete(dbPath);
        }

        private void SeedDefaultData(SQLiteConnection db)
        {
            db.CreateTable<MetaEntry>();

            var defaults = DefaultData.Load(_paths);
            var currentVersion = db.Table<MetaEntry>()
                .FirstOrDefault(m => m.Key == "defaultsVersion")?.Value;

            // 版本未变且数据已存在 → 跳过
            if (currentVersion == defaults.Version && db.Table<AmmunitionSpec>().Count() > 0)
                return;

            // 插入或更新默认数据（按 Id 覆盖，不删除用户自建数据）
            foreach (var a in defaults.Ammunition) db.InsertOrReplace(a);
            foreach (var f in defaults.FireUnits) db.InsertOrReplace(f);
            foreach (var l in defaults.LaunchPlatforms) db.InsertOrReplace(l);
            foreach (var d in defaults.Drones) db.InsertOrReplace(d);
            foreach (var s in defaults.Sensors) db.InsertOrReplace(s);
            foreach (var e in defaults.Environments) db.InsertOrReplace(e);
            foreach (var f in defaults.Formations) db.InsertOrReplace(f);
            foreach (var r in defaults.Routes)
            {
                r.WaypointsJson = System.Text.Json.JsonSerializer.Serialize(r.Waypoints);
                db.InsertOrReplace(r);
            }

            db.InsertOrReplace(new MetaEntry { Key = "defaultsVersion", Value = defaults.Version });
        }

        private void SeedDefaultScenarios(SQLiteConnection db)
        {
            var currentVersion = db.Table<MetaEntry>()
                .FirstOrDefault(m => m.Key == "scenariosVersion")?.Value;

            // 版本未变 → 跳过
            if (currentVersion == "5") return;

            var defaults = DefaultData.Load(_paths);
            var scenario = new ScenarioService(
                new ScenarioRepository(db), new CombatSceneRepository(db),
                new ControlZoneRepository(db), new ScenarioDroneRepository(db),
                new ScenarioUnitRepository(db), new CloudDispersalRepository(db),
                new RoutePlanRepository(db), new WaypointRepository(db));

            DefaultScenarios.Seed(scenario, defaults);

            db.InsertOrReplace(new MetaEntry { Key = "scenariosVersion", Value = "5" });
        }

        private void CreateMainTables(SQLiteConnection db)
        {
            // 删除旧版本残留表
            db.Execute("DROP TABLE IF EXISTS SimTask");
            db.Execute("DROP TABLE IF EXISTS TargetConfig");
            db.Execute("DROP TABLE IF EXISTS DroneProfile");
            db.Execute("DROP TABLE IF EXISTS EquipmentDeployment");
            db.Execute("DROP TABLE IF EXISTS SimulationReport");

            db.CreateTable<ModelInfo>();
            db.CreateTable<AmmunitionSpec>();
            db.CreateTable<Scenario>();
            db.CreateTable<CombatScene>();
            db.CreateTable<ControlZone>();
            db.CreateTable<ScenarioDrone>();
            db.CreateTable<ScenarioUnit>();
            db.CreateTable<CloudDispersal>();
            db.CreateTable<RoutePlan>();
            db.CreateIndex("RoutePlan", new[] { "ScenarioId", "WaveId" }, true);
            db.CreateTable<Waypoint>();
            db.CreateTable<SimulationReport>();
            db.CreateTable<FireUnitSpec>();
            db.CreateTable<LaunchPlatformSpec>();
            db.CreateTable<DroneSpec>();
            db.CreateTable<SensorSpec>();
            db.CreateTable<EnvironmentSpec>();
            db.CreateTable<FormationTemplate>();
            db.CreateTable<RouteTemplate>();

            db.CreateIndex("Scenario", "ScenarioNumber");
            db.CreateIndex("ControlZone", "ScenarioId");
            db.CreateIndex("ScenarioDrone", "ScenarioId");
            db.CreateIndex("ScenarioUnit", "ScenarioId");
            db.CreateIndex("Waypoint", "ScenarioId");
            db.CreateIndex("SimulationReport", "ScenarioId");
        }
    }
}
