using MES.Service.DB;
using MES.Service.Dto.webApi.QcIssueResult;
using MES.Service.Modes.QcIssueResult;
using MES.Service.Modes.SentLaboratory;
using Newtonsoft.Json;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace MES.Service.service.SentLaboratory;

public class SaveLaboratoryManager:Repository<UpdateMesQmsSample>
{

    public class ResultObject
    {
        public string? SjReleaseNo { get; set; }
        public decimal? IS_SJ { get; set; }
        public string? DEPARTMENTNAME { get; set; }
        public string? DEPARTMENTCODE { get; set; }
        public DateTime? CREATE_DATE { get; set; }
        public DateTime? CompleteDate { get; set; }
        public string? REMARKS { get; set; }
        public string? SIM_REQ { get; set; }
        public string? Test_REQ { get; set; }
        //磁铁拉力
        public decimal? Check01 { get; set; }

        //磁铁承重
        public decimal? Check02 { get; set; }

        //盐雾

        public decimal? Check03 { get; set; }

        //IP
        public decimal? Check04 { get; set; }

        //老化测试
        public decimal? Check05 { get; set; }

        //传导辐射
        public decimal? Check06 { get; set; }

        public decimal? Check07 { get; set; }

        //电线拉力
        public decimal? Check08 { get; set; }

        //接口插拔
        public decimal? Check09 { get; set; }

        //光谱测试
        public decimal? Check10 { get; set; }

        //ROHS
        public decimal? Check11 { get; set; }

        //电池充放电
        public decimal? Check12 { get; set; }

        //高温
        public decimal? Check13 { get; set; }

        //低温
        public decimal? Check14 { get; set; }

        //IK
        public decimal? Check15 { get; set; }

        //温升测试
        public decimal? Check16 { get; set; }

        //寿命
        public decimal? Check17 { get; set; }

        //其它
        public decimal? Check18 { get; set; }

        //其它的内容
        public string? Line04 { get; set; }
        public List<AllItem>? allItem { get; set; }
    }

    public class AllItem
    {
        public decimal? Pid { get; set; }
        public string? sj_no { get; set; }
        public string? release_no { get; set; }
        public decimal? ITEM_ID { get; set; }
        public string? ITEM_NO { get; set; }
        public string? QUANTITY { get; set; }
        public decimal? URGENT_FLAG { get; set; }
        public DateTime? CREATE_DATE { get; set; }
        public string? SUPP_NAME { get; set; }
        public string? DECISION_OUTCOMES { get; set; }
       
    }

    public ResultObject GetListAll(string RELEASE_NO)
    {
        // 假设你有一个数据库上下文或数据访问层
        // 这里用模拟数据代替实际数据库查询
        var mainTable = GetMainTableData(RELEASE_NO);
        var subTable = GetSubTableData(RELEASE_NO);

        // 创建返回对象
        var result = new ResultObject
        {

            SjReleaseNo = mainTable.ReleaseNo,
            IS_SJ = mainTable.IsSubmit,
            DEPARTMENTNAME = mainTable.DEPARTMENTNAME,
            DEPARTMENTCODE=mainTable.DEPARTMENTCODE,
            REMARKS = mainTable.REMARKS,
            SIM_REQ = mainTable.SimReq,
            CompleteDate=mainTable.CompleteDate,
            Test_REQ=mainTable.TestReq,
            Check01 = mainTable.Check01,
            Check02 = mainTable.Check02,
            Check03 = mainTable.Check03,
            Check04 = mainTable.Check04,
            Check05 = mainTable.Check05,
            Check06 = mainTable.Check06,
            Check07 = mainTable.Check07,
            Check08 = mainTable.Check08,
            Check09 = mainTable.Check09,
            Check10 = mainTable.Check10,
            Check11 = mainTable.Check11,
            Check12 = mainTable.Check12,
            Check13 = mainTable.Check13,
            Check14 = mainTable.Check14,
            Check15 = mainTable.Check15,
            Check16 = mainTable.Check16,
            Check17 = mainTable.Check17,
            Check18 = mainTable.Check18,
            Line04 = mainTable.Line04,
            allItem = subTable.Select(item => new AllItem
            {
                Pid=item.PID,
                sj_no = item.SJ_NO,
                CREATE_DATE = item.CREATE_DATE,
                ITEM_ID = item.ITEM_ID,
                release_no=item.RELEASE_NO,
                ITEM_NO = item.ITEM_NO,
                SUPP_NAME = item.SUPP_NAME,
                QUANTITY=item.QUANTITY,
                URGENT_FLAG=item.URGENT_FLAG,
                DECISION_OUTCOMES=item.DECISION_OUTCOMES,

            }).ToList()
        };

        return result;
    }

