using System;
using System.Data;
using System.IO;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using System.Text.Json;
using System.Text.Json.Serialization;
using Microsoft.Data.Sqlite;
using Npgsql;
using Dapper;

namespace DatabaseCleanupTool
{
    // 配置类
    class AppConfig
    {
        [JsonPropertyName("SourceDatabase")]
        public string SourceDatabase { get; set; } = @"D:\NewProductionLineMonitorDB.db";
        
        [JsonPropertyName("TargetDatabase")]
        public string TargetDatabase { get; set; } = @"D:\NewProductionLineMonitorDB_Dev.db";
        
        [JsonPropertyName("KeepDays")]
        public int KeepDays { get; set; } = 14;
        
        [JsonPropertyName("SyncIntervalHours")]
        public int SyncIntervalHours { get; set; } = 24;
        
        [JsonPropertyName("IncrementalMode")]
        public bool IncrementalMode { get; set; } = true;
        
        [JsonPropertyName("StructureTables")]
        public List<string> StructureTables { get; set; } = new();
        
        [JsonPropertyName("DataTables")]
        public Dictionary<string, string> DataTables { get; set; } = new();
        
        [JsonPropertyName("TableStructureChange")]
        public TableStructureChangeConfig TableStructureChange { get; set; } = new();
        
        [JsonPropertyName("PostgreSQLConnectionString")]
        public string? PostgreSQLConnectionString { get; set; }
        
        [JsonPropertyName("SourceType")]
        public string SourceType { get; set; } = "SQLite";  // SQLite 或 PostgreSQL
    }
    
    class TableStructureChangeConfig
    {
        [JsonPropertyName("AutoFullSync")]
        public bool AutoFullSync { get; set; } = false;
        
        [JsonPropertyName("SyncNewTablesOnly")]
        public bool SyncNewTablesOnly { get; set; } = true;
        
        [JsonPropertyName("CheckIntervalHours")]
        public int CheckIntervalHours { get; set; } = 24;
    }

    class Program
    {
        // 配置对象
        static AppConfig _config = new AppConfig();
        
        // 运行时配置（可通过交互菜单修改）
        static string SOURCE_DB => _config.SourceDatabase;
        static string TARGET_DB => _config.TargetDatabase;
        static int KEEP_DAYS => _config.KeepDays;
        
        // 定期同步配置
        static bool _watchMode = false;
        static int _syncIntervalHours
        {
            get => _config.SyncIntervalHours;
            set => _config.SyncIntervalHours = value;
        }
        static string _logFile = "DatabaseCleanupTool.log";
        static CancellationTokenSource _cancellationTokenSource = new CancellationTokenSource();
        static bool _interactiveMode = true;  // 是否显示交互菜单
        static bool _incrementalMode
        {
            get => _config.IncrementalMode;
            set => _config.IncrementalMode = value;
        }
        static bool _fullSyncMode = false;   // 完整同步模式（删除重建）
        
        // 表配置（从配置文件读取）
        static string[] STRUCTURE_TABLES => _config.StructureTables.ToArray();
        static Dictionary<string, string> DATA_TABLES => _config.DataTables;
        
        // 表结构变化配置
        static bool _autoFullSync => _config.TableStructureChange.AutoFullSync;
        static bool _syncNewTablesOnly => _config.TableStructureChange.SyncNewTablesOnly;
        static DateTime _lastStructureCheck = DateTime.MinValue;
        static int _structureCheckIntervalHours => _config.TableStructureChange.CheckIntervalHours;
        
        // 数据源类型
        static bool _isPostgreSQLSource => _config.SourceType?.Equals("PostgreSQL", StringComparison.OrdinalIgnoreCase) == true;
        static string? _pgConnectionString => _config.PostgreSQLConnectionString;

        static void Main(string[] args)
        {
            // 加载配置文件
            LoadConfiguration();
            
            // 解析命令行参数
            ParseArguments(args);

            // 显示标题
            Console.ForegroundColor = ConsoleColor.Cyan;
            Console.WriteLine("╔═══════════════════════════════════════════════════════════════╗");
            Console.WriteLine("║         生产线监控数据库清理工具 v2.0                         ║");
            Console.WriteLine("║       Database Cleanup Tool for Production Line Monitor       ║");
            Console.WriteLine("╚═══════════════════════════════════════════════════════════════╝");
            Console.ResetColor();

            // 如果指定了命令行参数，跳过交互菜单（除非明确指定--interactive）
            if (args.Length > 0)
            {
                var hasInteractive = args.Any(a => a.ToLower() == "--interactive" || a.ToLower() == "--menu");
                if (!hasInteractive)
                {
                    _interactiveMode = false;
                }
            }

            // 交互式配置菜单
            if (_interactiveMode)
            {
                ShowInteractiveMenu();
            }
            else
            {
                // 非交互模式，显示配置但不询问保存
                ShowConfiguration(askSave: false);
            }

            // 检查源数据库
            if (!_isPostgreSQLSource)
            {
                if (!File.Exists(SOURCE_DB))
                {
                    Error($"错误: 源数据库不存在: {SOURCE_DB}");
                    Console.WriteLine("\n按任意键退出...");
                    Console.ReadKey();
                    return;
                }
            }
            else
            {
                if (string.IsNullOrEmpty(_pgConnectionString))
                {
                    Error("错误: PostgreSQL 连接字符串未配置");
                    Console.WriteLine("\n按任意键退出...");
                    Console.ReadKey();
                    return;
                }
                
                // 测试 PostgreSQL 连接
                try
                {
                    using var testConn = new NpgsqlConnection(_pgConnectionString);
                    testConn.Open();
                    Info("✅ PostgreSQL 连接测试成功");
                }
                catch (Exception ex)
                {
                    Error($"错误: PostgreSQL 连接失败: {ex.Message}");
                    Console.WriteLine("\n按任意键退出...");
                    Console.ReadKey();
                    return;
                }
            }

            // 设置Ctrl+C处理
            Console.CancelKeyPress += (sender, e) =>
            {
                e.Cancel = true;
                _cancellationTokenSource.Cancel();
                WriteLog("收到退出信号，正在停止...");
                Console.WriteLine("\n\n正在停止，请稍候...");
            };

            if (_watchMode)
            {
                // 定期同步模式
                RunWatchMode().Wait();
            }
            else
            {
                // 单次执行模式
                RunOnce();
            }
        }

