using SqlSugar;
using System;
using System.IO;
using RainOpsMini.Models;
using System.Reflection;
using System.Linq;

using RainOpsMini.Helpers.SquadGameLog;

namespace RainOpsMini.Helpers
{
    public class DbHelper
    {
        public static string DbPath => Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "RainOpsData.db");
        private static string ConnectionString => $"Data Source={DbPath}";

        public static SqlSugarScope GetInstance()
        {
            var db = new SqlSugarScope(new ConnectionConfig()
            {
                ConnectionString = ConnectionString,
                DbType = DbType.Sqlite,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute,
                ConfigureExternalServices = new ConfigureExternalServices
                {
                    // Handle nullable types for SQLite
                    EntityService = (c, p) =>
                    {
                        if (p.IsPrimarykey == false && new NullabilityInfoContext().Create(c).WriteState is NullabilityState.Nullable)
                        {
                            p.IsNullable = true;
                        }
                    }
                }
            });
            
            // Print SQL for debugging
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                // Console.WriteLine($"[SQL] {sql}"); // Reduce noise
            };
            
            return db;
        }

        public static void InitDatabase()
        {
            try
            {
                Console.WriteLine($"[DB] Initializing database at: {DbPath}");
                
                // Ensure directory exists
                var dir = Path.GetDirectoryName(DbPath);
                if (!Directory.Exists(dir)) Directory.CreateDirectory(dir);

                using (var db = GetInstance())
                {
                    // Create database file if not exists
                    if (!File.Exists(DbPath))
                    {
                        Console.WriteLine("[DB] Creating new database file...");
                        db.DbMaintenance.CreateDatabase();
                    }

                    // Create Tables - ensure ChatLog and ServerInfoLog tables exist
                    try 
                    {
                        Console.WriteLine($"[DB] Starting CodeFirst InitTables for {typeof(Log_Disconnect).Name} etc...");
                        db.CodeFirst.InitTables(
                            typeof(ChatLog), 
                            typeof(ServerInfoLog),
                            typeof(Log_Disconnect),
                            typeof(Log_MatchLost),
                            typeof(Log_BattleEnd),
                            typeof(Log_CreateSquad),
                            typeof(Log_NewPlayerConnect),
                            typeof(Log_Wound),
                            typeof(Log_Die),
                            typeof(Log_Revived),
                            typeof(Log_Attack),
                            typeof(Log_Tick),
                            typeof(Log_SetNextLayer),
                            typeof(Log_ChangeLayer),
                            typeof(Log_RemovedPlayer),
                            typeof(RainOpsMini.Models.PluginConfigEntity),
                            typeof(RainOpsMini.Models.BanRecord),
                            typeof(RainOpsMini.Models.UserLoginLog),
                            typeof(RainOpsMini.Models.UserIntegral),
                            typeof(RainOpsMini.Models.CdkInfo),
                            typeof(PlayerProfile),
                            typeof(PlayerSession)
                        );
                        Console.WriteLine("[DB] CodeFirst InitTables executed successfully.");

                        // Ensure Log_Tick has PlayerCount column (Migration for existing databases)
                        try 
                        {
                            var tickTableInfo = db.DbMaintenance.GetColumnInfosByTableName("Log_Tick");
                            if (tickTableInfo != null && tickTableInfo.Count > 0 && !tickTableInfo.Any(c => c.DbColumnName.Equals("PlayerCount", StringComparison.OrdinalIgnoreCase)))
                            {
                                Console.WriteLine("[DB] Adding missing column PlayerCount to Log_Tick table...");
                                db.DbMaintenance.AddColumn("Log_Tick", new DbColumnInfo 
                                { 
                                    DbColumnName = "PlayerCount", 
                                    DataType = "INTEGER", 
                                    IsNullable = true 
                                });
                            }
                            
                            if (tickTableInfo != null && tickTableInfo.Count > 0 && !tickTableInfo.Any(c => c.DbColumnName.Equals("MatchTime", StringComparison.OrdinalIgnoreCase)))
                            {
                                Console.WriteLine("[DB] Adding missing column MatchTime to Log_Tick table...");
                                db.DbMaintenance.AddColumn("Log_Tick", new DbColumnInfo 
                                { 
                                    DbColumnName = "MatchTime", 
                                    DataType = "INTEGER", 
                                    IsNullable = true 
                                });
                            }
                            
                            if (tickTableInfo != null && tickTableInfo.Count > 0 && !tickTableInfo.Any(c => c.DbColumnName.Equals("MapName", StringComparison.OrdinalIgnoreCase)))
                            {
                                Console.WriteLine("[DB] Adding missing column MapName to Log_Tick table...");
                                db.DbMaintenance.AddColumn("Log_Tick", new DbColumnInfo 
                                { 
                                    DbColumnName = "MapName", 
                                    DataType = "Text",
                                    IsNullable = true
                                });
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"[DB] Failed to migrate Log_Tick: {ex.Message}");
                        }

                        // Migration for Log_Die, Log_Wound, Log_Revived (New columns)
                        try 
                        {
                            string[] tables2 = new[] { "Log_Die", "Log_Wound", "Log_Revived" };
                            foreach (var table in tables2)
                            {
                                var cols = db.DbMaintenance.GetColumnInfosByTableName(table);
                                var newCols = new[] { 
                                    new { Name = "MapName", Type = "Text" },
                                    new { Name = "MatchTime", Type = "Text" },
                                    new { Name = "IsTK", Type = "Boolean" }, // Sqlite stores bool as int usually, SqlSugar handles it
                                    new { Name = "AttackerName", Type = "Text" },
                                    new { Name = "AttackerSteamID", Type = "Text" },
                                    new { Name = "VictimName", Type = "Text" },
                                    new { Name = "VictimSteamID", Type = "Text" }
                                };

                                foreach (var newCol in newCols)
                                {
                                    if (cols != null && !cols.Any(c => c.DbColumnName.Equals(newCol.Name, StringComparison.OrdinalIgnoreCase)))
                                    {
                                        // Specific handling for Log_Revived which might not need Attacker/Victim names if we use Reviver/Revived
                                        // But consistent schema is fine. 
                                        // Actually Log_Revived uses Reviver/Revived. I added Attacker/Victim to Log_Revived in entities?
                                        // Wait, I added MapName, MatchTime, IsTK to Revived. I did NOT add Attacker/Victim to Revived in the Entity update step?
                                        // Let me double check my Entity update.
                                        
                                        // Logic check:
                                        // Log_Die/Wound: Added MapName, MatchTime, IsTK, AttackerName, AttackerSteamID, VictimName, VictimSteamID
                                        // Log_Revived: Added MapName, MatchTime, IsTK. (Already has Reviver/Revived).
                                        
                                        if (table == "Log_Revived" && (newCol.Name.Contains("Attacker") || newCol.Name.Contains("Victim"))) continue;

                                        Console.WriteLine($"[DB] Adding missing column {newCol.Name} to {table} table...");
                                        db.DbMaintenance.AddColumn(table, new DbColumnInfo 
                                        { 
                                            DbColumnName = newCol.Name, 
                                            DataType = newCol.Type,
                                            IsNullable = true
                                        });
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"[DB] Failed to migrate Log_Die/Wound/Revived: {ex.Message}");
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"[DB] CodeFirst CRITICAL FAILURE: {ex.Message}");
                        Console.WriteLine($"[DB] StackTrace: {ex.StackTrace}");
                        // 尝试写入文件以便调试
                        File.WriteAllText("db_init_error.log", $"{DateTime.Now}: {ex.Message}\n{ex.StackTrace}");
                        throw; // 抛出异常，阻止程序继续运行
                    }
                    
                    // Verify table existence
                    var tables = db.DbMaintenance.GetTableInfoList();
                    bool exists = tables.Any(t => t.Name.Equals("ChatLogs", StringComparison.OrdinalIgnoreCase));
                    
                    if (!exists)
                    {
                        Console.WriteLine("[DB] Warning: ChatLogs table not found after CodeFirst. Attempting manual creation...");
                        // Manual fallback
                        string createTableSql = @"
                            CREATE TABLE IF NOT EXISTS ChatLogs (
                                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                                Type TEXT,
                                EosId TEXT,
                                SteamId TEXT,
                                Name TEXT,
                                Message TEXT,
                                Timestamp datetime,
                                TeamId INTEGER,
                                SquadId INTEGER
                            );";
                        db.Ado.ExecuteCommand(createTableSql);
                        Console.WriteLine("[DB] Manual creation SQL executed.");
                        
                        // Verify again
                        exists = db.DbMaintenance.GetTableInfoList().Any(t => t.Name.Equals("ChatLogs", StringComparison.OrdinalIgnoreCase));
                    }

                    if (exists)
                    {
                        Console.WriteLine("[DB] Verification: ChatLogs table exists.");
                    }
                    else
                    {
                         Console.WriteLine("[DB] Error: ChatLogs table was NOT created!");
                    }

                    // Verify ServerInfoLogs existence
                    bool serverInfoExists = db.DbMaintenance.GetTableInfoList().Any(t => t.Name.Equals("ServerInfoLogs", StringComparison.OrdinalIgnoreCase));
                    if (!serverInfoExists)
                    {
                        Console.WriteLine("[DB] Warning: ServerInfoLogs table not found after CodeFirst. Attempting manual creation...");
                         string createTableSql = @"
                            CREATE TABLE IF NOT EXISTS ServerInfoLogs (
                                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                                Timestamp datetime,
                                PlayerCount INTEGER,
                                MaxPlayers INTEGER,
                                MapName TEXT,
                                ServerName TEXT
                            );";
                        db.Ado.ExecuteCommand(createTableSql);
                        Console.WriteLine("[DB] Manual creation for ServerInfoLogs executed.");
                    }
                    else
                    {
                        Console.WriteLine("[DB] Verification: ServerInfoLogs table exists.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"[Error] Database initialization failed: {ex.Message}");
                Console.WriteLine(ex.StackTrace);
            }
        }
    }
}