    // 模拟主表数据查询
    private UpdateMesQmsSample GetMainTableData(string RELEASE_NO)
    {
        var db = SqlSugarHelper.GetInstance();

        var map = db.Queryable<UpdateMesQmsSample>()
         .Where(t => t.ReleaseNo == RELEASE_NO)
         .First();
      

        // 这里应该是实际的数据库查询逻辑
        // 例如：return db.MainTable.FirstOrDefault(m => m.RELEASE_NO == RELEASE_NO);
        return new UpdateMesQmsSample
        {
            ReleaseNo = map.ReleaseNo,
            DEPARTMENTNAME = map.DEPARTMENTNAME,
            CreateDate = map.CreateDate,
            QUANTITY = map.QUANTITY,
            CompleteDate = map.CompleteDate,
            TestReq = map.TestReq,
            SimReq = map.SimReq,
            ItemNo = map.ItemNo,
            UrgentFlag = map.UrgentFlag,
            JYDH = map.JYDH,
            REMARKS = map.REMARKS,
            ItemName = map.ItemName,
            DEPARTMENTCODE = map.DEPARTMENTCODE,
            PAPERBILLNO = map.PAPERBILLNO,
            IsSubmit = map.IsSubmit,
            ZHBS = map.ZHBS,
            Check01 = map.Check01,
            Check02 = map.Check02,
            Check03 = map.Check03,
            Check04 = map.Check04,
            Check05 = map.Check05,
            Check06 = map.Check06,
            Check07 = map.Check07,
            Check08 = map.Check08,
            Check09 = map.Check09,
            Check10 = map.Check10,
            Check11 = map.Check11,
            Check12 = map.Check12,
            Check13 = map.Check13,
            Check14 = map.Check14,
            Check15 = map.Check15,
            Check16 = map.Check16,
            Check17 = map.Check17,
            Check18 = map.Check18,
            Line04=map.Line04
           
        };
    }

    // 模拟子表数据查询
    private List<UpdateMesQmsSamplePjt> GetSubTableData(string RELEASE_NO)
    {
        var db = SqlSugarHelper.GetInstance();

        var mapping = db.Queryable<UpdateMesQmsSamplePjt>()
            .Where(t => t.SJ_NO == RELEASE_NO)
            .ToList();

        // 如果查询结果为空，返回空列表
        if (mapping == null || mapping.Count == 0)
        {
            return new List<UpdateMesQmsSamplePjt>();
        }

        // 返回查询结果
        return mapping;
   
    }

    public dynamic Save(GetLaboratoryDetails getLaboratoryDetails)
    {

        var mesUpdateMesQmsSample = GetMesQcIssueResults(getLaboratoryDetails);

        return UseTransaction(db =>
        {
            return SaveOrUpdateData(db, mesUpdateMesQmsSample,getLaboratoryDetails) ? 1 : 0;
        }) > 0;
    }