        static void ShowInteractiveMenu()
        {
            Console.WriteLine("\n╔═══════════════════════════════════════════════════════════════╗");
            Console.WriteLine("║                    配置菜单                                    ║");
            Console.WriteLine("╚═══════════════════════════════════════════════════════════════╝\n");

            // 1. 运行模式
            Console.WriteLine("【1】运行模式");
            Console.WriteLine("  1. 单次执行（执行一次后退出）");
            Console.WriteLine("  2. 定期同步（持续运行，定期同步）");
            Console.Write($"  当前选择: {(_watchMode ? "2. 定期同步" : "1. 单次执行")} → 请选择 (1/2，直接回车保持当前): ");
            var modeChoice = Console.ReadLine();
            if (!string.IsNullOrWhiteSpace(modeChoice))
            {
                _watchMode = (modeChoice == "2");
            }

            // 2. 如果选择定期同步，设置同步间隔和模式
            if (_watchMode)
            {
                Console.WriteLine($"\n【2】同步间隔（当前: 每 {_syncIntervalHours} 小时）");
                Console.WriteLine("  1. 每 6 小时");
                Console.WriteLine("  2. 每 12 小时");
                Console.WriteLine("  3. 每 24 小时（默认）");
                Console.WriteLine("  4. 每 48 小时");
                Console.WriteLine("  5. 自定义");
                Console.Write("  请选择 (1-5，直接回车保持当前): ");
                var intervalChoice = Console.ReadLine();
                if (!string.IsNullOrWhiteSpace(intervalChoice))
                {
                    switch (intervalChoice)
                    {
                        case "1": _syncIntervalHours = 6; break;
                        case "2": _syncIntervalHours = 12; break;
                        case "3": _syncIntervalHours = 24; break;
                        case "4": _syncIntervalHours = 48; break;
                        case "5":
                            Console.Write("  请输入小时数: ");
                            if (int.TryParse(Console.ReadLine(), out int customHours) && customHours > 0)
                            {
                                _syncIntervalHours = customHours;
                            }
                            break;
                    }
                }

                // 2.1 同步模式（仅定期同步时）
                Console.WriteLine($"\n【2.1】同步模式（当前: {(_incrementalMode ? "增量同步" : "完整同步")}）");
                Console.WriteLine("  1. 增量同步（推荐，只同步新增数据，速度快）");
                Console.WriteLine("  2. 完整同步（删除重建，确保数据一致性）");
                Console.Write("  请选择 (1/2，直接回车保持当前): ");
                var syncModeChoice = Console.ReadLine();
                if (!string.IsNullOrWhiteSpace(syncModeChoice))
                {
                    _config.IncrementalMode = (syncModeChoice == "1");
                    _fullSyncMode = (syncModeChoice == "2");
                }
            }

            // 3. 保留天数
            Console.WriteLine($"\n【3】保留天数（当前: {KEEP_DAYS} 天）");
            Console.WriteLine("  1. 7 天（1周）");
            Console.WriteLine("  2. 14 天（2周，默认）");
            Console.WriteLine("  3. 30 天（1个月）");
            Console.WriteLine("  4. 自定义");
            Console.Write("  请选择 (1-4，直接回车保持当前): ");
            var daysChoice = Console.ReadLine();
            if (!string.IsNullOrWhiteSpace(daysChoice))
            {
                switch (daysChoice)
                {
                    case "1": _config.KeepDays = 7; break;
                    case "2": _config.KeepDays = 14; break;
                    case "3": _config.KeepDays = 30; break;
                    case "4":
                        Console.Write("  请输入天数: ");
                        if (int.TryParse(Console.ReadLine(), out int customDays) && customDays > 0)
                        {
                            _config.KeepDays = customDays;
                        }
                        break;
                }
            }

            // 4. 源数据库路径
            Console.WriteLine($"\n【4】源数据库路径");
            Console.WriteLine($"  当前: {SOURCE_DB}");
            Console.Write("  是否修改? (y/n，直接回车保持当前): ");
            var changeSource = Console.ReadLine()?.ToLower();
            if (changeSource == "y")
            {
                Console.Write("  请输入源数据库完整路径: ");
                var newSource = Console.ReadLine()?.Trim();
                if (!string.IsNullOrWhiteSpace(newSource))
                {
                    _config.SourceDatabase = newSource;
                }
            }

            // 5. 目标数据库路径
            Console.WriteLine($"\n【5】目标数据库路径");
            Console.WriteLine($"  当前: {TARGET_DB}");
            Console.Write("  是否修改? (y/n，直接回车保持当前): ");
            var changeTarget = Console.ReadLine()?.ToLower();
            if (changeTarget == "y")
            {
                Console.Write("  请输入目标数据库完整路径: ");
                var newTarget = Console.ReadLine()?.Trim();
                if (!string.IsNullOrWhiteSpace(newTarget))
                {
                    _config.TargetDatabase = newTarget;
                }
            }

            // 6. 数据源类型
            Console.WriteLine($"\n【6】数据源类型（当前: {_config.SourceType}）");
            Console.WriteLine("  1. SQLite（从SQLite数据库复制）");
            Console.WriteLine("  2. PostgreSQL（从PostgreSQL数据库复制）");
            Console.Write("  请选择 (1/2，直接回车保持当前): ");
            var sourceTypeChoice = Console.ReadLine();
            if (!string.IsNullOrWhiteSpace(sourceTypeChoice))
            {
                _config.SourceType = sourceTypeChoice == "2" ? "PostgreSQL" : "SQLite";
                
                if (_config.SourceType == "PostgreSQL")
                {
                    Console.Write("  请输入PostgreSQL连接字符串: ");
                    var pgConnStr = Console.ReadLine()?.Trim();
                    if (!string.IsNullOrWhiteSpace(pgConnStr))
                    {
                        _config.PostgreSQLConnectionString = pgConnStr;
                    }
                }
            }

            Console.WriteLine("\n" + new string('═', 70));
            
            // 显示配置并询问是否保存
            ShowConfiguration(askSave: true);
        }

        static void SaveConfiguration()
        {
            try
            {
                var options = new JsonSerializerOptions
                {
                    WriteIndented = true,
                    Encoder = System.Text.Encodings.Web.JavaScriptEncoder.UnsafeRelaxedJsonEscaping
                };
                var json = JsonSerializer.Serialize(_config, options);
                File.WriteAllText("appsettings.json", json);
                Success("✅ 配置已保存到 appsettings.json");
                WriteLog("配置已保存到 appsettings.json");
            }
            catch (Exception ex)
            {
                Error($"⚠️  保存配置失败: {ex.Message}");
                WriteLog($"保存配置失败: {ex.Message}");
            }
        }

        static void ShowConfiguration(bool askSave = false)
        {
            Console.WriteLine($"\n【最终配置】");
            Console.WriteLine($"  数据源类型: {_config.SourceType}");
            if (_isPostgreSQLSource)
            {
                var connStr = _pgConnectionString ?? "";
                var displayConnStr = connStr.Length > 60 ? connStr.Substring(0, 60) + "..." : connStr;
                Console.WriteLine($"  PostgreSQL连接: {displayConnStr}");
            }
            else
            {
                Console.WriteLine($"  源数据库: {SOURCE_DB}");
            }
            Console.WriteLine($"  目标数据库: {TARGET_DB}");
            Console.WriteLine($"  保留天数: 最近 {KEEP_DAYS} 天");
            Console.WriteLine($"  截止日期: {DateTime.Now:yyyy-MM-dd HH:mm:ss}");
            Console.WriteLine($"  起始日期: {DateTime.Now.AddDays(-KEEP_DAYS):yyyy-MM-dd HH:mm:ss}");
            
            if (_watchMode)
            {
                Console.WriteLine($"  运行模式: 定期同步模式");
                Console.WriteLine($"  同步模式: {(_incrementalMode ? "增量同步（只同步新增数据）" : "完整同步（删除重建）")}");
                Console.WriteLine($"  同步间隔: 每 {_syncIntervalHours} 小时");
                Console.WriteLine($"  日志文件: {_logFile}");
                Console.WriteLine($"  提示: 在定期同步模式下，按 'S' 键可立即同步");
            }
            else
            {
                Console.WriteLine($"  运行模式: 单次执行模式（完整同步）");
            }
            
            // 如果是在交互菜单中，询问是否保存
            if (askSave)
            {
                Console.Write("\n是否保存当前配置到 appsettings.json? (y/n，直接回车不保存): ");
                var saveChoice = Console.ReadLine()?.ToLower();
                if (saveChoice == "y")
                {
                    SaveConfiguration();
                }
            }
        }

        static void ParseArguments(string[] args)
        {
            for (int i = 0; i < args.Length; i++)
            {
                var arg = args[i].ToLower();
                
                if (arg == "--watch" || arg == "-w" || arg == "--continuous" || arg == "-c")
                {
                    _watchMode = true;
                    _interactiveMode = false;  // 命令行参数模式，跳过交互菜单
                }
                else if (arg == "--interval" || arg == "-i")
                {
                    if (i + 1 < args.Length && int.TryParse(args[i + 1], out int hours))
                    {
                        _syncIntervalHours = hours;
                        i++; // 跳过下一个参数
                    }
                    else
                    {
                        Console.WriteLine("警告: --interval 参数需要指定小时数，使用默认值24小时");
                    }
                }
                else if (arg == "--source" || arg == "-s")
                {
                    if (i + 1 < args.Length)
                    {
                        _config.SourceDatabase = args[i + 1];
                        i++;
                    }
                }
                else if (arg == "--target" || arg == "-t")
                {
                    if (i + 1 < args.Length)
                    {
                        _config.TargetDatabase = args[i + 1];
                        i++;
                    }
                }
                else if (arg == "--days" || arg == "-d")
                {
                    if (i + 1 < args.Length && int.TryParse(args[i + 1], out int days))
                    {
                        _config.KeepDays = days;
                        i++;
                    }
                }
                else if (arg == "--interactive" || arg == "--menu")
                {
                    _interactiveMode = true;  // 强制显示交互菜单
                }
                else if (arg == "--help" || arg == "-h")
                {
                    ShowHelp();
                    Environment.Exit(0);
                }
            }
        }

