using MES.Service.Models;
using Microsoft.IdentityModel.Tokens;
using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using NewPdaSqlServer.entity;
using NewPdaSqlServer.util;
using SqlSugar;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using static Azure.Core.HttpHeader;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace NewPdaSqlServer.service.QC;

public class IpqcService : RepositoryNoEntity
{
    public (List<VIpqcSj> item, int TotalCount) getPageSj(XJPageResult queryObj)
    {
        var parsedGuid = Guid.Empty;
        if (!queryObj.id.IsNullOrEmpty())
        {
            var isValid = Guid.TryParse(queryObj.id, out parsedGuid);
            if (!isValid)
                throw new ApplicationException("GUID转换错误");
        }


        var totalCount = 0;

        var pageList = Db.Queryable<VIpqcSj>()
            .WhereIF(!string.IsNullOrWhiteSpace(queryObj.keyword), a =>
                a.ItemNo.Contains(queryObj.keyword) ||
                a.ItemName.Contains(queryObj.keyword) ||
                a.Daa001.Contains(queryObj.keyword) ||
                a.ReleaseNo.Contains(queryObj.keyword) ||
                a.DepartmentName.Contains(queryObj.keyword )||
                a.XtName.Contains(queryObj.keyword) 
            )
            .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
                a => a.Guid == parsedGuid.ToString() )
            .Where(a => (a.FSubmit ?? 0) == 0)
            .OrderByDescending(a => a.CreateDate)
            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