    public UpdateMesQmsSample GetMesQcIssueResults(GetLaboratoryDetails getLaboratoryDetails)
    {

        if (getLaboratoryDetails.URGENT_FLAG == null) {
            getLaboratoryDetails.URGENT_FLAG = 0;
        }

        var mesUpdateMesQmsSample = new UpdateMesQmsSample
        {
            ReleaseNo = getLaboratoryDetails.RELEASE_NO,
            DEPARTMENTNAME = getLaboratoryDetails.DEPARTMENTNAME,
            CreateDate = getLaboratoryDetails.CREATE_DATE1,
            QUANTITY = getLaboratoryDetails.QUANTITY,
            CompleteDate = getLaboratoryDetails.COMPLETE_DATE,
            TestReq=getLaboratoryDetails.TEST_REQ,
            SimReq = getLaboratoryDetails.SIM_REQ,
            ItemNo = getLaboratoryDetails.ITEM_NO,
            UrgentFlag = getLaboratoryDetails.URGENT_FLAG,
            JYDH = getLaboratoryDetails.JYDH,
            REMARKS = getLaboratoryDetails.REMARKS,
            ItemName = getLaboratoryDetails.ITEM_NAME,
            DEPARTMENTCODE = getLaboratoryDetails.DEPARTMENTCODE,
            PAPERBILLNO = getLaboratoryDetails.LOT_NO,
            IsSubmit = getLaboratoryDetails.IS_SUBMIT,
            ZHBS = getLaboratoryDetails.ZH_BS

        };

        mesUpdateMesQmsSample.Check01 = 0;
        mesUpdateMesQmsSample.Check02 = 0;
        mesUpdateMesQmsSample.Check03 = 0;
        mesUpdateMesQmsSample.Check04 = 0;
        mesUpdateMesQmsSample.Check05 = 0;
        mesUpdateMesQmsSample.Check06 = 0;
        mesUpdateMesQmsSample.Check07 = 0;
        mesUpdateMesQmsSample.Check08 = 0;
        mesUpdateMesQmsSample.Check09 = 0;
        mesUpdateMesQmsSample.Check10 = 0;
        mesUpdateMesQmsSample.Check11 = 0;
        mesUpdateMesQmsSample.Check12 = 0;
        mesUpdateMesQmsSample.Check13 = 0;
        mesUpdateMesQmsSample.Check14 = 0;
        mesUpdateMesQmsSample.Check15 = 0;
        mesUpdateMesQmsSample.Check16 = 0;
        mesUpdateMesQmsSample.Check17 = 0;
        mesUpdateMesQmsSample.Check18 = 0;

        // 遍历 CHECKBOX 并赋值
        foreach (var item in getLaboratoryDetails.CHECKBOX)
        {
            switch (item.InspectionItems)
            {
                case "磁铁拉力":
                    mesUpdateMesQmsSample.Check01 = 1;
                    break;
                case "磁铁承重":
                    mesUpdateMesQmsSample.Check02 = 1;
                    break;
                case "盐雾":
                    mesUpdateMesQmsSample.Check03 = 1;
                    break;
                case "IP":
                    mesUpdateMesQmsSample.Check04 = 1;
                    break;
                case "老化测试":
                    mesUpdateMesQmsSample.Check05 = 1;
                    break;
                case "传导辐射":
                    mesUpdateMesQmsSample.Check06 = 1;
                    break;
                case "跌落测试":
                    mesUpdateMesQmsSample.Check07 = 1;
                    break;
                case "电线拉力":
                    mesUpdateMesQmsSample.Check08 = 1;
                    break;
                case "接口插拔":
                    mesUpdateMesQmsSample.Check09 = 1;
                    break;
                case "光谱测试":
                    mesUpdateMesQmsSample.Check10 = 1;
                    break;
                case "ROHS":
                    mesUpdateMesQmsSample.Check11 = 1;
                    break;
                case "电池充放电":
                    mesUpdateMesQmsSample.Check12 = 1;
                    break;
                case "高温":
                    mesUpdateMesQmsSample.Check13 = 1;
                    break;
                case "低温":
                    mesUpdateMesQmsSample.Check14 = 1;
                    break;
                case "IK":
                    mesUpdateMesQmsSample.Check15 = 1;
                    break;
                case "温升测试":
                    mesUpdateMesQmsSample.Check16 = 1;
                    break;
                case "寿命":
                    mesUpdateMesQmsSample.Check17 = 1;
                    break;
                case "其它":
                    mesUpdateMesQmsSample.Check18 = 1;
                    mesUpdateMesQmsSample.Line04 = getLaboratoryDetails.Line04;
                    break;
                default:
                    // 如果 InspectionItems 不匹配任何条件，可以选择忽略或记录日志
                    break;
            }
        }
        return mesUpdateMesQmsSample;
    }