        static void ShowHelp()
        {
            Console.WriteLine("\n使用方法:");
            Console.WriteLine("  DatabaseCleanupTool.exe [选项]");
            Console.WriteLine("\n选项:");
            Console.WriteLine("  --watch, -w, --continuous, -c    启用定期同步模式");
            Console.WriteLine("  --interval, -i <小时数>          设置同步间隔（默认24小时）");
            Console.WriteLine("  --source, -s <路径>               源数据库路径");
            Console.WriteLine("  --target, -t <路径>               目标数据库路径");
            Console.WriteLine("  --days, -d <天数>                 保留天数（默认14天）");
            Console.WriteLine("  --interactive, --menu             显示交互式配置菜单");
            Console.WriteLine("  --help, -h                       显示帮助信息");
            Console.WriteLine("\n示例:");
            Console.WriteLine("  DatabaseCleanupTool.exe                    # 显示交互菜单");
            Console.WriteLine("  DatabaseCleanupTool.exe --watch            # 定期同步，每24小时");
            Console.WriteLine("  DatabaseCleanupTool.exe --watch --interval 12  # 定期同步，每12小时");
            Console.WriteLine("  DatabaseCleanupTool.exe --source D:\\DB.db --target D:\\Dev.db  # 指定路径");
            Console.WriteLine("\n交互式菜单:");
            Console.WriteLine("  - 不带任何参数运行程序，会显示交互式配置菜单");
            Console.WriteLine("  - 可以方便地选择运行模式、同步间隔、保留天数等");
            Console.WriteLine("\n定期同步模式:");
            Console.WriteLine("  - 程序将持续运行，定期同步数据库");
            Console.WriteLine("  - 按 Ctrl+C 可以优雅退出");
            Console.WriteLine("  - 同步日志会保存到 DatabaseCleanupTool.log 文件");
        }

        static async Task RunWatchMode()
        {
            WriteLog("启动定期同步模式");
            WriteLog($"同步间隔: 每 {_syncIntervalHours} 小时");
            WriteLog($"同步模式: {(_incrementalMode ? "增量同步" : "完整同步")}");
            
            Console.WriteLine("\n✅ 定期同步模式已启动");
            Console.WriteLine($"   同步模式: {(_incrementalMode ? "增量同步（只同步新增数据）" : "完整同步（删除重建）")}");
            Console.WriteLine($"   同步间隔: 每 {_syncIntervalHours} 小时");
            Console.WriteLine($"   日志文件: {_logFile}");
            Console.WriteLine($"   按 'S' 键可立即同步");
            Console.WriteLine($"   按 Ctrl+C 退出\n");

            var nextSyncTime = DateTime.Now;  // 立即执行第一次同步
            var syncCount = 0;
            var manualSyncRequested = false;

            // 启动键盘监听任务（用于立即同步）
            var keyboardTask = Task.Run(async () =>
            {
                while (!_cancellationTokenSource.Token.IsCancellationRequested)
                {
                    if (Console.KeyAvailable)
                    {
                        var key = Console.ReadKey(true);
                        if (key.Key == ConsoleKey.S && !char.IsControl(key.KeyChar))
                        {
                            manualSyncRequested = true;
                            Console.WriteLine($"\n[{DateTime.Now:yyyy-MM-dd HH:mm:ss}] 收到立即同步请求...");
                        }
                    }
                    await Task.Delay(100, _cancellationTokenSource.Token);
                }
            });

            while (!_cancellationTokenSource.Token.IsCancellationRequested)
            {
                try
                {
                    // 检查是否到了同步时间或收到立即同步请求
                    if (DateTime.Now >= nextSyncTime || manualSyncRequested)
                    {
                        syncCount++;
                        var isManual = manualSyncRequested;
                        manualSyncRequested = false;
                        
                        WriteLog($"========== 开始第 {syncCount} 次同步 {(isManual ? "(手动触发)" : "")} ==========");
                        Console.WriteLine($"\n[{DateTime.Now:yyyy-MM-dd HH:mm:ss}] 开始第 {syncCount} 次同步{(isManual ? " (手动触发)" : "")}...");
                        
                        var startTime = DateTime.Now;
                        
                        // 根据模式选择同步方法
                        if (_incrementalMode && File.Exists(TARGET_DB))
                        {
                            RunIncrementalSync();
                        }
                        else
                        {
                            RunSync();  // 完整同步
                        }
                        
                        var endTime = DateTime.Now;
                        
                        var duration = (endTime - startTime).TotalSeconds;
                        WriteLog($"第 {syncCount} 次同步完成，耗时: {duration:F2} 秒");
                        Console.WriteLine($"[{DateTime.Now:yyyy-MM-dd HH:mm:ss}] 同步完成，耗时: {duration:F2} 秒");
                        
                        // 计算下次同步时间（如果是手动触发，不重置定时器）
                        if (!isManual)
                        {
                            nextSyncTime = DateTime.Now.AddHours(_syncIntervalHours);
                            WriteLog($"下次同步时间: {nextSyncTime:yyyy-MM-dd HH:mm:ss}");
                            Console.WriteLine($"下次同步时间: {nextSyncTime:yyyy-MM-dd HH:mm:ss}\n");
                        }
                        else
                        {
                            Console.WriteLine($"下次自动同步时间: {nextSyncTime:yyyy-MM-dd HH:mm:ss}\n");
                        }
                    }
                    else
                    {
                        // 显示倒计时（每分钟更新一次）
                        var remaining = nextSyncTime - DateTime.Now;
                        if (remaining.TotalSeconds > 0 && DateTime.Now.Second < 5)  // 每分钟的前5秒显示
                        {
                            var hours = (int)remaining.TotalHours;
                            var minutes = remaining.Minutes;
                            Console.Write($"\r等待下次同步，剩余时间: {hours}小时 {minutes}分钟... (按 'S' 立即同步)");
                        }
                    }

                    // 等待10秒再检查（平衡响应速度和CPU占用）
                    await Task.Delay(TimeSpan.FromSeconds(10), _cancellationTokenSource.Token);
                }
                catch (TaskCanceledException)
                {
                    // 正常退出
                    break;
                }
                catch (Exception ex)
                {
                    WriteLog($"同步过程中发生错误: {ex.Message}");
                    Error($"同步错误: {ex.Message}");
                    // 发生错误后，等待1小时再重试
                    await Task.Delay(TimeSpan.FromHours(1), _cancellationTokenSource.Token);
                }
            }

            WriteLog("定期同步模式已停止");
            Console.WriteLine("\n定期同步模式已停止");
        }

        static void RunOnce()
        {
            try
            {
                // 确认继续
                Console.Write("\n是否继续? (y/n): ");
                var confirm = Console.ReadLine()?.ToLower();
                if (confirm != "y")
                {
                    Console.WriteLine("操作已取消");
                    return;
                }

                RunSync();
            }
            catch (Exception ex)
            {
                Error($"\n❌ 错误: {ex.Message}");
                Error($"详细信息: {ex.StackTrace}");
            }

            Console.WriteLine("\n按任意键退出...");
            Console.ReadKey();
        }

