using Masuit.Tools;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.service.BasicData;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MES.Service.service.Warehouse
{
    public class WomdaaManager
    {
        /// <summary>
        /// 获取产测登录号对应工单信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetTickeInfo(string LineId,string LoginId)
        {
            OracleSQLHelper SQLHelper = new();
            //查工单信息
            //var sql1 = @"select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from MESLINE_LIKE_CCLOGIN A 
            //            join MES_LINE b on A.LINEID=b.ID
            //            left join WOMDAA C on C.DAA015=B.LINE_NO
            //            left join mes_items s on s.id = C.daa002
            //            where LOGINID='" + id + "' and DAA018='已开工'  order by DAA016 asc";

            //改为按站位号反查产线，再通过产线查出工单信息
            //var sql1 = @"select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from MESLINE_LIKE_CCLOGIN A
            //            left join MESLINE_LIKE_CCLOGIN_WORKS A1 on A.id=a1.LOGINID
            //            join MES_LINE b on A.LINEID=b.ID
            //            left join WOMDAA C on C.DAA015=B.LINE_NO
            //            left join mes_items s on s.id = C.daa002
            //           where WORKSNO='" + id + "' and DAA018='已开工'  order by DAA016 asc";


            //取直接关联线体的产测登录号数据，去取获取工单
            //var sql1 = "select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from " +
            //    "MESLINE_LIKE_CCLOGIN_WORKS A1" +
            //    "                        join MES_LINE b on A1.LOGINID=b.ID" +
            //    "                        left join WOMDAA C on C.DAA015=B.LINE_NO" +
            //    "                        left join mes_items s on s.id = C.daa002" +
            //    "                       where WORKSNO='" + id + "' and DAA018='已开工'  order by DAA016 asc";
            //var sql1 = "select DAA001 tickeNo, s.ITEM_NO itemNo, s.ITEM_NAME itemName, s.ITEM_MODEL itemModel, DAA008 plannedNum\r\nfrom  WOMDAA C\r\n         left join mes_items s on s.id = C.daa002\r\nleft join womcaa A on A.caa001=C.daa021\r\nwhere C.DAA015 = '" + LineId + "'\r\n  and DAA018 = '已开工' and A.CAA023='2'\r\norder by DAA016 asc";
            var sql1 = "select DAA001 tickeNo, s.ITEM_NO itemNo, s.ITEM_NAME itemName, s.ITEM_MODEL itemModel, DAA008 plannedNum\r\nfrom  WOMDAA C\r\n         left join mes_items s on s.id = C.daa002\r\nleft join womcaa A on A.caa001=C.daa021\r\nleft join MESPROJECT_LIKE_CCLOGIN pr1 on A.PROJECT=pr1.PROJECT_ID\r\nleft join MESLINE_LIKE_CCLOGIN_WORKS2 pr2 on pr1.id=pr2.LOGINID\r\nwhere C.DAA015 = '" + LineId + "' and pr2.WORKSNO='"+ LoginId + "'\r\n  and DAA018 = '已开工' and A.CAA023='2'\r\norder by DAA016 asc";
            return SQLHelper.ExecuteQuery(sql1);
        }


        /// <summary>
        /// 记录产测过站信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        //public bool AddAsnInfo(AsnInfo asnInfo)
        //{
        //    OracleSQLHelper SQLHelper = new();
        //    //记录产测过站信息
        //    var sql1 = @"INSERT  INTO MES_SN_STATUS_DETAILS (ID,TICKET_NO,SN_NO,WORKSTATION_NO,SN_STATE,LOGIN_ID) VALUES (SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,'" + asnInfo.tickeNo+ "','"+ asnInfo.snNo+ "','"+ asnInfo.workstationNo+ "','"+ asnInfo.snState+ "','"+asnInfo.loginId+"')";
        //    bool result = true;
        //    int i= SQLHelper.ExecuteNonQuery(sql1);
        //    //主表信息插入成功，继续插入项目
        //    if (i > 0) {
        //        //判断项目是否为空，为空不插入
        //        if (asnInfo.failItems.Count>0)
        //        {
        //            //插入逻辑

        //        }
        //        else
        //        {
        //            result = true;
        //        }


        //    } else { result = false; }

        //    return result;
        //}
        public bool AddAsnInfo(AsnInfo asnInfo)
        {
            var sqlHelper = new OracleSQLHelper();
            bool result = true;

            var (connection, transaction) = sqlHelper.BeginTransaction();

            try
            {
                // 1. 主表插入
                var sql1 = $@"
            INSERT INTO MES_SN_STATUS_DETAILS
              (ID, TICKET_NO, SN_NO, WORKSTATION_NO, SN_STATE, LOGIN_ID,TURNOVER_CODE)
              VALUES (SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,
                      '{asnInfo.tickeNo}',
                      '{asnInfo.snNo}',
                      '{asnInfo.workstationNo}',
                      '{asnInfo.snState}',
                      '{asnInfo.loginId}','{asnInfo.turnoverCode}')";
                int i = sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql1);

                // 2. 失败项插入
                if (i > 0 && asnInfo.failItems?.Count > 0)
                {
                    foreach (var item in asnInfo.failItems)
                    {
                        var sql2 = $@"
                    INSERT INTO CC_FAIL_ITEMS
                      (ID, MID, DEFECT_ISSUE, ROOT_CAUSE, REPAIR_METHOD, MATERIAL_HANDLING, CREATE_DATE, CREATED_BY)
                      VALUES (SEQ_CC_FAIL_ITEMS.NEXTVAL,
                              SEQ_MES_SN_STATUS_DETAILS_ID.CURRVAL,
                              '{item.DefectIssue}',
                              '{item.RootCause}',
                              '{item.RepairMethod}',
                              '{item.MaterialHandling}',
                              SYSDATE,
                              '{asnInfo.loginId}')";
                        sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
                    }
                }

                // 3. 配件绑定/解绑 & 日志记录
                if (asnInfo.accessoryList?.Count > 0)
                {
                    foreach (var accessory in asnInfo.accessoryList)
                    {
                        var now = "SYSDATE";
                        var user = asnInfo.loginId;
                        var snNo = asnInfo.snNo;
                        var partSn = accessory.accessorySn;
                        var partName = accessory.accessoryName;

                        if (accessory.bindingState == "0")
                        {
                            // ====== 校验：同一个配件SN是否已绑定 ======
                            var checkSql = $@"
                        SELECT COUNT(1) FROM MES_SN_PART_BINDING 
                         WHERE PART_SN_NO = '{partSn}'";
                            var exists = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, checkSql));
                            if (exists > 0)
                            {
                                throw new Exception($"配件SN[{partSn}]已绑定，请先解绑再绑定！");
                            }

                            // 绑定
                            var sqlBind = $@"
                        INSERT INTO MES_SN_PART_BINDING
                          (ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER)
                          VALUES (SEQ_MES_SN_PART_BINDING.NEXTVAL,
                                  '{snNo}',
                                  '{partSn}',
                                  '{partName}',
                                  {now},
                                  '{user}')";
                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlBind);

                            // 日志
                            var sqlLog = $@"
                        INSERT INTO MES_SN_PART_BINDING_LOG
                          (ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER, OP_TYPE)
                          VALUES (SEQ_MES_SN_PART_BINDING_LOG.NEXTVAL,
                                  '{snNo}',
                                  '{partSn}',
                                  '{partName}',
                                  {now},
                                  '{user}',
                                  0)";
                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlLog);
                        }
                        else if (accessory.bindingState == "1")
                        {
                            // 解绑
                            var sqlUnbind = $@"
                        DELETE FROM MES_SN_PART_BINDING
                         WHERE SN_NO = '{snNo}'
                           AND PART_SN_NO = '{partSn}'";
                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlUnbind);

                            // 日志
                            var sqlLog = $@"
                        INSERT INTO MES_SN_PART_BINDING_LOG
                          (ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER, OP_TYPE)
                          VALUES (SEQ_MES_SN_PART_BINDING_LOG.NEXTVAL,
                                  '{snNo}',
                                  '{partSn}',
                                  '{partName}',
                                  {now},
                                  '{user}',
                                  1)";
                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlLog);
                        }
                    }
                }

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                result = false;
                throw;
            }
            finally
            {
                sqlHelper.CloseConnection(connection);
            }

            return result;
        }



        /// <summary>
        /// 设置中箱码（绑定/解绑 SN）
        /// </summary>
        /// <param name="mesMiddleBox">中箱码及SN数据</param>
        /// <returns>返回执行状态</returns>
        public string[] SetMediumBoxCode(MesMiddleBox mesMiddleBox)
        {
            var sqlHelper = new OracleSQLHelper();
            string[] msg = new string[2];
            msg[0] = "true";

            var (connection, transaction) = sqlHelper.BeginTransaction();

            try
            {
                if (mesMiddleBox.type.ToUpper() == "A") // 绑定
                {
                    if (mesMiddleBox.SnList.Count > 0)
                    {
                        string sql1 = $"select count(*) count from MES_MIDDLE_BOX where MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
                        DataTable data = sqlHelper.ExecuteQuery(sql1);

                        if (data.Rows[0]["COUNT"].ToString() != "0")
                        {
                            msg[0] = "false";
                            msg[1] = "该中箱码已经绑定SN数据，如需再次绑定请先解绑！中箱码：" + mesMiddleBox.MediumBoxCode;
                            transaction.Rollback();
                            return msg;
                        }

                        foreach (var item in mesMiddleBox.SnList)
                        {
                            string sqlInsert = $@"
                        INSERT INTO MES_MIDDLE_BOX (
                            ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY
                        ) VALUES (
                            SEQ_MES_MIDDLE_BOX_ID.NEXTVAL,
                            '{mesMiddleBox.MediumBoxCode}',
                            '{mesMiddleBox.LoginId}',
                            '{item.SnNo}',
                            '{item.TicketNo}',
                            SYSDATE,
                            '{mesMiddleBox.LoginId}'
                        )";

                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlInsert);

                            // 日志记录插入：操作类型 0（绑定）
                            string logInsert = $@"
                        INSERT INTO MES_MIDDLE_BOX_LOG (
                            ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY, OPERATION_TYPE
                        ) VALUES (
                            MES_MIDDLE_BOX_LOG_ID.NEXTVAL,
                            '{mesMiddleBox.MediumBoxCode}',
                            '{mesMiddleBox.LoginId}',
                            '{item.SnNo}',
                            '{item.TicketNo}',
                            SYSDATE,
                            '{mesMiddleBox.LoginId}',
                            '0'
                        )";
                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
                        }
                    }
                    else
                    {
                        msg[0] = "false";
                        msg[1] = "SN明细为空，无法绑定！";
                    }
                }
                else if (mesMiddleBox.type.ToUpper() == "F") // 解绑
                {
                    // 查询要解绑的记录
                    string sqlSelect = $@"
                SELECT * FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";

                    DataTable dt = sqlHelper.ExecuteQuery(sqlSelect);

                    foreach (DataRow row in dt.Rows)
                    {
                        // 插入解绑日志：操作类型 1（解绑）
                        string logInsert = $@"
                    INSERT INTO MES_MIDDLE_BOX_LOG (
                        ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY, OPERATION_TYPE
                    ) VALUES (
                        MES_MIDDLE_BOX_LOG_ID.NEXTVAL,
                        '{row["MEDIUMBOXCODE"]}',
                        '{row["LOGIN_ID"]}',
                        '{row["SN_NO"]}',
                        '{row["TICKET_NO"]}',
                        SYSDATE,
                        '{mesMiddleBox.LoginId}',
                        '1'
                    )";
                        sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
                    }

                    // 执行解绑（删除）
                    string sqlDelete = $@"DELETE FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
                    sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlDelete);
                }
                else
                {
                    msg[0] = "false";
                    msg[1] = "无效的操作类型！";
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                msg[0] = "false";
                msg[1] = ex.Message;
                throw; // 建议记录日志
            }
            finally
            {
                sqlHelper.CloseConnection(connection);
            }

            return msg;
        }



        /// <summary>
        /// 设置栈板码（绑定/解绑 SN）
        /// </summary>
        /// <param name="mesPalletBinding">绑定参数</param>
        /// <returns>返回绑定结果</returns>
        public string[] SetStackCode(MesPalletBinding mesPalletBinding)
        {
            var sqlHelper = new OracleSQLHelper();
            string[] msg = new string[2];
            msg[0] = "true";

            var (connection, transaction) = sqlHelper.BeginTransaction();

            try
            {
                // 绑定
                if (mesPalletBinding.Type.ToUpper() == "A")
                {
                    if (mesPalletBinding.SnList.Count > 0)
                    {
                        string sql1 = $"SELECT COUNT(*) COUNT FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
                        DataTable data = sqlHelper.ExecuteQuery(sql1);

                        if (data.Rows[0]["COUNT"].ToString() != "0")
                        {
                            msg[0] = "false";
                            msg[1] = "该栈板码已经绑定SN数据，如需再次绑定请先解绑！栈板码：" + mesPalletBinding.StackCode;
                            transaction.Rollback();
                            return msg;
                        }

                        foreach (var item in mesPalletBinding.SnList)
                        {
                            string sqlInsert = $@"
                        INSERT INTO MES_PALLET_BINDING (
                            ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY
                        ) VALUES (
                            SEQ_MES_PALLET_BINDING_ID.NEXTVAL,
                            '{mesPalletBinding.StackCode}',
                            '{mesPalletBinding.LoginId}',
                            '{item.SnNo}',
                            '{item.TicketNo}',
                            '{item.MediumBoxCode}',
                            SYSDATE,
                            '{mesPalletBinding.LoginId}'
                        )";

                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlInsert);

                            // 插入绑定日志（操作类型 0）
                            string logInsert = $@"
                        INSERT INTO MES_PALLET_BINDING_LOG (
                            ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY, OPERATION_TYPE
                        ) VALUES (
                            MES_PALLET_BINDING_LOG_ID.NEXTVAL,
                            '{mesPalletBinding.StackCode}',
                            '{mesPalletBinding.LoginId}',
                            '{item.SnNo}',
                            '{item.TicketNo}',
                            '{item.MediumBoxCode}',
                            SYSDATE,
                            '{mesPalletBinding.LoginId}',
                            '0'
                        )";

                            sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
                        }
                    }
                    else
                    {
                        msg[0] = "false";
                        msg[1] = "SN明细为空，请确认！";
                    }
                }
                // 解绑
                else if (mesPalletBinding.Type.ToUpper() == "F")
                {
                    // 先查询绑定的记录
                    string sqlSelect = $"SELECT * FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
                    DataTable dt = sqlHelper.ExecuteQuery( sqlSelect);

                    foreach (DataRow row in dt.Rows)
                    {
                        // 插入解绑日志（操作类型 1）
                        string logInsert = $@"
                    INSERT INTO MES_PALLET_BINDING_LOG (
                        ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY, OPERATION_TYPE
                    ) VALUES (
                        MES_PALLET_BINDING_LOG_ID.NEXTVAL,
                        '{row["STACKCODE"]}',
                        '{row["LOGIN_ID"]}',
                        '{row["SN_NO"]}',
                        '{row["TICKET_NO"]}',
                        '{row["MEDIUMBOXCODE"]}',
                        SYSDATE,
                        '{mesPalletBinding.LoginId}',
                        '1'
                    )";

                        sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
                    }

                    // 删除绑定记录
                    string sqlDelete = $"DELETE FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
                    sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlDelete);
                }

                // 提交事务
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                msg[0] = "false";
                msg[1] = ex.Message;
                throw; // 建议记录日志
            }
            finally
            {
                sqlHelper.CloseConnection(connection);
            }

            return msg;
        }


    }
}