namespace EnablementDAL.Query.Show;

public static class ShowRunQB
{
    // -------------------------------------------------------------------------
    // Insert a new run record when a show is triggered for a user.
    // FIX: CURRENTSTEPID → CURRENTSHOWSTEPID; DATABASENAME removed; TENANTID added.
    // Parameters: all from ShowRunDTO properties.
    // -------------------------------------------------------------------------
    public const string INSERT_SHOW_RUN = @"
        INSERT INTO TSHOWRUN
               (SHOWRUNID, SHOWID, USERID, REQUESTID,
                RUNSTATE, STARTEDBY, CURRENTSHOWSTEPID,
                STARTEDON, TRIGGERSNAPSHOT,
                VERSION, STATUS, SORTORDER,
                CREATEDBYID, CREATEDON,
                MODIFIEDBYID, MODIFIEDON,
                SOURCETYPE, TENANTID)
        VALUES (@ShowRunId, @ShowId, @UserId, @RequestId,
                @RunState, @StartedBy, @CurrentShowStepId,
                @StartedOn, @TriggerSnapshot,
                0, 1, 9999,
                @CreatedById, GETUTCDATE(),
                @ModifiedById, GETUTCDATE(),
                @SourceType, @TenantId)";

    // -------------------------------------------------------------------------
    // Advance the current step pointer as the user navigates.
    // FIX: CURRENTSTEPID → CURRENTSHOWSTEPID; DATABASENAME → TENANTID.
    // Parameters: @CurrentShowStepId, @ModifiedById, @ShowRunId, @TenantId, @UserId
    // -------------------------------------------------------------------------
    public const string UPDATE_SHOW_RUN_STEP_ID = @"
        UPDATE TSHOWRUN
        SET    CURRENTSHOWSTEPID = @CurrentShowStepId,
               MODIFIEDBYID      = @ModifiedById,
               MODIFIEDON        = GETUTCDATE()
        WHERE  SHOWRUNID = @ShowRunId
        AND    TENANTID  = @TenantId
        AND    USERID    = @UserId";

    // -------------------------------------------------------------------------
    // Mark a run as Completed / Dismissed / Expired / Failed.
    // FIX: DATABASENAME → TENANTID.
    // Parameters: @RunState, @ModifiedById, @ShowRunId, @TenantId, @UserId
    // -------------------------------------------------------------------------
    public const string UPDATE_SHOW_RUN_STATE = @"
        UPDATE TSHOWRUN
        SET    RUNSTATE      = @RunState,
               COMPLETEDON   = GETUTCDATE(),
               MODIFIEDBYID  = @ModifiedById,
               MODIFIEDON    = GETUTCDATE()
        WHERE  SHOWRUNID = @ShowRunId
        AND    TENANTID  = @TenantId
        AND    USERID    = @UserId";

    // -------------------------------------------------------------------------
    // Insert a step-level detail row each time the user acts on a step.
    // FIX: SHOWRUNSTEPID (PK) was missing from insert list; STEPID → SHOWSTEPID.
    // Parameters: all from ShowRunStepDTO properties.
    // -------------------------------------------------------------------------
    public const string INSERT_SHOW_RUN_STEP = @"
        INSERT INTO TSHOWRUNSTEP
               (SHOWRUNSTEPID, SHOWRUNID, SLNO, SHOWSTEPID,
                STEPSTATE, ACTIONTAKEN,
                ACTIONPAYLOAD, TIMESPENTONMS, STEPPEDON)
        VALUES (@ShowRunStepId, @ShowRunId, @SlNo, @ShowStepId,
                @StepState, @ActionTaken,
                @ActionPayload, @TimeSpentOnMs, GETUTCDATE())";

    // -------------------------------------------------------------------------
    // Ownership check — confirms the run belongs to this user before any mutation.
    // FIX: DATABASENAME → TENANTID.
    // Parameters: @ShowRunId, @TenantId, @UserId
    // -------------------------------------------------------------------------
    public const string VALIDATE_SHOW_RUN_OWNER = @"
        SELECT COUNT(1)
        FROM   TSHOWRUN
        WHERE  SHOWRUNID = @ShowRunId
        AND    TENANTID  = @TenantId
        AND    USERID    = @UserId";

    // -------------------------------------------------------------------------
    // Paginated run history for a user, optionally scoped to one show.
    // FIX: CURRENTSTEPID → CURRENTSHOWSTEPID; DATABASENAME → TENANTID.
    // Parameters: @TenantId, @UserId, @ShowId, @PageNo, @PageSize
    // -------------------------------------------------------------------------
    public const string GET_SHOW_RUN_HISTORY = @"
        SELECT R.SHOWRUNID          AS ShowRunId,
               R.SHOWID             AS ShowId,
               R.USERID             AS UserId,
               R.REQUESTID          AS RequestId,
               R.RUNSTATE           AS RunState,
               R.STARTEDBY          AS StartedBy,
               R.CURRENTSHOWSTEPID  AS CurrentShowStepId,
               R.STARTEDON          AS StartedOn,
               R.COMPLETEDON        AS CompletedOn,
               R.TRIGGERSNAPSHOT    AS TriggerSnapshot,
               R.VERSION            AS Version,
               R.STATUS             AS Status,
               R.SORTORDER          AS SortOrder,
               R.CREATEDBYID        AS CreatedById,
               R.CREATEDON          AS CreatedOn,
               R.MODIFIEDBYID       AS ModifiedById,
               R.MODIFIEDON         AS ModifiedOn,
               R.SOURCETYPE         AS SourceType,
               R.TENANTID           AS TenantId
        FROM   TSHOWRUN R
        WHERE  R.TENANTID = @TenantId
        AND    R.USERID   = @UserId
        AND    (@ShowId = -1 OR R.SHOWID = @ShowId)
        ORDER  BY R.STARTEDON DESC
        OFFSET (@PageNo - 1) * @PageSize ROWS
        FETCH  NEXT @PageSize ROWS ONLY";

    // -------------------------------------------------------------------------
    // Total count for the paginated history query (same filters, no paging).
    // FIX: DATABASENAME → TENANTID.
    // Parameters: @TenantId, @UserId, @ShowId
    // -------------------------------------------------------------------------
    public const string GET_SHOW_RUN_HISTORY_COUNT = @"
        SELECT COUNT(1)
        FROM   TSHOWRUN R
        WHERE  R.TENANTID = @TenantId
        AND    R.USERID   = @UserId
        AND    (@ShowId = -1 OR R.SHOWID = @ShowId)";
}