-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeletedTopicsProcessing]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @deleteid  varchar(40)
    SET @deleteid = '{00000000-1FEF-0000-0000-FFD000000000}'

    -- Any documents that are no longer accessible via
    -- the outline get marked as being deleted
    UPDATE document
        SET remove_date = GETUTCDATE()
        FROM outline
        RIGHT JOIN document
        ON outline.doc_id = document.doc_id
        WHERE
            outline.doc_id IS NULL

    -- We only care about deleted topics so
    -- remove info on delete sections and
    -- outlines.
    DELETE FROM document
        WHERE remove_date IS NOT NULL AND
        doc_type  != 'Topic'


    -- Check it the table is empty.  If not add the
    -- deleted outline and deleted topics to the deleted
    -- outline.
    DECLARE @tcount int
    SELECT  @tcount = COUNT(*)
        FROM document
        WHERE remove_date IS NOT NULL

    IF (@tcount > 0)
    BEGIN
        -- Add an entry for the Deleted topics
        -- to the document table
        INSERT INTO document
            ( doc_id,
              doc_name,
              doc_type,
              create_date,
              remove_date )
             VALUES
            (
              @deleteid,
              'Deleted Topics', -- Localize me
              'Deleted',
              GETUTCDATE(),
              GETUTCDATE() )

        -- Add an entry to the outline for the
        -- Delete topics
        INSERT INTO outline
            ( order_num,
              doc_id,
              depth,
              parent_id )
             VALUES
            ( 100000000,
              @deleteid,
              0,
              NULL)


        -- Insert delete documents into the
        -- the delete section in the outline.
        INSERT INTO outline
            ( order_num,
              doc_id,
              depth,
              parent_id)


            -- The self join below is here as a creative way
            -- to generate a row number for each row being
            -- added.  The row number is used to generated
            -- a unique order number for each topic being added to the
        -- delete section. .
            -- Note that if SQL 2005 was an option as minimum DB then
            -- the RANK function could be used instead.
             SELECT  
            (COUNT(*) + 100000000) AS order_num,
            t1.doc_id,
            1 AS depth,
            @deleteid AS parent_id
             FROM document as t1
             JOIN document as t2
             ON
            t1.doc_name + t1.doc_id >= t2.doc_name + t2.doc_id

            WHERE t1.remove_date IS NOT NULL AND
             t1.doc_id != @deleteid
         
             GROUP BY t1.doc_id, t1. doc_name, t1.doc_type
    END
END
GO