        static void RunSync()
        {
            var startTime = DateTime.Now;
            WriteLog($"开始完整同步，时间: {startTime:yyyy-MM-dd HH:mm:ss}");

            // 步骤1: 删除旧的目标数据库
            if (File.Exists(TARGET_DB))
            {
                Info("删除旧的目标数据库...");
                File.Delete(TARGET_DB);
            }

            // 步骤2: 复制数据库结构
            Info("复制数据库结构...");
            if (_isPostgreSQLSource)
            {
                CopyDatabaseStructureFromPostgreSQL();
            }
            else
            {
                CopyDatabaseStructure();
            }

            // 步骤3: 复制结构表数据
            Info("复制结构表数据...");
            foreach (var table in STRUCTURE_TABLES)
            {
                if (_isPostgreSQLSource)
                {
                    CopyStructureTableFromPostgreSQL(table);
                }
                else
                {
                    CopyStructureTable(table);
                }
            }

            // 步骤4: 复制数据表（按时间过滤）
            Info($"复制数据表（保留最近 {KEEP_DAYS} 天）...");
            var cutoffDate = DateTime.Now.AddDays(-KEEP_DAYS);
            foreach (var (table, dateColumn) in DATA_TABLES)
            {
                if (_isPostgreSQLSource)
                {
                    CopyDataTableFromPostgreSQL(table, dateColumn, cutoffDate);
                }
                else
                {
                    CopyDataTable(table, dateColumn, cutoffDate);
                }
            }

            // 步骤5: 优化数据库
            Info("优化目标数据库...");
            OptimizeDatabase();

            // 步骤6: 生成报告
            var endTime = DateTime.Now;
            GenerateReport(startTime, endTime);

            Success($"\n✅ 完成! 耗时: {(endTime - startTime).TotalSeconds:F2} 秒");
            Success($"新数据库位置: {TARGET_DB}");
            Success($"新数据库大小: {GetFileSize(TARGET_DB)}");
            
            WriteLog($"完整同步完成，耗时: {(endTime - startTime).TotalSeconds:F2} 秒");
        }

        static void RunIncrementalSync()
        {
            var startTime = DateTime.Now;
            WriteLog($"开始增量同步，时间: {startTime:yyyy-MM-dd HH:mm:ss}");

            // 检查目标数据库是否存在
            if (!File.Exists(TARGET_DB))
            {
                Info("目标数据库不存在，执行完整同步...");
                RunSync();
                return;
            }

            // 检查表结构是否一致（根据配置决定是否检查）
            var structureChanged = CheckTableStructure(out var newTables, out var missingTables);
            if (structureChanged)
            {
                if (_autoFullSync)
                {
                    // 配置为自动全量同步
                    Warn("检测到表结构变化，执行完整同步...");
                    WriteLog("表结构不一致，切换到完整同步模式");
                    RunSync();
                    return;
                }
                else if (_syncNewTablesOnly && (newTables.Any() || missingTables.Any()))
                {
                    // 配置为只同步新表
                    Warn($"检测到表结构变化：新增表 {string.Join(", ", newTables)}，缺失表 {string.Join(", ", missingTables)}");
                    WriteLog($"表结构变化：新增 {newTables.Count} 个表，缺失 {missingTables.Count} 个表");
                    
                    // 增量处理新表
                    SyncNewTablesOnly(newTables, missingTables);
                    // 继续增量同步
                }
                else
                {
                    // 其他情况，继续增量同步（可能只是列变化，不影响数据同步）
                    Warn("检测到表结构变化，但配置为继续增量同步...");
                }
            }

            Info("增量同步模式：只同步新增数据...");

            using var sourceConn = new SqliteConnection($"Data Source={SOURCE_DB};Mode=ReadOnly");
            using var targetConn = new SqliteConnection($"Data Source={TARGET_DB}");
            sourceConn.Open();
            targetConn.Open();

            var totalNewRows = 0;
            var cutoffDate = DateTime.Now.AddDays(-KEEP_DAYS);

            // 1. 同步结构表（全量更新，因为配置可能变化）
            Info("更新结构表数据...");
            foreach (var table in STRUCTURE_TABLES)
            {
                var updated = SyncStructureTableIncremental(sourceConn, targetConn, table);
                if (updated > 0)
                {
                    totalNewRows += updated;
                }
            }

            // 2. 增量同步数据表（只同步新增数据）
            Info($"增量同步数据表（保留最近 {KEEP_DAYS} 天）...");
            foreach (var (table, dateColumn) in DATA_TABLES)
            {
                var newRows = SyncDataTableIncremental(sourceConn, targetConn, table, dateColumn, cutoffDate);
                totalNewRows += newRows;
            }

            // 3. 清理过期数据（删除超过保留天数的数据）
            Info("清理过期数据...");
            var deletedRows = CleanupExpiredData(targetConn, cutoffDate);
            
            // 4. 优化数据库
            Info("优化目标数据库...");
            OptimizeDatabase();

            var endTime = DateTime.Now;
            var duration = (endTime - startTime).TotalSeconds;

            Success($"\n✅ 增量同步完成! 耗时: {duration:F2} 秒");
            Success($"新增数据: {totalNewRows:N0} 行");
            Success($"删除过期数据: {deletedRows:N0} 行");
            Success($"数据库大小: {GetFileSize(TARGET_DB)}");
            
            WriteLog($"增量同步完成，耗时: {duration:F2} 秒，新增: {totalNewRows} 行，删除: {deletedRows} 行");
        }

        static void LoadConfiguration()
        {
            var configFile = "appsettings.json";
            if (File.Exists(configFile))
            {
                try
                {
                    var json = File.ReadAllText(configFile);
                    _config = JsonSerializer.Deserialize<AppConfig>(json, new JsonSerializerOptions
                    {
                        PropertyNameCaseInsensitive = true,
                        ReadCommentHandling = JsonCommentHandling.Skip
                    }) ?? new AppConfig();
                    Info($"✅ 已加载配置文件: {configFile}");
                }
                catch (Exception ex)
                {
                    Warn($"⚠️  加载配置文件失败: {ex.Message}，使用默认配置");
                    _config = new AppConfig();
                }
            }
            else
            {
                Warn($"⚠️  配置文件不存在: {configFile}，使用默认配置");
                // 创建默认配置文件
                SaveDefaultConfiguration();
            }
        }

        static void SaveDefaultConfiguration()
        {
            try
            {
                // 初始化默认配置
                var defaultConfig = new AppConfig
                {
                    SourceDatabase = @"D:\NewProductionLineMonitorDB.db",
                    TargetDatabase = @"D:\NewProductionLineMonitorDB_Dev.db",
                    KeepDays = 14,
                    SyncIntervalHours = 24,
                    IncrementalMode = true,
                    StructureTables = new List<string>
                    {
                        "__EFMigrationsHistory",
                        "ProductionLines",
                        "Machines",
                        "Roles",
                        "Users",
                        "Menus",
                        "RoleMenus",
                        "Recipes",
                        "Cims",
                        "ElectricEnergyMeters"
                    },
                    DataTables = new Dictionary<string, string>
                    {
                        { "MachineOutPutPerHours", "DataTime" },
                        { "MachineFaultRecords", "StartTime" },
                        { "MachineStatistics", "Date" },
                        { "ElectricEnergyMeterLogs", "CreateTime" },
                        { "MachinePowerConsumptions", "CreateTime" },
                        { "MachineProcessBTs", "CreateTime" },
                        { "MachineProcessFOGs", "CreateTime" },
                        { "MachineProcessPSs", "CreateTime" },
                        { "MachineProcessPSEPDs", "CreateTime" },
                        { "MachineFaultComparisons", "CreateTime" }
                    },
                    TableStructureChange = new TableStructureChangeConfig
                    {
                        AutoFullSync = false,
                        SyncNewTablesOnly = true,
                        CheckIntervalHours = 24
                    }
                };
                
                var options = new JsonSerializerOptions
                {
                    WriteIndented = true,
                    Encoder = System.Text.Encodings.Web.JavaScriptEncoder.UnsafeRelaxedJsonEscaping
                };
                var json = JsonSerializer.Serialize(defaultConfig, options);
                File.WriteAllText("appsettings.json", json);
                _config = defaultConfig;
                Info("✅ 已创建默认配置文件: appsettings.json");
            }
            catch (Exception ex)
            {
                Warn($"⚠️  创建默认配置文件失败: {ex.Message}");
            }
        }

