antissoft / lvqianmeiye_ERP

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NCC.Common.Extension;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.FriendlyException;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using SqlSugar;

namespace NCC.Extend
{
    /// <summary>
    /// 文件URL迁移服务 - 将本地文件路径转换为OSS路径
    /// </summary>
    [ApiDescriptionSettings(Tag = "数据迁移", Name = "FileUrlMigration", Order = 999)]
    [Route("api/Extend/[controller]")]
    public class FileUrlMigrationService : IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqKdKdjlbEntity> _repository;
        private readonly SqlSugarScope _db;
        private readonly ILogger<FileUrlMigrationService> _logger;

        private const string OSS_BASE_URL = "http://oss.lvqianmeiye.com/Files/SystemFile";
        private const string OLD_URL_PREFIX = "/api/File/Image/annexpic/";
        private const string OLD_OSS_URL = "http://oss.lvqianmeiye.com";
        private const string NEW_OSS_URL = "https://lvqian-erip.oss-cn-chengdu.aliyuncs.com";

        /// <summary>
        /// 初始化一个<see cref="FileUrlMigrationService"/>类型的新实例
        /// </summary>
        /// <param name="repository">SqlSugar仓储</param>
        /// <param name="logger">日志记录器</param>
        public FileUrlMigrationService(ISqlSugarRepository<LqKdKdjlbEntity> repository, ILogger<FileUrlMigrationService> logger)
        {
            _repository = repository;
            _db = _repository.Context;
            _logger = logger;
        }

        /// <summary>
        /// 迁移所有表的文件URL路径
        /// </summary>
        /// <remarks>
        /// 将数据库中的文件URL从本地路径格式转换为OSS路径格式
        /// 
        /// 处理的表和字段：
        /// 1. lq_kd_kdjlb: scwj, hyqz, F_FIleUrl
        /// 2. lq_xh_feedback: F_BeforeImage, F_AfterImage
        /// 3. lq_hytk_hytk: F_FileUrl, F_SignatureFile
        /// 4. lq_purchase_records: F_Attachment
        /// 
        /// URL转换规则：
        /// 原格式：/api/File/Image/annexpic/文件名
        /// 新格式：http://oss.lvqianmeiye.com/Files/SystemFile/文件名
        /// </remarks>
        /// <returns>迁移结果统计</returns>
        [HttpPost("MigrateAllFileUrls")]
        public async Task<dynamic> MigrateAllFileUrls()
        {
            var startTime = DateTime.Now;
            _logger.LogInformation("开始迁移文件URL路径...");

            var totalResults = new
            {
                lq_kd_kdjlb_scwj = await MigrateTableField("lq_kd_kdjlb", "scwj", "F_Id"),
                lq_kd_kdjlb_hyqz = await MigrateTableField("lq_kd_kdjlb", "hyqz", "F_Id"),
                lq_kd_kdjlb_F_FIleUrl = await MigrateTableField("lq_kd_kdjlb", "F_FIleUrl", "F_Id"),
                lq_xh_feedback_F_BeforeImage = await MigrateTableField("lq_xh_feedback", "F_BeforeImage", "F_Id"),
                lq_xh_feedback_F_AfterImage = await MigrateTableField("lq_xh_feedback", "F_AfterImage", "F_Id"),
                lq_hytk_hytk_F_FileUrl = await MigrateTableField("lq_hytk_hytk", "F_FileUrl", "F_Id"),
                lq_hytk_hytk_F_SignatureFile = await MigrateTableField("lq_hytk_hytk", "F_SignatureFile", "F_Id"),
                lq_purchase_records_F_Attachment = await MigrateTableField("lq_purchase_records", "F_Attachment", "F_Id")
            };

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

            // 统计总数
            var totalProcessed = 0;
            var totalUpdated = 0;
            var totalErrors = 0;

            var resultsList = new List<MigrationResult>
            {
                totalResults.lq_kd_kdjlb_scwj,
                totalResults.lq_kd_kdjlb_hyqz,
                totalResults.lq_kd_kdjlb_F_FIleUrl,
                totalResults.lq_xh_feedback_F_BeforeImage,
                totalResults.lq_xh_feedback_F_AfterImage,
                totalResults.lq_hytk_hytk_F_FileUrl,
                totalResults.lq_hytk_hytk_F_SignatureFile,
                totalResults.lq_purchase_records_F_Attachment
            };

            foreach (var result in resultsList)
            {
                totalProcessed += result.TotalProcessed;
                totalUpdated += result.TotalUpdated;
                totalErrors += result.TotalErrors;
            }

            _logger.LogInformation($"文件URL迁移完成，耗时：{duration:F2}秒，总处理：{totalProcessed}，总更新：{totalUpdated}，总错误：{totalErrors}");

            return new
            {
                success = true,
                duration = $"{duration:F2}秒",
                summary = new
                {
                    totalProcessed,
                    totalUpdated,
                    totalErrors
                },
                details = totalResults
            };
        }