        return (pageList, totalCount);
    }

    public List<dynamic> GetItems(string? releaseNo,
        string? id)
    {
        var parsedGuid = Guid.Empty;

        var sql = string.Format(@"SELECT [a].[guid]                                AS [Guid],
       [a].[parent_guid]                         AS [ParentGuid],
       [a].[release_no]                          AS [ReleaseNo],
       [a].[check_qyt]                           AS [CheckQyt],
       [a].[fac_level]                           AS [FacLevel],
       [a].[fcheck_item]                         AS [FcheckItem],
       [a].[fcheck_tool]                         AS [FcheckTool],
       [a].[fdown_allow]                         AS [FdownAllow],
       [a].[fcheck_level]                        AS [FcheckLevel],
       [a].[fstand]                              AS [Fstand],
       [a].[fup_allow]                           AS [FupAllow],
       [a].[sample_size_no]                      AS [SampleSizeNo],
       [a].[fspec_requ]                          AS [FspecRequ],
       [a].[fre_qty]                             AS [FreQty],
       N'1000'                                   AS [Factory],
       N'1000'                                   AS [Company],
       COUNT([b].[guid])                         AS [FenterQty],
       [a].[fcheck_resu]                         AS [FcheckResu],
       [a].[forder]                              AS [Order],
       [a].[ybsl]                                AS [Ybsl],
       [a].stationName,
       [a].IS_GWSCAN,
       ISNULL((SELECT COUNT(1)
               FROM MES_QA_ITEMS_DETECT_DETAIL13 C
               WHERE C.parent_guid = A.guid), 0) AS [YbslIn]
FROM [MES_QA_ITEMS_DETECT_DETAIL5] [a]
         Left JOIN [MES_QA_ITEMS_DETECT_DETAIL12] [b] ON ([a].[guid] = [b].[parent_guid])
WHERE ([a].[release_no] = N'{0}' AND IS_GWSCAN = 1)
GROUP BY [a].[guid], [a].[parent_guid], [a].[release_no], [a].[fac_level], [a].[fcheck_item], [a].[fcheck_tool],
         [a].[fdown_allow], [a].[fcheck_level], [a].[fstand], [a].[fup_allow], [a].[sample_size_no], [a].[fspec_requ],
         [a].[fre_qty], [a].[check_qyt], [a].[fcheck_resu], [a].[forder], [a].[ybsl], [a].stationName,
         [a].IS_GWSCAN
ORDER BY [a].[forder] ASC", releaseNo);

        var Ybsl_In = Db.Ado.SqlQuery<dynamic>(sql).ToList();

        return Ybsl_In;
    }


    public (List<VIpqcXj> item, int TotalCount) getPageXj(XJPageResult queryObj)
    {
        var parsedGuid = Guid.Empty;
        if (!queryObj.id.IsNullOrEmpty())
        {
            var isValid = Guid.TryParse(queryObj.id, out parsedGuid);
            if (!isValid)
                throw new ApplicationException("GUID转换错误");
        }


        var totalCount = 0;

        var pageList = Db.Queryable<VIpqcXj>()
            .WhereIF(!string.IsNullOrWhiteSpace(queryObj.keyword), a =>
                a.ItemNo.Contains(queryObj.keyword) ||
                a.ItemName.Contains(queryObj.keyword) ||
                a.Daa001.Contains(queryObj.keyword) ||
                a.ReleaseNo.Contains(queryObj.keyword) ||
                a.DepartmentName.Contains(queryObj.keyword) ||
                a.xtName.Contains(queryObj.keyword)
            )
            .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
                a => a.Guid == parsedGuid.ToString())
            .Where(a => (a.FSubmit ?? 0) == 0)
            .OrderByDescending(a => a.CreateDate)
            .OrderBy(a => a.checkTimeSlot)
            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

        return (pageList, totalCount);
    }


    //更新不合格描述
    public int updateIpqcRzxx(dynamic dto)
    {
        if (dto == null) throw new ArgumentNullException(nameof(dto), "参数对象不能为 null");
        if (string.IsNullOrEmpty(dto.pid?.ToString()))
            throw new ArgumentException("项目明细id存在问题，请联系管理员！", nameof(dto.pid));

        var sqlParams = new List<SugarParameter> {
            new("@inRzxxValue", dto.inRzxxValue),
            new("@pid", dto.pid)
        };

        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_rzxx = @inRzxxValue 
                    WHERE guid = @pid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }


    public dynamic CreateByWomdaa(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query));

        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户账号不能为空", nameof(query.userAccount));

        if (string.IsNullOrEmpty(query.inOrderGuid1?.ToString()))
            throw new ArgumentException("检验单GUID不能为空", nameof(query.inOrderGuid1));

            if (string.IsNullOrEmpty(query.DAA001?.ToString()))
                throw new ArgumentException("工单号不能为空", nameof(query.inOrderGuid1));

            var _strMsg = "";
        var _status = -1;

        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        using (var cmd = new SqlCommand("ipqc_createByWomdaa", conn))
        {
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter[] parameters = 
                {
                    new("@po_outMsg", SqlDbType.NVarChar, 150) { Direction = ParameterDirection.Output },
                    new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output },
                    new("@pi_user", SqlDbType.NVarChar, 150) { Value = query.userAccount },
                    new("@pi_OrderGuid1", SqlDbType.UniqueIdentifier) { Value = Guid.Parse(query.inOrderGuid1.ToString()) },
                    new("@pi_DAA001", SqlDbType.NVarChar, 150) { Value = query.DAA001.ToString() },
                    new("@pi_inP1", SqlDbType.NVarChar, 20) { Value = query.pi_inP1 ?? DBNull.Value },
                    new("@pi_inP2", SqlDbType.NVarChar, 20) { Value = query.pi_inP2 ?? DBNull.Value }
                };

                cmd.Parameters.AddRange(parameters);
                cmd.ExecuteNonQuery();

                _strMsg = parameters[0].Value?.ToString() ?? "";
                _status = Convert.ToInt32(parameters[1].Value ?? -1);

                if (_status <= 0) throw new Exception(_strMsg);

                return new 
                {
                    message = _strMsg,
                    status = _status
                };
            }
            catch (Exception ex)
            {
                throw new Exception($"检验单更新失败：{ex.Message}");
            }
        }
    }

    public int UpdateTableConfig(string selectedWater, string selectedFlow, string tableData, string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@selectedWater", selectedWater),
            new("@selectedFlow", selectedFlow),
            new("@tableData", tableData),
            new("@mxguid", mxguid)
        };

        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_zrxn_sel1 = @selectedWater,
                        ipqc_zrxn_sel2 = @selectedFlow,
                        ipqc_zrxn_table = @tableData 
                    WHERE guid = @mxguid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }

    public dynamic GetTableConfig(string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@mxguid", mxguid)
        };

        var sql = @"SELECT ipqc_zrxn_sel1,ipqc_zrxn_sel2,ipqc_zrxn_table 
                    FROM MES_QA_ITEMS_DETECT_DETAIL5 
                    WHERE guid = @mxguid";

        var result = Db.Ado.SqlQuery<dynamic>(sql, sqlParams).FirstOrDefault();
        
        if (result == null)
            throw new Exception("配置信息查询结果为空");

        return result;
    }

    public List<dynamic> GetRzxxList(string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@mxguid", mxguid)
        };

        var sql = @"SELECT a.s_type, a.defect_name 
                    FROM [dbo].[MES_DEFECT_CODE] a 
                    WHERE a.s_type = '认证信息' 
                    AND a.pid = ( 
                        SELECT TOP 1 b.guid 
                        FROM [dbo].[MES_DEFECT_CODE] b 
                        INNER JOIN [dbo].[MES_QA_ITEMS_DETECT_DETAIL5] d 
                            ON b.defect_name = d.fcheck_item 
                        WHERE d.guid = @mxguid 
                        ORDER BY b.guid 
                    )";

        var result = Db.Ado.SqlQuery<dynamic>(sql, sqlParams);
        
        if (result == null || result.Count == 0)
            throw new Exception("未找到相关认证信息");

        return result;
    }

    public int UpdateScDate(string scDateValue, string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@scDateValue", scDateValue),
            new("@mxguid", mxguid)
        };

        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_rzxx_date = @scDateValue 
                    WHERE guid = @mxguid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }

    public int UpdateRzxxNum(string ipqcrzxxNum, string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@ipqcrzxxNum", ipqcrzxxNum),
            new("@mxguid", mxguid)
        };

        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_rzxx_num = @ipqcrzxxNum 
                    WHERE guid = @mxguid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }

    public int UpdateCpscs(string ipqCpscs, string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@ipqCpscs", ipqCpscs),
            new("@mxguid", mxguid)
        };

        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_pscs = @ipqCpscs 
                    WHERE guid = @mxguid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }


    public dynamic GetIpqcXjDaa(dynamic unity)
    {

        var sqlParams = new List<SugarParameter> { };
        sqlParams.Add(new("@xt", unity.xt));

        var sql2 = new StringBuilder(@"
        SELECT '['+DAA001+']['+DAA021+']' AS daaInfo,DAA001,DAA021
        FROM WOMDAA
        WHERE DAA015 = @xt AND daa018 NOT IN ('W:完工', 'D:待开工')");

        if (!string.IsNullOrWhiteSpace(unity.selectKey?.ToString()))
        {
            sqlParams.Add(new("@selectKey", unity.selectKey));
            sql2.Append(@"
            AND (DAA001 LIKE '%' + @selectKey + '%' 
            OR DAA021 LIKE '%' + @selectKey + '%')");
        }

        var XcslItem = Db.Ado.SqlQuery<dynamic>(sql2.ToString(), sqlParams);

        if (XcslItem == null)
        {
            throw new Exception("该条件下无对应工单信息，请重新输入！");
        }

        return XcslItem;
    }


    public dynamic SelIpqcItemsByGw(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query));

        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));

        if (string.IsNullOrEmpty(query.inOrderGuid1?.ToString()))
            throw new ArgumentException("检验单主表GUID不允许为空", nameof(query.inOrderGuid1));

        var _strMsg = "";
        var _status = -1;

        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        using (var cmd = new SqlCommand("prc_selIpqcItemsByGw", conn))
        {
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter[] parameters = 
                {
                    new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
                    new("@pi_gw", SqlDbType.NVarChar, 100) { Value = query.gw },
                    new("@pi_QaGuid1", SqlDbType.NVarChar, 100) { Value = query.inOrderGuid1 },
                    new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                    new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
                };

                cmd.Parameters.AddRange(parameters);
                cmd.ExecuteNonQuery();

                _strMsg = parameters[3].Value?.ToString() ?? "";
                _status = Convert.ToInt32(parameters[4].Value ?? -1);

                if (_status <= 0) throw new Exception(_strMsg);

                return new 
                {
                    message = _strMsg,
                    status = _status,
                    qaGuid = query.inOrderGuid1,
                    gw = query.gw
                };
            }
            catch (Exception ex)
            {
                throw new Exception($"工位扫描失败：{ex.Message}");
            }
        }
    }
}