        static bool CheckTableStructure(out List<string> newTables, out List<string> missingTables)
        {
            newTables = new List<string>();
            missingTables = new List<string>();
            
            // 定期检查表结构（根据配置的检查间隔）
            var now = DateTime.Now;
            if ((now - _lastStructureCheck).TotalHours < _structureCheckIntervalHours)
            {
                // 还没到检查时间，返回false（假设没有变化）
                return false;
            }
            _lastStructureCheck = now;
            
            try
            {
                using var sourceConn = new SqliteConnection($"Data Source={SOURCE_DB};Mode=ReadOnly");
                using var targetConn = new SqliteConnection($"Data Source={TARGET_DB}");
                sourceConn.Open();
                targetConn.Open();

                // 获取源数据库的所有表
                var sourceTables = sourceConn.Query<string>(
                    "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name")
                    .ToHashSet();

                // 获取目标数据库的所有表
                var targetTables = targetConn.Query<string>(
                    "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name")
                    .ToHashSet();

                // 检查表是否一致
                if (!sourceTables.SetEquals(targetTables))
                {
                    newTables = sourceTables.Except(targetTables).ToList();
                    missingTables = targetTables.Except(sourceTables).ToList();
                    
                    if (newTables.Any())
                    {
                        Warn($"源数据库新增表: {string.Join(", ", newTables)}");
                    }
                    if (missingTables.Any())
                    {
                        Warn($"目标数据库缺失表: {string.Join(", ", missingTables)}");
                    }
                    return true;
                }

                // 检查每个表的列结构
                foreach (var table in sourceTables)
                {
                    var sourceColumns = sourceConn.Query<dynamic>(
                        $"PRAGMA table_info([{table}])").Select(r => (string)r.name).OrderBy(n => n).ToList();
                    
                    var targetColumns = targetConn.Query<dynamic>(
                        $"PRAGMA table_info([{table}])").Select(r => (string)r.name).OrderBy(n => n).ToList();

                    if (sourceColumns.Count != targetColumns.Count)
                    {
                        Warn($"表 {table} 的列数不一致（源: {sourceColumns.Count}, 目标: {targetColumns.Count}）");
                        return true;  // 列结构变化，返回true
                    }

                    // 检查列名是否一致
                    if (!sourceColumns.SequenceEqual(targetColumns))
                    {
                        var missing = sourceColumns.Except(targetColumns).ToList();
                        var extra = targetColumns.Except(sourceColumns).ToList();
                        if (missing.Any())
                            Warn($"表 {table} 缺少列: {string.Join(", ", missing)}");
                        if (extra.Any())
                            Warn($"表 {table} 多余列: {string.Join(", ", extra)}");
                        return true;  // 列结构变化，返回true
                    }
                }

                return false;  // 表结构一致
            }
            catch (Exception ex)
            {
                Warn($"检查表结构时出错: {ex.Message}");
                return false;  // 出错时返回false，不中断同步
            }
        }

        static int SyncStructureTableIncremental(SqliteConnection sourceConn, SqliteConnection targetConn, string tableName)
        {
            try
            {
                // 检查表是否存在
                var tableExists = sourceConn.ExecuteScalar<int>(
                    "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=@name",
                    new { name = tableName });

                if (tableExists == 0)
                {
                    return 0;
                }

                // 获取源表的所有数据
                var sourceData = sourceConn.Query($"SELECT * FROM [{tableName}]").ToList();
                
                // 清空目标表
                targetConn.Execute($"DELETE FROM [{tableName}]");

                // 重新插入所有数据
                if (sourceData.Count > 0)
                {
                    using var transaction = targetConn.BeginTransaction();
                    foreach (var row in sourceData)
                    {
                        var dict = (IDictionary<string, object>)row;
                        var columns = string.Join(", ", dict.Keys.Select(k => $"[{k}]"));
                        var parameters = string.Join(", ", dict.Keys.Select(k => $"@{k}"));
                        var sql = $"INSERT INTO [{tableName}] ({columns}) VALUES ({parameters})";
                        targetConn.Execute(sql, dict, transaction);
                    }
                    transaction.Commit();
                }

                Info($"  ✅ {tableName,-30} (更新 {sourceData.Count:N0} 行)");
                return sourceData.Count;
            }
            catch (Exception ex)
            {
                Warn($"  ⚠️  同步结构表 {tableName} 时出错: {ex.Message}");
                return 0;
            }
        }

        static int SyncDataTableIncremental(SqliteConnection sourceConn, SqliteConnection targetConn, 
            string tableName, string dateColumn, DateTime cutoffDate)
        {
            try
            {
                // 检查表是否存在
                var tableExists = sourceConn.ExecuteScalar<int>(
                    "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=@name",
                    new { name = tableName });

                if (tableExists == 0)
                {
                    return 0;
                }

                // 获取目标表中最大的时间戳（用于增量同步）
                DateTime? maxDate = null;
                try
                {
                    var maxDateStr = targetConn.ExecuteScalar<string>(
                        $"SELECT MAX([{dateColumn}]) FROM [{tableName}]");
                    if (!string.IsNullOrEmpty(maxDateStr) && DateTime.TryParse(maxDateStr, out var parsedDate))
                    {
                        maxDate = parsedDate;
                    }
                }
                catch
                {
                    // 如果表为空或不存在，maxDate 保持为 null
                }

                // 查询需要同步的新数据（大于最大时间戳，且在保留天数内）
                object parameters;
                string query;
                
                if (maxDate.HasValue)
                {
                    query = $@"SELECT * FROM [{tableName}] 
                              WHERE [{dateColumn}] >= @cutoffDate AND [{dateColumn}] > @maxDate";
                    parameters = new 
                    { 
                        cutoffDate = cutoffDate.ToString("yyyy-MM-dd HH:mm:ss"),
                        maxDate = maxDate.Value.ToString("yyyy-MM-dd HH:mm:ss")
                    };
                }
                else
                {
                    query = $@"SELECT * FROM [{tableName}] 
                              WHERE [{dateColumn}] >= @cutoffDate";
                    parameters = new { cutoffDate = cutoffDate.ToString("yyyy-MM-dd HH:mm:ss") };
                }

                var newRows = sourceConn.Query(query, parameters).ToList();

                if (newRows.Count == 0)
                {
                    Info($"  📋 {tableName,-30} (无新数据)");
                    return 0;
                }

                // 批量插入新数据
                using var transaction = targetConn.BeginTransaction();
                var inserted = 0;
                foreach (var row in newRows)
                {
                    var dict = (IDictionary<string, object>)row;
                    var columns = string.Join(", ", dict.Keys.Select(k => $"[{k}]"));
                    var paramsList = string.Join(", ", dict.Keys.Select(k => $"@{k}"));
                    var sql = $"INSERT OR IGNORE INTO [{tableName}] ({columns}) VALUES ({paramsList})";
                    targetConn.Execute(sql, dict, transaction);
                    inserted++;
                }
                transaction.Commit();

                Info($"  ✅ {tableName,-30} (新增 {inserted:N0} 行)");
                return inserted;
            }
            catch (Exception ex)
            {
                Warn($"  ⚠️  增量同步表 {tableName} 时出错: {ex.Message}");
                return 0;
            }
        }