        /// <summary>
        /// 迁移指定表的指定字段
        /// </summary>
        private async Task<MigrationResult> MigrateTableField(string tableName, string fieldName, string primaryKey)
        {
            var result = new MigrationResult
            {
                TableName = tableName,
                FieldName = fieldName
            };

            try
            {
                _logger.LogInformation($"开始处理表 {tableName}.{fieldName}");

                // 查询需要处理的数据
                var sql = $@"
                    SELECT {primaryKey} as Id, {fieldName} as FieldValue
                    FROM {tableName}
                    WHERE {fieldName} IS NOT NULL 
                      AND {fieldName} != ''
                      AND {fieldName} != '[]'
                      AND {fieldName} LIKE '%{OLD_URL_PREFIX}%'";

                var records = await _db.Ado.SqlQueryAsync<dynamic>(sql);
                result.TotalProcessed = records.Count;

                if (records.Count == 0)
                {
                    _logger.LogInformation($"表 {tableName}.{fieldName} 没有需要处理的数据");
                    return result;
                }

                _logger.LogInformation($"表 {tableName}.{fieldName} 找到 {records.Count} 条需要处理的数据");

                // 分批处理，每批100条
                const int batchSize = 100;
                var totalBatches = (int)Math.Ceiling((double)records.Count / batchSize);

                for (int batchIndex = 0; batchIndex < totalBatches; batchIndex++)
                {
                    var batch = records.Skip(batchIndex * batchSize).Take(batchSize).ToList();
                    var updateList = new List<(string id, string newValue)>();

                    foreach (var record in batch)
                    {
                        try
                        {
                            var id = record.Id?.ToString();
                            var fieldValue = record.FieldValue?.ToString();

                            if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(fieldValue))
                                continue;

                            var newValue = ConvertFileUrlJson(fieldValue);
                            if (newValue != null && newValue != fieldValue)
                            {
                                updateList.Add((id, newValue));
                            }
                        }
                        catch (Exception ex)
                        {
                            result.TotalErrors++;
                            _logger.LogError(ex, $"处理记录时出错，表：{tableName}，字段：{fieldName}，记录ID：{record.Id}");
                        }
                    }

                    // 批量更新
                    if (updateList.Any())
                    {
                        try
                        {
                            _db.BeginTran();
                            foreach (var (id, newValue) in updateList)
                            {
                                // 使用参数化查询防止SQL注入
                                var updateSql = $"UPDATE `{tableName}` SET `{fieldName}` = @Value WHERE `{primaryKey}` = @Id";
                                await _db.Ado.ExecuteCommandAsync(updateSql, new { Value = newValue, Id = id });
                            }
                            _db.CommitTran();
                            result.TotalUpdated += updateList.Count;
                            _logger.LogInformation($"表 {tableName}.{fieldName} 批次 {batchIndex + 1} 成功更新 {updateList.Count} 条记录");
                        }
                        catch (Exception ex)
                        {
                            _db.RollbackTran();
                            result.TotalErrors += updateList.Count;
                            _logger.LogError(ex, $"批量更新时出错，表：{tableName}，字段：{fieldName}，批次：{batchIndex + 1}");
                        }
                    }

                    _logger.LogInformation($"表 {tableName}.{fieldName} 批次 {batchIndex + 1}/{totalBatches} 处理完成");
                }

                _logger.LogInformation($"表 {tableName}.{fieldName} 处理完成，处理：{result.TotalProcessed}，更新：{result.TotalUpdated}，错误：{result.TotalErrors}");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"处理表 {tableName}.{fieldName} 时发生错误");
                result.TotalErrors = result.TotalProcessed;
            }

            return result;
        }

        /// <summary>
        /// 转换文件URL JSON字符串
        /// </summary>
        private string ConvertFileUrlJson(string jsonString)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(jsonString) || jsonString.Trim() == "[]")
                    return jsonString;

                // 解析JSON数组
                var jsonArray = JArray.Parse(jsonString);
                bool hasChanges = false;

                foreach (var item in jsonArray)
                {
                    if (item is JObject obj && obj["url"] != null)
                    {
                        var url = obj["url"].ToString();
                        if (url.StartsWith(OLD_URL_PREFIX))
                        {
                            // 提取文件名
                            var fileName = url.Substring(OLD_URL_PREFIX.Length);
                            // 构建新的OSS URL
                            var newUrl = $"{OSS_BASE_URL}/{fileName}";
                            obj["url"] = newUrl;
                            hasChanges = true;
                        }
                    }
                }

                return hasChanges ? jsonArray.ToString(Formatting.None) : jsonString;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"转换JSON时出错：{jsonString}");
                return jsonString; // 转换失败时返回原值
            }
        }

        /// <summary>
        /// 迁移所有表的OSS URL（将旧OSS地址替换为新OSS地址）
        /// </summary>
        /// <remarks>
        /// 将数据库中的文件URL从旧OSS地址格式转换为新OSS地址格式
        /// 
        /// 处理的表和字段：
        /// 1. lq_kd_kdjlb: scwj, hyqz, F_FIleUrl
        /// 2. lq_xh_feedback: F_BeforeImage, F_AfterImage
        /// 3. lq_hytk_hytk: F_FileUrl, F_SignatureFile
        /// 4. lq_purchase_records: F_Attachment
        /// 
        /// URL转换规则：
        /// 原格式：http://oss.lvqianmeiye.com/...
        /// 新格式：https://lvqian-erip.oss-cn-chengdu.aliyuncs.com/...
        /// </remarks>
        /// <returns>迁移结果统计</returns>
        [HttpPost("MigrateOssUrls")]
        public async Task<dynamic> MigrateOssUrls()
        {
            var startTime = DateTime.Now;
            _logger.LogInformation("开始迁移OSS URL路径...");

            var totalResults = new
            {
                lq_kd_kdjlb_scwj = await MigrateOssUrlInTableField("lq_kd_kdjlb", "scwj", "F_Id"),
                lq_kd_kdjlb_hyqz = await MigrateOssUrlInTableField("lq_kd_kdjlb", "hyqz", "F_Id"),
                lq_kd_kdjlb_F_FIleUrl = await MigrateOssUrlInTableField("lq_kd_kdjlb", "F_FIleUrl", "F_Id"),
                lq_xh_feedback_F_BeforeImage = await MigrateOssUrlInTableField("lq_xh_feedback", "F_BeforeImage", "F_Id"),
                lq_xh_feedback_F_AfterImage = await MigrateOssUrlInTableField("lq_xh_feedback", "F_AfterImage", "F_Id"),
                lq_hytk_hytk_F_FileUrl = await MigrateOssUrlInTableField("lq_hytk_hytk", "F_FileUrl", "F_Id"),
                lq_hytk_hytk_F_SignatureFile = await MigrateOssUrlInTableField("lq_hytk_hytk", "F_SignatureFile", "F_Id"),
                lq_purchase_records_F_Attachment = await MigrateOssUrlInTableField("lq_purchase_records", "F_Attachment", "F_Id")
            };

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

            // 统计总数
            var totalProcessed = 0;
            var totalUpdated = 0;
            var totalErrors = 0;

            var resultsList = new List<MigrationResult>
            {
                totalResults.lq_kd_kdjlb_scwj,
                totalResults.lq_kd_kdjlb_hyqz,
                totalResults.lq_kd_kdjlb_F_FIleUrl,
                totalResults.lq_xh_feedback_F_BeforeImage,
                totalResults.lq_xh_feedback_F_AfterImage,
                totalResults.lq_hytk_hytk_F_FileUrl,
                totalResults.lq_hytk_hytk_F_SignatureFile,
                totalResults.lq_purchase_records_F_Attachment
            };

            foreach (var result in resultsList)
            {
                totalProcessed += result.TotalProcessed;
                totalUpdated += result.TotalUpdated;
                totalErrors += result.TotalErrors;
            }

            _logger.LogInformation($"OSS URL迁移完成，耗时：{duration:F2}秒，总处理：{totalProcessed}，总更新：{totalUpdated}，总错误：{totalErrors}");

            return new
            {
                success = true,
                duration = $"{duration:F2}秒",
                summary = new
                {
                    totalProcessed,
                    totalUpdated,
                    totalErrors
                },
                details = totalResults
            };
        }

        /// <summary>
        /// 迁移指定表的指定字段中的OSS URL
        /// </summary>
        private async Task<MigrationResult> MigrateOssUrlInTableField(string tableName, string fieldName, string primaryKey)
        {
            var result = new MigrationResult
            {
                TableName = tableName,
                FieldName = fieldName
            };

            try
            {
                _logger.LogInformation($"开始处理表 {tableName}.{fieldName} 的OSS URL迁移");

                // 查询需要处理的数据（包含旧OSS URL的记录）
                var sql = $@"
                    SELECT {primaryKey} as Id, {fieldName} as FieldValue
                    FROM {tableName}
                    WHERE {fieldName} IS NOT NULL 
                      AND {fieldName} != ''
                      AND {fieldName} != '[]'
                      AND {fieldName} LIKE '%{OLD_OSS_URL}%'";

                var records = await _db.Ado.SqlQueryAsync<dynamic>(sql);
                result.TotalProcessed = records.Count;

                if (records.Count == 0)
                {
                    _logger.LogInformation($"表 {tableName}.{fieldName} 没有需要处理的数据");
                    return result;
                }

                _logger.LogInformation($"表 {tableName}.{fieldName} 找到 {records.Count} 条需要处理的数据");

                // 分批处理，每批100条
                const int batchSize = 100;
                var totalBatches = (int)Math.Ceiling((double)records.Count / batchSize);

                for (int batchIndex = 0; batchIndex < totalBatches; batchIndex++)
                {
                    var batch = records.Skip(batchIndex * batchSize).Take(batchSize).ToList();
                    var updateList = new List<(string id, string newValue)>();

                    foreach (var record in batch)
                    {
                        try
                        {
                            var id = record.Id?.ToString();
                            var fieldValue = record.FieldValue?.ToString();

                            if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(fieldValue))
                                continue;

                            var newValue = ReplaceOssUrlInJson(fieldValue);
                            if (newValue != null && newValue != fieldValue)
                            {
                                updateList.Add((id, newValue));
                            }
                        }
                        catch (Exception ex)
                        {
                            result.TotalErrors++;
                            _logger.LogError(ex, $"处理记录时出错，表：{tableName}，字段：{fieldName}，记录ID：{record.Id}");
                        }
                    }

                    // 批量更新
                    if (updateList.Any())
                    {
                        try
                        {
                            _db.BeginTran();
                            foreach (var (id, newValue) in updateList)
                            {
                                // 使用参数化查询防止SQL注入
                                var updateSql = $"UPDATE `{tableName}` SET `{fieldName}` = @Value WHERE `{primaryKey}` = @Id";
                                await _db.Ado.ExecuteCommandAsync(updateSql, new { Value = newValue, Id = id });
                            }
                            _db.CommitTran();
                            result.TotalUpdated += updateList.Count;
                            _logger.LogInformation($"表 {tableName}.{fieldName} 批次 {batchIndex + 1} 成功更新 {updateList.Count} 条记录");
                        }
                        catch (Exception ex)
                        {
                            _db.RollbackTran();
                            result.TotalErrors += updateList.Count;
                            _logger.LogError(ex, $"批量更新时出错，表：{tableName}，字段：{fieldName}，批次：{batchIndex + 1}");
                        }
                    }

                    _logger.LogInformation($"表 {tableName}.{fieldName} 批次 {batchIndex + 1}/{totalBatches} 处理完成");
                }

                _logger.LogInformation($"表 {tableName}.{fieldName} 处理完成，处理：{result.TotalProcessed}，更新：{result.TotalUpdated}，错误：{result.TotalErrors}");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"处理表 {tableName}.{fieldName} 时发生错误");
                result.TotalErrors = result.TotalProcessed;
            }

            return result;
        }

        /// <summary>
        /// 替换JSON字符串中的OSS URL
        /// </summary>
        private string ReplaceOssUrlInJson(string jsonString)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(jsonString) || jsonString.Trim() == "[]")
                    return jsonString;

                // 如果包含旧OSS URL，直接替换
                if (jsonString.Contains(OLD_OSS_URL))
                {
                    var newJsonString = jsonString.Replace(OLD_OSS_URL, NEW_OSS_URL);

                    // 如果是JSON数组格式，需要解析并替换
                    try
                    {
                        var jsonArray = JArray.Parse(jsonString);
                        bool hasChanges = false;

                        foreach (var item in jsonArray)
                        {
                            if (item is JObject obj && obj["url"] != null)
                            {
                                var url = obj["url"].ToString();
                                if (url.Contains(OLD_OSS_URL))
                                {
                                    obj["url"] = url.Replace(OLD_OSS_URL, NEW_OSS_URL);
                                    hasChanges = true;
                                }
                            }
                        }

                        return hasChanges ? jsonArray.ToString(Formatting.None) : jsonString;
                    }
                    catch
                    {
                        // 如果不是JSON格式，直接替换字符串
                        return newJsonString;
                    }
                }

                return jsonString;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"替换OSS URL时出错：{jsonString}");
                return jsonString; // 转换失败时返回原值
            }
        }

        /// <summary>
        /// 迁移结果
        /// </summary>
        private class MigrationResult
        {
            public string TableName { get; set; }
            public string FieldName { get; set; }
            public int TotalProcessed { get; set; }
            public int TotalUpdated { get; set; }
            public int TotalErrors { get; set; }
        }
    }
}