using EntitlementDAL.Constants;

namespace EntitlementDAL.QueryBuilders;

// Required indexes:
//   MPROVISIONINGJOB: UX (CLIENTID) — one row per client
//   MPROVISIONINGJOB: UX (IDMSENGAGEMENTREF), filtered WHERE IDMSENGAGEMENTREF != ''
public static class ProvisioningJobQB
{
    private const string COLUMNS = @"
       PROVISIONINGJOBID AS ProvisioningJobId,
       CLIENTID          AS ClientId,
       IDMSENGAGEMENTREF AS IdmsEngagementRef,
       PROVISIONINGMODE  AS ProvisioningMode,
       TRIALMODE         AS TrialMode,
       PLANID            AS PlanId,
       DEPLOYMENTTYPE    AS DeploymentType,
       JOBSTATUS         AS JobStatus,
       CURRENTSTEP       AS CurrentStep,
       COMPLETEDSTEPS    AS CompletedSteps,
       LASTERROR         AS LastError,
       RETRYCOUNT        AS RetryCount,
       STARTEDON         AS StartedOn,
       COMPLETEDON       AS CompletedOn,
       DOCKERPACKAGEURL  AS DockerPackageUrl,
       REQUESTPAYLOAD    AS RequestPayload,
       VERSION           AS Version,
       STATUS            AS Status,
       SORTORDER         AS SortOrder,
       CREATEDBYID       AS CreatedById,
       CREATEDON         AS CreatedOn,
       MODIFIEDBYID      AS ModifiedById,
       MODIFIEDON        AS ModifiedOn,
       SOURCETYPE        AS SourceType,
       TENANTID          AS TenantId,
       SWCHANGEREQUESTID  AS SwChangeRequestId,
       SWCLIENTDATABASEID AS SwClientDatabaseId,
       PENDINGPACKAGECHAIN AS PendingPackageChain,
       LEADID              AS LeadId";

    public const string GET_BY_ID = "SELECT" + COLUMNS + @"
FROM   MPROVISIONINGJOB
WHERE  PROVISIONINGJOBID = @ProvisioningJobId";

    public const string GET_BY_CLIENT = "SELECT" + COLUMNS + @"
FROM   MPROVISIONINGJOB
WHERE  CLIENTID = @ClientId";

    public const string GET_BY_ENGAGEMENT_REF = "SELECT" + COLUMNS + @"
FROM   MPROVISIONINGJOB
WHERE  IDMSENGAGEMENTREF = @IdmsEngagementRef
  AND  IDMSENGAGEMENTREF != ''";

    // Jobs currently waiting on a SqlWorkbench ClientProvisioning ChangeRequest to reach
    // Executed — polled once per "sqlworkbench.changerequest.executed" Dapr notification
    // (never a standing timer loop) by ClientOnboardingOrchestratorBLL.ResumeAfterProvisioningExecutedAsync.
    // CURRENTSTEP literal is compile-time-concatenated from ProvisioningStepNames.DbProvisioningCrSubmitted
    // so this filter can never silently drift from the value SET_SW_LINKAGE/ProvisioningStepNames writes.
    public const string GET_AWAITING_DB_PROVISIONING = "SELECT" + COLUMNS + @"
FROM   MPROVISIONINGJOB
WHERE  JOBSTATUS = 1 -- Running
  AND  CURRENTSTEP = '" + ProvisioningStepNames.DbProvisioningCrSubmitted + @"'
  AND  SWCHANGEREQUESTID IS NOT NULL";

    // Resolves ProvisioningJobId from the SqlWorkbench ChangeRequestId it's waiting on — used by
    // ClientProvisioningProgressSubscriber to relay per-script DDL execution progress (§37
    // Decision 2) to the right job's ProvisioningHub group without SqlWorkbench itself knowing
    // anything about ProvisioningJobId.
    public const string GET_BY_SW_CHANGE_REQUEST_ID = "SELECT" + COLUMNS + @"
FROM   MPROVISIONINGJOB
WHERE  SWCHANGEREQUESTID = @SwChangeRequestId";

