using Masuit.Tools;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.util;
using SqlSugar;

namespace MES.Service.service.QC;

/// <summary>
/// 产线提交服务
/// </summary>
public class ProductionLineService
{
    private readonly BaseService _baseService = new();

    /// <summary>
    /// 获取产线提交页面数据（未提交/已提交）
    /// </summary>
    /// <param name="queryObj">查询参数</param>
    /// <returns></returns>
    public (List<ProductionLinePageResult> item, int TotalCount) GetProductionLinePage(ProductionLineQueryDto queryObj)
    {
        var db = SqlSugarHelper.GetInstance();

        if (queryObj.Status == 0)
        {
            // 未提交页面查询
            return GetUnsubmittedData(queryObj, db);
        }
        else
        {
            // 已提交页面查询
            return GetSubmittedData(queryObj, db);
        }
    }

    /// <summary>
    /// 获取未提交数据（从工单表查询，包含不合格的首检单）
    /// </summary>
    private (List<ProductionLinePageResult> item, int TotalCount) GetUnsubmittedData(ProductionLineQueryDto queryObj, SqlSugarClient db)
    {
        var totalCount = 0;

        //// 第一步：查询工单基础数据
        //var workOrderSql = @"
        //SELECT 
        //    A.ID,
        //    A.DAA001,
        //    A.DAA002 AS ItemNo,
        //    A.DAA003,
        //    A.DAA004,
        //    A.DAA008,
        //    A.DAA018,
        //    A.DAA019 AS DaA019,
        //    A.DAA020 AS Line,
        //    A.DAA024,
        //    A.PID AS Pbaid,
        //    T.PROJECT_CODE AS ProjecT_CODE,
        //    u.FNAME AS FName,
        //    0 AS Urgent,
        //    0 AS IsFirst,
        //    CAST(NULL AS VARCHAR2(50)) AS BillNo,
        //    CAST(NULL AS VARCHAR2(50)) AS Result,
        //    CAST(NULL AS VARCHAR2(50)) AS StatusUser,
        //    CAST(NULL AS DATE) AS CreateTime,
        //    CAST(NULL AS NUMBER) AS InspectionId
        //FROM WOMDAA A
        //LEFT JOIN WOMCAA c ON c.CAA001 = A.DAA021
        //LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
        //LEFT JOIN MES_UNIT u ON TO_CHAR(u.ID) = TO_CHAR(A.DAA005)
        //WHERE A.DAA018 LIKE '%已开工%' 
        //    AND A.FSTATUS = 1 
        //    AND A.F_TYPE = 0
        //    AND CASE 
        //        WHEN C.CAA023 = '0' THEN '开立'
        //        WHEN C.CAA023 = '1' THEN '已核准'
        //        WHEN C.CAA023 = '2' THEN '开工'
        //        WHEN C.CAA023 = '3' THEN '完工'
        //        WHEN C.CAA023 = '4' THEN '核准中' 
        //    END LIKE '%开工%'";

        // 第一步：查询工单基础数据
        // 第一步：查询工单基础数据
        var workOrderSql = @"
        SELECT 
            A.ID,
            A.DAA001,
            item.ITEM_NO AS ItemNo,
            A.DAA003,
            A.DAA004,
            A.DAA008,
            A.DAA018,
            A.DAA019 AS DaA019,
            A.DAA020 AS Line,
            
            A.PID AS Pbaid,
            T.PROJECT_CODE AS ProjecT_CODE,
            u.FNAME AS FName,
            0 AS Urgent,
            0 AS IsFirst,
            CAST(NULL AS VARCHAR2(50)) AS BillNo,
            CAST(NULL AS VARCHAR2(50)) AS Result,
            CAST(BE1.FNAME AS VARCHAR2(50)) AS StatusUser,
            CAST(NULL AS DATE) AS CreateTime,
            CAST(NULL AS NUMBER) AS InspectionId
        FROM WOMDAA A
        LEFT JOIN WOMCAA c ON c.CAA001 = A.DAA021
        LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
        LEFT JOIN MES_UNIT u ON TO_CHAR(u.ID) = TO_CHAR(A.DAA005)
 left join MES_ITEMS item on item.ITEM_ID=c.CAA006
  LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
         LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
         LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
        WHERE A.DAA018 LIKE '%已开工%' 
            AND A.FSTATUS = 1 
            AND A.F_TYPE = 0
            AND CASE 
                WHEN C.CAA023 = '0' THEN '开立'
                WHEN C.CAA023 = '1' THEN '已核准'
                WHEN C.CAA023 = '2' THEN '开工'
                WHEN C.CAA023 = '3' THEN '完工'
                WHEN C.CAA023 = '4' THEN '核准中' 
            END LIKE '%开工%'";

        // 添加搜索条件
        if (StringUtil.IsNotNullOrEmpty(queryObj.SearchValue))
        {
            workOrderSql += " AND (A.DAA001 LIKE :SearchValue OR A.DAA002 LIKE :SearchValue)";
        }

        var workOrders = db.SqlQueryable<ProductionLinePageResult>(workOrderSql)
            .AddParameters(new { SearchValue = $"%{queryObj.SearchValue}%" })
            .ToList();

        if (!workOrders.Any())
        {
            return (new List<ProductionLinePageResult>(), 0);
        }

        // 转换为字符串用于IN查询
        var workOrderIdStrings = workOrders
            .Where(w => !string.IsNullOrEmpty(w.ID.ToString()))
            .Select(w => w.ID)
            .ToList();

        if (!workOrderIdStrings.Any())
        {
            return (new List<ProductionLinePageResult>(), 0);
        }

        // 第二步：查询这些工单对应的首检单及检验结果
        var inspectionResultSql = $@"
        SELECT
    A.ID,
    A.BILL_NO AS BillNo,
    A.PBAID,
    A.STATUS_USER AS StatusUser,
    A.CREATE_TIME AS CreateTime,
    CASE
        WHEN RES.检验结果 IS NULL THEN '未完成'
        ELSE RES.检验结果
    END AS Result
FROM (
    -- 只取每个工单(PBAID)最新一条首检单
    SELECT *
    FROM (
        SELECT
            R.*,
            ROW_NUMBER() OVER(PARTITION BY R.PBAID ORDER BY R.CREATE_TIME DESC) AS RN
        FROM QS_ITEM_IPI_REQ R
    ) WHERE RN = 1
) A
LEFT JOIN (
    SELECT
        PID,
        CASE
            WHEN (COUNT(CASE WHEN PASSED = -1 THEN 1 END) > 0) THEN NULL
            WHEN (COUNT(CASE WHEN PASSED = 1 THEN 1 END) = COUNT(*)) THEN '合格'
            ELSE '不合格'
        END AS 检验结果
    FROM (
        SELECT
            A.PID,
            CASE
                WHEN (A.IS_PASS IS NULL OR B.完成状态 IS NULL) THEN -1
                WHEN (NVL(A.IS_PASS, -1) = NVL(B.完成状态, -1) AND A.IS_PASS = 1) THEN 1
                WHEN (NVL(A.IS_PASS, -1) = NVL(B.完成状态, -1) AND A.IS_PASS = 0) THEN 0
                ELSE -1
            END AS PASSED
        FROM QS_ITEM_IPI_ITEM A
        LEFT JOIN (
            SELECT
                PID,
                CASE
                    WHEN (COUNT(CASE WHEN FSTAND IS NULL THEN 1 END) > 0) THEN NULL
                    WHEN (COUNT(CASE WHEN FSTAND = '√' THEN 1 END) = COUNT(1)) THEN 1
                    ELSE 0
                END AS 完成状态
            FROM QS_ITEM_IPI_ITEM_DETAIL
            GROUP BY PID
        ) B ON A.ID = B.PID
    )
    GROUP BY PID
) RES ON RES.PID = A.ID
        WHERE A.PBAID IN ({string.Join(",", workOrderIdStrings)})";

        var inspections = db.SqlQueryable<InspectionResultDto>(inspectionResultSql).ToList();

        // 第三步：合并数据并过滤
        var result = (from wo in workOrders
                      join ins in inspections on wo.ID.ToString() equals ins.Pbaid.ToString() into insGroup
                      from ins in insGroup.DefaultIfEmpty()
                      where ins == null || ins.Result == "不合格"  // 未提交或不合格的显示
                      select new ProductionLinePageResult
                      {
                          ID = wo.ID,
                          DAA001 = wo.DAA001,
                          ItemNo = wo.ItemNo,
                          DAA003 = wo.DAA003,
                          DAA004 = wo.DAA004,
                          DAA008 = wo.DAA008,
                          DAA018 = wo.DAA018,
                          DaA019 = wo.DaA019,
                          Line = wo.Line,
                          DAA024 = wo.DAA024,
                          ProjecT_CODE = wo.ProjecT_CODE,
                          FName = wo.FName,
                          BillNo = ins?.BillNo,
                          Result = ins?.Result,
                          StatusUser = wo?.StatusUser,
                          CreateTime = ins?.CreateTime,
                          InspectionId = ins?.ID,
                          Pbaid = wo.Pbaid,
                          Urgent = wo.Urgent,
                          IsFirst = wo.IsFirst,
                          SortOrder = ins?.Result == "不合格" ? 1 : 0  // 不合格置顶
                      })
                     .OrderByDescending(x => x.SortOrder)
                     .ThenByDescending(x => x.DAA024)
                     .ToList();

        totalCount = result.Count;

        // 分页
        var pagedData = result
            .Skip((queryObj.PageIndex - 1) * queryObj.Limit)
            .Take(queryObj.Limit)
            .ToList();

        return (pagedData, totalCount);
    }

