namespace EnablementDAL.Query.Show;

// -----------------------------------------------------------------------------
// Admin CRUD for TSHOWCONTENTLINK (author-time). Links a Show, or a specific
// Step within it (STEPID = -1 means show-level), to CMS content, an external
// URL, a file, a video, or a document.
// -----------------------------------------------------------------------------
public static class ShowContentLinkQB
{
    public const string GET_SHOW_CONTENT_LINK = @"
        SELECT L.SHOWCONTENTLINKID AS ShowContentLinkId,
               L.SHOWID            AS ShowId,
               L.STEPID            AS StepId,
               L.SLNO              AS SlNo,
               L.LINKTYPE          AS LinkType,
               L.CONTENTID         AS ContentId,
               L.LINKURL           AS LinkUrl,
               L.LINKTITLE         AS LinkTitle,
               L.DISPLAYHINT       AS DisplayHint,
               L.ISACTIVE          AS IsActive,
               L.REMARKS           AS Remarks,
               L.VERSION           AS Version,
               L.STATUS            AS Status,
               L.SORTORDER         AS SortOrder,
               L.CREATEDBYID       AS CreatedById,
               L.CREATEDON         AS CreatedOn,
               L.MODIFIEDBYID      AS ModifiedById,
               L.MODIFIEDON        AS ModifiedOn,
               L.SOURCETYPE        AS SourceType,
               L.TENANTID          AS TenantId
        FROM   TSHOWCONTENTLINK L
        WHERE  L.SHOWCONTENTLINKID = @ShowContentLinkId
        AND    L.TENANTID          = @TenantId";

    // StepId sentinel: pass -2 to mean "all levels" (show-level + every step).
    public const string GET_SHOW_CONTENT_LINK_LIST = @"
        SELECT L.SHOWCONTENTLINKID AS ShowContentLinkId,
               L.SHOWID            AS ShowId,
               L.STEPID            AS StepId,
               L.SLNO              AS SlNo,
               L.LINKTYPE          AS LinkType,
               L.CONTENTID         AS ContentId,
               L.LINKURL           AS LinkUrl,
               L.LINKTITLE         AS LinkTitle,
               L.DISPLAYHINT       AS DisplayHint,
               L.ISACTIVE          AS IsActive,
               L.REMARKS           AS Remarks,
               L.VERSION           AS Version,
               L.STATUS            AS Status,
               L.SORTORDER         AS SortOrder,
               L.CREATEDBYID       AS CreatedById,
               L.CREATEDON         AS CreatedOn,
               L.MODIFIEDBYID      AS ModifiedById,
               L.MODIFIEDON        AS ModifiedOn,
               L.SOURCETYPE        AS SourceType,
               L.TENANTID          AS TenantId
        FROM   TSHOWCONTENTLINK L
        WHERE  L.SHOWID   = @ShowId
        AND    L.TENANTID = @TenantId
        AND    L.STATUS   <> 0
        AND    (@StepId = -2 OR L.STEPID = @StepId)
        ORDER  BY L.STEPID, L.SLNO, L.SORTORDER
        OFFSET (@PageNo - 1) * @PageSize ROWS
        FETCH  NEXT @PageSize ROWS ONLY";

    public const string GET_SHOW_CONTENT_LINK_LIST_COUNT = @"
        SELECT COUNT(1)
        FROM   TSHOWCONTENTLINK L
        WHERE  L.SHOWID   = @ShowId
        AND    L.TENANTID = @TenantId
        AND    L.STATUS   <> 0
        AND    (@StepId = -2 OR L.STEPID = @StepId)";

    // Part B, Slice 3 — the Show's optional CMS Channel source (NULL if not linked).
    public const string GET_SHOW_CMS_CHANNEL_ID = @"
        SELECT CMSCHANNELID
        FROM   TSHOW
        WHERE  SHOWID = @ShowId";

    public const string INSERT_SHOW_CONTENT_LINK = @"
        INSERT INTO TSHOWCONTENTLINK
               (SHOWCONTENTLINKID, SHOWID, STEPID, SLNO,
                LINKTYPE, CONTENTID, LINKURL, LINKTITLE, DISPLAYHINT,
                ISACTIVE, REMARKS,
                VERSION, STATUS, SORTORDER,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES (@ShowContentLinkId, @ShowId, @StepId, @SlNo,
                @LinkType, @ContentId, @LinkUrl, @LinkTitle, @DisplayHint,
                @IsActive, @Remarks,
                0, 1, @SortOrder,
                @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @SourceType, @TenantId)";

    public const string UPDATE_SHOW_CONTENT_LINK = @"
        UPDATE TSHOWCONTENTLINK
        SET    STEPID       = @StepId,
               SLNO         = @SlNo,
               LINKTYPE     = @LinkType,
               CONTENTID    = @ContentId,
               LINKURL      = @LinkUrl,
               LINKTITLE    = @LinkTitle,
               DISPLAYHINT  = @DisplayHint,
               ISACTIVE     = @IsActive,
               REMARKS      = @Remarks,
               SORTORDER    = @SortOrder,
               VERSION      = VERSION + 1,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  SHOWCONTENTLINKID = @ShowContentLinkId
        AND    TENANTID          = @TenantId";

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