namespace EnablementDAL.Query.Show;

// -----------------------------------------------------------------------------
// Admin CRUD for MSHOWSPACE (author-time). "Space" is the top-level Knowledge
// Space category every Show belongs to (see TSHOW.SPACEID FK).
// -----------------------------------------------------------------------------
public static class ShowSpaceQB
{
    public const string GET_SHOW_SPACE = @"
        SELECT S.SPACEID       AS SpaceId,
               S.SPACECODE     AS SpaceCode,
               S.SPACENAME     AS SpaceName,
               S.DESCRIPTION   AS Description,
               S.OWNERTEAM     AS OwnerTeam,
               S.ICON          AS Icon,
               S.COLORHEX      AS ColorHex,
               S.SUBSCRIPTIONTYPE  AS SubscriptionType,
               S.AUTOSUBSCRIBERULE AS AutoSubscribeRule,
               S.ISACTIVE      AS IsActive,
               S.VERSION       AS Version,
               S.STATUS        AS Status,
               S.SORTORDER     AS SortOrder,
               S.CREATEDBYID   AS CreatedById,
               S.CREATEDON     AS CreatedOn,
               S.MODIFIEDBYID  AS ModifiedById,
               S.MODIFIEDON    AS ModifiedOn,
               S.SOURCETYPE    AS SourceType,
               S.TENANTID      AS TenantId
        FROM   MSHOWSPACE S
        WHERE  S.SPACEID  = @SpaceId
        AND    S.TENANTID = @TenantId";

    public const string GET_SHOW_SPACE_LIST = @"
        SELECT S.SPACEID       AS SpaceId,
               S.SPACECODE     AS SpaceCode,
               S.SPACENAME     AS SpaceName,
               S.DESCRIPTION   AS Description,
               S.OWNERTEAM     AS OwnerTeam,
               S.ICON          AS Icon,
               S.COLORHEX      AS ColorHex,
               S.SUBSCRIPTIONTYPE  AS SubscriptionType,
               S.AUTOSUBSCRIBERULE AS AutoSubscribeRule,
               S.ISACTIVE      AS IsActive,
               S.VERSION       AS Version,
               S.STATUS        AS Status,
               S.SORTORDER     AS SortOrder,
               S.CREATEDBYID   AS CreatedById,
               S.CREATEDON     AS CreatedOn,
               S.MODIFIEDBYID  AS ModifiedById,
               S.MODIFIEDON    AS ModifiedOn,
               S.SOURCETYPE    AS SourceType,
               S.TENANTID      AS TenantId
        FROM   MSHOWSPACE S
        WHERE  S.TENANTID = @TenantId
        AND    S.STATUS   <> 0
        ORDER  BY S.SORTORDER
        OFFSET (@PageNo - 1) * @PageSize ROWS
        FETCH  NEXT @PageSize ROWS ONLY";

    public const string GET_SHOW_SPACE_LIST_COUNT = @"
        SELECT COUNT(1)
        FROM   MSHOWSPACE S
        WHERE  S.TENANTID = @TenantId
        AND    S.STATUS   <> 0";

    public const string INSERT_SHOW_SPACE = @"
        INSERT INTO MSHOWSPACE
               (SPACEID, SPACECODE, SPACENAME, DESCRIPTION, OWNERTEAM, ICON, COLORHEX,
                SUBSCRIPTIONTYPE, AUTOSUBSCRIBERULE,
                ISACTIVE,
                VERSION, STATUS, SORTORDER,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES (@SpaceId, @SpaceCode, @SpaceName, @Description, @OwnerTeam, @Icon, @ColorHex,
                @SubscriptionType, @AutoSubscribeRule,
                @IsActive,
                0, 1, @SortOrder,
                @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @SourceType, @TenantId)";

    public const string UPDATE_SHOW_SPACE = @"
        UPDATE MSHOWSPACE
        SET    SPACECODE    = @SpaceCode,
               SPACENAME    = @SpaceName,
               DESCRIPTION  = @Description,
               OWNERTEAM    = @OwnerTeam,
               ICON         = @Icon,
               COLORHEX     = @ColorHex,
               SUBSCRIPTIONTYPE  = @SubscriptionType,
               AUTOSUBSCRIBERULE = @AutoSubscribeRule,
               ISACTIVE     = @IsActive,
               SORTORDER    = @SortOrder,
               VERSION      = VERSION + 1,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  SPACEID  = @SpaceId
        AND    TENANTID = @TenantId";

    // Soft delete — same convention as TSHOWRULE/TSHOWSCHEDULE/TSHOWCONTENTLINK (STATUS = 0).
    public const string DELETE_SHOW_SPACE = @"
        UPDATE MSHOWSPACE
        SET    STATUS       = 0,
               ISACTIVE     = 0,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  SPACEID  = @SpaceId
        AND    TENANTID = @TenantId";

    // Lightweight picklist for dropdowns (e.g. the Show authoring form's Space picker).
    public const string GET_SELECTLIST_SHOW_SPACE = @"
        SELECT S.SPACEID   AS SpaceId,
               S.SPACECODE AS SpaceCode,
               S.SPACENAME AS SpaceName
        FROM   MSHOWSPACE S
        WHERE  S.ISACTIVE = 1
        AND    S.STATUS   <> 0
        AND    S.TENANTID = @TenantId
        ORDER  BY S.SORTORDER";
}