using System.Data;
using System.Linq;
using System.Collections.Generic;
using Neo.LegitimateLicences.Data.Models;

namespace Neo.LegitimateLicences.AppProviderIssueRequest.Models.Database
{
    public class AppProviderIssueDatabase
    {
        #region GetAppProviderIssueView
        public Vw_AppProviderIssueDetails GetAppProviderIssueView(long appProviderIssueDetailId, long userID)
        {
            var db = new LegitimateDatabase();
            var details = db.Context.Database.SqlQuery<Vw_AppProviderIssueDetails>(
                @"SELECT 
                    APID.AppProviderIssueDetailID,
                    APID.ApplicationDetailID,
                    APID.VerifiedProviderCertificateID,
                    APID.WorkflowInstanceID,
                    AD.ApplicationNumber AS ApplicationReferenceNumber,
                    ART.Title AS RequestType,
                    PP.LegalName,
                    PP.TradingName,
                    ART.Title AS ApplicationDescription, -- Use RequestType title as description (matches Accreditation pattern)
                    PC.CertificateNumber,
                    NULL AS IsStillToBeAcquired,
                    AD.ApplicationRequestTypeID,
                    PP.BusinessRegNumber,
                    PP.Email,
                    PP.Phone
                  FROM App.AppProviderIssueDetails APID
                  INNER JOIN App.ApplicationDetails AD ON AD.ApplicationDetailID = APID.ApplicationDetailID
                  INNER JOIN Md.ApplicationRequestTypes ART ON ART.ApplicationRequestTypeID = AD.ApplicationRequestTypeID
                  INNER JOIN Verified.ProviderCertificates PC ON PC.ProviderCertificateID = APID.VerifiedProviderCertificateID
                  INNER JOIN Verified.PlatformProviders PP ON PP.PlatformProviderID = PC.PlatformProviderID
                  WHERE APID.AppProviderIssueDetailID = @p0",
                new object[] { appProviderIssueDetailId }).FirstOrDefault();

            return details;
        }
        #endregion

        #region GetAppProviderIssueForm
        public AppProviderIssueDetail GetAppProviderIssueForm(long appProviderIssueDetailID, long userID)
        {
            var db = new LegitimateDatabase();
            var details = db.Context.Database.SqlQuery<AppProviderIssueDetail>(
                @"SELECT 
                    AppProviderIssueDetailID,
                    WorkflowInstanceID,
                    ApplicationDetailID,
                    VerifiedProviderCertificateID,
                    CreatedByID,
                    CreatedDate,
                    UpdatedByID,
                    UpdatedDate,
                    IsCancelled,
                    IsDocumentVerificationRequired
                  FROM App.AppProviderIssueDetails
                  WHERE AppProviderIssueDetailID = @p0",
                new object[] { appProviderIssueDetailID }).FirstOrDefault();

            return details;
        }
        #endregion

        #region GetAppProviderIssuingWorkflowSummary
        public List<Pr_Dashboard_AppProviderIssuingWorkflowSummaryGet_Result> GetAppProviderIssuingWorkflowSummary(long appProviderIssueDetailID, long userID)
        {
            var db = new LegitimateDatabase();
            return db.Context.Database.SqlQuery<Pr_Dashboard_AppProviderIssuingWorkflowSummaryGet_Result>(
                "EXEC App.Pr_Dashboard_AppProviderIssuingWorkflowSummaryGet @p0, @p1", 
                new object[] { appProviderIssueDetailID, userID }).ToList();
        }
        #endregion

        #region Save
        public void Save()
        {
            // Save logic will be implemented when Save action is created
            // This will update AppProviderIssueDetails record
        }
        #endregion
    }

    // Result class for stored procedure
    public class Pr_Dashboard_AppProviderIssuingWorkflowSummaryGet_Result
    {
        public string Title { get; set; }
        public string Status { get; set; }
        public string StatusDetail { get; set; } // For date or additional info
        public int? OrderBy { get; set; }
    }
}