namespace CMSDAL.Query.ContentVersion;
public static class ContentVersionQB
{
    public const string GET_BY_CONTENT = @"
        SELECT CONTENTVERSIONID AS ContentVersionId, CONTENTID AS ContentId,
               VERSION AS Version, SNAPSHOTJSON AS SnapshotJson, NOTES AS Notes,
               CREATEDON AS CreatedOn, CREATEDBYID AS CreatedById, TENANTID AS TenantId
        FROM DBO.TCONTENTVERSION
        WHERE CONTENTID = @ContentId AND TENANTID = @TenantId
        ORDER BY VERSION DESC";

    public const string INSERT = @"
        INSERT INTO DBO.TCONTENTVERSION (CONTENTID, VERSION, SNAPSHOTJSON, NOTES, TENANTID, CREATEDBYID)
        VALUES (@ContentId, @Version, @SnapshotJson, @Notes, @TenantId, @CreatedById)";

    public const string GET_SNAPSHOT = @"
        SELECT SNAPSHOTJSON AS SnapshotJson
        FROM DBO.TCONTENTVERSION
        WHERE CONTENTVERSIONID = @ContentVersionId AND TENANTID = @TenantId";

    public const string RESTORE_TO_DRAFT = @"
        UPDATE DBO.TCONTENT
        SET    DATAJSON     = @DataJson,
               CONTENTSTATUS = 0,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETDATE()
        WHERE  CONTENTID = @ContentId AND TENANTID = @TenantId";

    public const string GET_NEXT_VERSION = @"
        SELECT ISNULL(MAX(VERSION), 0) + 1 FROM DBO.TCONTENTVERSION
        WHERE CONTENTID = @ContentId AND TENANTID = @TenantId";

    // Atomic insert used inside an ExecuteSaveAsync persistFunc — computes the next version
    // number and inserts in one statement so it is safe under the caller's own transaction
    // without a separate round trip (avoids a race between reading GET_NEXT_VERSION and the
    // insert when two transitions on the same Content run concurrently).
    public const string INSERT_WITH_NEXT_VERSION = @"
        INSERT INTO DBO.TCONTENTVERSION (CONTENTID, VERSION, SNAPSHOTJSON, NOTES, TENANTID, CREATEDBYID)
        SELECT @ContentId, ISNULL(MAX(VERSION), 0) + 1, @SnapshotJson, @Notes, @TenantId, @CreatedById
        FROM DBO.TCONTENTVERSION WITH (UPDLOCK, HOLDLOCK)
        WHERE CONTENTID = @ContentId AND TENANTID = @TenantId;
        SELECT SCOPE_IDENTITY();";
}