        static void SyncNewTablesOnly(List<string> newTables, List<string> missingTables)
        {
            using var sourceConn = new SqliteConnection($"Data Source={SOURCE_DB};Mode=ReadOnly");
            using var targetConn = new SqliteConnection($"Data Source={TARGET_DB}");
            sourceConn.Open();
            targetConn.Open();

            Info("增量处理新表...");

            // 1. 处理源数据库新增的表
            foreach (var tableName in newTables)
            {
                try
                {
                    // 复制表结构
                    var createSql = sourceConn.Query<string>(
                        "SELECT sql FROM sqlite_master WHERE type='table' AND name=@name",
                        new { name = tableName }).FirstOrDefault();
                    
                    if (!string.IsNullOrEmpty(createSql))
                    {
                        targetConn.Execute(createSql);
                        Info($"  ✅ 已创建新表结构: {tableName}");
                    }

                    // 判断是结构表还是数据表
                    bool isStructureTable = STRUCTURE_TABLES.Contains(tableName);
                    bool isDataTable = DATA_TABLES.ContainsKey(tableName);

                    if (isStructureTable)
                    {
                        // 结构表：全量复制
                        var count = SyncStructureTableIncremental(sourceConn, targetConn, tableName);
                        Info($"  ✅ 已同步结构表数据: {tableName} ({count:N0} 行)");
                    }
                    else if (isDataTable)
                    {
                        // 数据表：按时间过滤复制
                        var dateColumn = DATA_TABLES[tableName];
                        var cutoffDate = DateTime.Now.AddDays(-KEEP_DAYS);
                        var count = CopyDataTableForNewTable(sourceConn, targetConn, tableName, dateColumn, cutoffDate);
                        Info($"  ✅ 已同步数据表数据: {tableName} ({count:N0} 行，保留最近 {KEEP_DAYS} 天)");
                    }
                    else
                    {
                        // 未配置的表：只复制结构，不复制数据
                        Warn($"  ⚠️  表 {tableName} 未在配置中，只复制结构，不复制数据");
                        Info($"  💡 提示: 如需复制数据，请在 appsettings.json 中添加配置");
                    }
                }
                catch (Exception ex)
                {
                    Warn($"  ⚠️  处理新表 {tableName} 时出错: {ex.Message}");
                }
            }

            // 2. 处理目标数据库多余的表（源数据库已删除）
            foreach (var tableName in missingTables)
            {
                try
                {
                    // 可以选择删除或保留
                    // 这里选择保留，因为可能是历史数据
                    Warn($"  ⚠️  目标数据库存在源数据库已删除的表: {tableName}（保留）");
                }
                catch (Exception ex)
                {
                    Warn($"  ⚠️  处理缺失表 {tableName} 时出错: {ex.Message}");
                }
            }
        }

        static int CopyDataTableForNewTable(SqliteConnection sourceConn, SqliteConnection targetConn, 
            string tableName, string dateColumn, DateTime cutoffDate)
        {
            try
            {
                // 获取要复制的行数
                var keepCount = sourceConn.ExecuteScalar<int>(
                    $"SELECT COUNT(*) FROM [{tableName}] WHERE [{dateColumn}] >= @cutoffDate",
                    new { cutoffDate = cutoffDate.ToString("yyyy-MM-dd HH:mm:ss") });

                if (keepCount == 0)
                {
                    return 0;
                }

                // 流式处理：分批查询和插入
                using var transaction = targetConn.BeginTransaction();
                var batchSize = 1000;
                var insertedCount = 0;
                var offset = 0;

                while (insertedCount < keepCount)
                {
                    var currentBatch = sourceConn.Query(
                        $"SELECT * FROM [{tableName}] WHERE [{dateColumn}] >= @cutoffDate LIMIT @limit OFFSET @offset",
                        new 
                        { 
                            cutoffDate = cutoffDate.ToString("yyyy-MM-dd HH:mm:ss"),
                            limit = batchSize,
                            offset = offset
                        }).ToList();

                    if (currentBatch.Count == 0)
                        break;

                    // 批量插入
                    InsertBatch(targetConn, tableName, currentBatch.Cast<object>().ToList(), transaction);
                    insertedCount += currentBatch.Count;
                    offset += batchSize;
                }

                transaction.Commit();
                return insertedCount;
            }
            catch (Exception ex)
            {
                Warn($"  ⚠️  复制新数据表 {tableName} 时出错: {ex.Message}");
                return 0;
            }
        }

        static int CleanupExpiredData(SqliteConnection targetConn, DateTime cutoffDate)
        {
            var totalDeleted = 0;
            foreach (var (table, dateColumn) in DATA_TABLES)
            {
                try
                {
                    var deleted = targetConn.Execute(
                        $"DELETE FROM [{table}] WHERE [{dateColumn}] < @cutoffDate",
                        new { cutoffDate = cutoffDate.ToString("yyyy-MM-dd HH:mm:ss") });
                    totalDeleted += deleted;
                }
                catch (Exception ex)
                {
                    Warn($"  ⚠️  清理表 {table} 过期数据时出错: {ex.Message}");
                }
            }
            return totalDeleted;
        }

