using System.Data;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using SqlSugar;

namespace MES.Service.service.QC;

public class CqjService
{
    /// <summary>
    /// 获取超期检验列表（分页）
    /// </summary>
    /// <param name="queryObj">查询对象</param>
    /// <returns>分页数据</returns>
    public (List<LtsCqj> item, int TotalCount) GetPage(XJPageResult queryObj)
    {
        if (string.IsNullOrEmpty(queryObj.createUser)) return ([], 0);

        var db = SqlSugarHelper.GetInstance();
        var id = Convert.ToDecimal(queryObj.id);
        int totalCount = 0;

        // 查询超期检验列表
        var pageList = db.Queryable<LtsCqj>()
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                a => a.FcheckResu == null)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                a => a.FcheckResu != null)
            .WhereIF(id > 0, a => a.Id == id)
            // 搜索条件
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 1, // 物料编号搜索
                a => a.ItemNo != null && a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 2, // 物料名称搜索
                a => a.ItemName != null && a.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 3, // 送检批次搜索
                a => a.LotNo != null && a.LotNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 4, // 送检单号搜索
                a => a.DeclarationNo != null && a.DeclarationNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 5, // 检验单号搜索
                a => a.ReleaseNo != null && a.ReleaseNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 6, // 物料规格搜索
                a => a.ItemModel != null && a.ItemModel.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .Select(a => new LtsCqj
            {
                Id = a.Id,
                ReleaseNo = a.ReleaseNo,
                ItemId = a.ItemId,
                ItemNo = a.ItemNo,
                ItemName = a.ItemName,
                ItemModel = a.ItemModel,
                LotNo = a.LotNo,
                Fsubmit = a.Fsubmit,
                CreateDate = a.CreateDate,
                Cjr = a.Cjr,
                FcheckResu = a.FcheckResu,
                LotNo1 = a.LotNo1,
                FngDesc = a.FngDesc,
                FbatchQty = a.FbatchQty,
                Tjr = a.Tjr,
                Fnumber = a.Fnumber,
                Sjr = a.Sjr,
                DeclarationNo = a.DeclarationNo
            })
            .OrderBy(a => a.CreateDate, OrderByType.Desc)
            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

        // 批量获取检验项目数量
        if (pageList.Any())
        {
            var releaseNos = pageList.Select(x => x.ReleaseNo).Distinct().ToList();
            
            foreach (var item in pageList)
            {
                var count = db.Queryable<MesCqItemsDetectDetail5>()
                    .Where(x => x.ReleaseNo == item.ReleaseNo)
                    .Count();
                item.InspectionItemCount = count;
            }
        }

        // 计算总数
        int allDataQuery = db.Queryable<LtsCqj>()
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                "未完成".Equals(queryObj.result),
                a => a.FcheckResu == null)
            .WhereIF(
                StringUtil.IsNotNullOrEmpty(queryObj.result) &&
                !"未完成".Equals(queryObj.result),
                a => a.FcheckResu != null)
            .WhereIF(id > 0, a => a.Id == id)
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 1,
                a => a.ItemNo != null && a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 2,
                a => a.ItemName != null && a.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 3,
                a => a.LotNo != null && a.LotNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 4,
                a => a.DeclarationNo != null && a.DeclarationNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 5,
                a => a.ReleaseNo != null && a.ReleaseNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 6,
                a => a.ItemModel != null && a.ItemModel.ToLower().Contains(queryObj.SearchValue.ToLower()))
            .Select(a => a.ReleaseNo)
            .Distinct()
            .Count();

        return (pageList, allDataQuery);
    }

    /// <summary>
    /// 调用存储过程，重新获取检验项目
    /// </summary>
    /// <param name="itemNo">物料编号</param>
    /// <param name="quantity">数量</param>
    /// <param name="releaseNo">检验单号</param>
    /// <returns>执行结果</returns>
    public string[] SetItems(decimal itemNo, decimal quantity, string releaseNo)
    {
        // 定义输出参数
        var outputResult = new SugarParameter("PO_RESULT", null,
            System.Data.DbType.Int32, ParameterDirection.Output, 4000);

        var outputMessage = new SugarParameter("PO_TEXT", null,
            System.Data.DbType.String, ParameterDirection.Output, 4000);

        // 定义输入参数
        var parameters = new List<SugarParameter>
        {
            new("P_RELEASE_NO", releaseNo, System.Data.DbType.String, ParameterDirection.Input),
            new("P_ITEM_ID", itemNo, System.Data.DbType.Decimal, ParameterDirection.Input),
            outputResult,
            outputMessage
        };

        var db = SqlSugarHelper.GetInstance();

        // 使用 SqlSugar 执行存储过程
        db.Ado.ExecuteCommand(
            "BEGIN PRC_CQ_ITEM_INSERT_BTN(:P_RELEASE_NO, :P_ITEM_ID, :PO_RESULT, :PO_TEXT); END;",
            parameters.ToArray());

        // 获取输出参数的值
        var resultValue = outputResult.Value?.ToString();
        var messageValue = outputMessage.Value?.ToString();
        
        string[] msg = new string[2];
        msg[0] = resultValue;
        msg[1] = messageValue;
        return msg;
    }

    /// <summary>
    /// 获取检验项目列表
    /// </summary>
    /// <param name="releaseNo">检验单号</param>
    /// <param name="id">项目ID</param>
    /// <returns>检验项目列表</returns>
    public List<MesCqItemsDetectDetail5> GetItems(string? releaseNo, decimal? id)
    {
        var db = SqlSugarHelper.GetInstance();

        return db.Queryable<MesCqItemsDetectDetail5, MesCqItemsDetectDetail12>(
                (a, b) => new JoinQueryInfos(JoinType.Left, a.Id == b.MainId))
            .Where((a, b) => a.ReleaseNo == releaseNo)
            .GroupBy((a, b) => new
            {
                a.Id,
                a.ReleaseNo,
                a.FacLevel,
                a.FcheckItem,
                a.FcheckTool,
                a.FdownAllow,
                a.FcheckLevel,
                a.Fstand,
                a.FupAllow,
                a.SampleSizeNo,
                a.FspecRequ,
                a.FreQty,
                a.CheckQyt,
                a.FcheckResu,
                a.FcheckItemDesc,
                a.Funit,
                a.Meom,
                a.FngQty,  // 添加不合格数
                a.FacQty   // 添加AC数
            }).Select((a, b) => new MesCqItemsDetectDetail5
            {
                Id = a.Id,
                ReleaseNo = a.ReleaseNo,
                CheckQyt = a.CheckQyt,
                FacLevel = a.FacLevel,
                FcheckItem = a.FcheckItem,
                FcheckTool = a.FcheckTool,
                FdownAllow = a.FdownAllow,
                FcheckLevel = a.FcheckLevel,
                Fstand = a.Fstand,
                FupAllow = a.FupAllow,
                SampleSizeNo = a.SampleSizeNo,
                FspecRequ = a.FspecRequ,
                FreQty = a.FreQty,
                Factory = "1000",
                Company = "1000",
                FenterQty = SqlFunc.AggregateCount(b.Id),  // 已录入数量
                FngQty = a.FngQty,  // 不合格数（从数据库读取，由 updateDetail5 更新）
                FacQty = a.FacQty,  // AC数
                FcheckResu = a.FcheckResu,
                FcheckItemDesc = a.FspecRequ,
                Funit = a.Funit,
                Meom = a.Meom
            })
            .OrderBy(a => SqlFunc.IIF(a.Fstand != null, 0, 1))
            .OrderBy(a => a.FcheckItem, OrderByType.Desc)
            .ToList();
    }

    /// <summary>
    /// 删除超期检验单
    /// </summary>
    /// <param name="releaseNo">检验单号</param>
    /// <returns>影响行数</returns>
    public int RemoveCqj(string? releaseNo)
    {
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var commit = 0;
            // 删除主表
            commit += db.Deleteable<MesCqItemsDetect01>()
                .Where(s => s.ReleaseNo == releaseNo)
                .ExecuteCommand();
            // 删除子表
            commit += db.Deleteable<MesCqItemsDetectDetail5>()
                .Where(s => s.ReleaseNo == releaseNo)
                .ExecuteCommand();
            // 删除孙表
            commit += db.Deleteable<MesCqItemsDetectDetail12>()
                .Where(s => s.ReleaseNo == releaseNo)
                .ExecuteCommand();
            return commit;
        });

        return withOracle;
    }

    /// <summary>
    /// 保存不良描述到主表
    /// </summary>
    /// <param name="gid">主表ID</param>
    /// <param name="releaseNo">检验单号</param>
    /// <param name="fngDesc">不良描述</param>
    /// <returns>影响行数</returns>
    public int SaveRemarksGid(decimal gid, string? releaseNo, string? fngDesc)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Updateable<MesCqItemsDetect01>()
            .SetColumns(it => new MesCqItemsDetect01
            {
                FngDesc = fngDesc  // 使用FNG_DESC字段存储不良描述
            })
            .Where(it => it.Id == gid && it.ReleaseNo == releaseNo)
            .ExecuteCommand();
    }

    /// <summary>
    /// 保存备注到主表
    /// </summary>
    /// <param name="gid">主表ID</param>
    /// <param name="releaseNo">检验单号</param>
    /// <param name="lotNo1">备注内容</param>
    /// <returns>影响行数</returns>
    public int SaveLotNo1(decimal gid, string? releaseNo, string? lotNo1)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Updateable<MesCqItemsDetect01>()
            .SetColumns(it => new MesCqItemsDetect01
            {
                LotNo1 = lotNo1  // 使用LOT_NO1字段存储备注
            })
            .Where(it => it.Id == gid && it.ReleaseNo == releaseNo)
            .ExecuteCommand();
    }

    /// <summary>
    /// 保存不良描述到子表
    /// </summary>
    /// <param name="pid">子表ID</param>
    /// <param name="funit">不良描述</param>
    /// <returns>影响行数</returns>
    public int SaveRemarksPid(decimal pid, string? funit)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Updateable<MesCqItemsDetectDetail5>()
            .SetColumns(it => new MesCqItemsDetectDetail5
            {
                Funit = funit
            })
            .Where(it => it.Id == pid)
            .ExecuteCommand();
    }

    /// <summary>
    /// 保存备注到子表
    /// </summary>
    /// <param name="pid">子表ID</param>
    /// <param name="meom">备注</param>
    /// <returns>影响行数</returns>
    public int SaveMeom(decimal pid, string? meom)
    {
        var db = SqlSugarHelper.GetInstance();
        return db.Updateable<MesCqItemsDetectDetail5>()
            .SetColumns(it => new MesCqItemsDetectDetail5
            {
                Meom = meom
            })
            .Where(it => it.Id == pid)
            .ExecuteCommand();
    }

    /// <summary>
    /// 新增检验详情记录（批量）
    /// </summary>
    /// <param name="detail">检验详情对象</param>
    /// <returns>影响行数</returns>
    public int SetQSItemDetail(MesCqItemsDetectDetail12 detail)
    {
        var dbd = SqlSugarHelper.GetInstance();
        var oracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            List<MesCqItemsDetectDetail12> result = new();
            for (var i = 0; i < detail.count; i++)
            {
                var item = new MesCqItemsDetectDetail12();
                item.MainId = detail.MainId;
                item.ReleaseNo = detail.ReleaseNo;
                item.Fstand = detail.Fstand;
                item.FcheckResu = detail.FcheckResu;
                item.CreateBy = detail.LastupdateBy;
                item.CreateDate = DateTime.Now;
                item.Factory = "1000";
                item.Company = "1000";
                result.Add(item);
            }

            return db.Insertable(result).ExecuteCommand();
        });

        detail.CreateBy = detail.LastupdateBy;

        updateDetail5(detail);
        autoResult(detail);

        return oracle;
    }

    //更新检验明细已检、不合格数量  
    private int updateDetail5(MesCqItemsDetectDetail12 detail)
    {
        var db = SqlSugarHelper.GetInstance();

        //查询这个检验项目下的检验数量
        var count = db.Queryable<MesCqItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId).Count();

        //获取不合格数
        var countNo = db.Queryable<MesCqItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId && s.Fstand == "×").Count();
        
        //更新检验明细已检数量  
        var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            return db.Updateable<MesCqItemsDetectDetail5>()
                .SetColumns(s => s.FenterQty == count)
                .SetColumns(s => s.FngQty == countNo)
                .Where(s => s.Id == detail.MainId)
                .ExecuteCommand();
        });

        return withOracle;
    }

    private int autoResult(MesCqItemsDetectDetail12 detail)
    {
        var db = SqlSugarHelper.GetInstance();

        // Single 没有查询到结果时返回Null，如果结果大于1条会抛出错误
        var QsItemOqcItem = db.Queryable<MesCqItemsDetectDetail5>()
            .Single(s => s.Id == detail.MainId);

        if (QsItemOqcItem == null) return 0;

        //查询这个检验项目下的检验结果
        var count = db.Queryable<MesCqItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId).Count();

        updateDetail5(detail);
        var result = "";

        //检验实际结果不等于应该检验的个数时直接退出
        if (QsItemOqcItem.CheckQyt != count) return 0;

        //合格的有多少个
        var passCount = db.Queryable<MesCqItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId && s.Fstand == "√").Count();
        //不合格的有多少个
        var noCount = db.Queryable<MesCqItemsDetectDetail12>()
            .Where(s => s.MainId == detail.MainId && s.Fstand == "×").Count();

        if (count == passCount)
            result = "合格";
        else if (noCount >= QsItemOqcItem.FreQty)
            result = "不合格";
        var useTransactionWithOracle = SqlSugarHelper.UseTransactionWithOracle(
            db =>
            {
                var commit = 0;
                commit += db.Updateable<MesCqItemsDetectDetail5>()
                    .SetColumns(s => s.FcheckResu == result)
                    .SetColumns(s => s.FenterQty == count)
                    .Where(s => s.Id == detail.MainId)
                    .ExecuteCommand();

                return commit;
            });

        return useTransactionWithOracle;
    }

    /// <summary>
    /// 获取检验详情记录列表
    /// </summary>
    /// <param name="id">主检验项目ID</param>
    /// <param name="releaseNo">检验单号</param>
    /// <returns>检验详情列表</returns>
    public List<MesCqItemsDetectDetail12> GetQSItemDetail(decimal id, string? releaseNo)
    {
        var db = SqlSugarHelper.GetInstance();
        
        return db.Queryable<MesCqItemsDetectDetail12>()
            .Where(x => x.MainId == id && x.ReleaseNo == releaseNo)
            .OrderBy(x => x.CreateDate, OrderByType.Asc)
            .Select(x => new MesCqItemsDetectDetail12
            {
                Id = x.Id,
                MainId = x.MainId,
                ReleaseNo = x.ReleaseNo,
                Fstand = x.Fstand,
                FcheckResu = x.FcheckResu,
                CreateBy = x.CreateBy,
                CreateDate = x.CreateDate,
                LastupdateBy = x.LastupdateBy,
                Factory = x.Factory,
                Company = x.Company
                // 不包含 LastupdateDate 字段
            })
            .ToList();
    }

    /// <summary>
    /// 更新检验详情记录
    /// </summary>
    /// <param name="id">详情记录ID</param>
    /// <param name="mainId">主检验项目ID</param>
    /// <param name="releaseNo">检验单号</param>
    /// <param name="fstand">检验标准</param>
    /// <param name="fcheckResu">检验结果</param>
    /// <param name="updateBy">更新人</param>
    /// <returns>影响行数</returns>
    public int UpdateQSItemDetail(decimal id, decimal mainId, string? releaseNo, string? fstand, string? fcheckResu, string? updateBy)
    {
        var db = SqlSugarHelper.GetInstance();
        
        // 先更新检验详情记录
        var updateResult = db.Updateable<MesCqItemsDetectDetail12>()
            .SetColumns(it => new MesCqItemsDetectDetail12
            {
                Fstand = fstand,
                FcheckResu = fcheckResu,
                LastupdateBy = updateBy
                // 不设置 LastupdateDate，因为数据库中不存在此字段
            })
            .Where(it => it.Id == id && it.MainId == mainId && it.ReleaseNo == releaseNo)
            .ExecuteCommand();

        // 构造 detail 对象用于调用 autoResult
        var detail = new MesCqItemsDetectDetail12
        {
            Id = id,
            MainId = mainId,
            ReleaseNo = releaseNo,
            Fstand = fstand,
            FcheckResu = fcheckResu,
            LastupdateBy = updateBy,
            CreateBy = updateBy
        };

        // 更新已检数量和不合格数量（在更新记录之后执行，确保能查询到最新的 Fstand）
        updateDetail5(detail);
        
        // 自动判定检验结果
        autoResult(detail);

        return updateResult;
    }

    /// <summary>
    /// 检验提交
    /// </summary>
    /// <param name="userNo">用户账号</param>
    /// <param name="releaseNo">检验单号</param>
    /// <returns>执行结果</returns>
    public string[] SubmitInspection(string? userNo, string? releaseNo)
    {
        try
        {
            var db = SqlSugarHelper.GetInstance();

            // 获取检验单信息（只查询需要的字段，避免实体映射错误）
            var fngDesc = db.Queryable<MesCqItemsDetect01>()
                .Where(x => x.ReleaseNo == releaseNo)
                .Select(x => x.FngDesc)
                .First();

            // 检查检验单是否存在
            var exists = db.Queryable<MesCqItemsDetect01>()
                .Where(x => x.ReleaseNo == releaseNo)
                .Any();

            if (!exists)
            {
                return new[] { "1", "检验单不存在" };
            }

            // 定义输出参数
            var outputResult = new SugarParameter("o_Result", null,
                System.Data.DbType.Int32, ParameterDirection.Output, 4000);

            var outputMessage = new SugarParameter("o_Msg", null,
                System.Data.DbType.String, ParameterDirection.Output, 4000);

            // 定义输入参数（参数顺序必须与存储过程定义完全一致）
            var parameters = new List<SugarParameter>
            {
                new("PI_FACTORY", "1000", System.Data.DbType.String, ParameterDirection.Input, 50),
                new("PI_COMPANY", "1000", System.Data.DbType.String, ParameterDirection.Input, 50),
                new("p_Release_No", releaseNo ?? "", System.Data.DbType.String, ParameterDirection.Input, 50),
                new("p_badReason", "", System.Data.DbType.String, ParameterDirection.Input, 200),  // 空字符串而非NULL
                new("p_iqcStatus", "", System.Data.DbType.String, ParameterDirection.Input, 200),  // 空字符串而非NULL
                new("p_workShop", "", System.Data.DbType.String, ParameterDirection.Input, 200),   // 空字符串而非NULL
                new("p_remark", fngDesc ?? "", System.Data.DbType.String, ParameterDirection.Input, 4000),  // 不良描述
                new("p_User", userNo ?? "", System.Data.DbType.String, ParameterDirection.Input, 50),
                outputResult,
                outputMessage
            };

            // 调用存储过程
            db.Ado.ExecuteCommand(
                "BEGIN Prc_Mes_CQJ_Qa_Submit(:PI_FACTORY, :PI_COMPANY, :p_Release_No, :p_badReason, :p_iqcStatus, :p_workShop, :p_remark, :p_User, :o_Result, :o_Msg); END;",
                parameters.ToArray());

            // 获取输出参数的值
            var resultValue = outputResult.Value?.ToString() ?? "1";
            var messageValue = outputMessage.Value?.ToString() ?? "提交失败";

            return new[] { resultValue, messageValue };
        }
        catch (Exception ex)
        {
            return new[] { "1", $"提交异常：{ex.Message}" };
        }
    }
}