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 T.PROJECT_CODE LIKE '%' || :SearchValue || '%'  -- 项目(来自MES_PROJECT表)
        //           OR A.DAA001 LIKE '%' || :SearchValue || '%'        -- 工单号(来自WOMDAA表)
        //           OR item.ITEM_NO LIKE '%' || :SearchValue || '%'    -- 物料号(来自MES_ITEMS表)
        //           OR item.ITEM_NAME LIKE '%' || :SearchValue || '%'  -- 物料名称(来自MES_ITEMS表)
        //       )"; 
        //       }



        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 MES_LINE ml ON ml.LINE_NO = A.DAA020

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 (!string.IsNullOrEmpty(queryObj.SearchValue))
        {
            switch (queryObj.SelectedIndex)
            {
                case "2": // 工单号
                    workOrderSql += " AND A.DAA001 LIKE '%' || :SearchValue || '%'";
                    break;
                case "3": // 物料号
                    workOrderSql += " AND item.ITEM_NO LIKE '%' || :SearchValue || '%'";
                    break;
                case "4": // 物料名称
                    workOrderSql += " AND item.ITEM_NAME LIKE '%' || :SearchValue || '%'";
                    break;
                case "0": // 项目编码
                    workOrderSql += " AND T.PROJECT_CODE LIKE '%' || :SearchValue || '%'";
                    break;
                case "1": // 线体
                    workOrderSql += " AND A.DAA020 LIKE '%' || :SearchValue || '%'";
                    break;
                default:
                    return (new List<ProductionLinePageResult>(), 0);
            }
        }
        // 根据 selectedIndex 添加额外的搜索条件


        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 += sql += @"
        //    AND (
        //           A.BILL_NO LIKE :SearchValue
        //        OR DA.DAA001 LIKE :SearchValue
        //        OR DA.DAA002 LIKE :SearchValue
        //        OR T.PROJECT_CODE LIKE '%' || :SearchValue || '%'  -- 项目(来自MES_PROJECT表)
        //       OR ml.LINE_NAME LIKE '%' || :SearchValue || '%'    -- 线体(来自MES_LINE表)
        //      OR A.DAA001 LIKE '%' || :SearchValue || '%'        -- 工单号(来自WOMDAA表)
        //      OR item.ITEM_NO LIKE '%' || :SearchValue || '%'    -- 物料号(来自MES_ITEMS表)
        //      OR item.ITEM_NAME LIKE '%' || :SearchValue || '%'  -- 物料名称(来自MES_ITEMS表)
        //    )";

        //        }


        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 MES_LINE ml ON ml.LINE_NO = DA.DAA020

    --变更后

    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 (!string.IsNullOrEmpty(queryObj.SearchValue))
        {
            switch (queryObj.SelectedIndex)
            {
                case "2": // 工单号
                    sql += " AND DA.DAA001 LIKE '%' || :SearchValue || '%'";
                    break;
                case "3": // 物料号
                    sql += " AND item.ITEM_NO LIKE '%' || :SearchValue || '%'";
                    break;
                case "4": // 物料名称
                    sql += " AND item.ITEM_NAME LIKE '%' || :SearchValue || '%'";
                    break;
                case "0": // 项目编码
                    sql += " AND T.PROJECT_CODE LIKE '%' || :SearchValue || '%'";
                    break;
                case "1": // 线体
                    sql += " AND DA.DAA020 LIKE '%' || :SearchValue || '%'";
                    break;
                default:
                    return (new List<ProductionLinePageResult>(), 0);
            }
        }

        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}");
        }
    }



}