        static void WriteLog(string message)
        {
            try
            {
                var logMessage = $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss}] {message}";
                File.AppendAllText(_logFile, logMessage + Environment.NewLine);
            }
            catch
            {
                // 忽略日志写入错误
            }
        }

        static void CopyDatabaseStructure()
        {
            // 使用只读模式打开源数据库，避免创建WAL文件
            using var sourceConn = new SqliteConnection($"Data Source={SOURCE_DB};Mode=ReadOnly");
            sourceConn.Open();

            using var targetConn = new SqliteConnection($"Data Source={TARGET_DB}");
            targetConn.Open();

            // 获取所有表的创建语句
            var tables = sourceConn.Query<string>(
                "SELECT sql FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'");

            foreach (var createSql in tables)
            {
                if (!string.IsNullOrEmpty(createSql))
                {
                    targetConn.Execute(createSql);
                }
            }

            // 复制索引
            var indexes = sourceConn.Query<string>(
                "SELECT sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL");

            foreach (var indexSql in indexes)
            {
                try
                {
                    targetConn.Execute(indexSql);
                }
                catch
                {
                    // 忽略已存在的索引
                }
            }
        }

        static void CopyDatabaseStructureFromPostgreSQL()
        {
            if (string.IsNullOrEmpty(_pgConnectionString))
            {
                throw new Exception("PostgreSQL 连接字符串未配置");
            }

            using var pgConn = new NpgsqlConnection(_pgConnectionString);
            pgConn.Open();

            using var targetConn = new SqliteConnection($"Data Source={TARGET_DB}");
            targetConn.Open();

            // 获取所有表名
            var tables = pgConn.Query<string>(
                @"SELECT table_name 
                  FROM information_schema.tables 
                  WHERE table_schema = 'public' 
                  AND table_type = 'BASE TABLE'
                  ORDER BY table_name");

            foreach (var tableName in tables)
            {
                try
                {
                    // 获取表结构
                    var columns = pgConn.Query<dynamic>(
                        @"SELECT 
                            column_name,
                            data_type,
                            character_maximum_length,
                            is_nullable,
                            column_default
                          FROM information_schema.columns
                          WHERE table_schema = 'public' 
                          AND table_name = @tableName
                          ORDER BY ordinal_position",
                        new { tableName });

                    if (!columns.Any())
                        continue;

                    // 构建 CREATE TABLE 语句
                    var columnDefs = new List<string>();
                    foreach (var col in columns)
                    {
                        var colName = (string)col.column_name;
                        var dataType = MapPostgreSQLTypeToSQLite((string)col.data_type, col.character_maximum_length);
                        var nullable = (string)col.is_nullable == "YES" ? "" : " NOT NULL";
                        var defaultValue = col.column_default != null ? $" DEFAULT {col.column_default}" : "";
                        
                        columnDefs.Add($"[{colName}] {dataType}{nullable}{defaultValue}");
                    }

                    var createTableSql = $"CREATE TABLE IF NOT EXISTS [{tableName}] ({string.Join(", ", columnDefs)})";
                    targetConn.Execute(createTableSql);
                    
                    Info($"  ✅ 已创建表: {tableName}");
                }
                catch (Exception ex)
                {
                    Warn($"  ⚠️  创建表 {tableName} 失败: {ex.Message}");
                }
            }
        }

        static string MapPostgreSQLTypeToSQLite(string pgType, object? maxLength)
        {
            // PostgreSQL 类型到 SQLite 类型映射
            return pgType.ToLower() switch
            {
                "integer" or "int" or "int4" => "INTEGER",
                "bigint" or "int8" => "INTEGER",
                "smallint" or "int2" => "INTEGER",
                "real" or "float4" => "REAL",
                "double precision" or "float8" => "REAL",
                "numeric" or "decimal" => "REAL",
                "boolean" or "bool" => "INTEGER",  // SQLite 用 0/1 表示布尔值
                "text" => "TEXT",
                "character varying" or "varchar" => maxLength != null ? $"TEXT" : "TEXT",
                "character" or "char" => maxLength != null ? $"TEXT" : "TEXT",
                "timestamp" or "timestamp without time zone" => "TEXT",
                "timestamp with time zone" => "TEXT",
                "date" => "TEXT",
                "time" => "TEXT",
                "uuid" => "TEXT",
                "json" or "jsonb" => "TEXT",
                "bytea" => "BLOB",
                _ => "TEXT"  // 默认映射为 TEXT
            };
        }

        static void CopyStructureTable(string tableName)
        {
            using var sourceConn = new SqliteConnection($"Data Source={SOURCE_DB};Mode=ReadOnly");
            using var targetConn = new SqliteConnection($"Data Source={TARGET_DB}");

            sourceConn.Open();
            targetConn.Open();

            // 检查表是否存在
            var tableExists = sourceConn.ExecuteScalar<int>(
                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=@name",
                new { name = tableName });

            if (tableExists == 0)
            {
                Warn($"  ⚠️  表不存在: {tableName}，跳过");
                return;
            }

            // 获取行数
            var count = sourceConn.ExecuteScalar<int>($"SELECT COUNT(*) FROM [{tableName}]");

            if (count == 0)
            {
                Info($"  📋 {tableName,-30} (0 行)");
                return;
            }

            // 复制数据
            var data = sourceConn.Query($"SELECT * FROM [{tableName}]");

            using var transaction = targetConn.BeginTransaction();
            foreach (var row in data)
            {
                var dict = (IDictionary<string, object>)row;
                var columns = string.Join(", ", dict.Keys.Select(k => $"[{k}]"));
                var parameters = string.Join(", ", dict.Keys.Select(k => $"@{k}"));

                var sql = $"INSERT INTO [{tableName}] ({columns}) VALUES ({parameters})";
                targetConn.Execute(sql, dict, transaction);
            }
            transaction.Commit();

            Success($"  ✅ {tableName,-30} ({count:N0} 行)");
        }

        static void CopyStructureTableFromPostgreSQL(string tableName)
        {
            if (string.IsNullOrEmpty(_pgConnectionString))
            {
                Warn($"  ⚠️  PostgreSQL 连接字符串未配置，跳过表: {tableName}");
                return;
            }

            using var pgConn = new NpgsqlConnection(_pgConnectionString);
            using var targetConn = new SqliteConnection($"Data Source={TARGET_DB}");

            pgConn.Open();
            targetConn.Open();

            // 检查表是否存在
            var tableExists = pgConn.ExecuteScalar<int>(
                @"SELECT COUNT(*) 
                  FROM information_schema.tables 
                  WHERE table_schema = 'public' 
                  AND table_name = @tableName",
                new { tableName });

            if (tableExists == 0)
            {
                Warn($"  ⚠️  表不存在: {tableName}，跳过");
                return;
            }

            // 获取行数
            var count = pgConn.ExecuteScalar<long>($"SELECT COUNT(*) FROM \"{tableName}\"");

            if (count == 0)
            {
                Info($"  📋 {tableName,-30} (0 行)");
                return;
            }

            // 分批复制数据
            using var transaction = targetConn.BeginTransaction();
            var batchSize = 1000;
            var offset = 0;
            var totalInserted = 0;

            while (true)
            {
                var data = pgConn.Query($"SELECT * FROM \"{tableName}\" LIMIT {batchSize} OFFSET {offset}").ToList();
                
                if (data.Count == 0)
                    break;

                foreach (var row in data)
                {
                    var dict = (IDictionary<string, object>)row;
                    // 处理 PostgreSQL 特殊类型
                    var processedDict = ProcessPostgreSQLData(dict);
                    
                    var columns = string.Join(", ", processedDict.Keys.Select(k => $"[{k}]"));
                    var parameters = string.Join(", ", processedDict.Keys.Select(k => $"@{k}"));

                    var sql = $"INSERT INTO [{tableName}] ({columns}) VALUES ({parameters})";
                    targetConn.Execute(sql, processedDict, transaction);
                    totalInserted++;
                }

                offset += batchSize;
                
                if (totalInserted % 1000 == 0)
                {
                    Console.Write($"\r  ⏳ {tableName,-30} ({totalInserted:N0}/{count:N0})");
                }
            }

            transaction.Commit();
            Console.Write("\r");
            Success($"  ✅ {tableName,-30} ({totalInserted:N0} 行)");
        }

        static Dictionary<string, object> ProcessPostgreSQLData(IDictionary<string, object> dict)
        {
            var result = new Dictionary<string, object>();
            foreach (var kvp in dict)
            {
                var value = kvp.Value;
                
                // 处理 PostgreSQL 特殊类型
                if (value == null || value == DBNull.Value)
                {
                    result[kvp.Key] = null;
                }
                else if (value is bool boolValue)
                {
                    // SQLite 用 0/1 表示布尔值
                    result[kvp.Key] = boolValue ? 1 : 0;
                }
                else if (value is DateTime dateTimeValue)
                {
                    // 转换为字符串
                    result[kvp.Key] = dateTimeValue.ToString("yyyy-MM-dd HH:mm:ss");
                }
                else if (value is DateTimeOffset dateTimeOffsetValue)
                {
                    result[kvp.Key] = dateTimeOffsetValue.ToString("yyyy-MM-dd HH:mm:ss");
                }
                else if (value is Guid guidValue)
                {
                    result[kvp.Key] = guidValue.ToString();
                }
                else
                {
                    result[kvp.Key] = value;
                }
            }
            return result;
        }

        static void CopyDataTable(string tableName, string dateColumn, DateTime cutoffDate)
        {
            using var sourceConn = new SqliteConnection($"Data Source={SOURCE_DB};Mode=ReadOnly");
            using var targetConn = new SqliteConnection($"Data Source={TARGET_DB}");

            sourceConn.Open();
            targetConn.Open();

            // 检查表是否存在
            var tableExists = sourceConn.ExecuteScalar<int>(
                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=@name",
                new { name = tableName });

            if (tableExists == 0)
            {
                Warn($"  ⚠️  表不存在: {tableName}，跳过");
                return;
            }

            // 获取总行数和要复制的行数
            var totalCount = sourceConn.ExecuteScalar<int>($"SELECT COUNT(*) FROM [{tableName}]");
            var keepCount = sourceConn.ExecuteScalar<int>(
                $"SELECT COUNT(*) FROM [{tableName}] WHERE [{dateColumn}] >= @cutoffDate",
                new { cutoffDate = cutoffDate.ToString("yyyy-MM-dd HH:mm:ss") });

            if (keepCount == 0)
            {
                Info($"  📋 {tableName,-30} (0 行，共 {totalCount:N0} 行)");
                return;
            }

            // 流式处理：分批查询和插入，避免一次性加载所有数据到内存
            using var transaction = targetConn.BeginTransaction();
            var batchSize = 1000;
            var insertedCount = 0;
            var offset = 0;
            var lastUpdateTime = DateTime.Now;

            // 使用LIMIT和OFFSET分批查询
            while (insertedCount < keepCount)
            {
                var currentBatch = sourceConn.Query(
                    $"SELECT * FROM [{tableName}] WHERE [{dateColumn}] >= @cutoffDate LIMIT @limit OFFSET @offset",
                    new 
                    { 
                        cutoffDate = cutoffDate.ToString("yyyy-MM-dd HH:mm:ss"),
                        limit = batchSize,
                        offset = offset
                    }).ToList();

                if (currentBatch.Count == 0)
                    break;

                // 批量插入
                InsertBatch(targetConn, tableName, currentBatch.Cast<object>().ToList(), transaction);
                insertedCount += currentBatch.Count;
                offset += batchSize;

                // 每1000行或每2秒更新一次进度显示
                var now = DateTime.Now;
                if (insertedCount % 1000 == 0 || (now - lastUpdateTime).TotalSeconds >= 2)
                {
                    var percent = keepCount > 0 ? (insertedCount * 100.0 / keepCount) : 0;
                    Console.Write($"\r  ⏳ {tableName,-30} ({insertedCount:N0}/{keepCount:N0} - {percent:F1}%)");
                    lastUpdateTime = now;
                }
            }

            transaction.Commit();

            var deletedCount = totalCount - keepCount;
            var deletedPercent = totalCount > 0 ? (deletedCount * 100.0 / totalCount) : 0;

            Console.Write("\r");
            Success($"  ✅ {tableName,-30} (保留 {keepCount:N0} 行，删除 {deletedCount:N0} 行 [{deletedPercent:F1}%])");
        }

        static void CopyDataTableFromPostgreSQL(string tableName, string dateColumn, DateTime cutoffDate)
        {
            if (string.IsNullOrEmpty(_pgConnectionString))
            {
                Warn($"  ⚠️  PostgreSQL 连接字符串未配置，跳过表: {tableName}");
                return;
            }

            using var pgConn = new NpgsqlConnection(_pgConnectionString);
            using var targetConn = new SqliteConnection($"Data Source={TARGET_DB}");

            pgConn.Open();
            targetConn.Open();

            // 检查表是否存在
            var tableExists = pgConn.ExecuteScalar<int>(
                @"SELECT COUNT(*) 
                  FROM information_schema.tables 
                  WHERE table_schema = 'public' 
                  AND table_name = @tableName",
                new { tableName });

            if (tableExists == 0)
            {
                Warn($"  ⚠️  表不存在: {tableName}，跳过");
                return;
            }

            // 获取总行数和要复制的行数
            var totalCount = pgConn.ExecuteScalar<long>($"SELECT COUNT(*) FROM \"{tableName}\"");
            var keepCount = pgConn.ExecuteScalar<long>(
                $"SELECT COUNT(*) FROM \"{tableName}\" WHERE \"{dateColumn}\" >= @cutoffDate",
                new { cutoffDate = cutoffDate });

            if (keepCount == 0)
            {
                Info($"  📋 {tableName,-30} (0 行，共 {totalCount:N0} 行)");
                return;
            }

            // 流式处理：分批查询和插入
            using var transaction = targetConn.BeginTransaction();
            var batchSize = 1000;
            var insertedCount = 0L;
            var offset = 0;
            var lastUpdateTime = DateTime.Now;

            // 使用LIMIT和OFFSET分批查询
            while (insertedCount < keepCount)
            {
                var currentBatch = pgConn.Query(
                    $"SELECT * FROM \"{tableName}\" WHERE \"{dateColumn}\" >= @cutoffDate ORDER BY \"{dateColumn}\" LIMIT {batchSize} OFFSET {offset}",
                    new { cutoffDate = cutoffDate }).ToList();

                if (currentBatch.Count == 0)
                    break;

                // 批量插入
                foreach (var row in currentBatch)
                {
                    var dict = (IDictionary<string, object>)row;
                    var processedDict = ProcessPostgreSQLData(dict);
                    var columns = string.Join(", ", processedDict.Keys.Select(k => $"[{k}]"));
                    var parameters = string.Join(", ", processedDict.Keys.Select(k => $"@{k}"));
                    var sql = $"INSERT INTO [{tableName}] ({columns}) VALUES ({parameters})";
                    targetConn.Execute(sql, processedDict, transaction);
                    insertedCount++;
                }

                offset += batchSize;

                // 每1000行或每2秒更新一次进度显示
                var now = DateTime.Now;
                if (insertedCount % 1000 == 0 || (now - lastUpdateTime).TotalSeconds >= 2)
                {
                    var percent = keepCount > 0 ? (insertedCount * 100.0 / keepCount) : 0;
                    Console.Write($"\r  ⏳ {tableName,-30} ({insertedCount:N0}/{keepCount:N0} - {percent:F1}%)");
                    lastUpdateTime = now;
                }
            }

            transaction.Commit();

            var deletedCount = totalCount - keepCount;
            var deletedPercent = totalCount > 0 ? (deletedCount * 100.0 / totalCount) : 0;

            Console.Write("\r");
            Success($"  ✅ {tableName,-30} (保留 {keepCount:N0} 行，删除 {deletedCount:N0} 行 [{deletedPercent:F1}%])");
        }

        static void InsertBatch(SqliteConnection conn, string tableName, List<object> batch, SqliteTransaction transaction)
        {
            if (batch.Count == 0) return;

            var firstRow = (IDictionary<string, object>)batch[0];
            var columns = string.Join(", ", firstRow.Keys.Select(k => $"[{k}]"));
            var parameters = string.Join(", ", firstRow.Keys.Select(k => $"@{k}"));
            var sql = $"INSERT INTO [{tableName}] ({columns}) VALUES ({parameters})";

            // 使用批量插入优化性能
            foreach (var row in batch)
            {
                conn.Execute(sql, row, transaction);
            }
        }

        static void OptimizeDatabase()
        {
            using var conn = new SqliteConnection($"Data Source={TARGET_DB}");
            conn.Open();

            // SQLite不支持存储过程，使用CommandType.Text
            conn.Execute("VACUUM", commandType: CommandType.Text);
            conn.Execute("ANALYZE", commandType: CommandType.Text);
        }

        static void GenerateReport(DateTime startTime, DateTime endTime)
        {
            using var conn = new SqliteConnection($"Data Source={TARGET_DB}");
            conn.Open();

            Console.WriteLine("\n" + new string('=', 70));
            Console.ForegroundColor = ConsoleColor.Yellow;
            Console.WriteLine("【数据库统计报告】");
            Console.ResetColor();
            Console.WriteLine(new string('=', 70));

            // 获取所有表的行数
            var tables = conn.Query<string>(
                "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name");

            Console.WriteLine($"\n{"表名",-35} {"行数",15}");
            Console.WriteLine(new string('-', 50));

            var totalRows = 0;
            foreach (var table in tables)
            {
                var count = conn.ExecuteScalar<int>($"SELECT COUNT(*) FROM [{table}]");
                totalRows += count;
                Console.WriteLine($"{table,-35} {count,15:N0}");
            }

            Console.WriteLine(new string('-', 50));
            Console.WriteLine($"{"总计",-35} {totalRows,15:N0}");

            Console.WriteLine($"\n耗时: {(endTime - startTime).TotalSeconds:F2} 秒");
            Console.WriteLine($"源数据库大小: {GetFileSize(SOURCE_DB)}");
            Console.WriteLine($"新数据库大小: {GetFileSize(TARGET_DB)}");

            var sourceSize = new FileInfo(SOURCE_DB).Length;
            var targetSize = new FileInfo(TARGET_DB).Length;
            var reduction = sourceSize - targetSize;
            var reductionPercent = (reduction * 100.0 / sourceSize);

            Console.WriteLine($"减少大小: {FormatSize(reduction)} ({reductionPercent:F1}%)");
            Console.WriteLine(new string('=', 70));
        }

        static string GetFileSize(string filePath)
        {
            var fileInfo = new FileInfo(filePath);
            return FormatSize(fileInfo.Length);
        }

        static string FormatSize(long bytes)
        {
            string[] sizes = { "B", "KB", "MB", "GB" };
            double len = bytes;
            int order = 0;
            while (len >= 1024 && order < sizes.Length - 1)
            {
                order++;
                len = len / 1024;
            }
            return $"{len:0.##} {sizes[order]}";
        }

        static void Info(string message)
        {
            Console.ForegroundColor = ConsoleColor.Cyan;
            Console.WriteLine($"[{DateTime.Now:HH:mm:ss}] {message}");
            Console.ResetColor();
        }

        static void Success(string message)
        {
            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine(message);
            Console.ResetColor();
        }

        static void Warn(string message)
        {
            Console.ForegroundColor = ConsoleColor.Yellow;
            Console.WriteLine(message);
            Console.ResetColor();
        }

        static void Error(string message)
        {
            Console.ForegroundColor = ConsoleColor.Red;
            Console.WriteLine(message);
            Console.ResetColor();
        }
    }
}