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

using Dapper;

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

namespace Fab2ApprovalSystem.DMO;

public class ChangeControlDMO {

    private readonly IDbConnection db = new SqlConnection(GlobalVars.DB_CONNECTION_STRING);

    internal ChangeControlViewModel InsertChangeControl(ChangeControlViewModel cc) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", value: cc.PlanNumber, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@OwnerID", cc.OwnerID);

        db.Execute("CCInsertChangeControl", parameters, commandType: CommandType.StoredProcedure);
        cc.PlanNumber = parameters.Get<int>("@PlanNumber");

        return cc;
    }

    internal ChangeControlViewModel GetChangeControl(int planNumber, out int canViewITAR, int userID) {
        canViewITAR = 1;
        ChangeControlViewModel ccItem = new();
        DynamicParameters parameters = new();
        parameters.Add("@planNumber", planNumber);

        parameters.Add("@UserID", userID);
        parameters.Add("@CanViewITAR", value: canViewITAR, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

        using (var multipleResultItems = db.QueryMultiple("CCGetChangeControl", parameters, commandType: CommandType.StoredProcedure)) {
            ccItem = multipleResultItems.Read<ChangeControlViewModel>().SingleOrDefault();

            List<int> gens = multipleResultItems.Read<int>().ToList();
            List<int> logis = multipleResultItems.Read<int>().ToList();
            List<int> procs = multipleResultItems.Read<int>().ToList();
        }
        return ccItem;
    }

    internal ChangeControlViewModel GetChangeControlRead(int planNumber, out int canViewITAR, int userID) {
        canViewITAR = 1;
        ChangeControlViewModel ccItem = new();
        DynamicParameters parameters = new();
        parameters.Add("@planNumber", planNumber);

        parameters.Add("@UserID", userID);
        parameters.Add("@CanViewITAR", value: canViewITAR, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

        using (var multipleResultItems = db.QueryMultiple("CCGetChangeControlRead", parameters, commandType: CommandType.StoredProcedure)) {
            ccItem = multipleResultItems.Read<ChangeControlViewModel>().SingleOrDefault();

            List<int> gens = multipleResultItems.Read<int>().ToList();
            List<int> logis = multipleResultItems.Read<int>().ToList();
            List<int> procs = multipleResultItems.Read<int>().ToList();
            canViewITAR = parameters.Get<int>("@CanViewITAR");

        }
        return ccItem;
    }

    internal List<CCGeneration> GetGenerations() {
        DynamicParameters parameters = new();
        return db.Query<CCGeneration>("CCGetGenerations", commandType: CommandType.StoredProcedure).ToList();
    }

    internal List<CCLogistics> GetLogistics() {
        DynamicParameters parameters = new();

        return db.Query<CCLogistics>("CCGetLogistics", commandType: CommandType.StoredProcedure).ToList();
    }

    internal List<CCProcess> GetProcesses() {
        DynamicParameters parameters = new();
        return db.Query<CCProcess>("CCGetProcesses", commandType: CommandType.StoredProcedure).ToList();
    }

    internal List<CCPartNumber> GetPartNumbers() {
        DynamicParameters parameters = new();
        return db.Query<CCPartNumber>("CCGetPartNumbers", commandType: CommandType.StoredProcedure).ToList();
    }

    internal int UpdateChangeControl(ChangeControlViewModel model, int userID) {
        int result = 0;
        DynamicParameters parameters = new();
        using (TransactionScope transaction = new()) {
            try {
                parameters.Add("@PlanNumber", model.PlanNumber);
                parameters.Add("@ChangeLevel", model.ChangeLevel);
                parameters.Add("@IsITAR", model.IsITAR);
                parameters.Add("@IsMedical", model.IsMedical);
                parameters.Add("@IsRadHard", model.IsRadHard);
                parameters.Add("@Notes", model.Notes);
                parameters.Add("@IsAutomotive", model.IsAutomotive);
                parameters.Add("@Title", model.Title);
                parameters.Add("@ReasonForChange", model.ReasonForChange);
                parameters.Add("@ChangeDescription", model.ChangeDescription);
                db.Execute("CCUpdateChangeControl", param: parameters, commandType: CommandType.StoredProcedure);

                transaction.Complete();
            } catch (Exception ex) {
                transaction.Dispose();
                throw new Exception(ex.Message + " " + ex.InnerException);
            }
        }
        return result;
    }

    public IEnumerable<MeetingDecisionSummaryList> GetMeetingDecisionSummaryList(int planNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", planNumber);
        List<MeetingDecisionSummaryList> data = db.Query<MeetingDecisionSummaryList>("CCGetMeetingDecisionSummaryList", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    public IEnumerable<CCAttachment> GetCCAttachment(int planNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", planNumber);
        List<CCAttachment> data = db.Query<CCAttachment>("CCGetCCAttachments", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal CCAttachment InsertCCAttachment(CCAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", value: attachment.ID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@PlanNumber", attachment.PlanNumber);
        parameters.Add("@Title", attachment.Title);
        parameters.Add("@RequirementsNotes", attachment.RequirementsNotes);
        db.Execute("CCInsertCCAttachment", parameters, commandType: CommandType.StoredProcedure);
        attachment.ID = parameters.Get<int>("@ID");

        return attachment;
    }

    internal void UpdateCCAttachmentDocument(CCAttachment attachment) {
        DynamicParameters parameters = new();

        parameters.Add("@AttachmentID", attachment.ID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@FileGUID", attachment.FileGUID);

        parameters.Add("@UploadedByID", attachment.UploadedByID);

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

    internal void UpdateCCAttachemnt(CCAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", attachment.ID);
        parameters.Add("@Title", attachment.Title);
        parameters.Add("@RequirementsNotes", attachment.RequirementsNotes);
        db.Execute("CCUpdateCCAttachment", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteCCAttachemnt(CCAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", attachment.ID);

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

    internal string GetCCFileName(string fileGUID) {
        DynamicParameters parameters = new();
        parameters.Add("@FileGUID", fileGUID);
        var fileName = db.Query<string>("CCGetCCFileName", parameters, commandType: CommandType.StoredProcedure).Single();
        return fileName;
    }

    internal int InsertMeeting(int planNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", planNumber);
        parameters.Add("@MeetingID", value: 0, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

        db.Execute("CCInsertMeeting", parameters, commandType: CommandType.StoredProcedure);
        int meetingID = parameters.Get<int>("@MeetingID");

        return meetingID;
    }

    internal CCMeeting GetMeeting(int meetingID, out int canViewITAR, int userID) {
        canViewITAR = 1;
        CCMeeting ccMeeting = new();
        DynamicParameters parameters = new();
        parameters.Add("@MeetingID", meetingID);
        parameters.Add("@UserID", userID);
        parameters.Add("@CanViewITAR", value: canViewITAR, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

        using (var multipleResultItems = db.QueryMultiple("CCGetMeetingRead", parameters, commandType: CommandType.StoredProcedure)) {
            ccMeeting = multipleResultItems.Read<CCMeeting>().SingleOrDefault();
            List<string> pcrvalues = multipleResultItems.Read<string>().ToList();
            if (ccMeeting != null && pcrvalues != null) {
                if (pcrvalues.Count > 0)
                    ccMeeting.PCRValueIDs.AddRange(pcrvalues);
            }
        }

        return ccMeeting;
    }

    internal CCMeeting GetMeetingRead(int meetingID, out int canViewITAR, int userID) {
        canViewITAR = 1;
        CCMeeting ccMeeting = new();
        DynamicParameters parameters = new();
        parameters.Add("@MeetingID", meetingID);
        parameters.Add("@UserID", userID);
        parameters.Add("@CanViewITAR", value: canViewITAR, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

        using (var multipleResultItems = db.QueryMultiple("CCGetMeetingRead", parameters, commandType: CommandType.StoredProcedure)) {
            ccMeeting = multipleResultItems.Read<CCMeeting>().SingleOrDefault();
            List<string> pcrvalues = multipleResultItems.Read<string>().ToList();
            if (ccMeeting != null && pcrvalues != null) {
                if (pcrvalues.Count > 0)
                    ccMeeting.PCRValueIDs.AddRange(pcrvalues);
            }
        }

        return ccMeeting;
    }

    internal IEnumerable<CCPCRB> GetPCRB(int PlanNumber, string PCRB) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", PlanNumber);
        parameters.Add("@PCRB", PCRB);
        List<CCPCRB> data = db.Query<CCPCRB>("CCGetPCRB", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal int UpdateMeeting(CCMeeting meeting) {
        int result = 0;
        DynamicParameters parameters = new();
        parameters.Add("@MeetingID", meeting.MeetingID);
        parameters.Add("@Decision", meeting.Decision);
        parameters.Add("@MeetingDate", meeting.MeetingDate);
        parameters.Add("@Notes", meeting.Notes);

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

        parameters = new DynamicParameters();
        parameters.Add("@MeetingID", meeting.MeetingID);
        db.Execute("CCDeleteMeetingPCRValues", parameters, commandType: CommandType.StoredProcedure);
        List<string> pcrValues = meeting.PCRValueIDs;
        if (pcrValues != null) {
            foreach (string pcrValue in pcrValues) {
                parameters = new DynamicParameters();
                parameters.Add("@MeetingID", meeting.MeetingID);
                parameters.Add("@PCRValue", pcrValue);
                db.Execute("CCInsertMeetingPCRValues", parameters, commandType: CommandType.StoredProcedure);
            }
        }

        return result;
    }

    public IEnumerable<CCMeetingAttachment> GetMeetingAttachments(int meetingID) {
        DynamicParameters parameters = new();
        parameters.Add("@MeetingID", meetingID);
        List<CCMeetingAttachment> data = db.Query<CCMeetingAttachment>("CCGetMeetingAttachments", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal CCMeetingAttachment InsertMeetingAttachmentAttrib(CCMeetingAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", value: attachment.ID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@MeetingID", attachment.MeetingID);
        parameters.Add("@Title", attachment.Title);
        db.Execute("CCInsertMeetingAttachmentAttrib", parameters, commandType: CommandType.StoredProcedure);
        attachment.ID = parameters.Get<int>("@ID");
        return attachment;
    }

    internal void UpdateMeetingAttachmentAttrib(CCMeetingAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", attachment.ID);
        parameters.Add("@Title", attachment.Title);

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

    internal void UpdateMeetingAttachmentDocument(CCMeetingAttachment attachment) {
        DynamicParameters parameters = new();

        parameters.Add("@AttachmentID", attachment.ID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@FileGUID", attachment.FileGUID);

        parameters.Add("@UploadedByID", attachment.UploadedByID);

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

    internal void DeleteMeetingAttachemnt(CCMeetingAttachment attachment) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", attachment.ID);

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

    internal string GetMeetingFileName(string fileGUID) {
        DynamicParameters parameters = new();
        parameters.Add("@FileGUID", fileGUID);
        var fileName = db.Query<string>("CCGetMeetingFileName", parameters, commandType: CommandType.StoredProcedure).Single();
        return fileName;
    }

    internal CCDecisionSummary InsertDecisionSummary(CCDecisionSummary decision) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", value: decision.ID, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
        parameters.Add("@MeetingID", decision.MeetingID);
        parameters.Add("@DecisionNotes", decision.DecisionNotes);
        db.Execute("CCInsertDecisionSummary", parameters, commandType: CommandType.StoredProcedure);

        decision.ID = parameters.Get<int>("@ID");
        return decision;
    }

    internal void UpdateDecisionSummary(CCDecisionSummary decision) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", decision.ID);
        parameters.Add("@DecisionNotes", decision.DecisionNotes);

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

    internal void DeleteDecisionSummary(CCDecisionSummary decision) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", decision.ID);

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

    internal void UpdateDecisionSummaryLinks(int id, string ecnLinks, string ldLinks) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", id);
        parameters.Add("@ECNLinks", ecnLinks);
        parameters.Add("@LotDispoLinks", ldLinks);
        db.Execute("CCUpdateDecisionSummaryLinks", parameters, commandType: CommandType.StoredProcedure);
    }

    internal IEnumerable<CCDecisionSummary> GetDecisionsSummaryList(int meetingID) {
        DynamicParameters parameters = new();
        parameters.Add("@MeetingID", meetingID);
        List<CCDecisionSummary> data = db.Query<CCDecisionSummary>("CCGetDecisionsSummaryList", parameters, commandType: CommandType.StoredProcedure).ToList();
        return data;
    }

    internal IEnumerable<CCMeetingAttendee> GetMeetingAttendees(int meetingID) {
        DynamicParameters parameters = new();
        parameters.Add("@MeetingID", meetingID);

        List<CCMeetingAttendee> meetingAttendee = db.Query<CCMeetingAttendee>("CCGetMeetingAttendees", parameters, commandType: CommandType.StoredProcedure).ToList();

        return meetingAttendee;
    }

    internal IEnumerable<CCMeetingAttendee> GetPCRBAttendees(int PCRBID) {
        DynamicParameters parameters = new();
        parameters.Add("@PCRBID", PCRBID);

        List<CCMeetingAttendee> pcrbAttendee = db.Query<CCMeetingAttendee>("CCGetPCRBAttendees", parameters, commandType: CommandType.StoredProcedure).ToList();

        return pcrbAttendee;
    }

    internal void InsertMeetingAttendee(CCPCRBAttendee meetingAttendee) {
        DynamicParameters parameters = new();
        parameters.Add("@MeetingID", meetingAttendee.PCRBID);
        parameters.Add("@AttendeeID", meetingAttendee.AttendeeID);
        parameters.Add("@JobTitle", meetingAttendee.JobTitle);
        parameters.Add("@Location", meetingAttendee.Location);
        db.Execute("CCInsertMeetingAttendee", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void InsertNewMeetingAttendee(int meetingId, string attendeeName, string jobTitle, string siteName) {
        DynamicParameters parameters = new();
        parameters.Add("@meetingId", meetingId);
        parameters.Add("@AttendeeName", attendeeName);
        parameters.Add("@JobTitle", jobTitle);
        parameters.Add("@Site", siteName);
        db.Execute("CCInsertNewMeetingAttendee", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void UpdateMeetingAttendee(int id, string attendeeName, string jobTitle, string siteName) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", id);
        parameters.Add("@AttendeeName", attendeeName);
        parameters.Add("@JobTitle", jobTitle);
        parameters.Add("@Site", siteName);
        db.Execute("CCUpdateMeetingAttendee", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void UpdatePCRBAttendee(int id, int attendeeID, string jobTitle, string siteName) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", id);
        parameters.Add("@AttendeeID", attendeeID);
        parameters.Add("@JobTitle", jobTitle);
        parameters.Add("@Site", siteName);
        db.Execute("CCUpdatePCRBAttendee", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteMeetingAttendee(CCMeetingAttendee meetingAttendee) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", meetingAttendee.ID);
        db.Execute("CCDeleteMeetingAttendee", parameters, commandType: CommandType.StoredProcedure);
    }

    internal IEnumerable<CCMeetingActionItem> GetMeetingActionItems(int meetingID) {
        DynamicParameters parameters = new();
        parameters.Add("@MeetingID", meetingID);

        List<CCMeetingActionItem> meetingAttendee = db.Query<CCMeetingActionItem>("CCGetMeetingActionItems", parameters, commandType: CommandType.StoredProcedure).ToList();

        return meetingAttendee;
    }

    internal IEnumerable<CCMeetingActionItem> GetPCRBActionItems(int pcrbID) {
        DynamicParameters parameters = new();
        parameters.Add("@PCRBID", pcrbID);

        List<CCMeetingActionItem> actionItem = db.Query<CCMeetingActionItem>("CCGetPCRBActionItems", parameters, commandType: CommandType.StoredProcedure).ToList();

        return actionItem;
    }

    internal IEnumerable<CCMeetingActionItemAll> GetMeetingActionItems_All(int planNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", planNumber);
        List<CCMeetingActionItemAll> meetingAttendee = db.Query<CCMeetingActionItemAll>("CCGetMeetingActionItems_All", parameters, commandType: CommandType.StoredProcedure).ToList();

        return meetingAttendee;
    }

    internal void UpdateMeetingActionItem_All(CCMeetingActionItemAll meetingActionItem) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", meetingActionItem.ID);
        parameters.Add("@Updates", meetingActionItem.Updates);
        parameters.Add("@ClosedStatus", meetingActionItem.ClosedStatus);
        parameters.Add("@DueDate", meetingActionItem.DueDate);
        parameters.Add("@UserID", meetingActionItem.ClosedBy);
        db.Execute("CCUpdateMeetingActionItem_All", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void InsertMeetingActionItem(CCMeetingActionItem meetingActionItem) {
        DynamicParameters parameters = new();
        parameters.Add("@MeetingID", meetingActionItem.MeetingID);
        parameters.Add("@ActionItemName", meetingActionItem.ActionItemName);
        parameters.Add("@ResponsibleID", meetingActionItem.ResponsibleID);
        parameters.Add("@Gating", meetingActionItem.Gating);
        parameters.Add("@DueDate", meetingActionItem.DueDate);
        db.Execute("CCInsertMeetingActionItem", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void InsertPCRBAttendee(CCPCRBAttendee meetingAttendee) {
        DynamicParameters parameters = new();
        parameters.Add("@PCRBID", meetingAttendee.PCRBID);
        parameters.Add("@AttendeeID", meetingAttendee.AttendeeID);
        parameters.Add("@JobTitle", meetingAttendee.JobTitle);
        parameters.Add("@Location", meetingAttendee.Location);
        db.Execute("CCAddPCRBAttendees", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void InsertPCRBActionItem(CCPCRBActionItem pcrbActionItem) {
        DynamicParameters parameters = new();
        parameters.Add("@PCRBID", pcrbActionItem.PCRBID);
        parameters.Add("@ActionItemName", pcrbActionItem.ActionItemName);
        parameters.Add("@ResponsibleID", pcrbActionItem.ResponsibleID);
        parameters.Add("@Gating", pcrbActionItem.Gating);
        parameters.Add("@DueDate", pcrbActionItem.DueDate);
        db.Execute("CCInsertPCRBActionItem", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void UpdateMeetingActionItem(CCMeetingActionItem meetingActionItem) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", meetingActionItem.ID);
        parameters.Add("@ActionItemName", meetingActionItem.ActionItemName);
        parameters.Add("@ResponsibleID", meetingActionItem.ResponsibleID);
        parameters.Add("@Gating", meetingActionItem.Gating);
        parameters.Add("@DueDate", meetingActionItem.DueDate);
        db.Execute("CCUpdateMeetingActionItem", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void UpdatePCRBActionItem(CCPCRBActionItem meetingActionItem) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", meetingActionItem.ID);
        parameters.Add("@ActionItemName", meetingActionItem.ActionItemName);
        parameters.Add("@ResponsibleID", meetingActionItem.ResponsibleID);
        parameters.Add("@Gating", meetingActionItem.Gating);
        parameters.Add("@DueDate", meetingActionItem.DueDate);
        db.Execute("CCUpdatePCRBActionItem", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void ReassignMeetingActionItemResponsiblePersons(int meetingActionItemId, string newRPIds, string comments, int userId) {
        DynamicParameters parameters = new();
        parameters.Add("@MeetingActionItemID", meetingActionItemId);
        parameters.Add("@ResponsibleID", newRPIds);
        parameters.Add("@Comments", comments);
        parameters.Add("@UserID", userId);
        db.Execute("CCUpdateMeetingActionItemRespPersons", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeleteMeetingActionItem(CCMeetingActionItem meetingAttendee) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", meetingAttendee.ID);
        db.Execute("CCDeleteMeetingActionItem", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void DeletePCRBActionItem(CCPCRBActionItem pcrbActionItem) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", pcrbActionItem.ID);
        db.Execute("CCDeletePCRBActionItem", parameters, commandType: CommandType.StoredProcedure);
    }

    public IEnumerable<CCMeeting> GetMeetingList(int planNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", planNumber);

        List<CCMeeting> meetingList = db.Query<CCMeeting>("CCGetMeetingList", parameters, commandType: CommandType.StoredProcedure).ToList();

        foreach (var meeting in meetingList) {
            CCMeeting meetingToParse = GetMeeting(meeting.MeetingID, out int canViewITAR, 0);
            meeting.PCRValueIDs = meetingToParse.PCRValueIDs;

        }
        return meetingList;
    }

    internal void CompleteCC(int planNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", planNumber);
        db.Execute("CCCompleteCC", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void CancelCC(int planNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", planNumber);
        db.Execute("CCCancelCC", parameters, commandType: CommandType.StoredProcedure);
    }

    internal void UpdateActionItemAttachment(CCMeetingActionItemAll attachment) {
        DynamicParameters parameters = new();

        parameters.Add("@ID", attachment.ID);
        parameters.Add("@FileName", attachment.FileName);
        parameters.Add("@FileGUID", attachment.FileGUID);

        parameters.Add("@UploadedByID", attachment.UploadedByID);

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

    internal string GetActionItemFileName(string fileGUID) {
        DynamicParameters parameters = new();
        parameters.Add("@FileGUID", fileGUID);
        var fileName = db.Query<string>("CCGetActionItemFileName", parameters, commandType: CommandType.StoredProcedure).Single();
        return fileName;
    }

    internal List<UserList> GetUsers() {
        DynamicParameters parameters = new();
        return db.Query<UserList>("CCGetUsers", commandType: CommandType.StoredProcedure).ToList();
    }

    internal List<ActionItemResponsible> GetActionItemResponsible() {
        DynamicParameters parameters = new();
        return db.Query<ActionItemResponsible>("CCGetActionItemResponsible", commandType: CommandType.StoredProcedure).ToList();
    }

    internal List<SiteList> GetSites() {
        DynamicParameters parameters = new();
        return db.Query<SiteList>("CCGetSites", commandType: CommandType.StoredProcedure).ToList();
    }

    internal List<PCRValue> GetPCRValues() {
        DynamicParameters parameters = new();
        return db.Query<PCRValue>("CCGetPCRValues", commandType: CommandType.StoredProcedure).ToList();
    }

    internal void UpdateMeetingNotes(int meetingID, string notes) {
        DynamicParameters parameters = new();
        parameters.Add("@ID", meetingID);
        parameters.Add("@Notes", notes);

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

    internal void ReleaseLockOnDocument(int userID, int planNumber) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", planNumber);
        parameters.Add("@UserID", userID);
        db.Execute("CCReleaseLockOnCCDocuments", parameters, commandType: CommandType.StoredProcedure);
    }

    public void ReassignOwner(int planNumber, int newOwnerID, string comments, int userID) {
        DynamicParameters parameters = new();
        parameters.Add("@PlanNumber", planNumber);
        parameters.Add("@NewOwnerID", newOwnerID);
        parameters.Add("@Comments", comments);
        parameters.Add("@UserID", userID);

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

}