    /**
    * 插入数据库表
    */
    private bool SaveOrUpdateData(SqlSugarScope db, UpdateMesQmsSample updateMesQmsSample, GetLaboratoryDetails getLaboratoryDetails)
    {

        // 查询 MES_QMS_SAMPLE 表中是否存在匹配的记录
        var existingRecord = db.Queryable<UpdateMesQmsSample>()
            .Where(t => t.ReleaseNo == updateMesQmsSample.ReleaseNo)
            .First(); // 使用 First 获取第一条记录
       
        if (existingRecord != null)
        {
            Debug.WriteLine("hhhhhhhhh!");
            // 更新 UpdateMesQaItems 表中的字段
            updateMesQmsSample.Id = existingRecord.Id;

            // 执行更新操作
            var updateResult = db.Updateable(updateMesQmsSample).ExecuteCommand();

            var mapping = db.Queryable<UpdateMesQmsSamplePjt>()
             .Where(t => t.RELEASE_NO == updateMesQmsSample.JYDH && t.PID == updateMesQmsSample.Id)
             .Select(t => new UpdateMesQmsSamplePjt
             {
                 PID=t.PID
             })
             .ToList();


            Debug.WriteLine(updateMesQmsSample.JYDH);

            if (mapping.Any())
            {
                updateResult = db.Updateable<UpdateMesQmsSamplePjt>()
                .SetColumns(t => new UpdateMesQmsSamplePjt
             {
                 SJ_NO = updateMesQmsSample.ReleaseNo,
                 RELEASE_NO = updateMesQmsSample.JYDH,
                 ITEM_NO = updateMesQmsSample.ItemNo,
                 QUANTITY = updateMesQmsSample.QUANTITY,
                 URGENT_FLAG = updateMesQmsSample.UrgentFlag,
                 CREATE_DATE = System.DateTime.Now,
                 ITEM_ID = getLaboratoryDetails.ITEM_ID,
                 SUPP_NAME = getLaboratoryDetails.SUPP_NAME
             })
             .Where(t => t.RELEASE_NO == updateMesQmsSample.JYDH && t.PID == updateMesQmsSample.Id) // 显式指定更新条件
             .ExecuteCommand();
               
            }
            else
            {
                Debug.WriteLine("hhhhhhhhaddadah!");
                var mesUpdateMesQmsSamplePjt = new UpdateMesQmsSamplePjt
                {

                    PID = updateMesQmsSample.Id,
                    SJ_NO = updateMesQmsSample.ReleaseNo,
                    RELEASE_NO = updateMesQmsSample.JYDH,
                    ITEM_NO = updateMesQmsSample.ItemNo,
                    QUANTITY = updateMesQmsSample.QUANTITY,
                    URGENT_FLAG = updateMesQmsSample.UrgentFlag,
                    CREATE_DATE = System.DateTime.Now,
                    ITEM_ID = getLaboratoryDetails.ITEM_ID,
                    SUPP_NAME = getLaboratoryDetails.SUPP_NAME

                };

                updateResult = db.Insertable(mesUpdateMesQmsSamplePjt).ExecuteCommand();

            }
           

             var maping = db.Queryable<UpdateMesQaItems>()
             .Where(t => t.ReleaseNo == updateMesQmsSample.JYDH)
             .Select(t => new UpdateMesQaItems
             {
                 ISSJ = t.ISSJ,
                 SJ_NO = t.SJ_NO
             })
             .ToList();

            if (maping.Any())
            {
                updateResult = db.Updateable<UpdateMesQaItems>()
               .SetColumns(t => new UpdateMesQaItems
               {
                   ISSJ = updateMesQmsSample.IsSubmit,
                   SJ_NO = updateMesQmsSample.ReleaseNo
               })
               .Where(t => t.ReleaseNo == updateMesQmsSample.JYDH) // 显式指定更新条件
               .ExecuteCommand();
            }
            else
            {
                throw new NotImplementedException("未找到对应来料检验申请单");
            }

            return updateResult > 0; // 如果更新成功，返回 true

        }
        else
        {
            //Debug.WriteLine("插入");
            //如果 mapping 为空，插入新记录到 QcIssueResults 表
            var orUpdate = base.Insert(updateMesQmsSample);

            var insertedId = 0;

            if (orUpdate)
            {
                // 使用 RELEASE_NO 查询刚刚插入的记录的 id
                 insertedId =(int) db.Queryable<UpdateMesQmsSample>()
                    .Where(it => it.ReleaseNo == updateMesQmsSample.ReleaseNo)
                    .Select(it => it.Id)
                    .Single(); // 如果 RELEASE_NO 是唯一字段，可以使用 Single()
            };

            var mesUpdateMesQmsSamplePjt = new UpdateMesQmsSamplePjt
            {
             
                PID= insertedId,
                SJ_NO= updateMesQmsSample.ReleaseNo,
                RELEASE_NO= updateMesQmsSample.JYDH,
                ITEM_NO= updateMesQmsSample.ItemNo,
                QUANTITY= updateMesQmsSample.QUANTITY,
                URGENT_FLAG= updateMesQmsSample.UrgentFlag,
                CREATE_DATE = System.DateTime.Now,
                ITEM_ID= getLaboratoryDetails.ITEM_ID,
                SUPP_NAME=getLaboratoryDetails.SUPP_NAME

            };

            var updateResult = db.Insertable(mesUpdateMesQmsSamplePjt).ExecuteCommand();

            var mapping = db.Queryable<UpdateMesQaItems>()
            .Where(t => t.ReleaseNo == updateMesQmsSample.JYDH)
            .Select(t => new UpdateMesQaItems
            {
                ISSJ = t.ISSJ,
                SJ_NO= t.SJ_NO
            }) 
            .ToList();

            if (mapping.Any())
            {
               updateResult = db.Updateable<UpdateMesQaItems>()
              .SetColumns(t => new UpdateMesQaItems
              {
                 ISSJ= updateMesQmsSample.IsSubmit,
                 SJ_NO = updateMesQmsSample.ReleaseNo
              })
              .Where(t => t.ReleaseNo == updateMesQmsSample.JYDH) // 显式指定更新条件
              .ExecuteCommand();
            }
            else
            {
                throw new NotImplementedException("未找到对应来料检验申请单");
            }

           if (orUpdate) return true;
           throw new NotImplementedException("插入或更新失败");
        }

    }