    /// <summary>
    /// 获取已提交数据（从首检单表查询）
    /// </summary>
    private (List<ProductionLinePageResult> item, int TotalCount) GetSubmittedData(ProductionLineQueryDto queryObj, SqlSugarClient db)
    {
        var totalCount = 0;

        var sql = @"
            SELECT 
                A.ID AS InspectionId,
                A.BILL_NO AS BillNo,
                A.PBAID AS Pbaid,
                DA.DAA001,
               item.ITEM_NO AS ItemNo,
                DA.DAA003,
                DA.DAA004,
                DA.DAA008,
                DA.DAA018,
                DA.DAA019 AS DaA019,
                DA.DAA020 AS Line,
                A.CREATE_TIME AS CreateTime,
                 NVL(BE3.FNAME, BE1.FNAME)  AS StatusUser,
                A.FSUBMIT,
                A.CREATE_BY,
                0 AS Urgent,
                0 AS IsFirst,
                T.PROJECT_CODE AS ProjecT_CODE,
                CASE
                    WHEN 检验结果 IS NULL THEN '未完成'
                    ELSE 检验结果
                END AS Result
            FROM QS_ITEM_IPI_REQ A
            LEFT JOIN WOMDAA DA ON DA.ID = A.PBAID
            LEFT JOIN WOMCAA c ON c.CAA001 = DA.DAA021
            LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
 left join MES_ITEMS item on item.ITEM_ID=c.CAA006
--变更后
LEFT JOIN INSPECTOR_CHANGE_SJ_LOG chlog
                   ON chlog.INSPECTION_NO = a.BILL_NO AND chlog.IS_VALID = 'Y'
 LEFT JOIN SYS_USER BE3 ON chlog.INSPECTOR = BE3.FCODE
--变更前
  LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
         LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
         LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
            LEFT JOIN (
                SELECT 
                    PID,
                    CASE
                        WHEN (COUNT(CASE WHEN PASSED = -1 THEN 1 END) > 0) THEN NULL
                        WHEN (COUNT(CASE WHEN PASSED = 1 THEN 1 END) = COUNT(*)) THEN '合格'
                        ELSE '不合格'
                    END AS 检验结果
                FROM (
                    SELECT 
                        A.PID,
                        CASE
                            WHEN (A.IS_PASS IS NULL OR B.完成状态 IS NULL) THEN -1
                            WHEN (NVL(A.IS_PASS, -1) = NVL(B.完成状态, -1) AND A.IS_PASS = 1) THEN 1
                            WHEN (NVL(A.IS_PASS, -1) = NVL(B.完成状态, -1) AND A.IS_PASS = 0) THEN 0
                            ELSE -1
                        END AS PASSED
                    FROM QS_ITEM_IPI_ITEM A
                    LEFT JOIN (
                        SELECT 
                            PID,
                            CASE
                                WHEN (COUNT(CASE WHEN FSTAND IS NULL THEN 1 END) > 0) THEN NULL
                                WHEN (COUNT(CASE WHEN FSTAND = '√' THEN 1 END) = COUNT(1)) THEN 1
                                ELSE 0
                            END AS 完成状态
                        FROM QS_ITEM_IPI_ITEM_DETAIL
                        GROUP BY PID
                    ) B ON A.ID = B.PID
                )
                GROUP BY PID
            ) RES ON RES.PID = A.ID
            WHERE A.ID IS NOT NULL";

        // 添加搜索条件
        if (StringUtil.IsNotNullOrEmpty(queryObj.SearchValue))
        {
            sql += " AND (A.BILL_NO LIKE :SearchValue OR DA.DAA001 LIKE :SearchValue OR DA.DAA002 LIKE :SearchValue)";
        }

        sql += " ORDER BY A.CREATE_TIME DESC";

        var data = db.SqlQueryable<ProductionLinePageResult>(sql)
            .AddParameters(new { SearchValue = $"%{queryObj.SearchValue}%" })
            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

        return (data, totalCount);
    }



