using Masuit.Tools;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.service.BasicData;
using Oracle.ManagedDataAccess.Client;
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 WorkflowManager
    {
        /// <summary>
        /// 更新实验室测试委托状态，并保存文件信息
        /// </summary>
        public string[] SetTestResultDto(TestResultDto testResultDto)
        {
            var sqlHelper = new OracleSQLHelper();
            string[] msg = new string[2];
            msg[0] = "true";

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

            try
            {
                // 1. 更新 MES_QA_ITEMS_DETECT_01 状态与结果
                var sqlUpdate = @"
            UPDATE MES_QA_ITEMS_DETECT_01 
               SET SYS_STATES = :status
             WHERE ID = :mesid";

                var updateParams = new[]
                {
            new OracleParameter(":status", testResultDto.Status),
            new OracleParameter(":mesid", testResultDto.MesId)
        };

                sqlHelper.ExecuteNonQueryWithTransaction(sqlUpdate, updateParams, connection, transaction);

                // 2. 保存文件信息（TEST_RESULT_FILE） -> 先删再插
                var sqlDeleteFile = "DELETE FROM TEST_RESULT_FILE WHERE TESTRESULTID = :testResultId";
                sqlHelper.ExecuteNonQueryWithTransaction(sqlDeleteFile,
                    new[] { new OracleParameter(":testResultId", testResultDto.MesId) }, connection, transaction);

                if (testResultDto.FileList != null && testResultDto.FileList.Count > 0)
                {
                    foreach (var fileUrl in testResultDto.FileList)
                    {
                        var sqlInsertFile = @"
                    INSERT INTO TEST_RESULT_FILE (
                        ID, TESTRESULTID, FILE_URL
                    ) VALUES (
                        SEQ_TEST_RESULT_FILE.NEXTVAL, :testResultId, :fileUrl
                    )";

                        var insertParams = new[]
                        {
                    new OracleParameter(":testResultId", testResultDto.MesId),
                    new OracleParameter(":fileUrl", fileUrl)
                };

                        sqlHelper.ExecuteNonQueryWithTransaction(sqlInsertFile, insertParams, connection, transaction);
                    }
                }

                // 3. 保存测试结果（SYS_TestResults） -> 先删再插
                var sqlDeleteTestResults = "DELETE FROM SYS_TestResults WHERE TESTRESULTID = :testResultId";
                sqlHelper.ExecuteNonQueryWithTransaction(sqlDeleteTestResults,
                    new[] { new OracleParameter(":testResultId", testResultDto.MesId) }, connection, transaction);

                if (testResultDto.TestResults != null && testResultDto.TestResults.Count > 0)
                {
                    foreach (var testResult in testResultDto.TestResults)
                    {
                        var sqlTestResult = @"
                    INSERT INTO SYS_TestResults (
                        ID, TESTRESULTID, TESTRESULT
                    ) VALUES (
                        SEQ_SYS_TESTRESULTS.NEXTVAL, :testResultId, :TestResult
                    )";

                        var insertParams = new[]
                        {
                    new OracleParameter(":testResultId", testResultDto.MesId),
                    new OracleParameter(":TestResult", testResult)
                };

                        sqlHelper.ExecuteNonQueryWithTransaction(sqlTestResult, insertParams, connection, transaction);
                    }
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                msg[0] = "false";
                msg[1] = ex.Message;
                throw;
            }
            finally
            {
                sqlHelper.CloseConnection(connection);
            }

            return msg;
        }



    }
}