Uninstalling Meet

/*
    Meet for Rock RMS: uninstall clean-up script

    The Rock Shop has no uninstall button, and Rock doesn't run any plugin
    code when a plugin's files are removed. This script removes what Meet
    added to your Rock database, so nothing is left behind.

    WHAT IT REMOVES
      - The Meet page and every page under it, their routes and blocks
      - Meet's block types, including Meet Dashboard blocks you placed on other
        pages and the setup prompt in the internal site's footer
      - The Meet Staff and Meet Administrators security roles and their members
      - The six "Meet:" jobs and their history
      - The Meet Recording file type and any audio still waiting
      - The Meet Notification email template (emails already sent stay in
        Communication History)
      - Meet's entity types, dashboard-layout preferences and migration history

    WHAT IT KEEPS, UNLESS YOU ASK
      Meet's data: the staff list, check-ins, reviews, goals, to-dos, meetings,
      feedback and Meet's settings (including encrypted AI keys). Kept, so a
      reinstall picks up where you left off. Set @RemoveData = 1 below to delete
      all of it as well. That can't be undone except from a backup.

    WHAT IT NEVER TOUCHES
      Rock's people, groups, other pages, other security roles, other jobs,
      settings, and Communication History. Every item is found by Meet's own
      fixed GUIDs or by its com.fivepoint.Meet names, never by a plain name a
      church could have used itself. (One exception: the "Meet" category for
      system communications is removed by name, and only if it's empty.)

    HOW TO RUN IT
      1. Back up the Rock database, and copy this script somewhere outside
         RockWeb (step 2 deletes the folder it's installed in).
      2. Remove the plugin's files first: delete
         RockWeb\Bin\com.fivepoint.Meet.dll and the folder
         RockWeb\Plugins\com_fivepoint\Meet. (If Meet's DLL is still there
         after this script runs, Rock re-installs Meet on its next restart.)
      3. Run this script as it is, in SQL Server Management Studio or in
         Admin Tools > Power Tools > SQL Command with "Selection Query" set to
         Yes. It's a dry run: it does the work, lists everything it would
         remove, then undoes it all. Check the list.
      4. Change @Commit to 1 (and @RemoveData to 1 if you want the data gone
         too) and run it again.

    TO REINSTALL FROM THE ROCK SHOP LATER
      Rock remembers Shop installs in RockWeb\App_Data\InstalledStorePackages.json,
      and this script can't change files. Remove Meet's entry from that file,
      or the Shop treats Meet as still installed and installs nothing.

    Any error undoes everything, so it can never half-finish.
*/

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @Commit bit = 0;       -- 1 = keep the changes. 0 = dry run.
DECLARE @RemoveData bit = 0;   -- 1 = also delete all of Meet's data and settings.

-- Table variables survive a rollback, so the report below still shows what a
-- dry run would have removed.
DECLARE @Report TABLE ( [Order] int IDENTITY, [Item] nvarchar(60), [Name] nvarchar(400) );