    public string DeleteLaboratory(string JYDH)
    {
        try
        {
            var db = SqlSugarHelper.GetInstance();
            // 假设 db 是 SQLSugar 的 SqlSugarClient 实例
            var result = db.Updateable<UpdateMesQmsSample>()
              .SetColumns(t => new UpdateMesQmsSample
              {
                  IsSubmit = 2,
              })
              .Where(t => t.JYDH == JYDH) // 显式指定更新条件
              .ExecuteCommand();

            Debug.WriteLine(result);
            var mapping = db.Queryable<UpdateMesQaItems>()
           .Where(t => t.ReleaseNo ==JYDH)
           .Select(t => new UpdateMesQaItems
           {
              ISSJ = t.ISSJ,
           })
           .ToList();

            if (mapping.Any())
            {
                var updateResult = db.Updateable<UpdateMesQaItems>()
               .SetColumns(t => new UpdateMesQaItems
               {
                   ISSJ = 2,
                   SJ_NO=null
               })
               .Where(t => t.ReleaseNo == JYDH) // 显式指定更新条件
               .ExecuteCommand();
            }
            else
            {
                throw new NotImplementedException("未找到对应来料检验申请单");
            }

            if (result > 0)
            {
                return "撤回成功！";
            }
            else
            {
                return "未找到匹配的记录，撤回失败。";
            }
        }
        catch (Exception ex)
        {
            return "撤回失败，错误信息：" + ex.Message;
        }
    }

}