    /// <summary>
    /// 提交检验（调用存储过程生成首检单）
    /// </summary>
    /// <param name="workOrderId">工单ID</param>
    /// <param name="userNo">用户账号</param>
    /// <returns></returns>
    public (bool success, string message) SubmitInspection(decimal workOrderId, string userNo)
    {
        var db = SqlSugarHelper.GetInstance();

        try
        {
            // 定义存储过程参数
            var pId = new SugarParameter("P_ID", workOrderId);
            var pUser = new SugarParameter("P_USER", userNo);
            var pIsh = new SugarParameter("P_ISH", 0);  // 0表示首检
            var pFlag = new SugarParameter("P_FLAG", null, true) { Direction = System.Data.ParameterDirection.Output };
            var pText = new SugarParameter("P_TEXT", null, true)
            {
                Direction = System.Data.ParameterDirection.Output,
                Size = 255
            };

            // 调用存储过程
            db.Ado.UseStoredProcedure().ExecuteCommand(
                "PRC_SJ_GENERATE",
                pId, pUser, pIsh, pFlag, pText
            );

            // 获取输出参数
            var flag = pFlag.Value != null ? Convert.ToInt32(pFlag.Value) : 1;
            var text = pText.Value?.ToString() ?? "未知错误";

            if (flag == 0)
            {
                return (true, text);
            }
            else
            {
                return (false, text);
            }
        }
        catch (Exception ex)
        {
            return (false, $"提交检验失败: {ex.Message}");
        }
    }



}