BEGIN TRANSACTION;
BEGIN TRY

    DECLARE @PageEntityTypeId int = ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Page' );
    DECLARE @BlockEntityTypeId int = ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Block' );
    DECLARE @SystemCommunicationEntityTypeId int = ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.SystemCommunication' );

    /* ---------- Find Meet's items ---------- */

    -- The Meet page (by its fixed GUID) and every page under it.
    DECLARE @Pages TABLE ( [Id] int PRIMARY KEY );
    WITH Tree AS (
        SELECT [Id] FROM [Page] WHERE [Guid] = '76E00C35-8B4F-46E1-89D2-C850F2D02100'
        UNION ALL
        SELECT p.[Id] FROM [Page] p JOIN Tree t ON p.[ParentPageId] = t.[Id]
    )
    INSERT INTO @Pages SELECT DISTINCT [Id] FROM Tree;

    DECLARE @EntityTypes TABLE ( [Id] int PRIMARY KEY );
    INSERT INTO @EntityTypes SELECT [Id] FROM [EntityType] WHERE [Name] LIKE 'com.fivepoint.Meet.%';

    DECLARE @BlockTypes TABLE ( [Id] int PRIMARY KEY );
    INSERT INTO @BlockTypes SELECT [Id] FROM [BlockType] WHERE [EntityTypeId] IN ( SELECT [Id] FROM @EntityTypes );

    -- Blocks on Meet's pages, plus every Meet block anywhere else (a Meet
    -- Dashboard on the home page, the setup prompt in the footer).
    DECLARE @Blocks TABLE ( [Id] int PRIMARY KEY );
    INSERT INTO @Blocks SELECT [Id] FROM [Block]
    WHERE [PageId] IN ( SELECT [Id] FROM @Pages ) OR [BlockTypeId] IN ( SELECT [Id] FROM @BlockTypes );

    DECLARE @Roles TABLE ( [Id] int PRIMARY KEY );
    INSERT INTO @Roles SELECT [Id] FROM [Group]
    WHERE [Guid] IN ( 'B2DCD822-D96E-40E0-B2EB-B1C1E940D7B5', 'A48D3714-718D-43E4-95B2-766215CDFB58' );

    DECLARE @Jobs TABLE ( [Id] int PRIMARY KEY );
    INSERT INTO @Jobs SELECT [Id] FROM [ServiceJob] WHERE [Class] LIKE 'com.fivepoint.Meet.%';

    DECLARE @FileTypes TABLE ( [Id] int PRIMARY KEY );
    INSERT INTO @FileTypes SELECT [Id] FROM [BinaryFileType] WHERE [Guid] = 'FAC7F190-B53D-4E18-91FA-9D35D4757DD0';

    DECLARE @Templates TABLE ( [Id] int PRIMARY KEY );
    INSERT INTO @Templates SELECT [Id] FROM [SystemCommunication] WHERE [Guid] = '85E90F38-3658-4335-B674-F16F644EA498';

    DECLARE @Tables TABLE ( [Name] sysname PRIMARY KEY );
    IF @RemoveData = 1
        INSERT INTO @Tables SELECT [name] FROM sys.tables WHERE [name] LIKE '\_com\_fivepoint\_Meet\_%' ESCAPE '\';

    /* ---------- Write down what will go ---------- */

    INSERT INTO @Report ( [Item], [Name] )
    SELECT 'Page', p.[InternalName] FROM [Page] p JOIN @Pages x ON x.[Id] = p.[Id];
    INSERT INTO @Report ( [Item], [Name] )
    SELECT 'Block', b.[Name] + ISNULL( ' (on page ' + pg.[InternalName] + ')', ' (site-wide)' )
    FROM [Block] b JOIN @Blocks x ON x.[Id] = b.[Id] LEFT JOIN [Page] pg ON pg.[Id] = b.[PageId]
    WHERE b.[PageId] IS NULL OR b.[PageId] NOT IN ( SELECT [Id] FROM @Pages );
    INSERT INTO @Report ( [Item], [Name] )
    SELECT 'Security role', g.[Name] + ' (' + CAST( ( SELECT COUNT(*) FROM [GroupMember] m WHERE m.[GroupId] = g.[Id] ) AS varchar ) + ' members)'
    FROM [Group] g JOIN @Roles x ON x.[Id] = g.[Id];
    INSERT INTO @Report ( [Item], [Name] )
    SELECT 'Job', j.[Name] FROM [ServiceJob] j JOIN @Jobs x ON x.[Id] = j.[Id];
    INSERT INTO @Report ( [Item], [Name] )
    SELECT 'File type', f.[Name] + ' (' + CAST( ( SELECT COUNT(*) FROM [BinaryFile] b WHERE b.[BinaryFileTypeId] = f.[Id] ) AS varchar ) + ' audio files)'
    FROM [BinaryFileType] f JOIN @FileTypes x ON x.[Id] = f.[Id];
    INSERT INTO @Report ( [Item], [Name] )
    SELECT 'Email template', c.[Title] FROM [SystemCommunication] c JOIN @Templates x ON x.[Id] = c.[Id];
    INSERT INTO @Report ( [Item], [Name] )
    SELECT 'Block type', bt.[Name] FROM [BlockType] bt JOIN @BlockTypes x ON x.[Id] = bt.[Id];
    INSERT INTO @Report ( [Item], [Name] )
    SELECT 'Migration history', CAST( COUNT(*) AS varchar ) + ' rows' FROM [PluginMigration] WHERE [PluginAssemblyName] = 'com.fivepoint.Meet';
    IF @RemoveData = 1
    BEGIN
        INSERT INTO @Report ( [Item], [Name] ) SELECT 'Table (and all its data)', [Name] FROM @Tables;
        INSERT INTO @Report ( [Item], [Name] )
        SELECT 'Setting', [Key] FROM [Attribute]
        WHERE [EntityTypeId] IS NULL AND [EntityTypeQualifierColumn] = 'SystemSetting' AND [Key] LIKE 'com.fivepoint.Meet.%';
    END

    /* ---------- Jobs first, so none starts mid-way ---------- */

    DELETE FROM [ServiceJobHistory] WHERE [ServiceJobId] IN ( SELECT [Id] FROM @Jobs );
    DELETE FROM [ServiceJob] WHERE [Id] IN ( SELECT [Id] FROM @Jobs );

    /* ---------- Meet's own tables (only with @RemoveData = 1) ---------- */

    DECLARE @sql nvarchar(max) = N'';
    SELECT @sql += N'ALTER TABLE ' + QUOTENAME( SCHEMA_NAME( t.[schema_id] ) ) + N'.' + QUOTENAME( t.[name] )
                 + N' DROP CONSTRAINT ' + QUOTENAME( fk.[name] ) + N';' + CHAR(10)
    FROM sys.foreign_keys fk
    JOIN sys.tables t ON t.[object_id] = fk.[parent_object_id]
    JOIN sys.tables r ON r.[object_id] = fk.[referenced_object_id]
    WHERE t.[name] IN ( SELECT [Name] FROM @Tables ) OR r.[name] IN ( SELECT [Name] FROM @Tables );
    IF @sql <> N'' EXEC sp_executesql @sql;

    SET @sql = N'';
    SELECT @sql += N'DROP TABLE [dbo].' + QUOTENAME( [Name] ) + N';' + CHAR(10) FROM @Tables;
    IF @sql <> N'' EXEC sp_executesql @sql;

    /* ---------- Pages, routes, blocks, block types ---------- */

    DELETE FROM [Auth] WHERE [EntityTypeId] = @PageEntityTypeId AND [EntityId] IN ( SELECT [Id] FROM @Pages );
    DELETE FROM [Auth] WHERE [EntityTypeId] = @BlockEntityTypeId AND [EntityId] IN ( SELECT [Id] FROM @Blocks );
    DELETE FROM [PersonPreference] WHERE [EntityTypeId] = @BlockEntityTypeId AND [EntityId] IN ( SELECT [Id] FROM @Blocks );
    DELETE FROM [Block] WHERE [Id] IN ( SELECT [Id] FROM @Blocks );
    DELETE FROM [PageRoute] WHERE [PageId] IN ( SELECT [Id] FROM @Pages );
    DELETE FROM [PageContext] WHERE [PageId] IN ( SELECT [Id] FROM @Pages );
    DELETE FROM [Page] WHERE [Id] IN ( SELECT [Id] FROM @Pages );
    DELETE FROM [Attribute] WHERE [EntityTypeId] = @BlockEntityTypeId AND [EntityTypeQualifierColumn] = 'BlockTypeId'
        AND [EntityTypeQualifierValue] IN ( SELECT CAST( [Id] AS varchar ) FROM @BlockTypes );
    DELETE FROM [BlockType] WHERE [Id] IN ( SELECT [Id] FROM @BlockTypes );

    /* ---------- Security roles ---------- */

    DELETE FROM [Auth] WHERE [GroupId] IN ( SELECT [Id] FROM @Roles );
    DELETE FROM [AuthAuditLog] WHERE [GroupId] IN ( SELECT [Id] FROM @Roles );
    DELETE FROM [GroupMemberHistorical] WHERE [GroupId] IN ( SELECT [Id] FROM @Roles );
    DELETE FROM [GroupMember] WHERE [GroupId] IN ( SELECT [Id] FROM @Roles );
    DELETE FROM [GroupHistorical] WHERE [GroupId] IN ( SELECT [Id] FROM @Roles );
    DELETE FROM [Group] WHERE [Id] IN ( SELECT [Id] FROM @Roles );

    /* ---------- Recording file type and any audio still waiting ---------- */

    DELETE FROM [BinaryFileData] WHERE [Id] IN ( SELECT [Id] FROM [BinaryFile] WHERE [BinaryFileTypeId] IN ( SELECT [Id] FROM @FileTypes ) );
    DELETE FROM [BinaryFile] WHERE [BinaryFileTypeId] IN ( SELECT [Id] FROM @FileTypes );
    DELETE FROM [BinaryFileType] WHERE [Id] IN ( SELECT [Id] FROM @FileTypes );

    /* ---------- Email template and its "Meet" category ---------- */

    -- Sent emails stay in Communication History; they just lose the link to the template.
    UPDATE [Communication] SET [SystemCommunicationId] = NULL WHERE [SystemCommunicationId] IN ( SELECT [Id] FROM @Templates );
    DELETE FROM [SystemCommunication] WHERE [Id] IN ( SELECT [Id] FROM @Templates );
    DELETE FROM [Category]
    WHERE [EntityTypeId] = @SystemCommunicationEntityTypeId AND [Name] = 'Meet'
      AND NOT EXISTS ( SELECT 1 FROM [SystemCommunication] s WHERE s.[CategoryId] = [Category].[Id] )
      AND NOT EXISTS ( SELECT 1 FROM [Category] c WHERE c.[ParentCategoryId] = [Category].[Id] );

    /* ---------- Preferences, settings, entity types, migration history ---------- */

    DELETE FROM [PersonPreference] WHERE [Key] LIKE 'com.fivepoint.Meet.%';

    -- Meet's settings (including encrypted AI keys) and setup progress.
    IF @RemoveData = 1
        DELETE FROM [Attribute] WHERE [EntityTypeId] IS NULL AND [EntityTypeQualifierColumn] = 'SystemSetting' AND [Key] LIKE 'com.fivepoint.Meet.%';

    DELETE FROM [Auth] WHERE [EntityTypeId] IN ( SELECT [Id] FROM @EntityTypes );
    DELETE FROM [Attribute] WHERE [EntityTypeId] IN ( SELECT [Id] FROM @EntityTypes );
    -- An entity type that anything in Rock still points at (an audit or history
    -- row, a data view, a tag...) stays, rather than failing the whole run.
    -- It's inert once the DLL is gone. The check is built from the database's
    -- own foreign keys, so it covers tables added in later Rock versions too.
    SET @sql = N'DELETE e FROM [dbo].[EntityType] e WHERE e.[Name] LIKE ''com.fivepoint.Meet.%''';
    SELECT @sql += N' AND NOT EXISTS ( SELECT 1 FROM ' + QUOTENAME( SCHEMA_NAME( t.[schema_id] ) ) + N'.' + QUOTENAME( t.[name] )
                 + N' x WHERE x.' + QUOTENAME( c.[name] ) + N' = e.[Id] )'
    FROM sys.foreign_keys fk
    JOIN sys.foreign_key_columns fkc ON fkc.[constraint_object_id] = fk.[object_id]
    JOIN sys.tables t ON t.[object_id] = fk.[parent_object_id]
    JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.[column_id] = fkc.[parent_column_id]
    WHERE fk.[referenced_object_id] = OBJECT_ID( N'dbo.EntityType' ) AND t.[object_id] <> OBJECT_ID( N'dbo.EntityType' );
    EXEC sp_executesql @sql;

    -- So a later reinstall runs every install step again.
    DELETE FROM [PluginMigration] WHERE [PluginAssemblyName] = 'com.fivepoint.Meet';

    /* ---------- Check nothing's left ---------- */

    DECLARE @left nvarchar(400) =
          'pages ' + CAST( ( SELECT COUNT(*) FROM [Page] WHERE [Guid] = '76E00C35-8B4F-46E1-89D2-C850F2D02100' ) AS varchar )
        + ', block types ' + CAST( ( SELECT COUNT(*) FROM [BlockType] bt JOIN [EntityType] e ON e.[Id] = bt.[EntityTypeId] WHERE e.[Name] LIKE 'com.fivepoint.Meet.%' ) AS varchar )
        + ', entity types ' + CAST( ( SELECT COUNT(*) FROM [EntityType] WHERE [Name] LIKE 'com.fivepoint.Meet.%' ) AS varchar )
        + ', roles ' + CAST( ( SELECT COUNT(*) FROM [Group] WHERE [Guid] IN ( 'B2DCD822-D96E-40E0-B2EB-B1C1E940D7B5', 'A48D3714-718D-43E4-95B2-766215CDFB58' ) ) AS varchar )
        + ', jobs ' + CAST( ( SELECT COUNT(*) FROM [ServiceJob] WHERE [Class] LIKE 'com.fivepoint.Meet.%' ) AS varchar )
        + ', migrations ' + CAST( ( SELECT COUNT(*) FROM [PluginMigration] WHERE [PluginAssemblyName] = 'com.fivepoint.Meet' ) AS varchar );
    INSERT INTO @Report ( [Item], [Name] ) VALUES ( 'Check: left afterwards (all 0 but entity types)', @left );

    IF @Commit = 1
    BEGIN
        COMMIT TRANSACTION;
        INSERT INTO @Report ( [Item], [Name] ) VALUES ( 'RESULT',
            CASE WHEN @RemoveData = 1 THEN 'Done. Meet and all of its data have been removed.'
                 ELSE 'Done. Meet has been removed. Its data tables and settings were kept.' END );
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        INSERT INTO @Report ( [Item], [Name] ) VALUES ( 'RESULT',
            'DRY RUN: nothing was changed. Set @Commit = 1 to remove the items above.' );
    END

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    INSERT INTO @Report ( [Item], [Name] ) VALUES ( 'RESULT', 'FAILED, nothing was changed: ' + ERROR_MESSAGE() );
END CATCH;

SELECT [Item], [Name] FROM @Report ORDER BY [Order];