using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Transactions;

using Dapper;

using Fab2ApprovalSystem.Misc;
using Fab2ApprovalSystem.Models;
using Fab2ApprovalSystem.ViewModels;

namespace Fab2ApprovalSystem.DMO;

public class MRB_DMO {

    private readonly AppSettings _AppSettings;
    private readonly WorkflowDMO wfDMO = new();
    private readonly IDbConnection db = new SqlConnection(GlobalVars.DB_CONNECTION_STRING);

    public MRB_DMO(AppSettings appSettings) =>
        _AppSettings = appSettings;

    internal MRB InsertMRB(MRB mrb) {
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", value: mrb.MRBNumber, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@OriginatorID", mrb.OriginatorID);

        db.Execute("MRBInsertItem", parameters, commandType: CommandType.StoredProcedure);
        mrb.MRBNumber = parameters.Get<int>("@MRBNumber");

        return mrb;
    }

    internal void UpdateMRB(MRB mrb) {
        DynamicParameters parameters = new();
        using (TransactionScope transanction = new()) {
            // Lot Disposition 
            parameters = new DynamicParameters();
            parameters.Add("@MRBNumber", mrb.MRBNumber);
            parameters.Add("@Title", mrb.Title);
            parameters.Add("@MRBRaisedAreaID", mrb.MRBRaisedAreaID);
            parameters.Add("@Owner", mrb.Owner);
            parameters.Add("@Tool", mrb.Tool);
            parameters.Add("@ToolCSV", mrb.ToolCSV);
            parameters.Add("@NumberOfLotsAffected", mrb.NumberOfLotsAffected);
            parameters.Add("@Process", mrb.Process);
            parameters.Add("@CANo", mrb.CANo);
            parameters.Add("@NumberOfWafersAffected", mrb.NumberOfWafersAffected);
            parameters.Add("@NumberOfDiesAffected", mrb.NumberOfDiesAffected);
            parameters.Add("@IssueDescription", mrb.IssueDescription);
            parameters.Add("@Notes", mrb.Notes);
            parameters.Add("@IssueDetectedDate", mrb.IssueDetectedDate);
            parameters.Add("@IssueStartDate", mrb.IssueStartDate);
            parameters.Add("@IssueEndDate", mrb.IssueEndDate);

            parameters.Add("@OwnerApprover", mrb.OwnerApprover);
            parameters.Add("@PEApprover", mrb.PEApprover);
            parameters.Add("@MFGEngineeringAppover", mrb.MfgEngineeringAppover);
            parameters.Add("@OtherApprovers", mrb.OtherApprovers);
            parameters.Add("@YEApprover", mrb.YEApprover);
            parameters.Add("@QualityApprovers", mrb.QualityApprovers);
            parameters.Add("@DetailsFolder", mrb.DetailsFolder);
            parameters.Add("@CustomerImpacted", mrb.CustomerImpacted);

            db.Execute("MRBUpdateItem", parameters, commandType: CommandType.StoredProcedure);

            List<int> moduleIDs = mrb.ModuleIDs;
            if (moduleIDs != null) {
                parameters = new DynamicParameters();
                parameters.Add("@MRBNumber", mrb.MRBNumber);
                db.Execute("MRBDeleteModulesByMRB", parameters, commandType: CommandType.StoredProcedure);

                foreach (int moduleID in moduleIDs) {
                    parameters = new DynamicParameters();
                    parameters.Add("@MRBNumber", mrb.MRBNumber);
                    parameters.Add("@ModuleID", moduleID);
                    db.Execute("MRBInsertModulesByMRB", parameters, commandType: CommandType.StoredProcedure);
                }
            }

            List<int> partGroupIDs = mrb.PartGroupIDs;
            if (partGroupIDs != null) {
                parameters = new DynamicParameters();
                parameters.Add("@MRBNumber", mrb.MRBNumber);
                db.Execute("MRBDeletePartGroupsAffected", parameters, commandType: CommandType.StoredProcedure);

                foreach (int partGroupID in partGroupIDs) {
                    parameters = new DynamicParameters();
                    parameters.Add("@MRBNumber", mrb.MRBNumber);
                    parameters.Add("@PartGroupID", partGroupID);
                    db.Execute("MRBInsertPartGroupsAffected", parameters, commandType: CommandType.StoredProcedure);
                }

            }

            List<int> riskAssessmentIDs = mrb.RiskAssessmentIDs;
            if (riskAssessmentIDs != null) {
                parameters = new DynamicParameters();
                parameters.Add("@MRBNumber", mrb.MRBNumber);
                db.Execute("MRBDeleteRiskAssessmentsByMRB", parameters, commandType: CommandType.StoredProcedure);

                foreach (int riskAssessmentID in riskAssessmentIDs) {
                    parameters = new DynamicParameters();
                    parameters.Add("@MRBNumber", mrb.MRBNumber);
                    parameters.Add("@RiskAssessmentID", riskAssessmentID);
                    db.Execute("MRBInsertRiskAssessmentsByMRB", parameters, commandType: CommandType.StoredProcedure);
                }

            }

            transanction.Complete();
        }
    }

    internal MRB GetMRBItem(int mrbNumber, out int isITAR, int userID) {
        // IsITAR 
        // 0 = false
        // 1 = true
        // 2 = not required
        isITAR = 2;
        MRB mrbItem = new();
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", value: mrbNumber);
        parameters.Add("@UserID", userID);
        parameters.Add("@IsITAR", value: isITAR, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

        using (var multipleResultItems = db.QueryMultiple("MRBGetItem", parameters, commandType: CommandType.StoredProcedure)) {
            mrbItem = multipleResultItems.Read<MRB>().SingleOrDefault();

            List<int> partGroupIDs = multipleResultItems.Read<int>().ToList();
            if (mrbItem != null && partGroupIDs != null) {
                mrbItem.PartGroupIDs.AddRange(partGroupIDs);
            }

            List<int> moduleIDs = multipleResultItems.Read<int>().ToList();
            if (mrbItem != null && moduleIDs != null) {
                mrbItem.ModuleIDs.AddRange(moduleIDs);
            }

            List<int> dispositionIDs = multipleResultItems.Read<int>().ToList();
            if (mrbItem != null && dispositionIDs != null) {
                mrbItem.DispositionIDs.AddRange(dispositionIDs);
            }

            List<int> riskAssessmentIDs = multipleResultItems.Read<int>().ToList();
            if (mrbItem != null && riskAssessmentIDs != null) {
                mrbItem.RiskAssessmentIDs.AddRange(riskAssessmentIDs);
            }

            isITAR = parameters.Get<int>("@IsITAR");
        }

        return mrbItem;
    }

    internal List<Module> GetModules() {
        List<Module> modules = db.Query<Module>("MRBGetModules", null, commandType: CommandType.StoredProcedure).ToList();
        return modules;
    }

    internal List<PartGroup> GetPartGroups() {
        List<PartGroup> partGroup = db.Query<PartGroup>("MRBGetPartGroups", null, commandType: CommandType.StoredProcedure).ToList();
        return partGroup;
    }

    internal List<RiskAssessment> GetRiskAssessments() {
        List<RiskAssessment> riskAssessment = db.Query<RiskAssessment>("MRBGetRiskAssessments", null, commandType: CommandType.StoredProcedure).ToList();
        return riskAssessment;
    }

    internal void InsertDisposition(Disposition model) {
        DynamicParameters parameters = new();
        parameters.Add("@DispositionID", model.DispositionID, DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@MRBNumber", model.MRBNumber);
        parameters.Add("@DispositionType", model.DispositionType);
        parameters.Add("@DispositionName", model.DispositionName);
        parameters.Add("@DispositionNotes", model.DispositionNotes);
        parameters.Add("@CloseToQDBOptionID", (model.CloseToQDBOption == null ? 0 : model.CloseToQDBOption.CloseToQDBOptionID));

        db.Execute("MRBInsertDispositionBYMRB", parameters, commandType: CommandType.StoredProcedure);

        int id = parameters.Get<int>("@DispositionID");
        model.DispositionID = id;
    }

    internal IEnumerable<Disposition> GetDispositions(int mrbNumber) {
        db.Open();
        List<Disposition> disposition = db.Query<Disposition, CloseToQDBOptionViewModel, Disposition>
         (
             "MRBGetDispositionsByMRB",
             (dispo, closeToQDBOption) => {
                 dispo.CloseToQDBOption = closeToQDBOption;
                 return dispo;
             },
             new { @MRBNumber = mrbNumber },
             commandType: CommandType.StoredProcedure,
             splitOn: "CloseToQDBOptionID"
         ).OrderBy(d => d.DispositionType).ToList();

        return disposition;
    }

    /// 

    internal void UpdateDisposition(Disposition model) {
        DynamicParameters parameters = new();
        parameters.Add("@DispositionID", model.DispositionID);
        parameters.Add("@DispositionType", model.DispositionType);
        parameters.Add("@DispositionName", model.DispositionName);
        parameters.Add("@DispositionNotes", model.DispositionNotes);
        parameters.Add("@CloseToQDBOptionID", model.CloseToQDBOption.CloseToQDBOptionID);

        db.Execute("MRBUpdateDispositionByMRB", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void UpdateLotDispoType(int mrbNumber, string lotNumber, char? dispoType) {
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", mrbNumber);
        parameters.Add("@LotNumber", lotNumber);
        parameters.Add("@DispoType", dispoType);

        db.Execute("MRBUpdateLotDispoType", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteDisposition(Disposition model) {
        DynamicParameters parameters = new();
        parameters.Add("@DispositionID", model.DispositionID);
        db.Execute("MRBDeleteDisposition", parameters, commandType: CommandType.StoredProcedure);
    }

    internal IEnumerable<Lot> GetMRBLots(int mrbNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", value: mrbNumber);

        List<Lot> data = db.Query<Lot>("MRBGetLots", parameters, commandType: CommandType.StoredProcedure).ToList();

        return data;
    }

    internal IEnumerable<MRBHoldFlagReport> GetMRBHoldFlagReport(int mrbNumber) {
        string sql = "SELECT * FROM MRBHoldFlagReport  WHERE MRBNumber = @MRBNumber ORDER BY LotNo";
        List<MRBHoldFlagReport> data = db.Query<MRBHoldFlagReport>(sql, new { MRBNumber = mrbNumber }).ToList();

        return data;
    }

    internal IEnumerable<MRBLotsTobeSentToSPN> GetMRHoldFlagSentHistory(int mrbNumber) {
        string sql = "SELECT * FROM MRBLotsToBeSentToSPN  WHERE MRBNumber = @MRBNumber ORDER BY LotNumber";
        List<MRBLotsTobeSentToSPN> data = db.Query<MRBLotsTobeSentToSPN>(sql, new { MRBNumber = mrbNumber }).ToList();

        return data;
    }

    internal IEnumerable<string> GetTools() =>
        db.Query<string>("SELECT 'NA', '1' UNION SELECT DISTINCT LTRIM(RTRIM(ToolID)),LTRIM(RTRIM(ToolID)) FROM [TEMIRWAP019].[SPNPDB].[dbo].[tblToolGroups2] ORDER BY 2").ToList();

    internal MRB GetToolIssueStartEndDateData(int mrbNumber, IDbTransaction dbTrans = null) {
        var tempMRBInfo = db.Query(
           "SELECT ToolCSV, IssueStartDate, IssueEndDate FROM MRB WHERE MRBNumber = @mrbNo",
           new { mrbNo = mrbNumber }).First();

        var tools = ((string)tempMRBInfo.ToolCSV).Split(',');
        DateTime? issueStartDate = tempMRBInfo.IssueStartDate;
        DateTime? issueEndDate = tempMRBInfo.IssueEndDate;

        if (!issueStartDate.HasValue)
            throw new Exception("MRB Issue Start Date cannot be blank");
        if (!issueEndDate.HasValue)
            throw new Exception("MRB Issue End Date cannot be blank");

        MRB mrbData = new() { MRBNumber = mrbNumber, ToolCSV = tempMRBInfo.ToolCSV, IssueStartDate = tempMRBInfo.IssueStartDate, IssueEndDate = tempMRBInfo.IssueEndDate };
        return mrbData;
    }

    /// <summary>
    /// Update the first time the LotSplit Analysis was executed
    /// This will be used while running the Lot Split Analysis again in order to get the child lots 
    /// that were not found during the first lot split analysis
    /// </summary>

    internal void UpdateLastLotSplitAnalysisTime(int mrbNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", mrbNumber);

        db.Execute("MRBUpdateLastLotSplitAnalysisTime", parameters, commandType: CommandType.StoredProcedure);
    }

    internal bool InsertLotSplitsAffectedByIncident(int mrbNumber, string incidentLotNo, string toolCSV, DateTime? issueStartDate, DateTime? issueEndDate) {
        var analysisTime = DateTime.Now;

        // get mrb info
        // search WO for MRB tools between incident start+end to find the earliest WO record for this lot and use the Out time

        DateTime? incidentTime = null;
        var tools = toolCSV.Split(',');
        if (GlobalVars.DBConnection.ToUpper() == "TEST" || GlobalVars.DBConnection.ToUpper() == "QUALITY") {
            incidentTime = db.Query<DateTime?>(
            "SELECT MIN(OutTime) FROM WO_RECORD_MJ " +
            "WHERE WO_LOT_NO = @LotNo " +
            "AND WO_STATION_ID IN @Tools " +
            "AND (InTime BETWEEN @StartDate AND @EndDate " +
            "OR OutTime BETWEEN @StartDate AND @EndDate)",
            new {
                LotNo = incidentLotNo,
                Tools = tools,
                StartDate = issueStartDate.Value,
                EndDate = issueEndDate.Value
            }).FirstOrDefault();

            if (incidentTime == null)
                return false;
        } else {
            incidentTime = db.Query<DateTime?>(
            "SELECT MIN(OutTime) FROM vFAB2SPN_WO_RECORD " +
            "WHERE WO_LOT_NO = @LotNo " +
            "AND WO_STATION_ID IN @Tools " +
            "AND (InTime BETWEEN @StartDate AND @EndDate " +
            "OR OutTime BETWEEN @StartDate AND @EndDate)",
            new {
                LotNo = incidentLotNo,
                Tools = tools,
                StartDate = issueStartDate.Value,
                EndDate = issueEndDate.Value
            }).FirstOrDefault();

            if (incidentTime == null)
                return false;
        }

        // run proc

        string parmsXML =
            new System.Xml.Linq.XElement("IssueStartDate", issueStartDate).ToString() +
            new System.Xml.Linq.XElement("IssueEndDate", issueEndDate).ToString() +
            new System.Xml.Linq.XElement("ToolCSV", toolCSV).ToString();
        IEnumerable<LotSplitAnalysisResult> analysisResults = null;

        if (GlobalVars.DBConnection.ToUpper() == "TEST" || GlobalVars.DBConnection.ToUpper() == "QUALITY") {
            analysisResults = db.Query<LotSplitAnalysisResult>(
            "AnalyzeLotAncestry_TEST",
            new {
                LotNo = incidentLotNo,
                IncidentTime = incidentTime,
                LogIssueID = mrbNumber,
                LogDocumentType = (int)GlobalVars.DocumentType.MRB,
                LogParms = parmsXML,
                AnalysisType = "A"
            },
            commandType: CommandType.StoredProcedure);
        } else {
            analysisResults = db.Query<LotSplitAnalysisResult>(
            "AnalyzeLotAncestry",
            new {
                LotNo = incidentLotNo,
                IncidentTime = incidentTime,
                LogIssueID = mrbNumber,
                LogDocumentType = (int)GlobalVars.DocumentType.MRB,
                LogParms = parmsXML,
                AnalysisType = "A"
            },
            commandType: CommandType.StoredProcedure);
        }

        var dbTrans = db.BeginTransaction();
        try {
            // insert lots
            foreach (var result in analysisResults) {
                if ((result.IsAffected.HasValue) && (result.IsAffected.Value)) {
                    if (db.Query<int>(
                        "SELECT COUNT(*) FROM MRBLot WHERE LotNumber = @LotNo AND MRBNumber = @MRBNumber",
                        new { LotNo = result.LotNo, MRBNumber = mrbNumber },
                        dbTrans).Single() == 0) {
                        bool existingRowUpdated;
                        Lot lot = new();
                        lot.MRBNumber = mrbNumber;
                        lot.LotNumber = result.LotNo;
                        lot.SourceLot = result.ParentLotNo;
                        lot.SourceAction = result.ActionType;
                        lot.SourceActionTime = result.ActionTime;

                        MiscDMO.GetLotInformation(lot);

                        if (string.Equals(lot.Status?.TrimEnd(), "Z", StringComparison.OrdinalIgnoreCase))
                            continue; // skip lots with Z status

                        InsertLot(lot, false, out existingRowUpdated, dbTrans);

                        // set special fields to show lot's source
                        db.Execute(
                            "UPDATE MRBLot " +
                            "SET LotSplitAnalysisTime = @AnalysisTime, " +
                            "  SourceLot = @SourceLot, SourceAction = @SourceAction, SourceActionTime = @SourceActionTime " +
                            "WHERE LotNumber = @LotNo AND MRBNumber = @MRBNumber",
                            new {
                                LotNo = result.LotNo,
                                MRBNumber = mrbNumber,
                                AnalysisTime = analysisTime,
                                SourceLot = result.ParentLotNo,
                                SourceAction = result.ActionType,
                                SourceActionTime = result.ActionTime
                            },
                            dbTrans);
                    }

                }
            }

            // update source lot with analysis timestamp
            db.Execute(
                "UPDATE MRBLot SET LotSplitAnalysisTime = @AnalysisTime WHERE LotNumber = @LotNo AND MRBNumber = @MRBNumber",
                new { AnalysisTime = analysisTime, LotNo = incidentLotNo, MRBNumber = mrbNumber },
                dbTrans);

            // Update the Parent Lot Numbers in table which stores the records to be sent to SPN
            db.Execute(
               "UPDATE MS SET ParentLotNumber = M.SourceLot " +
               "FROM MRBLotsToBeSentToSPN MS INNER JOIN MRBLot M " +
               "ON MS.MRBNumber = M.MRBNumber AND MS.LotNumber = M.LotNumber " +
               "WHERE MS.MRBNumber = @MRBNumber",
               new { MRBNumber = mrbNumber },
               dbTrans);

            dbTrans.Commit();
            return true;
        } catch {
            dbTrans.Rollback();
            throw;
        }
    }

    /// <returns></returns>

    internal bool IsLotSentToSPN(string lotNumber, int mrbNumber, IDbTransaction dbTrans) {
        DynamicParameters parameters = new();

        parameters.Add("@LotNumber", lotNumber);

        parameters.Add("@MRBNumber", mrbNumber);
        parameters.Add("@LotSentToSPN", dbType: DbType.Boolean, direction: ParameterDirection.Output);

        db.Execute("MRBIsLotSentToSPN", parameters, commandType: CommandType.StoredProcedure, transaction: dbTrans);
        bool lotSentToSPN = parameters.Get<bool>("@LotSentToSPN");

        return lotSentToSPN;
    }
    /// <summary>
    /// Get all Lots that are added to the MRB, to find any new splits 
    /// since the last Lot Analysis or since the last time the lot was added to MRB
    /// </summary>

    internal IEnumerable<Lot> GetLotsToFindNewChildLots(int mrbNumber) {
        DynamicParameters parameters = new();

        parameters.Add("@MRBNumber", mrbNumber);

        IEnumerable<Lot> mrbLot = db.Query<Lot>(
        "SELECT MRBNumber, LotNumber, DispoType, IsDirty FROM MRBLotsToBeSentToSPN " +
        "WHERE MRBNumber = @MRBNumber ORDER BY LotNumber",
        //"WHERE IsDirty = 1 AND MRBNumber = @MRBNumber ORDER BY LotNumber",
        //"WHERE MRBNumber = @MRBNumber AND LotNumber LIKE 'A400612%' ORDER BY LotNumber",

        new {
            MRBNumber = mrbNumber
        });

        return mrbLot;
    }

    /// <summary>
    /// This routine will get all the child lots that are not in the MRB system
    /// ======================================================================================
    /// VERY IMPORTANT NOTE IN THE NEXT LINE
    /// This routine will get the lots that were SPLIT in SPN after the "First time the lot was Analysis time"
    /// ======================================================================================
    /// This routine will then set the new Dispo Type for all these child Lots before sending it to SPN
    /// NOTE: the child lots will not be uploaded into the MRBLot table
    /// </summary>

    internal bool ChildLotsUpdateInSPNWithNewDispoType(Lot parentLot, MRB mrb) {
        var analysisTime = DateTime.Now;
        DateTime? incidentTime = null;

        // This is the  "time", when the lot was first uploaded into the system
        // Get the child lots that were split after this datetime

        incidentTime = db.Query<DateTime?>(
        "SELECT InsertDateTimeStamp FROM MRBLot " +
        "WHERE MRBNumber = @MRBNumber AND LotNumber = @LotNumber",
        new {
            MRBNumber = parentLot.MRBNumber,
            LotNumber = parentLot.LotNumber
        }).FirstOrDefault();

        if (incidentTime == null)
            return false;

        // run proc

        string parmsXML =
            new System.Xml.Linq.XElement("IssueStartDate", mrb.IssueStartDate).ToString() +
            new System.Xml.Linq.XElement("IssueEndDate", mrb.IssueEndDate).ToString() +
            new System.Xml.Linq.XElement("ToolCSV", mrb.ToolCSV).ToString();

        List<LotSplitAnalysisResult> analysisResults;
        if (GlobalVars.DBConnection.ToUpper() == "TEST" || GlobalVars.DBConnection.ToUpper() == "QUALITY") {
            analysisResults = db.Query<LotSplitAnalysisResult>(
            "AnalyzeLotAncestry_TEST",
            //"AnalyzeLotAncestry",
            new {
                LotNo = parentLot.LotNumber,
                IncidentTime = incidentTime,
                LogIssueID = parentLot.MRBNumber,
                LogDocumentType = (int)GlobalVars.DocumentType.MRB,
                LogParms = parmsXML,
                AnalysisType = "S"
            },
            commandType: CommandType.StoredProcedure).ToList();
        } else {
            analysisResults = db.Query<LotSplitAnalysisResult>(
           "AnalyzeLotAncestry",
           new {
               LotNo = parentLot.LotNumber,
               IncidentTime = incidentTime,
               LogIssueID = parentLot.MRBNumber,
               LogDocumentType = (int)GlobalVars.DocumentType.MRB,
               LogParms = parmsXML,
               AnalysisType = "S"
           },
           commandType: CommandType.StoredProcedure).ToList();
        }
        if (db.State == ConnectionState.Closed)
            db.Open();
        var dbTrans = db.BeginTransaction();

        IEnumerable<LotSplitAnalysisResult> newAnalysisResults = analysisResults.Where(r => r.IsAffected.HasValue && r.IsAffected == true).ToList();
        try {
            // insert lots
            foreach (var result in newAnalysisResults) {
                if ((result.IsAffected.HasValue) && (result.IsAffected.Value)) {
                    // do not include the lots that are present in the MRB System
                    if (
                            (db.Query<int>(
                            "SELECT COUNT(*) FROM MRBLot WHERE LotNumber = @LotNo AND MRBNumber = @MRBNumber",
                            new { LotNo = result.LotNo, MRBNumber = parentLot.MRBNumber },
                            dbTrans).Single() == 0)
                            &&
                            // check for the lots in the MRBLotsToBeSentToSPN table, 
                            // that were record from the MRBLot record table
                            (db.Query<int>(
                            "SELECT COUNT(*) FROM MRBLotsToBeSentToSPN WHERE LotNumber = @LotNo AND MRBNumber = @MRBNumber AND AddRemoveChangeMRBFlag = @AddRemoveChangeMRBFlag",
                            new { LotNo = result.LotNo, MRBNumber = parentLot.MRBNumber, AddRemoveChangeMRBFlag = 'R' },
                            dbTrans).Single() == 0)
                       ) {
                        result.ParentLotNo = result.ParentLotNo == "" ? result.LotNo : result.ParentLotNo;

                        bool existingRowUpdated;
                        Lot lot = new();
                        lot.MRBNumber = parentLot.MRBNumber;
                        lot.LotNumber = result.LotNo;
                        lot.SourceLot = result.ParentLotNo;
                        lot.SourceAction = result.ActionType;
                        lot.SourceActionTime = result.ActionTime;

                        MiscDMO.GetLotInformation(lot);

                        if (string.Equals(lot.Status?.TrimEnd(), "Z", StringComparison.OrdinalIgnoreCase))
                            continue; // skip lots with Z status

                        // check if 

                        // Insert INTo TABLE MRBLotsToBeSentToSPN
                        // Do NOT Insert into the Lot table
                        // Only insert into the MRBLotsToBeSentToSPN, which is the table that consist of Lot that needs to have an MRB Flag
                        // Lot Dispo will pick up the MRB to associate the Lot Dispo with an MRB from this table and the MRBLot table

                        InsertChildLot_NotInTheMRB(result.ParentLotNo, result.LotNo, parentLot.MRBNumber, parentLot.DispoType, parentLot.IsDirty, dbTrans);
                    }

                }
            }

            dbTrans.Commit();
            return true;
        } catch {
            dbTrans.Rollback();
            throw;
        }
    }

    internal void InsertChildLot_NotInTheMRB(string parentLotNumber, string lotNumber, int mrbNumber, char? dispoType, bool parentIsDirty, IDbTransaction dbTransaction = null) {
        DynamicParameters parameters = new();
        parameters.Add("@LotNumber", lotNumber);
        parameters.Add("@MRBNumber", mrbNumber);
        parameters.Add("@DispoType", dispoType);
        parameters.Add("@ParentIsDirty", parentIsDirty);
        parameters.Add("@ParentLotNumber", parentLotNumber);

        db.Execute("MRBInsertChildLot_NotInTheMRB", parameters, commandType: CommandType.StoredProcedure, transaction: dbTransaction);
    }

    internal bool DoesMRBLotExist(string lotNumber) {
        string tempLotNumber;
        if (lotNumber.IndexOf('/') >= 0) {
            tempLotNumber = lotNumber.Substring(0, lotNumber.IndexOf('/'));
        } else
            tempLotNumber = lotNumber;

        DynamicParameters parameters = new();
        parameters.Add("@LotNumber", tempLotNumber);
        parameters.Add("@MRBLotCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

        db.Execute("MRBLotExists", parameters, commandType: CommandType.StoredProcedure);
        int mrbLotCount = parameters.Get<int>("@MRBLotCount");

        return mrbLotCount > 0;
    }

    internal int InsertLot(Lot lot, bool getLotInfo, out bool existingRowUpdated, IDbTransaction dbTransaction = null) {
        if (getLotInfo) {
            char? dispoType = lot.DispoType;
            MiscDMO.GetLotInformation(lot);
            if (dispoType.HasValue) {
                lot.DispoType = dispoType;
            }
        }

        DynamicParameters parameters = new();
        parameters.Add("@LotID", value: lot.LotID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@LotNumber", lot.LotNumber);
        parameters.Add("@Status", lot.Status);
        parameters.Add("@DispoType", lot.DispoType);
        parameters.Add("@DieLotNumber", lot.DieLotNumber);
        parameters.Add("@MRBNumber", lot.MRBNumber);
        parameters.Add("@Description", lot.Description);
        parameters.Add("@NewPartNo", lot.NewPartNo);
        parameters.Add("@WipPartNo", lot.WipPartNo);
        parameters.Add("@DiePartNo", lot.DiePartNo);
        parameters.Add("@ProductFamily", lot.ProductFamily);
        parameters.Add("@Gen", lot.Gen);

        parameters.Add("@Channel", lot.Channel);
        parameters.Add("@Hexsize", lot.Hexsize);

        parameters.Add("@Voltage", lot.Voltage);
        parameters.Add("@WaferCount", lot.WaferCount);
        parameters.Add("@DieCount", lot.DieCount);

        parameters.Add("@Location", lot.Location);
        parameters.Add("@TotalCost", lot.TotalCost);
        parameters.Add("@QualityCode", lot.QualityCode);

        parameters.Add("@ExistingRowUpdated", dbType: DbType.Boolean, direction: ParameterDirection.Output);

        db.Execute("MRBInsertLot", parameters, commandType: CommandType.StoredProcedure, transaction: dbTransaction);

        int lotID = parameters.Get<int>("@LotID");
        existingRowUpdated = parameters.Get<bool>("@ExistingRowUpdated");

        return lotID;
    }

    internal void UpdateMRBLot(Lot lot) {
        db.Open();
        var dbTrans = db.BeginTransaction();
        try {
            DynamicParameters parameters = new();
            parameters.Add("@LotID", lot.LotID);
            parameters.Add("@LotNumber", lot.LotNumber);
            parameters.Add("@DieLotNumber", lot.DieLotNumber);
            parameters.Add("@DispoType", lot.DispoType);

            parameters.Add("@IssueID", lot.MRBNumber);
            parameters.Add("@Description", lot.Description);
            parameters.Add("@NewPartNo", lot.NewPartNo);
            parameters.Add("@WipPartNo", lot.WipPartNo);
            parameters.Add("@DiePartNo", lot.DiePartNo);
            parameters.Add("@ProductFamily", lot.ProductFamily);
            parameters.Add("@Gen", lot.Gen);

            parameters.Add("@Channel", lot.Channel);
            parameters.Add("@Hexsize", lot.Hexsize);

            parameters.Add("@Voltage", lot.Voltage);
            parameters.Add("@WaferCount", lot.WaferCount);
            parameters.Add("@DieCount", lot.DieCount);

            parameters.Add("@Location", lot.Location);
            parameters.Add("@TotalCost", lot.TotalCost);

            db.Execute("MRBUpdateLot", parameters, commandType: CommandType.StoredProcedure, transaction: dbTrans);

            // routine to cascade the "dispo type" to all the child lots in SPN (Lot that are not present in FAb App Sys,)
            // MRB mrbInfo = GetToolIssueStartEndDateData(lot.MRBNumber, dbTrans);

            dbTrans.Commit();

        } catch {
            dbTrans.Rollback();
            throw;
        }

    }

    internal void DeleteMRBLot(int lotID) {
        DynamicParameters parameters = new();
        parameters.Add("@LotID", lotID);
        db.Execute("MRBDeleteLot", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteAllMRBLot(int mrbNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", mrbNumber);
        db.Execute("MRBDeleteAllLots", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteMRB(int mrbNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", mrbNumber);
        db.Execute("MRBDelete", parameters, commandType: CommandType.StoredProcedure);
    }

    internal IEnumerable<MRBAttachment> GetMRBAttachments(int mrbNumber) {
        StringBuilder sql = new();
        sql.Append("SELECT A.AttachmentID, A.MRBNumber, A.FileName, A.UserID, CONVERT(VARCHAR(10), A.UploadDate, 101) AS UploadDate, ");
        sql.Append("U.FirstName + ' ' + U.LastName AS FullName, A.Path  ");
        sql.Append("FROM MRBAttachment A INNER JOIN Users U ON A.UserID  =  U.UserID  ");
        sql.Append("WHERE MRBNumber = @MRBNumber ");

        List<MRBAttachment> attachments = db.Query<MRBAttachment>(sql.ToString(), new { MRBNumber = mrbNumber }).ToList();
        return attachments;
    }

    public MRBAttachment GetMRBAttachment(int attachmentID) {
        StringBuilder sql = new();
        sql.Append("SELECT A.AttachmentID, A.MRBNumber, A.FileName, A.UserID, CONVERT(VARCHAR(10), A.UploadDate, 101) AS UploadDate, ");
        sql.Append("U.FirstName + ' ' + U.LastName AS FullName, A.Path  ");
        sql.Append("FROM MRBAttachment A INNER JOIN Users U ON A.UserID  =  U.UserID  ");
        sql.Append("WHERE A.AttachmentID = @AttachmentID ");

        return db.Query<MRBAttachment>(sql.ToString(), new { AttachmentID = attachmentID }).SingleOrDefault();
    }

    internal void DeleteMRBAttachment(int attachmentID) {
        DynamicParameters parameters = new();
        parameters.Add("@AttachmentID", attachmentID);
        db.Execute("MRBDeleteAttachment", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void InsertMRBAttachment(MRBAttachment attach) {
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", attach.MRBNumber);
        parameters.Add("@UserID", attach.UserID);
        parameters.Add("@FileName", attach.FileName);
        parameters.Add("@Path", attach.Path);

        db.Execute("MRBInsertAttachment", parameters, commandType: CommandType.StoredProcedure);
    }

    internal IEnumerable<ContainmentActionObj> GetContainmentActions(int mrbNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", mrbNumber);

        List<ContainmentActionObj> data = db.Query<ContainmentActionObj>("MRBGetContainmentActions", parameters, commandType: CommandType.StoredProcedure).ToList();

        return data;
    }

    internal void InsertContainmentAction(ContainmentActionObj model) {
        DynamicParameters parameters = new();
        parameters.Add("@ContainmentActionID", model.ContainmentActionID, DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@MRBNumber", model.MRBNumber);
        parameters.Add("@ContainmentAction", model.ContainmentAction);
        parameters.Add("@ResponsibilityOwnerID", model.ResponsibilityOwnerID);
        parameters.Add("@ECD", model.ECD);
        parameters.Add("@ImplementedDate", model.ImplementedDate);

        db.Execute("MRBInsertContainmentAction", parameters, commandType: CommandType.StoredProcedure);

        int id = parameters.Get<int>("@ContainmentActionID");
        model.ContainmentActionID = id;
    }

    public void UpdateContainmentAction(ContainmentActionObj model) {
        DynamicParameters parameters = new();
        parameters.Add("@ContainmentActionID", model.ContainmentActionID);
        parameters.Add("@ContainmentAction", model.ContainmentAction);
        parameters.Add("@ResponsibilityOwnerID", model.ResponsibilityOwnerID);
        parameters.Add("@ECD", model.ECD);
        parameters.Add("@ImplementedDate", model.ImplementedDate);

        db.Execute("MRBUpdateContainmentAction", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteContainmentAction(ContainmentActionObj model) {
        DynamicParameters parameters = new();
        parameters.Add("@ContainmentActionID", model.ContainmentActionID);
        db.Execute("MRBDeleteContainmentAction", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void SubmitDocument(int mrbNumber, int userID) {
        string subRoles = wfDMO.GetSubRoleItems(mrbNumber, (int)GlobalVars.DocumentType.MRB);

        // bubble the error 
        int appoverCount = 0;
        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", mrbNumber);
        parameters.Add("@UserID", userID);
        parameters.Add("@DocumentTypeID", (int)GlobalVars.DocumentType.MRB);
        parameters.Add("@SubRoleCategoriesClause", subRoles);
        parameters.Add("@AppoverCount", appoverCount, dbType: DbType.Int32, direction: ParameterDirection.Output);

        db.Execute("MRBSubmitForApproval", parameters, commandType: CommandType.StoredProcedure);

        parameters.Get<int>("@AppoverCount");
    }

    internal void CloseDocument(int mrbNumber) =>
        db.Execute("UPDATE MRB SET ApprovalStatus = " + (int)GlobalVars.ApprovalOption.Closed + ", CloseDate = GETDATE() WHERE MRBNumber = " + mrbNumber, commandType: CommandType.Text);

    internal void SetDispositionTypeForAllLots(int mrbNumber, string dispoType) =>
        db.Execute("MRBSetDispositionTypeForAllLots", new { @MRBNumber = mrbNumber, @DispoType = dispoType }, commandType: CommandType.StoredProcedure);

    internal void GetLotWaferDieCount(int mrbNumber, out int lotCount, out int waferCount, out int dieCount) {
        lotCount = 0;
        waferCount = 0;
        dieCount = 0;

        DynamicParameters parameters = new();
        parameters.Add("@MRBNumber", mrbNumber);
        parameters.Add("@LotCount", lotCount, dbType: DbType.Int32, direction: ParameterDirection.Output);
        parameters.Add("@WaferCount", waferCount, dbType: DbType.Int32, direction: ParameterDirection.Output);
        parameters.Add("@DieCount", dieCount, dbType: DbType.Int32, direction: ParameterDirection.Output);

        db.Execute("MRBGetLotWaferDieCount", parameters, commandType: CommandType.StoredProcedure);

        lotCount = parameters.Get<int>("@LotCount");
        waferCount = parameters.Get<int>("@WaferCount");
        dieCount = parameters.Get<int>("@DieCount");
    }

    internal IEnumerable<string> GetMRBHoldLots(int mrbNumber) {
        List<string> data = db.Query<string>("MRBGetMRBHoldLots", new { @MRBNumber = mrbNumber }, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal void LogHoldFlagSentToSPNHistory(int mrbNumber) =>
        db.Execute("MRBLogHoldFlagSentToSPNHistory", new { @MRBNumber = mrbNumber }, commandType: CommandType.StoredProcedure);

    internal void InsertMRB_QDB_HoldFlag(string guid, string data, string operation) {
        string[] tempData = data.Split(new char[] { '~' });

        DynamicParameters parameters = new();
        parameters.Add("@GUID", guid);
        parameters.Add("@LotNumber", tempData[0].ToString());
        parameters.Add("@MRBNumber", tempData[1].ToString());
        parameters.Add("@IncludeChildLots", tempData[2].ToUpper() == "YES" ? 1 : 0);
        parameters.Add("@Operation", operation);
        db.Execute("MRBInsertQDB_HoldFlag", parameters, commandType: CommandType.StoredProcedure);
    }

    internal IEnumerable<string> GetMRB_QDB_HoldFlags(string guid) {
        List<string> data = db.Query<string>("MRBGetMRB_QDB_HoldFlags", new { @Guid = guid }, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal void UpdateMRB_QDB_HoldFlag(string guid, bool successResult) {
        DynamicParameters parameters = new();
        parameters.Add("@GUID", guid);
        parameters.Add("@SuccessResult", successResult);
        db.Execute("MRBUpdateQDB_HoldProcessedFlag", parameters, commandType: CommandType.StoredProcedure);
    }

    public IEnumerable<CAUserList> GetUserList() {
        DynamicParameters parameters = new();
        List<CAUserList> userList = db.Query<CAUserList>("_8DGetUserList", parameters, commandType: CommandType.StoredProcedure).ToList();
        return userList;
    }

    public static string FormCSV(params object[] args) {
        bool first = true;
        StringBuilder sb = new();
        foreach (object arg in args) {
            if (!first)
                sb.Append(",");
            string s = Convert.ToString(arg);
            s = s.Replace('\r', ' ').Replace('\n', ' ').Replace('"', ' ');
            if (s.Contains(',')) {
                sb.Append("\"");
                sb.Append(s);
                sb.Append("\"");
            } else {
                sb.Append(s);
            }
            first = false;
        }
        return sb.ToString();
    }

}