    public const string INSERT = @"
INSERT INTO MPROVISIONINGJOB
    (PROVISIONINGJOBID, CLIENTID, IDMSENGAGEMENTREF, PROVISIONINGMODE, TRIALMODE, PLANID, DEPLOYMENTTYPE,
     JOBSTATUS, CURRENTSTEP, COMPLETEDSTEPS, RETRYCOUNT, STARTEDON, REQUESTPAYLOAD, LEADID,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@ProvisioningJobId, @ClientId, @IdmsEngagementRef, @ProvisioningMode, @TrialMode, @PlanId, @DeploymentType,
     @JobStatus, @CurrentStep, @CompletedSteps, @RetryCount, @StartedOn, @RequestPayload, @LeadId,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    // Called while the job is in progress — advances CURRENTSTEP/COMPLETEDSTEPS, JOBSTATUS stays Running.
    public const string UPDATE_PROGRESS = @"
UPDATE MPROVISIONINGJOB SET
    JOBSTATUS      = @JobStatus,
    CURRENTSTEP    = @CurrentStep,
    COMPLETEDSTEPS = @CompletedSteps,
    MODIFIEDBYID   = @ModifiedById,
    MODIFIEDON     = @ModifiedOn
WHERE  PROVISIONINGJOBID = @ProvisioningJobId";

    // Read-modify-write target for appending a step name after the job's own scope already
    // finished (e.g. GoLiveDeclaredSubscriber recording BUNDLE_ISSUED/GOLIVE_ACTIVATED) —
    // JOBSTATUS is deliberately left untouched here.
    public const string UPDATE_COMPLETED_STEPS = @"
UPDATE MPROVISIONINGJOB SET
    COMPLETEDSTEPS = @CompletedSteps,
    MODIFIEDBYID   = @ModifiedById,
    MODIFIEDON     = @ModifiedOn
WHERE  PROVISIONINGJOBID = @ProvisioningJobId";

    public const string MARK_COMPLETED = @"
UPDATE MPROVISIONINGJOB SET
    JOBSTATUS      = @JobStatus,
    CURRENTSTEP    = @CurrentStep,
    COMPLETEDSTEPS = @CompletedSteps,
    COMPLETEDON    = @CompletedOn,
    MODIFIEDBYID   = @ModifiedById,
    MODIFIEDON     = @ModifiedOn
WHERE  PROVISIONINGJOBID = @ProvisioningJobId";

    public const string MARK_FAILED = @"
UPDATE MPROVISIONINGJOB SET
    JOBSTATUS    = @JobStatus,
    LASTERROR    = @LastError,
    RETRYCOUNT   = RETRYCOUNT + 1,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  PROVISIONINGJOBID = @ProvisioningJobId";

    // Records the CR this job is now waiting on — CURRENTSTEP moves to
    // 'DB_PROVISIONING_CR_SUBMITTED' so GET_AWAITING_DB_PROVISIONING picks it up. Also used
    // mid-chain (ClientOnboardingOrchestratorBLL's chain-advance branch) to record the NEXT
    // package's CR once the previous one reaches Executed — same shape, just called again
    // with a new SwChangeRequestId and a shrunken PendingPackageChain.
    public const string SET_SW_LINKAGE = @"
UPDATE MPROVISIONINGJOB SET
    SWCHANGEREQUESTID   = @SwChangeRequestId,
    SWCLIENTDATABASEID  = @SwClientDatabaseId,
    PENDINGPACKAGECHAIN = @PendingPackageChain,
    JOBSTATUS           = @JobStatus,
    CURRENTSTEP         = @CurrentStep,
    COMPLETEDSTEPS      = @CompletedSteps,
    MODIFIEDBYID        = @ModifiedById,
    MODIFIEDON          = @ModifiedOn
WHERE  PROVISIONINGJOBID = @ProvisioningJobId";
}