invariants.sql

/*
    Invariants: rules that must hold in every loaded dataset, on every Rock version.

    The loader bulk-copies around Rock's save hooks, so Rock never gets to enforce these itself. The verify tool
    (verify/README.md) runs each check after a load and fails when any returns more than zero. Each check starts
    with "-- check: name", says why it matters on the next "-- why:" line, and returns one number: how many rows
    break the rule. @ForeignKey is the dataset's key, for example SampleData:core-1. @CardPeople and @CardHouseholds
    are the dataset card's numbers from datasets/{key}.json, or null when the load used another seed than the
    default, which the card was not measured at. @AdminPersonId is Rock's admin person (Person Id 1), which the load
    turns into a Cast person but leaves without the dataset's ForeignKey (decision 177), so rules about this
    dataset's people name it alongside @ForeignKey.

    Rules that a foreign key or unique index already enforces are left out: the bulk copy checks those.
    "Every family has an adult" is left out on purpose: kids whose parents are not in Rock, and Jenny Greggs's
    memorial record, are child-only families, as they are in real churches.
*/

-- check: dataset-has-people
-- why: Every rule below looks only at this dataset's rows. A load that wrote nothing would pass them all.
SELECT CASE WHEN EXISTS ( SELECT 1 FROM [Person] WHERE [ForeignKey] = @ForeignKey ) THEN 0 ELSE 1 END;

-- check: admin-is-a-cast-person
-- why: Decision 177. Rock's admin person becomes the Cast person who replaces it, so signing in as admin shows real data. It keeps Rock's admin login, Rock Administrators, and an empty ForeignKey, so nothing that removes sample data by ForeignKey can remove the site's admin. Its primary family is this dataset's, and it is in no other family, so Rock's empty Admin family is gone.
SELECT CASE WHEN EXISTS (
        SELECT 1
        FROM [Person] AS [p]
        INNER JOIN [Group] AS [f] ON [f].[Id] = [p].[PrimaryFamilyId]
        WHERE [p].[Id] = @AdminPersonId AND [p].[ForeignKey] IS NULL AND [f].[ForeignKey] = @ForeignKey
            AND EXISTS ( SELECT 1 FROM [UserLogin] AS [ul] WHERE [ul].[PersonId] = [p].[Id] AND [ul].[UserName] = 'admin' )
            AND EXISTS ( SELECT 1 FROM [GroupMember] AS [gm] INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId] WHERE [gm].[PersonId] = [p].[Id] AND [g].[Guid] = '628C51A8-4613-43ED-A18D-4A6FB999273E' ) )
    AND NOT EXISTS (
        SELECT 1
        FROM [GroupMember] AS [gm]
        INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
        INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
        WHERE [gm].[PersonId] = @AdminPersonId AND [gt].[Guid] = '790E3215-3B10-442B-AF69-616C0DCB998E' AND ISNULL( [g].[ForeignKey], '' ) <> @ForeignKey )
    THEN 0 ELSE 1 END;

-- check: person-has-one-primary-alias
-- why: Rock finds a person through their primary alias; every audit column and gift points at it.
SELECT COUNT(*)
FROM [Person] AS [p]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND ( [p].[PrimaryAliasId] IS NULL
        OR NOT EXISTS ( SELECT 1 FROM [PersonAlias] AS [pa] WHERE [pa].[Id] = [p].[PrimaryAliasId] AND [pa].[PersonId] = [p].[Id] AND [pa].[AliasPersonId] = [p].[Id] )
        OR ( SELECT COUNT(*) FROM [PersonAlias] AS [pa] WHERE [pa].[PersonId] = [p].[Id] AND [pa].[AliasPersonId] = [p].[Id] ) <> 1 );

-- check: person-is-in-a-family
-- why: Rock expects every person to belong to at least one family; profile pages and check-in break without one.
SELECT COUNT(*)
FROM [Person] AS [p]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND NOT EXISTS (
        SELECT 1
        FROM [GroupMember] AS [gm]
        INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
        INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
        WHERE [gm].[PersonId] = [p].[Id] AND [gt].[Guid] = '790E3215-3B10-442B-AF69-616C0DCB998E' );

-- check: primary-family-is-a-family-the-person-is-in
-- why: Rock's save hook sets the primary family and campus; the loader fills them in itself.
SELECT COUNT(*)
FROM [Person] AS [p]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND ( [p].[PrimaryFamilyId] IS NULL
        OR NOT EXISTS ( SELECT 1 FROM [GroupMember] AS [gm] WHERE [gm].[GroupId] = [p].[PrimaryFamilyId] AND [gm].[PersonId] = [p].[Id] ) );

-- check: primary-campus-is-the-primary-family-campus
-- why: Rock shows and filters people by the campus of their primary family.
SELECT COUNT(*)
FROM [Person] AS [p]
INNER JOIN [Group] AS [f] ON [f].[Id] = [p].[PrimaryFamilyId]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND ISNULL( [p].[PrimaryCampusId], -1 ) <> ISNULL( [f].[CampusId], -1 );

-- check: giving-id-matches-giving-group
-- why: Rock groups gifts by GivingId (G + giving group, or P + person). A wrong one splits or merges giving units.
SELECT COUNT(*)
FROM [Person] AS [p]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND ( ISNULL( [p].[GivingId], '' ) <> CASE WHEN [p].[GivingGroupId] IS NULL THEN 'P' + CAST( [p].[Id] AS VARCHAR(20) ) ELSE 'G' + CAST( [p].[GivingGroupId] AS VARCHAR(20) ) END
        OR [p].[GivingLeaderId] IS NULL );

-- check: giving-leader-is-in-the-giving-unit
-- why: The giving leader receives the statement; it must be the person or an adult of their giving group.
SELECT COUNT(*)
FROM [Person] AS [p]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND [p].[GivingLeaderId] <> [p].[Id]
    AND NOT EXISTS ( SELECT 1 FROM [GroupMember] AS [gm] WHERE [gm].[GroupId] = [p].[GivingGroupId] AND [gm].[PersonId] = [p].[GivingLeaderId] );

-- check: birth-date-parts-agree
-- why: Rock stores the birth date three ways (date, day/month/year, and a sortable key); reports read all three.
SELECT COUNT(*)
FROM [Person] AS [p]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND [p].[BirthDate] IS NOT NULL
    AND ( [p].[BirthYear] <> YEAR( [p].[BirthDate] ) OR [p].[BirthMonth] <> MONTH( [p].[BirthDate] ) OR [p].[BirthDay] <> DAY( [p].[BirthDate] )
        OR [p].[BirthDateKey] <> YEAR( [p].[BirthDate] ) * 10000 + MONTH( [p].[BirthDate] ) * 100 + DAY( [p].[BirthDate] ) );

-- check: phones-use-the-reserved-range
-- why: Content standard 2. Phones are 555-0100 to 555-0199 in a Phoenix-area code, so no real person gets a call.
SELECT COUNT(*)
FROM [PhoneNumber] AS [pn]
INNER JOIN [Person] AS [p] ON [p].[Id] = [pn].[PersonId]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND ( LEN( [pn].[Number] ) <> 10 OR LEFT( [pn].[Number], 3 ) NOT IN ( '602', '480', '623' ) OR SUBSTRING( [pn].[Number], 4, 5 ) <> '55501' );

-- check: emails-use-reserved-domains
-- why: Content standard 3. Emails use example.org, or rocksolidchurchdemo.com for the sample church's staff.
SELECT COUNT(*)
FROM [Person] AS [p]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND ISNULL( [p].[Email], '' ) <> ''
    AND [p].[Email] NOT LIKE '%@example.org'
    AND [p].[Email] NOT LIKE '%@rocksolidchurchdemo.com';

-- check: no-race-or-ethnicity
-- why: Content standard 8. Photo ethnicity only makes matching family photos; it never goes into Rock.
SELECT COUNT(*)
FROM [Person] AS [p]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND ( [p].[RaceValueId] IS NOT NULL OR [p].[EthnicityValueId] IS NOT NULL );

-- check: home-addresses-are-mapped
-- why: Group Finder, campus distance, and maps need a point for every home address.
SELECT COUNT(*)
FROM [GroupLocation] AS [gl]
INNER JOIN [Group] AS [g] ON [g].[Id] = [gl].[GroupId]
INNER JOIN [Location] AS [l] ON [l].[Id] = [gl].[LocationId]
INNER JOIN [DefinedValue] AS [t] ON [t].[Id] = [gl].[GroupLocationTypeValueId]
WHERE [g].[ForeignKey] = @ForeignKey
    AND [t].[Guid] = '8C52E53C-2A66-435A-AE6E-5EE307D9A0DC'
    AND [l].[GeoPoint] IS NULL;

-- check: nothing-dated-in-the-future
-- why: The timeline counts back from the install day. A future gift, check-in, or step shows up in "upcoming" places.
--      Requests to serve in the weeks ahead are the one exception: they are scheduled and not attended (decision 74).
SELECT ( SELECT COUNT(*) FROM [Attendance] WHERE [ForeignKey] = @ForeignKey AND [StartDateTime] >= DATEADD( DAY, 1, CAST( GETDATE() AS DATE ) ) AND NOT ( [RequestedToAttend] = 1 AND [DidAttend] = 0 ) )
    + ( SELECT COUNT(*) FROM [FinancialTransaction] WHERE [ForeignKey] = @ForeignKey AND [TransactionDateTime] >= DATEADD( DAY, 1, CAST( GETDATE() AS DATE ) ) )
    + ( SELECT COUNT(*) FROM [Step] WHERE [ForeignKey] = @ForeignKey
            AND ( [StartDateTime] >= DATEADD( DAY, 1, CAST( GETDATE() AS DATE ) ) OR [EndDateTime] >= DATEADD( DAY, 1, CAST( GETDATE() AS DATE ) ) OR [CompletedDateTime] >= DATEADD( DAY, 1, CAST( GETDATE() AS DATE ) ) ) )
    + ( SELECT COUNT(*) FROM [StepProgramCompletion] WHERE [ForeignKey] = @ForeignKey AND [EndDateTime] >= DATEADD( DAY, 1, CAST( GETDATE() AS DATE ) ) );

-- check: transaction-has-positive-details
-- why: Rock totals a gift from its details. A gift with no detail, or a zero or negative one, breaks statements.
SELECT COUNT(*)
FROM [FinancialTransaction] AS [t]
WHERE [t].[ForeignKey] = @ForeignKey
    AND ( NOT EXISTS ( SELECT 1 FROM [FinancialTransactionDetail] AS [d] WHERE [d].[TransactionId] = [t].[Id] )
        OR EXISTS ( SELECT 1 FROM [FinancialTransactionDetail] AS [d] WHERE [d].[TransactionId] = [t].[Id] AND [d].[Amount] <= 0 ) );

-- check: transaction-is-inside-its-batch
-- why: Batches cover a date range. A gift outside its batch's range lands in the wrong batch report.
SELECT COUNT(*)
FROM [FinancialTransaction] AS [t]
INNER JOIN [FinancialBatch] AS [b] ON [b].[Id] = [t].[BatchId]
WHERE [t].[ForeignKey] = @ForeignKey
    AND ( [t].[TransactionDateTime] < [b].[BatchStartDateTime] OR [t].[TransactionDateTime] >= [b].[BatchEndDateTime] );

-- check: member-role-belongs-to-the-group-type
-- why: Rock only lets a member hold a role of their group's type. The database does not enforce it.
SELECT COUNT(*)
FROM [GroupMember] AS [gm]
INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
INNER JOIN [GroupTypeRole] AS [r] ON [r].[Id] = [gm].[GroupRoleId]
WHERE [gm].[ForeignKey] = @ForeignKey
    AND [r].[GroupTypeId] <> [g].[GroupTypeId];

-- check: one-current-history-row
-- why: Rock's history tables mark exactly one current row per group, member, and meeting location.
SELECT ( SELECT COUNT(*) FROM ( SELECT [GroupId] FROM [GroupHistorical] WHERE [ForeignKey] = @ForeignKey GROUP BY [GroupId] HAVING SUM( CASE WHEN [CurrentRowIndicator] = 1 THEN 1 ELSE 0 END ) <> 1 ) AS [x] )
    + ( SELECT COUNT(*) FROM ( SELECT [GroupMemberId] FROM [GroupMemberHistorical] WHERE [ForeignKey] = @ForeignKey GROUP BY [GroupMemberId] HAVING SUM( CASE WHEN [CurrentRowIndicator] = 1 THEN 1 ELSE 0 END ) <> 1 ) AS [x] )
    + ( SELECT COUNT(*) FROM ( SELECT [GroupLocationId] FROM [GroupLocationHistorical] WHERE [ForeignKey] = @ForeignKey GROUP BY [GroupLocationId] HAVING SUM( CASE WHEN [CurrentRowIndicator] = 1 THEN 1 ELSE 0 END ) <> 1 ) AS [x] );

-- check: photo-is-a-stored-person-image
-- why: A photo id must point at a Person Image file whose bytes are in the database, or the profile shows a broken image.
SELECT COUNT(*)
FROM [Person] AS [p]
LEFT JOIN [BinaryFile] AS [f] ON [f].[Id] = [p].[PhotoId]
LEFT JOIN [BinaryFileType] AS [ft] ON [ft].[Id] = [f].[BinaryFileTypeId]
WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
    AND [p].[PhotoId] IS NOT NULL
    AND ( [f].[Id] IS NULL
        OR [ft].[Guid] <> '03BD8476-8A9F-4078-B628-5B538F967AFC'
        OR NOT EXISTS ( SELECT 1 FROM [BinaryFileData] AS [d] WHERE [d].[Id] = [f].[Id] AND DATALENGTH( [d].[Content] ) > 0 ) );

-- check: only-this-dataset
-- why: A database holds one dataset. Rows from another would mean a load on a database that was not fresh.
SELECT ( SELECT COUNT(*) FROM [Person] WHERE [ForeignKey] LIKE 'SampleData:%' AND [ForeignKey] <> @ForeignKey )
    + ( SELECT COUNT(*) FROM [Group] WHERE [ForeignKey] LIKE 'SampleData:%' AND [ForeignKey] <> @ForeignKey )
    + ( SELECT COUNT(*) FROM [FinancialTransaction] WHERE [ForeignKey] LIKE 'SampleData:%' AND [ForeignKey] <> @ForeignKey );

-- check: note-is-on-a-person-of-this-dataset
-- why: A person note's EntityId must point at a loaded person, or the note is an orphan Rock never shows.
SELECT COUNT(*)
FROM [Note] AS [n]
INNER JOIN [NoteType] AS [nt] ON [nt].[Id] = [n].[NoteTypeId]
INNER JOIN [EntityType] AS [et] ON [et].[Id] = [nt].[EntityTypeId] AND [et].[Name] = 'Rock.Model.Person'
WHERE [n].[ForeignKey] = @ForeignKey
    AND NOT EXISTS ( SELECT 1 FROM [Person] AS [p] WHERE [p].[Id] = [n].[EntityId] AND ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId ) );

-- check: note-reply-matches-its-parent
-- why: Rock shows a reply under its parent, so it must be on the same entity, of the same type, and written after it.
SELECT COUNT(*)
FROM [Note] AS [r]
INNER JOIN [Note] AS [n] ON [n].[Id] = [r].[ParentNoteId]
WHERE [r].[ForeignKey] = @ForeignKey
    AND ( [r].[EntityId] <> [n].[EntityId] OR [r].[NoteTypeId] <> [n].[NoteTypeId] OR [r].[CreatedDateTime] < [n].[CreatedDateTime] );

-- check: note-author-is-not-the-noted-person
-- why: Staff write notes about other people; a note about its own author looks like a load error. Rock's own Event Registration notes are left out: Rock writes one on each registrant as the registrar, who is often the registrant (decision 213).
SELECT COUNT(*)
FROM [Note] AS [n]
INNER JOIN [NoteType] AS [nt] ON [nt].[Id] = [n].[NoteTypeId]
INNER JOIN [EntityType] AS [et] ON [et].[Id] = [nt].[EntityTypeId] AND [et].[Name] = 'Rock.Model.Person'
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [n].[CreatedByPersonAliasId]
WHERE [n].[ForeignKey] = @ForeignKey
    AND [nt].[Guid] <> 'BBADA8EF-23FC-4B46-B7A7-0F6D31F8C045'
    AND [pa].[PersonId] = [n].[EntityId];

-- check: person-note-looks-saved-by-rock
-- why: Rock's save hook approves notes whose type needs no approval, and its editor sets EditedDateTime and a private note's caption. Unsent flags would make Rock's Send Note Notifications job email staff about old notes. Rock's Event Registration notes come from Rock's code, not its editor, so they have no EditedDateTime (decision 213).
SELECT COUNT(*)
FROM [Note] AS [n]
INNER JOIN [NoteType] AS [nt] ON [nt].[Id] = [n].[NoteTypeId]
INNER JOIN [EntityType] AS [et] ON [et].[Id] = [nt].[EntityTypeId] AND [et].[Name] = 'Rock.Model.Person'
WHERE [n].[ForeignKey] = @ForeignKey
    AND ( [n].[ApprovalStatus] <> 1
        OR [n].[NotificationsSent] = 0
        OR [n].[ApprovalsSent] = 0
        OR ( [n].[EditedDateTime] IS NULL AND [nt].[Guid] <> 'BBADA8EF-23FC-4B46-B7A7-0F6D31F8C045' )
        OR [n].[CreatedByPersonAliasId] IS NULL
        OR ( [n].[IsPrivateNote] = 1 AND ISNULL( [n].[Caption], '' ) <> 'You - Personal Note' ) );

-- check: no-high-schooler-in-adult-groups
-- why: Small groups and serving teams are adult-only. Nobody joins or leads one while still in high school: a membership starts after June 1 of the person's graduation year, the day after Rock's last day of grade 12 (decisions 169 and 205).
SELECT COUNT(*)
FROM [GroupMember] AS [gm]
INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [gm].[PersonId]
WHERE [gm].[ForeignKey] = @ForeignKey
    AND [gt].[Guid] IN ( '50FCFB30-F51A-49DF-86F4-2B176EA1820B', '2C42B2D4-1C5F-4AD5-A9AD-08631B872AC4' )
    AND [p].[GraduationYear] IS NOT NULL
    AND [gm].[DateTimeAdded] < DATEFROMPARTS( [p].[GraduationYear], 6, 2 );

-- check: step-matches-its-membership
-- why: A Small Group, Serve, or Lead step records one membership (decision 86): same person, group, and dates, In Progress only while the member is Pending. And every membership that counts has its step.
WITH [Memberships] AS (
    SELECT [p].[PrimaryAliasId], [gm].[GroupId], [gt].[Guid] AS [GroupTypeGuid], [r].[IsLeader], [gm].[GroupMemberStatus], [gm].[DateTimeAdded],
        CASE WHEN [gm].[GroupMemberStatus] = 0 OR [gm].[IsArchived] = 1 THEN ISNULL( [gm].[InactiveDateTime], [gm].[ArchivedDateTime] ) END AS [MemberEnded],
        CASE WHEN [g].[IsActive] = 0 OR [g].[IsArchived] = 1 THEN ISNULL( [g].[InactiveDateTime], [g].[ArchivedDateTime] ) END AS [GroupEnded]
    FROM [GroupMember] AS [gm]
    INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
    INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
    INNER JOIN [GroupTypeRole] AS [r] ON [r].[Id] = [gm].[GroupRoleId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [gm].[PersonId]
    WHERE [gm].[ForeignKey] = @ForeignKey
        AND [gt].[Guid] IN ( '50FCFB30-F51A-49DF-86F4-2B176EA1820B', '2C42B2D4-1C5F-4AD5-A9AD-08631B872AC4' )
),
[Expected] AS (
    SELECT [PrimaryAliasId], [GroupId], CAST( 'EFA15A4F-5666-4153-B92F-AF3ECD73C504' AS UNIQUEIDENTIFIER ) AS [StepTypeGuid], [DateTimeAdded], [MemberEnded], [GroupEnded], 0 AS [IsInProgress]
    FROM [Memberships] WHERE [GroupTypeGuid] = '50FCFB30-F51A-49DF-86F4-2B176EA1820B' AND [GroupMemberStatus] <> 2
    UNION ALL
    SELECT [PrimaryAliasId], [GroupId], CAST( '71E66730-8F7D-4EEF-9C53-524C4BDE5E59' AS UNIQUEIDENTIFIER ), [DateTimeAdded], [MemberEnded], [GroupEnded], CASE WHEN [GroupMemberStatus] = 2 THEN 1 ELSE 0 END
    FROM [Memberships] WHERE [GroupTypeGuid] = '2C42B2D4-1C5F-4AD5-A9AD-08631B872AC4'
    UNION ALL
    SELECT [PrimaryAliasId], [GroupId], CAST( 'CFF0E432-3AB6-4290-8F6E-C1A3D8D8F375' AS UNIQUEIDENTIFIER ), [DateTimeAdded], [MemberEnded], [GroupEnded], 0
    FROM [Memberships] WHERE [IsLeader] = 1 AND [GroupMemberStatus] <> 2
),
[ExpectedSteps] AS (
    SELECT [PrimaryAliasId], [GroupId], [StepTypeGuid], [DateTimeAdded], [IsInProgress],
        CASE WHEN [MemberEnded] IS NULL THEN [GroupEnded] WHEN [GroupEnded] IS NULL OR [MemberEnded] < [GroupEnded] THEN [MemberEnded] ELSE [GroupEnded] END AS [Ended]
    FROM [Expected]
),
[Steps] AS (
    SELECT [s].[PersonAliasId], [s].[RelatedEntityId], [st].[Guid] AS [StepTypeGuid], [s].[StartDateTime], [s].[EndDateTime], CASE WHEN [ss].[IsCompleteStatus] = 1 THEN 0 ELSE 1 END AS [IsInProgress]
    FROM [Step] AS [s]
    INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
    INNER JOIN [StepStatus] AS [ss] ON [ss].[Id] = [s].[StepStatusId]
    WHERE [s].[ForeignKey] = @ForeignKey
        AND [st].[Guid] IN ( 'EFA15A4F-5666-4153-B92F-AF3ECD73C504', '71E66730-8F7D-4EEF-9C53-524C4BDE5E59', 'CFF0E432-3AB6-4290-8F6E-C1A3D8D8F375' )
)
SELECT ( SELECT COUNT(*) FROM [Steps] AS [s] WHERE NOT EXISTS (
            SELECT 1 FROM [ExpectedSteps] AS [e]
            WHERE [e].[PrimaryAliasId] = [s].[PersonAliasId] AND [e].[GroupId] = [s].[RelatedEntityId] AND [e].[StepTypeGuid] = [s].[StepTypeGuid]
                AND [e].[DateTimeAdded] = [s].[StartDateTime] AND ISNULL( [e].[Ended], '9999-01-01' ) = ISNULL( [s].[EndDateTime], '9999-01-01' ) AND [e].[IsInProgress] = [s].[IsInProgress] ) )
    + ( SELECT COUNT(*) FROM [ExpectedSteps] AS [e] WHERE NOT EXISTS (
            SELECT 1 FROM [Steps] AS [s]
            WHERE [e].[PrimaryAliasId] = [s].[PersonAliasId] AND [e].[GroupId] = [s].[RelatedEntityId] AND [e].[StepTypeGuid] = [s].[StepTypeGuid] AND [e].[DateTimeAdded] = [s].[StartDateTime] ) );

-- check: step-looks-saved-by-rock
-- why: Rock's step model fills the date keys from the dates, a Complete step has a completion date and an In Progress one has none, and the sample data puts every step on the person's primary alias and campus.
SELECT COUNT(*)
FROM [Step] AS [s]
INNER JOIN [StepStatus] AS [ss] ON [ss].[Id] = [s].[StepStatusId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[PersonAliasId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
WHERE [s].[ForeignKey] = @ForeignKey
    AND ( ISNULL( [s].[StartDateKey], 0 ) <> ISNULL( CONVERT( INT, FORMAT( [s].[StartDateTime], 'yyyyMMdd' ) ), 0 )
        OR ISNULL( [s].[EndDateKey], 0 ) <> ISNULL( CONVERT( INT, FORMAT( [s].[EndDateTime], 'yyyyMMdd' ) ), 0 )
        OR ISNULL( [s].[CompletedDateKey], 0 ) <> ISNULL( CONVERT( INT, FORMAT( [s].[CompletedDateTime], 'yyyyMMdd' ) ), 0 )
        OR ( [ss].[IsCompleteStatus] = 1 AND [s].[CompletedDateTime] IS NULL )
        OR ( [ss].[IsCompleteStatus] = 0 AND [s].[CompletedDateTime] IS NOT NULL )
        OR [s].[StartDateTime] IS NULL
        OR ISNULL( [s].[CampusId], 0 ) <> ISNULL( [p].[PrimaryCampusId], 0 )
        OR [s].[PersonAliasId] <> [p].[PrimaryAliasId] );

-- check: step-has-its-person-history
-- why: Rock's step save hook writes person history: one "Added" row per step on the day it was made, and a status change on the day a step that started In Progress was completed. The person's History tab shows each step.
WITH [StepHistory] AS (
    SELECT [h].*
    FROM [History] AS [h]
    INNER JOIN [Category] AS [c] ON [c].[Id] = [h].[CategoryId]
    WHERE [h].[ForeignKey] = @ForeignKey AND [c].[Guid] = '517BB2D3-0A50-4132-818E-63BB3C81EAE9'
)
SELECT ( SELECT COUNT(*)
        FROM [Step] AS [s]
        INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[PersonAliasId]
        WHERE [s].[ForeignKey] = @ForeignKey
            AND ( SELECT COUNT(*) FROM [StepHistory] AS [h]
                WHERE [h].[RelatedEntityId] = [s].[Id] AND [h].[EntityId] = [pa].[PersonId] AND [h].[Verb] = 'STEPADDED'
                    AND [h].[ValueName] = [st].[Name] AND [h].[CreatedDateTime] = [s].[StartDateTime] ) <> 1 )
    + ( SELECT COUNT(*)
        FROM [Step] AS [s]
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[PersonAliasId]
        WHERE [s].[ForeignKey] = @ForeignKey AND [s].[CompletedDateTime] > [s].[StartDateTime]
            AND NOT EXISTS ( SELECT 1 FROM [StepHistory] AS [h]
                WHERE [h].[RelatedEntityId] = [s].[Id] AND [h].[EntityId] = [pa].[PersonId] AND [h].[Verb] = 'MODIFY'
                    AND [h].[ValueName] = 'Step Status' AND [h].[CreatedDateTime] = [s].[CompletedDateTime] ) )
    + ( SELECT COUNT(*)
        FROM [StepHistory] AS [h]
        WHERE NOT EXISTS ( SELECT 1 FROM [Step] AS [s]
            INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[PersonAliasId]
            WHERE [s].[Id] = [h].[RelatedEntityId] AND [pa].[PersonId] = [h].[EntityId] ) );

-- check: baptism-step-agrees-with-baptism-date
-- why: The Baptism step and Rock's Baptism Date person attribute always agree, and the step's Baptized Here matches the person's.
WITH [Dates] AS (
    SELECT [av].[EntityId] AS [PersonId], [av].[ValueAsDateTime]
    FROM [AttributeValue] AS [av]
    INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [av].[EntityId]
    WHERE [a].[Guid] = 'D42763FA-28E9-4A55-A25A-48998D7D7FEF' AND ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId ) AND ISNULL( [av].[Value], '' ) <> ''
),
[Baptisms] AS (
    SELECT [pa].[PersonId], [s].[Id], [s].[StartDateTime], [s].[CompletedDateTime]
    FROM [Step] AS [s]
    INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[PersonAliasId]
    WHERE [st].[Guid] = '801CC43C-0641-4271-939E-75E428F31D06' AND [s].[ForeignKey] = @ForeignKey
),
[Here] AS (
    SELECT [b].[Id],
        ( SELECT [av].[Value] FROM [AttributeValue] AS [av] INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId] WHERE [a].[Guid] = '343A557E-92CC-4A75-B251-0DA732F5EB4E' AND [av].[EntityId] = [b].[Id] ) AS [OnStep],
        ( SELECT [av].[Value] FROM [AttributeValue] AS [av] INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId] WHERE [a].[Guid] = '87986F92-4AC3-49D2-9753-328588F3BB7A' AND [av].[EntityId] = [b].[PersonId] ) AS [OnPerson]
    FROM [Baptisms] AS [b]
)
SELECT ( SELECT COUNT(*) FROM [Dates] AS [d] WHERE NOT EXISTS ( SELECT 1 FROM [Baptisms] AS [b] WHERE [b].[PersonId] = [d].[PersonId] AND [b].[StartDateTime] = [d].[ValueAsDateTime] AND [b].[CompletedDateTime] = [d].[ValueAsDateTime] ) )
    + ( SELECT COUNT(*) FROM [Baptisms] AS [b] WHERE NOT EXISTS ( SELECT 1 FROM [Dates] AS [d] WHERE [d].[PersonId] = [b].[PersonId] AND [d].[ValueAsDateTime] = [b].[StartDateTime] ) )
    + ( SELECT COUNT(*) FROM [Here] WHERE [OnStep] IS NULL OR [OnPerson] IS NULL OR [OnStep] <> [OnPerson] );

-- check: serve-step-follows-starting-point
-- why: Rock's one Discipleship Path prerequisite: Serve needs a completed Starting Point Class first (decision 84). Every volunteer has one before their Serve step starts.
SELECT COUNT(*)
FROM [Step] AS [s]
INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
WHERE [st].[Guid] = '71E66730-8F7D-4EEF-9C53-524C4BDE5E59' AND [s].[ForeignKey] = @ForeignKey
    AND NOT EXISTS (
        SELECT 1
        FROM [Step] AS [c]
        INNER JOIN [StepType] AS [ct] ON [ct].[Id] = [c].[StepTypeId]
        INNER JOIN [StepStatus] AS [cs] ON [cs].[Id] = [c].[StepStatusId]
        WHERE [ct].[Guid] = 'F3585131-3EBF-49C4-83A0-3F88271A9B9B' AND [c].[PersonAliasId] = [s].[PersonAliasId]
            AND [cs].[IsCompleteStatus] = 1 AND [c].[CompletedDateTime] < [s].[StartDateTime] );

-- check: give-step-agrees-with-journey-stage
-- why: A Give step runs from the week a giver becomes Consistent to the week they become Lapsed or Former (decision 85). So a giver Rock shows as Consistent has an open Give step, and an open Give step belongs to a Consistent or Occasional giver.
WITH [Stages] AS (
    SELECT [p].[PrimaryAliasId], [av].[Value] AS [Stage]
    FROM [Person] AS [p]
    INNER JOIN [AttributeValue] AS [av] ON [av].[EntityId] = [p].[Id]
    INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId]
    WHERE [a].[Guid] = '13C55AEA-6D88-4470-B3AE-EE5138F044DF' AND ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId )
        AND [p].[RecordTypeValueId] <> ( SELECT [Id] FROM [DefinedValue] WHERE [Guid] = 'BF64ADD3-E70A-44CE-9C4B-E76BBED37550' )
),
[OpenGive] AS (
    SELECT DISTINCT [s].[PersonAliasId]
    FROM [Step] AS [s]
    INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
    WHERE [st].[Guid] = 'CE640CD3-DD6E-4BF3-ADDD-953F086E6E14' AND [s].[ForeignKey] = @ForeignKey AND [s].[EndDateTime] IS NULL
)
SELECT ( SELECT COUNT(*) FROM [Stages] AS [x] WHERE [x].[Stage] = '2' AND NOT EXISTS ( SELECT 1 FROM [OpenGive] AS [o] WHERE [o].[PersonAliasId] = [x].[PrimaryAliasId] ) )
    + ( SELECT COUNT(*) FROM [OpenGive] AS [o] WHERE NOT EXISTS ( SELECT 1 FROM [Stages] AS [x] WHERE [x].[PrimaryAliasId] = [o].[PersonAliasId] AND [x].[Stage] IN ( '2', '3' ) ) );

-- check: program-completions-follow-rocks-rule
-- why: Rock's Update Step Program Completions job records a completion for each person whose Complete steps cover every active step type in a program, dated from those steps, and points every step of theirs in the program at it. It runs for every program the dataset has steps in. Rock's job must find nothing to add or fix after install.
WITH [Programs] AS (
    SELECT DISTINCT [st].[StepProgramId] AS [Id]
    FROM [Step] AS [s]
    INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
    WHERE [s].[ForeignKey] = @ForeignKey
),
[PeopleWithAllSteps] AS (
    SELECT [st].[StepProgramId], [s].[PersonAliasId],
        MIN( COALESCE( [s].[StartDateTime], [s].[CreatedDateTime] ) ) AS [StartDateTime],
        MAX( COALESCE( [s].[CompletedDateTime], [s].[EndDateTime] ) ) AS [EndDateTime],
        MAX( [s].[CampusId] ) AS [CampusId]
    FROM [Step] AS [s]
    INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
    INNER JOIN [StepStatus] AS [ss] ON [ss].[Id] = [s].[StepStatusId] AND [ss].[IsCompleteStatus] = 1
    WHERE [st].[StepProgramId] IN ( SELECT [Id] FROM [Programs] ) AND [s].[ForeignKey] = @ForeignKey
    GROUP BY [st].[StepProgramId], [s].[PersonAliasId]
    HAVING COUNT( DISTINCT [s].[StepTypeId] ) = ( SELECT COUNT(*) FROM [StepType] AS [t] WHERE [t].[StepProgramId] = [st].[StepProgramId] AND [t].[IsActive] = 1 )
),
[Completions] AS (
    SELECT [c].[Id], [c].[StepProgramId], [c].[PersonAliasId], [c].[StartDateTime], [c].[EndDateTime], [c].[CampusId], [c].[StartDateKey]
    FROM [StepProgramCompletion] AS [c]
    WHERE [c].[StepProgramId] IN ( SELECT [Id] FROM [Programs] ) AND [c].[ForeignKey] = @ForeignKey
)
SELECT ( SELECT COUNT(*) FROM [PeopleWithAllSteps] AS [w] WHERE NOT EXISTS (
            SELECT 1 FROM [Completions] AS [c]
            WHERE [c].[StepProgramId] = [w].[StepProgramId] AND [c].[PersonAliasId] = [w].[PersonAliasId] AND [c].[StartDateTime] = [w].[StartDateTime]
                AND ISNULL( [c].[EndDateTime], '9999-01-01' ) = ISNULL( [w].[EndDateTime], '9999-01-01' )
                AND ISNULL( [c].[CampusId], 0 ) = ISNULL( [w].[CampusId], 0 ) AND [c].[StartDateKey] = CONVERT( INT, FORMAT( [w].[StartDateTime], 'yyyyMMdd' ) ) ) )
    + ( SELECT COUNT(*) FROM [Completions] AS [c] WHERE NOT EXISTS ( SELECT 1 FROM [PeopleWithAllSteps] AS [w] WHERE [w].[StepProgramId] = [c].[StepProgramId] AND [w].[PersonAliasId] = [c].[PersonAliasId] ) )
    + ( SELECT COUNT(*) FROM ( SELECT [StepProgramId], [PersonAliasId] FROM [Completions] GROUP BY [StepProgramId], [PersonAliasId] HAVING COUNT(*) > 1 ) AS [x] )
    + ( SELECT COUNT(*)
        FROM [Step] AS [s]
        INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
        LEFT JOIN [Completions] AS [c] ON [c].[StepProgramId] = [st].[StepProgramId] AND [c].[PersonAliasId] = [s].[PersonAliasId]
        WHERE [st].[StepProgramId] IN ( SELECT [Id] FROM [Programs] ) AND [s].[ForeignKey] = @ForeignKey
            AND ISNULL( [s].[StepProgramCompletionId], 0 ) <> ISNULL( [c].[Id], 0 ) );

-- check: people-match-the-dataset-card
-- why: Rock's installer shows the people count from datasets/{key}.json (@CardPeople). Returns how many people the load is off by when that is more than 2%.
SELECT CASE WHEN @CardPeople IS NOT NULL AND ABS( [c].[People] - @CardPeople ) > @CardPeople * 0.02 THEN ABS( [c].[People] - @CardPeople ) ELSE 0 END
FROM ( SELECT COUNT(*) AS [People] FROM [Person] AS [p] WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId ) ) AS [c];

-- check: households-match-the-dataset-card
-- why: Rock's installer shows the household count from datasets/{key}.json (@CardHouseholds). Returns how many households the load is off by when that is more than 2%.
SELECT CASE WHEN @CardHouseholds IS NOT NULL AND ABS( [c].[Households] - @CardHouseholds ) > @CardHouseholds * 0.02 THEN ABS( [c].[Households] - @CardHouseholds ) ELSE 0 END
FROM (
    SELECT COUNT(*) AS [Households]
    FROM [Group] AS [g]
    INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
    WHERE [g].[ForeignKey] = @ForeignKey AND [gt].[Guid] = '790E3215-3B10-442B-AF69-616C0DCB998E' ) AS [c];

-- check: student-step-belongs-to-a-student
-- why: The Student Path follows student check-ins (decision 115). Every Student Path step belongs to someone who was in grades 7 to 12 on its start and had checked in to the student service by then. Mission Trip is for high school students, and Student Leader for juniors and seniors. A student's grade comes from their graduation year and moves up the day after each June 1, as Rock does (decision 205).
WITH [StudentSteps] AS (
    SELECT [st].[Guid] AS [StepTypeGuid], [s].[StartDateTime], [pa].[PersonId], [p].[GraduationYear],
        12 - ( [p].[GraduationYear] - CASE WHEN CAST( [s].[StartDateTime] AS DATE ) > DATEFROMPARTS( YEAR( [s].[StartDateTime] ), 6, 1 ) THEN YEAR( [s].[StartDateTime] ) + 1 ELSE YEAR( [s].[StartDateTime] ) END ) AS [Grade]
    FROM [Step] AS [s]
    INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
    INNER JOIN [StepProgram] AS [sp] ON [sp].[Id] = [st].[StepProgramId]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[PersonAliasId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
    WHERE [sp].[Guid] = 'C8AD9B16-5132-4ACB-954B-1E61C734AA67' AND [s].[ForeignKey] = @ForeignKey
)
SELECT COUNT(*)
FROM [StudentSteps] AS [x]
WHERE [x].[GraduationYear] IS NULL
    OR [x].[Grade] > 12
    OR [x].[Grade] < CASE [x].[StepTypeGuid] WHEN '8464EF05-F8E2-4DB3-9AC4-BC1EEE18CBC9' THEN 9 WHEN '724C69AF-20BC-4299-8FD5-A84ED2B4D42D' THEN 11 ELSE 7 END
    OR NOT EXISTS (
        SELECT 1
        FROM [Attendance] AS [a]
        INNER JOIN [AttendanceOccurrence] AS [o] ON [o].[Id] = [a].[OccurrenceId]
        INNER JOIN [Group] AS [g] ON [g].[Id] = [o].[GroupId]
        INNER JOIN [PersonAlias] AS [apa] ON [apa].[Id] = [a].[PersonAliasId]
        WHERE [apa].[PersonId] = [x].[PersonId] AND [a].[DidAttend] = 1 AND [o].[OccurrenceDate] <= [x].[StartDateTime]
            AND [g].[Name] IN ( 'Grades 7-8', 'Grades 9-12' ) AND [g].[GroupTypeId] IN ( SELECT [Id] FROM [GroupType] WHERE [Guid] IN ( '7A17235B-69AD-439B-BAB0-1A0A472DB96F', '9A88743B-F336-4404-B877-2A623689195D' ) ) );

-- check: student-path-decisions-and-leaders
-- why: Follow Jesus is a decision made once. Student Leader is held once, and ends at graduation in May of the student's graduation year, or stays open while they are still in school.
SELECT ( SELECT COUNT(*)
        FROM ( SELECT [s].[PersonAliasId]
            FROM [Step] AS [s]
            INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
            WHERE [st].[Guid] IN ( 'FAC9E970-7434-4067-9E1C-180043CE6FCB', '724C69AF-20BC-4299-8FD5-A84ED2B4D42D' ) AND [s].[ForeignKey] = @ForeignKey
            GROUP BY [s].[PersonAliasId], [st].[Guid]
            HAVING COUNT(*) > 1 ) AS [x] )
    + ( SELECT COUNT(*)
        FROM [Step] AS [s]
        INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[PersonAliasId]
        INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
        WHERE [st].[Guid] = '724C69AF-20BC-4299-8FD5-A84ED2B4D42D' AND [s].[ForeignKey] = @ForeignKey
            AND ( ( [s].[EndDateTime] IS NOT NULL AND ( YEAR( [s].[EndDateTime] ) <> [p].[GraduationYear] OR MONTH( [s].[EndDateTime] ) <> 5 ) )
                OR ( [s].[EndDateTime] IS NULL AND DATEFROMPARTS( [p].[GraduationYear], 6, 1 ) < CAST( GETDATE() AS DATE ) ) ) );

-- check: student-baptism-follows-the-student-service
-- why: A student's baptism goes on the Discipleship Path's Baptism step (decision 87). A child is baptized here, on a Sunday, after they started coming to the student service. The Baptism Date and Baptized Here agree with the step (checked above).
SELECT COUNT(*)
FROM [Step] AS [s]
INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[PersonAliasId]
WHERE [st].[Guid] = '801CC43C-0641-4271-939E-75E428F31D06' AND [s].[ForeignKey] = @ForeignKey
    AND EXISTS (
        SELECT 1
        FROM [GroupMember] AS [gm]
        INNER JOIN [GroupTypeRole] AS [r] ON [r].[Id] = [gm].[GroupRoleId]
        WHERE [gm].[PersonId] = [pa].[PersonId] AND [r].[Guid] = 'C8B1814F-6AA7-4055-B2D7-48FE20429CB9' )
    AND ( DATEDIFF( DAY, '1900-01-07', [s].[StartDateTime] ) % 7 <> 0
        OR NOT EXISTS (
            SELECT 1
            FROM [Attendance] AS [a]
            INNER JOIN [AttendanceOccurrence] AS [o] ON [o].[Id] = [a].[OccurrenceId]
            INNER JOIN [Group] AS [g] ON [g].[Id] = [o].[GroupId]
            INNER JOIN [PersonAlias] AS [apa] ON [apa].[Id] = [a].[PersonAliasId]
            WHERE [apa].[PersonId] = [pa].[PersonId] AND [a].[DidAttend] = 1 AND [o].[OccurrenceDate] <= [s].[StartDateTime]
                AND [g].[Name] IN ( 'Grades 7-8', 'Grades 9-12' ) AND [g].[GroupTypeId] IN ( SELECT [Id] FROM [GroupType] WHERE [Guid] IN ( '7A17235B-69AD-439B-BAB0-1A0A472DB96F', '9A88743B-F336-4404-B877-2A623689195D' ) ) )
        OR NOT EXISTS (
            SELECT 1
            FROM [AttributeValue] AS [av]
            INNER JOIN [Attribute] AS [at] ON [at].[Id] = [av].[AttributeId]
            WHERE [at].[Guid] = '343A557E-92CC-4A75-B251-0DA732F5EB4E' AND [av].[EntityId] = [s].[Id] AND [av].[Value] = 'True' ) );

-- check: young-kid-checks-in-with-the-family
-- why: Kids from the nursery through 6th grade come only with their family (decision 146). Each of their check-ins is made by an adult of one of their families who was in the worship service that Sunday. A child whose families have no living, active adult checks in on their own, as before.
SELECT COUNT(*)
FROM [Attendance] AS [a]
INNER JOIN [AttendanceOccurrence] AS [o] ON [o].[Id] = [a].[OccurrenceId]
INNER JOIN [Group] AS [g] ON [g].[Id] = [o].[GroupId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [a].[PersonAliasId]
WHERE [a].[ForeignKey] = @ForeignKey AND [a].[DidAttend] = 1 AND [a].[AttendanceCodeId] IS NOT NULL
    AND [g].[GroupTypeId] IN ( SELECT [Id] FROM [GroupType] WHERE [Guid] IN ( 'CADB2D12-7836-44BC-8EEA-3C6AB22FD5E8', 'E3C8F7D6-5CEB-43BB-802F-66C3E734049E' ) )
    AND EXISTS (
        SELECT 1
        FROM [GroupMember] AS [kid]
        INNER JOIN [GroupMember] AS [adult] ON [adult].[GroupId] = [kid].[GroupId]
        INNER JOIN [GroupTypeRole] AS [r] ON [r].[Id] = [adult].[GroupRoleId]
        INNER JOIN [Person] AS [ap] ON [ap].[Id] = [adult].[PersonId]
        WHERE [kid].[PersonId] = [pa].[PersonId] AND [kid].[GroupTypeId] = [adult].[GroupTypeId]
            AND [r].[Guid] = '2639F9A5-2AAE-4E48-A8C3-4FFE86681E42'
            AND [ap].[IsDeceased] = 0 AND [ap].[RecordStatusValueId] = ( SELECT [Id] FROM [DefinedValue] WHERE [Guid] = '618F906C-C33D-4FA3-8AEF-E58CB7B63F1E' ) )
    AND NOT EXISTS (
        SELECT 1
        FROM [PersonAlias] AS [byAlias]
        INNER JOIN [GroupMember] AS [byMember] ON [byMember].[PersonId] = [byAlias].[PersonId]
        INNER JOIN [GroupTypeRole] AS [byRole] ON [byRole].[Id] = [byMember].[GroupRoleId]
        INNER JOIN [GroupMember] AS [kid] ON [kid].[GroupId] = [byMember].[GroupId] AND [kid].[PersonId] = [pa].[PersonId]
        INNER JOIN [PersonAlias] AS [wpa] ON [wpa].[PersonId] = [byAlias].[PersonId]
        INNER JOIN [Attendance] AS [w] ON [w].[PersonAliasId] = [wpa].[Id] AND [w].[DidAttend] = 1
        INNER JOIN [AttendanceOccurrence] AS [wo] ON [wo].[Id] = [w].[OccurrenceId] AND [wo].[SundayDate] = [o].[SundayDate]
        INNER JOIN [Group] AS [wg] ON [wg].[Id] = [wo].[GroupId]
        INNER JOIN [GroupType] AS [wgt] ON [wgt].[Id] = [wg].[GroupTypeId]
        WHERE [byAlias].[Id] = [a].[CheckedInByPersonAliasId]
            AND [byRole].[Guid] = '2639F9A5-2AAE-4E48-A8C3-4FFE86681E42'
            AND [wg].[Name] = 'Weekend Service' AND [wgt].[Guid] = '235BAE2B-5760-4763-AADF-3938F34BA100' );

-- check: onsite-gift-on-a-family-sunday
-- why: Checks and cash go in the offering only on a Sunday the family came (decision 146). A student who comes on their own does not make it a family Sunday, so every gift in a Sunday Offering batch falls on a Sunday when an adult of the giver's family attended.
SELECT COUNT(*)
FROM [FinancialTransaction] AS [t]
INNER JOIN [FinancialBatch] AS [b] ON [b].[Id] = [t].[BatchId]
INNER JOIN [PersonAlias] AS [gpa] ON [gpa].[Id] = [t].[AuthorizedPersonAliasId]
WHERE [t].[ForeignKey] = @ForeignKey AND [b].[Name] LIKE 'Sunday Offering %'
    AND NOT EXISTS (
        SELECT 1
        FROM [GroupMember] AS [giver]
        INNER JOIN [GroupMember] AS [adult] ON [adult].[GroupId] = [giver].[GroupId]
        INNER JOIN [GroupTypeRole] AS [r] ON [r].[Id] = [adult].[GroupRoleId]
        INNER JOIN [PersonAlias] AS [apa] ON [apa].[PersonId] = [adult].[PersonId]
        INNER JOIN [Attendance] AS [a] ON [a].[PersonAliasId] = [apa].[Id] AND [a].[DidAttend] = 1
        INNER JOIN [AttendanceOccurrence] AS [o] ON [o].[Id] = [a].[OccurrenceId]
        WHERE [giver].[PersonId] = [gpa].[PersonId] AND [giver].[GroupTypeId] = [adult].[GroupTypeId]
            AND [giver].[GroupTypeId] = ( SELECT [Id] FROM [GroupType] WHERE [Guid] = '790E3215-3B10-442B-AF69-616C0DCB998E' )
            AND [r].[Guid] = '2639F9A5-2AAE-4E48-A8C3-4FFE86681E42'
            AND [o].[SundayDate] = CAST( [t].[TransactionDateTime] AS DATE ) );

-- check: student-check-in-fits-the-grade
-- why: Only students in grades 7 to 12 check in to the student service, in the room for their grade on that day. A recent graduate's student check-ins end when they graduate (decision 148). The grade comes from the graduation year and moves up the day after each June 1, as Rock does (decision 205).
SELECT COUNT(*)
FROM (
    SELECT [g].[Name],
        12 - ( [p].[GraduationYear] - CASE WHEN CAST( [o].[OccurrenceDate] AS DATE ) > DATEFROMPARTS( YEAR( [o].[OccurrenceDate] ), 6, 1 ) THEN YEAR( [o].[OccurrenceDate] ) + 1 ELSE YEAR( [o].[OccurrenceDate] ) END ) AS [Grade]
    FROM [Attendance] AS [a]
    INNER JOIN [AttendanceOccurrence] AS [o] ON [o].[Id] = [a].[OccurrenceId]
    INNER JOIN [Group] AS [g] ON [g].[Id] = [o].[GroupId]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [a].[PersonAliasId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
    WHERE [a].[ForeignKey] = @ForeignKey AND [a].[DidAttend] = 1
        AND [g].[Name] IN ( 'Grades 7-8', 'Grades 9-12' ) AND [g].[GroupTypeId] IN ( SELECT [Id] FROM [GroupType] WHERE [Guid] IN ( '7A17235B-69AD-439B-BAB0-1A0A472DB96F', '9A88743B-F336-4404-B877-2A623689195D' ) )
) AS [x]
WHERE [x].[Grade] IS NULL
    OR ( [x].[Name] = 'Grades 7-8' AND [x].[Grade] NOT BETWEEN 7 AND 8 )
    OR ( [x].[Name] = 'Grades 9-12' AND [x].[Grade] NOT BETWEEN 9 AND 12 );

-- check: campus-account-dates-match-fund
-- why: Rock's giving pages hide an account outside its dates. A campus account with other dates than its fund would take gifts the fund says it cannot.
SELECT COUNT(*)
FROM [FinancialAccount] AS [child]
INNER JOIN [FinancialAccount] AS [fund] ON [fund].[Id] = [child].[ParentAccountId]
WHERE [child].[ForeignKey] = @ForeignKey AND [child].[CampusId] IS NOT NULL
    AND ( ISNULL( [child].[StartDate], '1900-01-01' ) <> ISNULL( [fund].[StartDate], '1900-01-01' )
        OR ISNULL( [child].[EndDate], '1900-01-01' ) <> ISNULL( [fund].[EndDate], '1900-01-01' ) );

-- check: pledge-is-on-a-top-level-fund
-- why: Rock's Pledge Analytics and statements count gifts to the pledged account and its child accounts. A pledge on a campus account would miss gifts to the fund's other campus accounts.
SELECT COUNT(*)
FROM [FinancialPledge] AS [fp]
INNER JOIN [FinancialAccount] AS [fa] ON [fa].[Id] = [fp].[AccountId]
WHERE [fp].[ForeignKey] = @ForeignKey AND [fa].[ParentAccountId] IS NOT NULL;

-- check: pledge-dates-fit-the-fund
-- why: Rock saves the date keys from the dates, and a pledge runs inside its fund's dates. Pledge Analytics and statements filter on both.
SELECT COUNT(*)
FROM [FinancialPledge] AS [fp]
INNER JOIN [FinancialAccount] AS [fa] ON [fa].[Id] = [fp].[AccountId]
WHERE [fp].[ForeignKey] = @ForeignKey
    AND ( [fp].[StartDate] > [fp].[EndDate]
        OR [fp].[StartDateKey] <> CONVERT( INT, FORMAT( [fp].[StartDate], 'yyyyMMdd' ) )
        OR [fp].[EndDateKey] <> CONVERT( INT, FORMAT( [fp].[EndDate], 'yyyyMMdd' ) )
        OR [fp].[StartDate] < ISNULL( [fa].[StartDate], [fp].[StartDate] )
        OR [fp].[EndDate] > ISNULL( [fa].[EndDate], [fp].[EndDate] ) );

-- check: one-pledge-per-giving-unit-and-fund
-- why: Pledge Analytics adds up pledges by giving unit. Two pledges to one fund from one unit would double the amount it shows as pledged.
SELECT COUNT(*)
FROM (
    SELECT [p].[GivingId], [fp].[AccountId]
    FROM [FinancialPledge] AS [fp]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [fp].[PersonAliasId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
    WHERE [fp].[ForeignKey] = @ForeignKey
    GROUP BY [p].[GivingId], [fp].[AccountId]
    HAVING COUNT(*) > 1
) AS [x];

-- check: gift-posts-to-campus-account
-- why: A fund with campus accounts takes gifts only through them, so campus giving reports add up. A gift to the fund itself would count toward no campus.
SELECT COUNT(*)
FROM [FinancialTransactionDetail] AS [ftd]
INNER JOIN [FinancialAccount] AS [fa] ON [fa].[Id] = [ftd].[AccountId]
WHERE [ftd].[ForeignKey] = @ForeignKey
    AND EXISTS ( SELECT 1 FROM [FinancialAccount] AS [child] WHERE [child].[ParentAccountId] = [fa].[Id] AND [child].[CampusId] IS NOT NULL );

-- check: communication-list-members-allow-mass-email
-- why: Decision 133. Nobody who chose "No mass emails" or "Do not email" is on a communication list, so a list send never reaches someone who asked not to get one.
SELECT COUNT(*)
FROM [GroupMember] AS [gm]
INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [gm].[PersonId]
WHERE [gt].[Guid] = 'D1D95777-FFA3-CBB3-4A6D-658706DAED33' AND [g].[ForeignKey] = @ForeignKey
    AND [gm].[GroupMemberStatus] = 1 AND [p].[EmailPreference] <> 0;

-- check: newsletter-members-belong-to-the-campus
-- why: A campus newsletter holds that campus's adults with an active email (decision 133), so a campus's news never goes to another campus. An email that bounced after the person joined carries Rock's bounce note.
SELECT COUNT(*)
FROM [GroupMember] AS [gm]
INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [gm].[PersonId]
WHERE [gt].[Guid] = 'D1D95777-FFA3-CBB3-4A6D-658706DAED33' AND [g].[ForeignKey] = @ForeignKey AND [g].[CampusId] IS NOT NULL
    AND ( [p].[PrimaryCampusId] <> [g].[CampusId] OR ISNULL( [p].[Email], '' ) = '' OR [p].[AgeClassification] <> 1
        OR ( [p].[IsEmailActive] = 0 AND ISNULL( [p].[EmailNote], '' ) NOT LIKE 'Email experienced a Hard Bounce%' ) );

-- check: volunteers-list-follows-the-teams
-- why: The Volunteers list is everyone active on a serving team (decision 92), so a message to volunteers reaches exactly the people who serve. A volunteer who left the list from one of its emails stays on it as Inactive.
WITH [Serving] AS (
    SELECT DISTINCT [gm].[PersonId]
    FROM [GroupMember] AS [gm]
    INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
    INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [gm].[PersonId]
    WHERE [gt].[Guid] = '2C42B2D4-1C5F-4AD5-A9AD-08631B872AC4' AND ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId ) AND [g].[IsActive] = 1 AND [g].[IsArchived] = 0
        AND [gm].[GroupMemberStatus] = 1 AND [gm].[IsArchived] = 0 AND [p].[EmailPreference] = 0
),
[Listed] AS (
    SELECT [gm].[PersonId]
    FROM [GroupMember] AS [gm]
    INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
    WHERE [g].[Guid] = '7C040D39-B8F0-481E-95B3-9526D566C47F' AND [g].[ForeignKey] = @ForeignKey
)
SELECT ( SELECT COUNT(*) FROM [Serving] AS [s] WHERE NOT EXISTS ( SELECT 1 FROM [Listed] AS [l] WHERE [l].[PersonId] = [s].[PersonId] ) )
    + ( SELECT COUNT(*) FROM [Listed] AS [l] WHERE NOT EXISTS ( SELECT 1 FROM [Serving] AS [s] WHERE [s].[PersonId] = [l].[PersonId] ) );

-- check: small-group-leaders-list-follows-the-groups
-- why: The Small Group Leaders list is everyone who leads an active small group (decision 92), so leader messages reach exactly the leaders. A leader who left the list from one of its emails stays on it as Inactive.
WITH [Leading] AS (
    SELECT DISTINCT [gm].[PersonId]
    FROM [GroupMember] AS [gm]
    INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
    INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
    INNER JOIN [GroupTypeRole] AS [r] ON [r].[Id] = [gm].[GroupRoleId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [gm].[PersonId]
    WHERE [gt].[Guid] = '50FCFB30-F51A-49DF-86F4-2B176EA1820B' AND ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId ) AND [g].[IsActive] = 1 AND [g].[IsArchived] = 0
        AND [gm].[GroupMemberStatus] = 1 AND [gm].[IsArchived] = 0 AND [r].[IsLeader] = 1 AND [p].[EmailPreference] = 0
),
[Listed] AS (
    SELECT [gm].[PersonId]
    FROM [GroupMember] AS [gm]
    INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
    WHERE [g].[Guid] = '3D755601-598D-4E8F-A13D-9DF0F0087CA9' AND [g].[ForeignKey] = @ForeignKey
)
SELECT ( SELECT COUNT(*) FROM [Leading] AS [x] WHERE NOT EXISTS ( SELECT 1 FROM [Listed] AS [l] WHERE [l].[PersonId] = [x].[PersonId] ) )
    + ( SELECT COUNT(*) FROM [Listed] AS [l] WHERE NOT EXISTS ( SELECT 1 FROM [Leading] AS [x] WHERE [x].[PersonId] = [l].[PersonId] ) );

-- check: group-sync-targets-a-sync-group
-- why: Decision 186. Rock's group detail shows the sync panel only on group types that allow group sync, and a sync's role must be one of its group's roles, so every sync opens and edits in Rock. Its data view is a person view, as Rock's sync requires.
SELECT COUNT(*)
FROM [GroupSync] AS [s]
INNER JOIN [Group] AS [g] ON [g].[Id] = [s].[GroupId]
INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
INNER JOIN [GroupTypeRole] AS [r] ON [r].[Id] = [s].[GroupTypeRoleId]
INNER JOIN [DataView] AS [dv] ON [dv].[Id] = [s].[SyncDataViewId]
WHERE [s].[ForeignKey] = @ForeignKey
    AND ( [gt].[AllowGroupSync] = 0 OR [r].[GroupTypeId] <> [g].[GroupTypeId]
        OR [dv].[EntityTypeId] <> ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Person' ) );

-- check: group-sync-refreshed-at-install
-- why: Decision 187. Each sync is written as the job leaves it after a run on install day, so Rock's group detail shows when it last ran and a sync with an interval waits for it.
SELECT COUNT(*)
FROM [GroupSync]
WHERE [ForeignKey] = @ForeignKey AND ( [LastRefreshDateTime] IS NULL OR [LastRefreshDateTime] < [CreatedDateTime] );

-- check: church-data-view-is-filed
-- why: Decision 185. Every data view the church adds sits in a child of the church's own Rock Solid Church category, so it never mixes with the views Rock ships, and later views have a place to go.
SELECT COUNT(*)
FROM [DataView] AS [dv]
LEFT JOIN [Category] AS [c] ON [c].[Id] = [dv].[CategoryId]
LEFT JOIN [Category] AS [parent] ON [parent].[Id] = [c].[ParentCategoryId]
WHERE [dv].[ForeignKey] = @ForeignKey
    AND ( [c].[Id] IS NULL OR ISNULL( [c].[ForeignKey], '' ) <> @ForeignKey OR ISNULL( [parent].[ForeignKey], '' ) <> @ForeignKey );

-- check: data-view-filter-tree-is-whole
-- why: Rock reads a view's filters from its root down. Every church view has a root filter, every filter belongs to its view, a group of filters names no component, and each single filter names its component and selection.
SELECT ( SELECT COUNT(*) FROM [DataView] WHERE [ForeignKey] = @ForeignKey AND [DataViewFilterId] IS NULL )
    + ( SELECT COUNT(*)
        FROM [DataViewFilter] AS [f]
        WHERE [f].[ForeignKey] = @ForeignKey
            AND ( [f].[DataViewId] IS NULL
                OR ( [f].[ExpressionType] = 0 AND ( [f].[EntityTypeId] IS NULL OR ISNULL( [f].[Selection], '' ) = '' ) )
                OR ( [f].[ExpressionType] <> 0 AND [f].[EntityTypeId] IS NOT NULL )
                OR ( [f].[ParentId] IS NULL AND NOT EXISTS ( SELECT 1 FROM [DataView] AS [dv] WHERE [dv].[DataViewFilterId] = [f].[Id] ) ) ) );

-- check: system-phone-numbers-are-reserved-and-unused
-- why: Content standard 2 and decision 93. The church's texting numbers are active, can text, use the reserved range, and are never a loaded person's number, so SMS Conversations never seems to text a member from their own phone.
SELECT COUNT(*)
FROM [SystemPhoneNumber] AS [spn]
WHERE [spn].[ForeignKey] = @ForeignKey
    AND ( [spn].[IsActive] = 0 OR [spn].[IsSmsEnabled] = 0 OR [spn].[IsSmsForwardingEnabled] = 1
        OR LEN( [spn].[Number] ) <> 12 OR LEFT( [spn].[Number], 2 ) <> '+1' OR SUBSTRING( [spn].[Number], 3, 3 ) NOT IN ( '602', '480', '623' ) OR SUBSTRING( [spn].[Number], 6, 5 ) <> '55501'
        OR EXISTS ( SELECT 1 FROM [PhoneNumber] AS [pn] INNER JOIN [Person] AS [p] ON [p].[Id] = [pn].[PersonId] WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId ) AND [pn].[Number] = SUBSTRING( [spn].[Number], 3, 10 ) ) );

-- check: communication-is-sent-and-never-sends-again
-- why: Decision 95. Rock's send job picks up any communication that is not Approved with a send time, so every sample communication is Approved, has its send time, and has the date key Rock fills from it. The one scheduled communication, which waits for a future send date (decision 248), is checked by scheduled-communication-waits-for-its-date.
SELECT COUNT(*)
FROM [Communication] AS [c]
WHERE [c].[ForeignKey] = @ForeignKey
    AND NOT ( [c].[SendDateTime] IS NULL AND [c].[FutureSendDateTime] > GETDATE() )
    AND ( [c].[Status] <> 3 OR [c].[SendDateTime] IS NULL OR [c].[SendDateKey] IS NULL
        OR [c].[SendDateKey] <> CONVERT( INT, CONVERT( CHAR(8), [c].[SendDateTime], 112 ) ) );

-- check: no-recipient-waits-to-send
-- why: Decision 95. A Pending or Sending recipient is one Rock's send job would still send to. Only the scheduled communication's recipients wait, as Pending (decision 248).
SELECT COUNT(*)
FROM [CommunicationRecipient] AS [cr]
INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
WHERE [cr].[ForeignKey] = @ForeignKey AND [cr].[Status] IN ( 0, 5 )
    AND NOT ( [cr].[Status] = 0 AND [c].[SendDateTime] IS NULL AND [c].[FutureSendDateTime] > GETDATE() );

-- check: scheduled-communication-waits-for-its-date
-- why: Decisions 220 and 248. A load writes at most one communication that sends after install. It is a bulk email, Approved and reviewed, with a future send date, no send date, and no communication list, so Rock's Send Communications job sends it on its date to exactly these people. Every recipient is Pending with no send times, and has an email on example.org or none, so it can never reach a real address.
SELECT CASE WHEN ( SELECT COUNT(*) FROM [Communication] AS [c] WHERE [c].[ForeignKey] = @ForeignKey AND [c].[SendDateTime] IS NULL ) > 1 THEN 1 ELSE 0 END
    + ( SELECT COUNT(*)
        FROM [Communication] AS [c]
        WHERE [c].[ForeignKey] = @ForeignKey AND [c].[SendDateTime] IS NULL
            AND ( [c].[Status] <> 3 OR [c].[CommunicationType] <> 1 OR [c].[IsBulkCommunication] = 0 OR [c].[ReviewerPersonAliasId] IS NULL OR [c].[ReviewedDateTime] IS NULL
                OR [c].[FutureSendDateTime] IS NULL OR [c].[FutureSendDateTime] <= GETDATE() OR [c].[SendDateKey] IS NOT NULL OR [c].[ListGroupId] IS NOT NULL
                OR NOT EXISTS ( SELECT 1 FROM [CommunicationRecipient] AS [cr] WHERE [cr].[CommunicationId] = [c].[Id] )
                OR EXISTS ( SELECT 1
                    FROM [CommunicationRecipient] AS [cr]
                    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [cr].[PersonAliasId]
                    INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
                    WHERE [cr].[CommunicationId] = [c].[Id]
                        AND ( [cr].[Status] <> 0 OR [cr].[SendDateTime] IS NOT NULL OR [cr].[DeliveredDateTime] IS NOT NULL
                            OR ( ISNULL( [p].[Email], '' ) <> '' AND [p].[Email] NOT LIKE '%@example.org' ) ) ) ) );

-- check: email-recipient-states-agree
-- why: Communication Detail counts and charts each status from these columns. A delivered or opened email was sent and confirmed, an opened one has its first open after the send, and a failed one says why. Only a bounce fails after Rock sent it.
SELECT COUNT(*)
FROM [CommunicationRecipient] AS [cr]
INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
WHERE [cr].[ForeignKey] = @ForeignKey AND [c].[CommunicationType] = 1
    AND ( ( [cr].[Status] IN ( 1, 4 ) AND ( [cr].[SendDateTime] IS NULL OR [cr].[DeliveredDateTime] IS NULL OR [cr].[DeliveredDateTime] < [cr].[SendDateTime] ) )
        OR ( [cr].[Status] = 4 AND ( [cr].[OpenedDateTime] IS NULL OR [cr].[OpenedDateTime] < [cr].[SendDateTime] OR [cr].[OpenedClient] IS NULL ) )
        OR ( [cr].[Status] <> 4 AND [cr].[OpenedDateTime] IS NOT NULL )
        OR ( [cr].[Status] = 2 AND ( [cr].[StatusNote] IS NULL OR ( [cr].[SendDateTime] IS NOT NULL AND [cr].[StatusNote] NOT LIKE '5__ 5.%' ) ) ) );

-- check: opens-and-clicks-follow-an-open
-- why: Rock records an open or a click as an interaction on the communication's component, pointing at the recipient. Only an opened recipient has them, the first is an open at the recipient's open time, and nothing comes before the send.
SELECT COUNT(*)
FROM [Interaction] AS [i]
INNER JOIN [InteractionComponent] AS [ic] ON [ic].[Id] = [i].[InteractionComponentId]
INNER JOIN [InteractionChannel] AS [ch] ON [ch].[Id] = [ic].[InteractionChannelId]
LEFT JOIN [CommunicationRecipient] AS [cr] ON [cr].[Id] = [i].[EntityId] AND [cr].[CommunicationId] = [ic].[EntityId]
WHERE [ch].[Guid] = 'C88A187F-0343-4E7C-AF3F-79A8989DFA65' AND [i].[ForeignKey] = @ForeignKey
    AND ( [cr].[Id] IS NULL OR [cr].[Status] <> 4 OR [i].[InteractionDateTime] < [cr].[OpenedDateTime] OR [i].[PersonAliasId] <> [cr].[PersonAliasId]
        OR ( [i].[Operation] = 'Click' AND ISNULL( [i].[InteractionData], '' ) NOT LIKE 'https://rocksolidchurchdemo.com/%' ) );

-- check: unsubscribe-matches-the-person
-- why: Decision 131. A list unsubscribe leaves the person Inactive in that list, a Bulk one leaves them on "No mass emails" or stricter, and Rock records a level with every unsubscribe time.
SELECT COUNT(*)
FROM [CommunicationRecipient] AS [cr]
INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [cr].[PersonAliasId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
WHERE [cr].[ForeignKey] = @ForeignKey
    AND ( ( [cr].[UnsubscribeDateTime] IS NULL AND [cr].[UnsubscribeLevel] IS NOT NULL ) OR ( [cr].[UnsubscribeDateTime] IS NOT NULL AND [cr].[UnsubscribeLevel] IS NULL )
        OR ( [cr].[UnsubscribeLevel] = 3 AND NOT EXISTS ( SELECT 1 FROM [GroupMember] AS [gm] WHERE [gm].[GroupId] = [c].[ListGroupId] AND [gm].[PersonId] = [p].[Id] AND [gm].[GroupMemberStatus] = 0 ) )
        OR ( [cr].[UnsubscribeLevel] = 2 AND [p].[EmailPreference] = 0 )
        OR ( [cr].[UnsubscribeLevel] = 4 AND NOT EXISTS ( SELECT 1 FROM [CommunicationFlowInstanceRecipient] AS [fr] WHERE [fr].[UnsubscribeCommunicationRecipientId] = [cr].[Id] AND [fr].[Status] = 2 AND [fr].[InactiveReason] = 1 ) ) );

-- check: flow-unsubscribe-leaves-the-flow
-- why: Decision 246. Leaving a flow from an email's link marks that run's row Unsubscribed, as Rock's job does, and every later run of the flow has the person Inactive as Unsubscribed From Flow, so no later send reaches them.
SELECT COUNT(*)
FROM [CommunicationFlowInstanceRecipient] AS [fr]
INNER JOIN [CommunicationRecipient] AS [cr] ON [cr].[Id] = [fr].[UnsubscribeCommunicationRecipientId]
INNER JOIN [CommunicationFlowInstance] AS [fi] ON [fi].[Id] = [fr].[CommunicationFlowInstanceId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [fr].[RecipientPersonAliasId]
WHERE [fr].[ForeignKey] = @ForeignKey
    AND ( [cr].[UnsubscribeLevel] <> 4 OR [fr].[Status] <> 2 OR [fr].[InactiveReason] <> 1
        OR EXISTS (
            SELECT 1
            FROM [CommunicationFlowInstanceRecipient] AS [later]
            INNER JOIN [CommunicationFlowInstance] AS [li] ON [li].[Id] = [later].[CommunicationFlowInstanceId]
            INNER JOIN [PersonAlias] AS [lpa] ON [lpa].[Id] = [later].[RecipientPersonAliasId]
            WHERE [li].[CommunicationFlowId] = [fi].[CommunicationFlowId] AND [lpa].[PersonId] = [pa].[PersonId] AND [li].[StartDate] > [cr].[UnsubscribeDateTime]
                AND NOT ( [later].[Status] = 2 AND [later].[InactiveReason] = 7 ) ) );

-- check: sent-email-has-its-history
-- why: Rock's email transport writes a "Sent" line on the person's History tab for every email it sends, so every sent email recipient has one.
SELECT ABS( ( SELECT COUNT(*) FROM [CommunicationRecipient] AS [cr] INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
        WHERE [cr].[ForeignKey] = @ForeignKey AND [c].[CommunicationType] = 1 AND [cr].[SendDateTime] IS NOT NULL )
    - ( SELECT COUNT(*) FROM [History] AS [h] WHERE [h].[ForeignKey] = @ForeignKey AND [h].[Verb] = 'SENT' AND [h].[ValueName] = 'Communication' ) );

-- check: serving-email-follows-a-request
-- why: Decision 159. Rock's Send Group Schedule Notifications job sends a confirmation the day a volunteer is asked to serve, and a reminder two days before a Sunday they did not decline. Each serving email has one recipient and the request it came from.
WITH [Serving] AS (
    SELECT [c].[Id], [c].[SendDateTime], [sc].[Guid] AS [Kind]
    FROM [Communication] AS [c]
    INNER JOIN [SystemCommunication] AS [sc] ON [sc].[Id] = [c].[SystemCommunicationId]
    WHERE [c].[ForeignKey] = @ForeignKey AND [sc].[Guid] IN ( 'BA1716E0-6B31-4E93-ABA1-42B3C81FDBDC', '8A20FE79-B73C-447A-82B1-416F9B50C038' )
), [Sent] AS (
    SELECT [s].[Kind], [pa].[PersonId], CONVERT( DATE, [s].[SendDateTime] ) AS [Day], COUNT(*) AS [Recipients]
    FROM [Serving] AS [s]
    INNER JOIN [CommunicationRecipient] AS [cr] ON [cr].[CommunicationId] = [s].[Id]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [cr].[PersonAliasId]
    GROUP BY [s].[Id], [s].[Kind], [pa].[PersonId], CONVERT( DATE, [s].[SendDateTime] )
), [Asked] AS (
    SELECT DISTINCT [pa].[PersonId], CONVERT( DATE, [a].[CreatedDateTime] ) AS [Day]
    FROM [Attendance] AS [a]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [a].[PersonAliasId]
    WHERE [a].[RequestedToAttend] = 1 AND [a].[ScheduleConfirmationSent] = 1
), [Reminded] AS (
    SELECT DISTINCT [pa].[PersonId], CONVERT( DATE, DATEADD( DAY, -2, [a].[StartDateTime] ) ) AS [Day]
    FROM [Attendance] AS [a]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [a].[PersonAliasId]
    WHERE [a].[RequestedToAttend] = 1 AND [a].[ScheduleReminderSent] = 1 AND ISNULL( [a].[RSVP], 3 ) <> 0
)
SELECT ( SELECT COUNT(*) FROM [Serving] WHERE NOT EXISTS ( SELECT 1 FROM [CommunicationRecipient] AS [cr] WHERE [cr].[CommunicationId] = [Serving].[Id] ) )
    + ( SELECT COUNT(*) FROM [Sent] AS [s]
        WHERE [s].[Recipients] <> 1
            OR ( [s].[Kind] = 'BA1716E0-6B31-4E93-ABA1-42B3C81FDBDC' AND NOT EXISTS ( SELECT 1 FROM [Asked] AS [q] WHERE [q].[PersonId] = [s].[PersonId] AND [q].[Day] = [s].[Day] ) )
            OR ( [s].[Kind] = '8A20FE79-B73C-447A-82B1-416F9B50C038' AND NOT EXISTS ( SELECT 1 FROM [Reminded] AS [r] WHERE [r].[PersonId] = [s].[PersonId] AND [r].[Day] = [s].[Day] ) ) );

-- check: serving-request-is-not-in-the-future
-- why: Decision 165. A request to serve, its answer, and its last change all happened by install day, even for a Sunday weeks ahead.
SELECT COUNT(*)
FROM [Attendance] AS [a]
WHERE [a].[ForeignKey] = @ForeignKey AND [a].[RequestedToAttend] = 1
    AND ( [a].[CreatedDateTime] > GETDATE() OR [a].[RSVPDateTime] > GETDATE() OR [a].[ModifiedDateTime] > GETDATE() );

-- check: serving-flag-has-its-email
-- why: Decision 165. Rock's scheduling job marks a confirmation or a reminder sent only when it emailed the volunteer, and it reminded only volunteers who confirmed. In the 2 years of communication history before install day, each flag has its email.
WITH [Window] AS (
    SELECT DATEADD( DAY, 1, DATEADD( YEAR, -2, CONVERT( DATE, GETDATE() ) ) ) AS [Start], CONVERT( DATE, GETDATE() ) AS [Today]
), [Emailed] AS (
    SELECT DISTINCT [sc].[Guid] AS [Kind], [pa].[PersonId], CONVERT( DATE, [c].[SendDateTime] ) AS [Day]
    FROM [Communication] AS [c]
    INNER JOIN [SystemCommunication] AS [sc] ON [sc].[Id] = [c].[SystemCommunicationId]
    INNER JOIN [CommunicationRecipient] AS [cr] ON [cr].[CommunicationId] = [c].[Id]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [cr].[PersonAliasId]
    WHERE [c].[ForeignKey] = @ForeignKey AND [sc].[Guid] IN ( 'BA1716E0-6B31-4E93-ABA1-42B3C81FDBDC', '8A20FE79-B73C-447A-82B1-416F9B50C038' )
), [Flagged] AS (
    SELECT [pa].[PersonId], [a].[RSVP], [a].[ScheduleConfirmationSent], [a].[ScheduleReminderSent], [a].[StartDateTime],
        CONVERT( DATE, [a].[CreatedDateTime] ) AS [AskedDay], CONVERT( DATE, DATEADD( DAY, -ISNULL( [gt].[ScheduleReminderEmailOffsetDays], 2 ), [a].[StartDateTime] ) ) AS [ReminderDay]
    FROM [Attendance] AS [a]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [a].[PersonAliasId]
    INNER JOIN [AttendanceOccurrence] AS [ao] ON [ao].[Id] = [a].[OccurrenceId]
    INNER JOIN [Group] AS [g] ON [g].[Id] = [ao].[GroupId]
    INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
    WHERE [a].[ForeignKey] = @ForeignKey AND [a].[RequestedToAttend] = 1 AND ( [a].[ScheduleConfirmationSent] = 1 OR [a].[ScheduleReminderSent] = 1 )
)
SELECT COUNT(*)
FROM [Flagged] AS [f]
CROSS JOIN [Window] AS [w]
WHERE ( [f].[ScheduleReminderSent] = 1 AND [f].[ReminderDay] < [w].[Today] AND ISNULL( [f].[RSVP], 3 ) <> 1 )
    OR ( [f].[ScheduleConfirmationSent] = 1 AND [f].[AskedDay] >= [w].[Start] AND [f].[AskedDay] < [w].[Today]
        AND NOT EXISTS ( SELECT 1 FROM [Emailed] AS [e] WHERE [e].[Kind] = 'BA1716E0-6B31-4E93-ABA1-42B3C81FDBDC' AND [e].[PersonId] = [f].[PersonId] AND [e].[Day] = [f].[AskedDay] ) )
    OR ( [f].[ScheduleReminderSent] = 1 AND [f].[ReminderDay] >= [w].[Start] AND [f].[ReminderDay] < [w].[Today]
        AND NOT EXISTS ( SELECT 1 FROM [Emailed] AS [e] WHERE [e].[Kind] = '8A20FE79-B73C-447A-82B1-416F9B50C038' AND [e].[PersonId] = [f].[PersonId] AND [e].[Day] = [f].[ReminderDay] ) );

-- check: serving-job-has-nothing-to-send
-- why: Decision 95. Rock's scheduling job emails any volunteer it can reach whose request for a coming Sunday is not yet marked confirmed, and reminds confirmed volunteers not yet reminded, so every such request is marked.
SELECT COUNT(*)
FROM [Attendance] AS [a]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [a].[PersonAliasId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
WHERE [a].[ForeignKey] = @ForeignKey AND [a].[RequestedToAttend] = 1 AND [a].[StartDateTime] >= CONVERT( DATE, GETDATE() )
    AND ISNULL( [p].[Email], '' ) <> '' AND [p].[IsEmailActive] = 1 AND [p].[EmailPreference] <> 2
    AND ( [a].[ScheduleConfirmationSent] = 0 OR ( [a].[ScheduleReminderSent] = 0 AND [a].[RSVP] = 1 ) );

-- check: one-to-one-email-skips-do-not-email
-- why: Rock sends a personal email to someone on "No mass emails", but never to someone on "Do not email". Every one-to-one email to a person on "Do not email" failed.
SELECT COUNT(*)
FROM [CommunicationRecipient] AS [cr]
INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [cr].[PersonAliasId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
WHERE [cr].[ForeignKey] = @ForeignKey AND [c].[CommunicationType] = 1 AND [c].[IsBulkCommunication] = 0 AND [p].[EmailPreference] = 2
    AND [cr].[Status] <> 2;

-- check: sent-push-is-delivered-with-its-history
-- why: Decision 161. Rock Mobile Push marks a sent push Delivered with its send and delivered times and writes a "Push Notification" line on the person's History. A person with no device that takes notifications fails with Rock's note.
SELECT ( SELECT COUNT(*)
        FROM [CommunicationRecipient] AS [cr]
        INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
        WHERE [cr].[ForeignKey] = @ForeignKey AND [c].[CommunicationType] = 3
            AND ( ( [cr].[Status] IN ( 1, 4 ) AND ( [cr].[SendDateTime] IS NULL OR [cr].[DeliveredDateTime] IS NULL OR [cr].[TransportEntityTypeName] <> 'Rock.Communication.Transport.RockMobilePush' ) )
                OR ( [cr].[Status] = 4 AND ( [cr].[OpenedDateTime] IS NULL OR [cr].[OpenedDateTime] < [cr].[SendDateTime] ) )
                OR ( [cr].[Status] = 2 AND ISNULL( [cr].[StatusNote], '' ) <> 'No Personal Devices with Messaging Enabled' )
                OR [cr].[Status] NOT IN ( 1, 2, 4 ) OR [c].[PushData] IS NULL ) )
    + ABS( ( SELECT COUNT(*) FROM [CommunicationRecipient] AS [cr] INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
            WHERE [cr].[ForeignKey] = @ForeignKey AND [c].[CommunicationType] = 3 AND [cr].[SendDateTime] IS NOT NULL )
        - ( SELECT COUNT(*) FROM [History] AS [h] WHERE [h].[ForeignKey] = @ForeignKey AND [h].[Verb] = 'SENT' AND [h].[ValueName] = 'Push Notification' ) );

-- check: push-goes-to-a-device-that-takes-notifications
-- why: Rock Mobile Push sends to every active device on the person with notifications on and a registration token. A delivered push had one when it was sent, and a failed one had none.
SELECT COUNT(*)
FROM [CommunicationRecipient] AS [cr]
INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
LEFT JOIN (
    SELECT [pd].[PersonAliasId], MIN( [pd].[CreatedDateTime] ) AS [FirstSeen]
    FROM [PersonalDevice] AS [pd]
    WHERE [pd].[IsActive] = 1 AND [pd].[NotificationsEnabled] = 1 AND ISNULL( [pd].[DeviceRegistrationId], '' ) <> ''
    GROUP BY [pd].[PersonAliasId]
) AS [d] ON [d].[PersonAliasId] = [cr].[PersonAliasId]
WHERE [cr].[ForeignKey] = @ForeignKey AND [c].[CommunicationType] = 3
    AND ( CASE WHEN [d].[FirstSeen] <= [c].[SendDateTime] THEN 1 ELSE 0 END ) <> ( CASE WHEN [cr].[Status] IN ( 1, 4 ) THEN 1 ELSE 0 END );

-- check: app-devices-never-reach-a-phone
-- why: Decision 95. Every sample device's registration token starts with "sample-data-", which Firebase never issues, so a push sent from a sample database can never reach a real phone.
SELECT COUNT(*)
FROM [PersonalDevice] AS [pd]
WHERE [pd].[ForeignKey] = @ForeignKey AND ISNULL( [pd].[DeviceRegistrationId], '' ) NOT LIKE 'sample-data-%';

-- check: open-flow-run-is-allowed
-- why: Decision 220, which retires decision 95's rule that nothing sends after install. A flow run may still be sending at install only in an active On-Demand or recurring flow, and its next message is due today or later, so Rock's Process Communication Flows job sends no backlog on its first run. Every message a load writes already went out.
SELECT ( SELECT COUNT(*)
        FROM [CommunicationFlowInstance] AS [i]
        INNER JOIN [CommunicationFlow] AS [f] ON [f].[Id] = [i].[CommunicationFlowId]
        OUTER APPLY ( SELECT TOP 1 [fc].[Order], [c].[SendDateTime]
            FROM [CommunicationFlowInstanceCommunication] AS [ic]
            INNER JOIN [CommunicationFlowCommunication] AS [fc] ON [fc].[Id] = [ic].[CommunicationFlowCommunicationId]
            INNER JOIN [Communication] AS [c] ON [c].[Id] = [ic].[CommunicationId]
            WHERE [ic].[CommunicationFlowInstanceId] = [i].[Id]
            ORDER BY [fc].[Order] DESC ) AS [last]
        OUTER APPLY ( SELECT TOP 1 [fc].[DaysToWait]
            FROM [CommunicationFlowCommunication] AS [fc]
            WHERE [fc].[CommunicationFlowId] = [f].[Id] AND [fc].[Order] > ISNULL( [last].[Order], -1 )
            ORDER BY [fc].[Order] ) AS [next]
        WHERE [i].[ForeignKey] = @ForeignKey AND [i].[IsMessagingCompleted] = 0
            AND ( [f].[IsActive] = 0 OR [f].[TriggerType] NOT IN ( 1, 2 ) OR [next].[DaysToWait] IS NULL
                OR DATEADD( DAY, [next].[DaysToWait], CAST( ISNULL( [last].[SendDateTime], [i].[StartDate] ) AS DATE ) ) < CAST( GETDATE() AS DATE ) ) )
    + ( SELECT COUNT(*)
        FROM [CommunicationFlowInstanceCommunication] AS [ic]
        INNER JOIN [Communication] AS [c] ON [c].[Id] = [ic].[CommunicationId]
        WHERE [ic].[ForeignKey] = @ForeignKey AND ( [c].[SendDateTime] IS NULL OR [c].[SendDateTime] > GETDATE() ) );

-- check: open-flow-run-reaches-only-reserved-contact-points
-- why: Decision 220. Rock's job sends a run's remaining messages after install, so everyone still Active in an open run has an email on example.org or none, and only phones in the reserved 555-0100 to 555-0199 range. Staff emails are on rocksolidchurchdemo.com, a real domain that receives mail.
SELECT COUNT(*)
FROM [CommunicationFlowInstanceRecipient] AS [r]
INNER JOIN [CommunicationFlowInstance] AS [i] ON [i].[Id] = [r].[CommunicationFlowInstanceId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [r].[RecipientPersonAliasId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
WHERE [i].[ForeignKey] = @ForeignKey AND [i].[IsMessagingCompleted] = 0 AND [r].[Status] = 1
    AND ( ( ISNULL( [p].[Email], '' ) <> '' AND [p].[Email] NOT LIKE '%@example.org' )
        OR EXISTS ( SELECT 1 FROM [PhoneNumber] AS [ph] WHERE [ph].[PersonId] = [p].[Id] AND [ph].[Number] NOT LIKE '___55501__' ) );

-- check: recurring-flow-audience-has-the-guard
-- why: Decision 220. Rock's job starts a recurring flow's next run on its schedule date after install, from its audience view, and a paused flow can be turned on with one click. So every recurring flow has a schedule and an audience view that includes Reserved or No Email directly, which keeps every real address, such as staff on rocksolidchurchdemo.com, out of every future run.
SELECT COUNT(*)
FROM [CommunicationFlow] AS [f]
WHERE [f].[ForeignKey] = @ForeignKey AND [f].[TriggerType] = 1
    AND ( [f].[ScheduleId] IS NULL OR [f].[TargetAudienceDataViewId] IS NULL
        OR NOT EXISTS ( SELECT 1
            FROM [DataViewFilter] AS [df]
            INNER JOIN [EntityType] AS [et] ON [et].[Id] = [df].[EntityTypeId]
            INNER JOIN [DataView] AS [guard] ON [guard].[Guid] = '7440C1BC-57B1-4B18-BAD5-40EDEFFF6779'
            WHERE [df].[DataViewId] = [f].[TargetAudienceDataViewId] AND [et].[Name] = 'Rock.Reporting.DataFilter.OtherDataViewFilter'
                AND [df].[Selection] LIKE '{"DataViewId":' + CAST( [guard].[Id] AS NVARCHAR( 20 ) ) + ',%' ) );

-- check: one-time-flow-is-finished
-- why: Decision 247. A one-time flow ran once, from its single-date schedule and its audience view, and that run finished long before install. Rock's job closes a one-time flow's messaging once its one run is done, so the flow shows Completed and the job never reads it again. That is why its audience view carries no guard.
SELECT COUNT(*)
FROM [CommunicationFlow] AS [f]
LEFT JOIN [Schedule] AS [s] ON [s].[Id] = [f].[ScheduleId]
WHERE [f].[ForeignKey] = @ForeignKey AND [f].[TriggerType] = 3
    AND ( [f].[IsMessagingClosed] = 0 OR [f].[IsConversionGoalTrackingClosed] = 0 OR [s].[Id] IS NULL OR [f].[TargetAudienceDataViewId] IS NULL
        OR [s].[EffectiveEndDate] <> [s].[EffectiveStartDate] OR ISNULL( [s].[iCalendarContent], '' ) LIKE '%RRULE%' OR ISNULL( [s].[iCalendarContent], '' ) LIKE '%RDATE%'
        OR ( SELECT COUNT(*) FROM [CommunicationFlowInstance] AS [i] WHERE [i].[CommunicationFlowId] = [f].[Id] ) <> 1
        OR EXISTS ( SELECT 1 FROM [CommunicationFlowInstance] AS [i]
            WHERE [i].[CommunicationFlowId] = [f].[Id]
                AND ( [i].[IsMessagingCompleted] = 0 OR [i].[IsConversionGoalTrackingCompleted] = 0 OR [i].[StartDate] <> [s].[EffectiveStartDate] OR [i].[StartDate] >= CAST( GETDATE() AS DATE ) ) ) );

-- check: flow-send-reaches-its-instance
-- why: Decision 96, decision 220, and decision 246. The job writes each flow message as a bulk email, text, or push from its message's flow template, to people in the run. With the exit rule "last message sent" it reaches everyone still Active in the run. A person who left, by any exit rule or by unsubscribing, gets no later message.
SELECT COUNT(*)
FROM [CommunicationFlowInstanceCommunication] AS [ic]
INNER JOIN [Communication] AS [c] ON [c].[Id] = [ic].[CommunicationId]
INNER JOIN [CommunicationFlowCommunication] AS [fc] ON [fc].[Id] = [ic].[CommunicationFlowCommunicationId]
INNER JOIN [CommunicationFlow] AS [f] ON [f].[Id] = [fc].[CommunicationFlowId]
INNER JOIN [CommunicationTemplate] AS [t] ON [t].[Id] = [fc].[CommunicationTemplateId]
WHERE [ic].[ForeignKey] = @ForeignKey
    AND ( [c].[CommunicationType] <> [fc].[CommunicationType] OR [c].[IsBulkCommunication] = 0 OR ISNULL( [c].[CommunicationTemplateId], 0 ) <> [t].[Id] OR [t].[UsageType] <> 1 OR [t].[Version] <> 1
        OR ( [fc].[CommunicationType] = 2 AND ( [t].[SmsFromSystemPhoneNumberId] IS NULL OR ISNULL( [c].[SmsFromSystemPhoneNumberId], 0 ) <> [t].[SmsFromSystemPhoneNumberId] ) )
        OR ( [f].[ExitConditionType] = 0 AND EXISTS ( SELECT 1 FROM [CommunicationFlowInstanceRecipient] AS [r]
            WHERE [r].[CommunicationFlowInstanceId] = [ic].[CommunicationFlowInstanceId] AND [r].[Status] = 1
                AND NOT EXISTS ( SELECT 1 FROM [CommunicationRecipient] AS [cr] WHERE [cr].[CommunicationId] = [c].[Id] AND [cr].[PersonAliasId] = [r].[RecipientPersonAliasId] ) ) )
        OR EXISTS ( SELECT 1 FROM [CommunicationRecipient] AS [cr]
            WHERE [cr].[CommunicationId] = [c].[Id]
                AND NOT EXISTS ( SELECT 1 FROM [CommunicationFlowInstanceRecipient] AS [r] WHERE [r].[CommunicationFlowInstanceId] = [ic].[CommunicationFlowInstanceId] AND [r].[RecipientPersonAliasId] = [cr].[PersonAliasId] ) )
        OR EXISTS ( SELECT 1 FROM [CommunicationRecipient] AS [cr]
            INNER JOIN [CommunicationFlowInstanceRecipient] AS [r] ON [r].[CommunicationFlowInstanceId] = [ic].[CommunicationFlowInstanceId] AND [r].[RecipientPersonAliasId] = [cr].[PersonAliasId]
            WHERE [cr].[CommunicationId] = [c].[Id] AND [r].[Status] = 2 AND ( [r].[ModifiedDateTime] < CAST( [c].[SendDateTime] AS DATE ) OR ( [r].[InactiveReason] = 7 AND [r].[ModifiedDateTime] < [c].[SendDateTime] ) ) ) );

-- check: flow-form-entry-has-its-workflow
-- why: Decision 235. The only way into an On-Demand flow is Rock's Activate Communication Flow workflow action. In a flow a workflow type activates (its Communication Flow attribute names the flow), every person in a run started that workflow themselves and it finished on the run's start date.
SELECT COUNT(*)
FROM [CommunicationFlowInstanceRecipient] AS [r]
INNER JOIN [CommunicationFlowInstance] AS [i] ON [i].[Id] = [r].[CommunicationFlowInstanceId]
INNER JOIN [CommunicationFlow] AS [f] ON [f].[Id] = [i].[CommunicationFlowId]
INNER JOIN [Attribute] AS [a] ON [a].[EntityTypeQualifierColumn] = 'WorkflowTypeId' AND [a].[Key] = 'CommunicationFlow' AND TRY_CAST( CASE WHEN LEN( [a].[DefaultValue] ) = 36 THEN [a].[DefaultValue] END AS UNIQUEIDENTIFIER ) = [f].[Guid]
INNER JOIN [PersonAlias] AS [rpa] ON [rpa].[Id] = [r].[RecipientPersonAliasId]
WHERE [i].[ForeignKey] = @ForeignKey
    AND NOT EXISTS ( SELECT 1
        FROM [Workflow] AS [w]
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [w].[InitiatorPersonAliasId]
        WHERE [w].[WorkflowTypeId] = TRY_CAST( [a].[EntityTypeQualifierValue] AS INT ) AND [pa].[PersonId] = [rpa].[PersonId]
            AND [w].[Status] = 'Completed' AND CAST( [w].[CompletedDateTime] AS DATE ) = [i].[StartDate] );

-- check: workflow-is-finished-as-rock-leaves-it
-- why: Decision 235. Each sample workflow is persisted and Completed, by the person who started it, with one completed activity, every action of that activity completed, and the person in its Person attribute, as Rock leaves a finished form workflow.
SELECT COUNT(*)
FROM [Workflow] AS [w]
WHERE [w].[ForeignKey] = @ForeignKey
    AND ( [w].[Status] <> 'Completed' OR [w].[CompletedDateTime] IS NULL OR [w].[InitiatorPersonAliasId] IS NULL OR [w].[CompletedDateTime] > GETDATE()
        OR ( SELECT COUNT(*) FROM [WorkflowActivity] AS [act] WHERE [act].[WorkflowId] = [w].[Id] AND [act].[CompletedDateTime] IS NOT NULL ) <> 1
        OR EXISTS ( SELECT 1 FROM [WorkflowActivity] AS [act]
            INNER JOIN [WorkflowActionType] AS [at] ON [at].[ActivityTypeId] = [act].[ActivityTypeId]
            WHERE [act].[WorkflowId] = [w].[Id]
                AND NOT EXISTS ( SELECT 1 FROM [WorkflowAction] AS [ac] WHERE [ac].[ActivityId] = [act].[Id] AND [ac].[ActionTypeId] = [at].[Id] AND [ac].[CompletedDateTime] IS NOT NULL ) )
        OR NOT EXISTS ( SELECT 1 FROM [AttributeValue] AS [av]
            INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId]
            INNER JOIN [PersonAlias] AS [pa] ON [pa].[Guid] = TRY_CAST( CASE WHEN LEN( [av].[Value] ) = 36 THEN [av].[Value] END AS UNIQUEIDENTIFIER )
            WHERE [av].[EntityId] = [w].[Id] AND [a].[Key] = 'Person' AND [a].[EntityTypeQualifierColumn] = 'WorkflowTypeId' AND [a].[EntityTypeQualifierValue] = CAST( [w].[WorkflowTypeId] AS NVARCHAR( 50 ) )
                AND [pa].[Id] = [w].[InitiatorPersonAliasId] ) );

-- check: sent-text-is-delivered-with-its-text
-- why: SMS Conversations shows an outbound text only when its recipient is Delivered, and reads its words only from SentMessage (decision 93). A text Rock could not send failed with Rock's note and has no send time.
SELECT COUNT(*)
FROM [CommunicationRecipient] AS [cr]
INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
WHERE [cr].[ForeignKey] = @ForeignKey AND [c].[CommunicationType] = 2
    AND ( ( [cr].[SendDateTime] IS NOT NULL AND ( [cr].[Status] <> 1 OR ISNULL( [cr].[SentMessage], '' ) = '' OR [cr].[DeliveredDateTime] IS NULL OR [c].[SmsFromSystemPhoneNumberId] IS NULL ) )
        OR ( [cr].[SendDateTime] IS NULL AND ( [cr].[Status] <> 2 OR [cr].[StatusNote] IS NULL ) ) );

-- check: sent-text-has-its-history
-- why: Rock's Twilio transport writes a "SMS message" line on the person's History tab for every text it sends.
SELECT ABS( ( SELECT COUNT(*) FROM [CommunicationRecipient] AS [cr] INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
        WHERE [cr].[ForeignKey] = @ForeignKey AND [c].[CommunicationType] = 2 AND [cr].[SendDateTime] IS NOT NULL )
    - ( SELECT COUNT(*) FROM [History] AS [h] WHERE [h].[ForeignKey] = @ForeignKey AND [h].[Verb] = 'SENT' AND [h].[ValueName] = 'SMS message' ) );

-- check: texted-stop-turns-off-texting
-- why: Decision 131. A texted STOP marks the person's newest text as a level All unsubscribe and turns off texting on their mobile, and Rock records the opt-out on the phone. A phone opted out in the sample data came from a STOP.
SELECT ( SELECT COUNT(*)
        FROM [CommunicationRecipient] AS [cr]
        INNER JOIN [Communication] AS [c] ON [c].[Id] = [cr].[CommunicationId]
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [cr].[PersonAliasId]
        WHERE [cr].[ForeignKey] = @ForeignKey AND [c].[CommunicationType] = 2 AND [cr].[UnsubscribeLevel] IS NOT NULL
            AND ( [cr].[UnsubscribeLevel] <> 1 OR NOT EXISTS (
                SELECT 1 FROM [PhoneNumber] AS [pn] WHERE [pn].[PersonId] = [pa].[PersonId] AND [pn].[IsMessagingOptedOut] = 1 AND [pn].[IsMessagingEnabled] = 0 AND [pn].[MessagingOptedOutDateTime] IS NOT NULL ) ) )
    + ( SELECT COUNT(*)
        FROM [PhoneNumber] AS [pn]
        INNER JOIN [Person] AS [p] ON [p].[Id] = [pn].[PersonId]
        WHERE ( [p].[ForeignKey] = @ForeignKey OR [p].[Id] = @AdminPersonId ) AND [pn].[IsMessagingOptedOut] = 1
            AND NOT EXISTS ( SELECT 1 FROM [CommunicationResponse] AS [r] INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [r].[FromPersonAliasId]
                WHERE [pa].[PersonId] = [p].[Id] AND [r].[Response] = 'STOP' AND [r].[ForeignKey] = @ForeignKey ) );

-- check: text-reply-comes-from-a-known-phone
-- why: SMS Conversations groups a person's texts by their number, so every text a person sent comes from their own mobile, to one of the church's active texting numbers.
SELECT COUNT(*)
FROM [CommunicationResponse] AS [r]
LEFT JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [r].[FromPersonAliasId]
LEFT JOIN [SystemPhoneNumber] AS [spn] ON [spn].[Id] = [r].[RelatedSmsFromSystemPhoneNumberId]
WHERE [r].[ForeignKey] = @ForeignKey
    AND ( [pa].[Id] IS NULL OR [spn].[Id] IS NULL OR [spn].[IsActive] = 0
        OR NOT EXISTS ( SELECT 1 FROM [PhoneNumber] AS [pn] WHERE [pn].[PersonId] = [pa].[PersonId] AND '1' + [pn].[Number] = [r].[MessageKey] ) );

-- check: open-requests-are-mostly-not-past-due
-- why: A working team's board shows most open requests not due yet (decision 173). Dating due dates from the request again would put nearly every active request past due, so past due stays under a third of them. It is judged only with 50 or more active requests: Core 1 and Core 3 have few, mostly the Cast's, and three Cast stories are past due on purpose.
SELECT CASE WHEN COUNT(*) >= 50 AND SUM( CASE WHEN CAST( [DueDate] AS DATE ) < CAST( GETDATE() AS DATE ) THEN 1 ELSE 0 END ) * 3 > COUNT(*) THEN 1 ELSE 0 END
FROM [ConnectionRequest]
WHERE [ForeignKey] = @ForeignKey AND [ConnectionState] = 0;

-- check: open-request-is-due-within-its-status-time
-- why: An open request's due date runs from its last status change, which is never after install day, so it is never due later than its status's days from today (decision 173).
SELECT COUNT(*)
FROM [ConnectionRequest] AS [cr]
INNER JOIN [ConnectionStatus] AS [cs] ON [cs].[Id] = [cr].[ConnectionStatusId]
WHERE [cr].[ForeignKey] = @ForeignKey AND [cr].[ConnectionState] = 0 AND [cs].[RequestStatusDueDateOffsetInDays] IS NOT NULL
    AND CAST( [cr].[DueDate] AS DATE ) > DATEADD( DAY, [cs].[RequestStatusDueDateOffsetInDays], CAST( GETDATE() AS DATE ) );

-- check: celebration-is-on-a-worked-request
-- why: Decision 219. A celebration is a Celebration Note the request's connector wrote after reaching the person: on a request of this dataset whose connection type turns celebrations on, with text (the Celebrations Report hides an empty one), by the connector, after the request was made, on an active or connected request past No Contact, the status of a request nobody has reached. The Connections Hub shows only a request's first celebration, so each request has at most one.
SELECT COUNT(*)
FROM [Note] AS [n]
INNER JOIN [NoteType] AS [nt] ON [nt].[Id] = [n].[NoteTypeId]
LEFT JOIN [ConnectionRequest] AS [cr] ON [cr].[Id] = [n].[EntityId]
LEFT JOIN [ConnectionStatus] AS [cs] ON [cs].[Id] = [cr].[ConnectionStatusId]
LEFT JOIN [ConnectionType] AS [ct] ON [ct].[Id] = [cr].[ConnectionTypeId]
WHERE [n].[ForeignKey] = @ForeignKey AND [nt].[Guid] = 'A480E95A-F941-473E-B200-50D2658C9417'
    AND ( [cr].[Id] IS NULL OR ISNULL( [cr].[ForeignKey], '' ) <> @ForeignKey
        OR [ct].[EnabledFeatures] & 2 <> 2
        OR LTRIM( RTRIM( ISNULL( [n].[Text], '' ) ) ) = ''
        OR [n].[CreatedByPersonAliasId] IS NULL OR [n].[CreatedByPersonAliasId] <> ISNULL( [cr].[ConnectorPersonAliasId], -1 )
        OR [n].[CreatedDateTime] < [cr].[CreatedDateTime]
        OR [cs].[IsDefault] = 1
        OR [cr].[ConnectionState] NOT IN ( 0, 3 )
        OR EXISTS ( SELECT 1 FROM [Note] AS [other] WHERE [other].[NoteTypeId] = [n].[NoteTypeId] AND [other].[EntityId] = [n].[EntityId] AND [other].[Id] < [n].[Id] ) );

-- check: event-occurrence-is-listed
-- why: Rock's calendar pages and the app list only active, approved events on a calendar. An occurrence of any other event never shows (decision 174).
SELECT COUNT(*)
FROM [EventItemOccurrence] AS [o]
INNER JOIN [EventItem] AS [e] ON [e].[Id] = [o].[EventItemId]
WHERE [o].[ForeignKey] = @ForeignKey
    AND ( [e].[IsActive] = 0 OR [e].[IsApproved] = 0
        OR NOT EXISTS ( SELECT 1 FROM [EventCalendarItem] AS [ci] WHERE [ci].[EventItemId] = [e].[Id] )
        OR NOT EXISTS ( SELECT 1 FROM [EventItemAudience] AS [a] WHERE [a].[EventItemId] = [e].[Id] ) );

-- check: event-occurrence-has-a-schedule
-- why: Rock builds an occurrence's dates from its schedule's iCalendar text, and its save hook sets the effective start. Without them the event has no dates.
SELECT COUNT(*)
FROM [EventItemOccurrence] AS [o]
LEFT JOIN [Schedule] AS [s] ON [s].[Id] = [o].[ScheduleId]
WHERE [o].[ForeignKey] = @ForeignKey
    AND ( [s].[Id] IS NULL OR [s].[IsActive] = 0 OR ISNULL( [s].[iCalendarContent], '' ) = '' OR [s].[EffectiveStartDate] IS NULL );

-- check: event-occurrence-contact-is-a-person-with-church-email
-- why: The event page shows its contact's name, phone, and email. Content standard 3: the email is the sample church's own domain.
SELECT COUNT(*)
FROM [EventItemOccurrence] AS [o]
LEFT JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [o].[ContactPersonAliasId]
WHERE [o].[ForeignKey] = @ForeignKey
    AND ( [pa].[Id] IS NULL OR [o].[ContactEmail] NOT LIKE '%@rocksolidchurchdemo.com' OR [o].[ContactPhone] NOT LIKE '(%) 555-01__' );

-- check: event-photo-is-a-stored-file
-- why: An event's photo shows on its web and app pages. The id must point at a file whose bytes are in the database.
SELECT COUNT(*)
FROM [EventItem] AS [e]
LEFT JOIN [BinaryFile] AS [f] ON [f].[Id] = [e].[PhotoId]
WHERE [e].[PhotoId] IS NOT NULL
    AND EXISTS ( SELECT 1 FROM [EventItemOccurrence] AS [o] WHERE [o].[EventItemId] = [e].[Id] AND [o].[ForeignKey] = @ForeignKey )
    AND ( [f].[Id] IS NULL OR NOT EXISTS ( SELECT 1 FROM [BinaryFileData] AS [d] WHERE [d].[Id] = [f].[Id] AND DATALENGTH( [d].[Content] ) > 0 ) );

-- check: dataset-has-logins
-- why: Decision 179. The verify tool always loads with a test password, so a load with no logins means logins broke, and every login rule below would pass on nothing.
SELECT CASE WHEN EXISTS ( SELECT 1 FROM [UserLogin] WHERE [ForeignKey] = @ForeignKey ) THEN 0 ELSE 1 END;

-- check: login-is-a-database-login-rock-can-check
-- why: Decision 180. Rock's Database login reads a BCrypt hash ($2a$, $2b$, or $2y$), and a demo person must sign in on the first try: not locked out, and not sent to change their password.
SELECT COUNT(*)
FROM [UserLogin] AS [u]
LEFT JOIN [EntityType] AS [et] ON [et].[Id] = [u].[EntityTypeId]
WHERE [u].[ForeignKey] = @ForeignKey
    AND ( [et].[Name] IS NULL OR [et].[Name] <> 'Rock.Security.Authentication.Database'
        OR [u].[Password] IS NULL OR LEFT( [u].[Password], 4 ) NOT IN ( '$2a$', '$2b$', '$2y$' ) OR LEN( [u].[Password] ) <> 60
        OR [u].[PersonId] IS NULL OR [u].[IsPasswordChangeRequired] = 1 OR [u].[IsLockedOut] = 1 OR [u].[IsConfirmed] IS NULL
        OR ( [u].[LastLoginDateTime] IS NOT NULL AND ( [u].[LastLoginDateTime] < [u].[CreatedDateTime] OR [u].[IsConfirmed] = 0 ) ) );

-- check: login-username-is-plain
-- why: Decision 179. Usernames follow Rock's rule (first initial and last name) in lowercase letters and digits only, and the sample data never takes Rock's own admin.
SELECT COUNT(*)
FROM [UserLogin] AS [u]
WHERE [u].[ForeignKey] = @ForeignKey
    AND ( [u].[UserName] LIKE '%[^a-z0-9]%' COLLATE Latin1_General_BIN OR [u].[UserName] = 'admin' );

-- check: login-belongs-to-an-adult
-- why: Decision 179. Only adults 18 and older have logins, never a child or a business.
SELECT COUNT(*)
FROM [UserLogin] AS [u]
INNER JOIN [Person] AS [p] ON [p].[Id] = [u].[PersonId]
WHERE [u].[ForeignKey] = @ForeignKey
    AND ( [p].[AgeClassification] <> 1 OR [p].[Age] < 18 OR [p].[RecordTypeValueId] <> ( SELECT [Id] FROM [DefinedValue] WHERE [Guid] = '36CF10D6-C695-413D-8E7C-4546EFEF385E' ) );

-- check: security-role-member-can-sign-in
-- why: Decision 79 and 179. Staff can sign in and see what their roles allow, so everyone the load puts in a security role has a login.
SELECT COUNT( DISTINCT [gm].[PersonId] )
FROM [GroupMember] AS [gm]
INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
WHERE [gm].[ForeignKey] = @ForeignKey AND [g].[IsSecurityRole] = 1 AND [gm].[IsArchived] = 0
    AND NOT EXISTS ( SELECT 1 FROM [UserLogin] AS [u] WHERE [u].[PersonId] = [gm].[PersonId] );

-- check: app-user-can-sign-in
-- why: Decision 179. A church app device tied to a person means they signed in to the app.
SELECT COUNT( DISTINCT [pa].[PersonId] )
FROM [PersonalDevice] AS [pd]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [pd].[PersonAliasId]
WHERE [pd].[ForeignKey] = @ForeignKey
    AND NOT EXISTS ( SELECT 1 FROM [UserLogin] AS [u] WHERE [u].[PersonId] = [pa].[PersonId] );

-- check: login-has-its-history
-- why: Decision 181. Rock's login save hook records each new login on the person's History tab, so every sample login has its "User Login" row.
SELECT COUNT(*)
FROM [UserLogin] AS [u]
WHERE [u].[ForeignKey] = @ForeignKey
    AND NOT EXISTS ( SELECT 1 FROM [History] AS [h]
        WHERE [h].[EntityId] = [u].[PersonId] AND [h].[RelatedEntityId] = [u].[Id] AND [h].[ValueName] = 'User Login' AND [h].[NewValue] = [u].[UserName] AND [h].[ForeignKey] = @ForeignKey );

-- check: account-protection-matches-rock
-- why: Decision 182. Rock's Process Elevated Security job sets each person's account protection profile: Extreme in a security role marked Extreme, High in one marked High or with an active scheduled gift or saved account, Medium with a login, and Low otherwise.
SELECT COUNT(*)
FROM [Person] AS [p]
WHERE [p].[ForeignKey] = @ForeignKey
    AND [p].[AccountProtectionProfile] <> CASE
        WHEN EXISTS ( SELECT 1 FROM [GroupMember] AS [gm] INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId] INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
            WHERE [gm].[PersonId] = [p].[Id] AND [gm].[IsArchived] = 0 AND [g].[IsActive] = 1 AND ( [g].[IsSecurityRole] = 1 OR [gt].[Guid] = 'AECE949F-704C-483E-A4FB-93D5E4720C4C' ) AND [g].[ElevatedSecurityLevel] = 2 ) THEN 3
        WHEN EXISTS ( SELECT 1 FROM [GroupMember] AS [gm] INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId] INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
                WHERE [gm].[PersonId] = [p].[Id] AND [gm].[IsArchived] = 0 AND [g].[IsActive] = 1 AND ( [g].[IsSecurityRole] = 1 OR [gt].[Guid] = 'AECE949F-704C-483E-A4FB-93D5E4720C4C' ) AND [g].[ElevatedSecurityLevel] = 1 )
            OR EXISTS ( SELECT 1 FROM [FinancialScheduledTransaction] AS [s] INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[AuthorizedPersonAliasId] WHERE [pa].[PersonId] = [p].[Id] AND [s].[IsActive] = 1 )
            OR EXISTS ( SELECT 1 FROM [FinancialPersonSavedAccount] AS [sa] INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [sa].[PersonAliasId] WHERE [pa].[PersonId] = [p].[Id] ) THEN 2
        WHEN EXISTS ( SELECT 1 FROM [UserLogin] AS [u] WHERE [u].[PersonId] = [p].[Id] ) THEN 1
        ELSE 0 END;

-- check: registration-linkage-points-at-a-listed-occurrence
-- why: Decision 189. Rock's event page shows a registration through its linkage to an occurrence. An occurrence of an inactive, unapproved, or uncalendared event never shows, so neither does its Register button.
SELECT COUNT(*)
FROM [EventItemOccurrenceGroupMap] AS [m]
LEFT JOIN [EventItemOccurrence] AS [o] ON [o].[Id] = [m].[EventItemOccurrenceId]
LEFT JOIN [EventItem] AS [e] ON [e].[Id] = [o].[EventItemId]
WHERE [m].[ForeignKey] = @ForeignKey
    AND ( [e].[Id] IS NULL OR [e].[IsActive] = 0 OR [e].[IsApproved] = 0
        OR NOT EXISTS ( SELECT 1 FROM [EventCalendarItem] AS [ci] WHERE [ci].[EventItemId] = [e].[Id] ) );

-- check: registration-linkage-campus-matches-occurrence
-- why: A linkage names the campus of the occurrence it links, as Rock's linkage screen sets it. A church-wide occurrence has no campus.
SELECT COUNT(*)
FROM [EventItemOccurrenceGroupMap] AS [m]
INNER JOIN [EventItemOccurrence] AS [o] ON [o].[Id] = [m].[EventItemOccurrenceId]
WHERE [m].[ForeignKey] = @ForeignKey
    AND ISNULL( [m].[CampusId], 0 ) <> ISNULL( [o].[CampusId], 0 );

-- check: registration-linkage-instance-and-template-are-active
-- why: Rock offers registration only for an active instance of an active template. Every linkage names an instance.
SELECT COUNT(*)
FROM [EventItemOccurrenceGroupMap] AS [m]
LEFT JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [m].[RegistrationInstanceId]
LEFT JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [i].[RegistrationTemplateId]
WHERE [m].[ForeignKey] = @ForeignKey
    AND ( [i].[Id] IS NULL OR [i].[IsActive] = 0 OR [t].[IsActive] = 0 );

-- check: registration-slug-matches-rocks-pattern
-- why: Rock's linkage screen accepts only lowercase letters and digits in words joined by single hyphens (^[a-z0-9]+(?:-[a-z0-9]+)*$). The event page finds a registration by its slug, and Rock checks the pattern only in its screens.
SELECT COUNT(*)
FROM [EventItemOccurrenceGroupMap] AS [m]
WHERE [m].[ForeignKey] = @ForeignKey
    AND ( ISNULL( [m].[UrlSlug], '' ) = ''
        OR [m].[UrlSlug] COLLATE Latin1_General_BIN LIKE '%[^a-z0-9-]%'
        OR [m].[UrlSlug] LIKE '-%' OR [m].[UrlSlug] LIKE '%-' OR [m].[UrlSlug] LIKE '%--%' );

-- check: registration-slug-is-unique
-- why: The event page opens the one linkage whose slug is in the address. Rock checks uniqueness only in its screens.
SELECT COUNT(*)
FROM [EventItemOccurrenceGroupMap] AS [m]
WHERE [m].[ForeignKey] = @ForeignKey
    AND ( SELECT COUNT(*) FROM [EventItemOccurrenceGroupMap] AS [other] WHERE [other].[UrlSlug] = [m].[UrlSlug] ) > 1;

-- check: registration-dates-are-in-order
-- why: An instance opens before it closes, and its event starts after registration closes and on or after the payment deadline. A deadline after the event would leave balances due after everyone went home.
SELECT COUNT(*)
FROM [RegistrationInstance] AS [i]
WHERE [i].[ForeignKey] = @ForeignKey
    AND ( [i].[StartDateTime] IS NULL OR [i].[EndDateTime] IS NULL OR [i].[StartDateTime] >= [i].[EndDateTime]
        OR NOT EXISTS (
            SELECT 1
            FROM [EventItemOccurrenceGroupMap] AS [m]
            INNER JOIN [EventItemOccurrence] AS [o] ON [o].[Id] = [m].[EventItemOccurrenceId]
            INNER JOIN [ScheduleDate] AS [sd] ON [sd].[ScheduleId] = [o].[ScheduleId]
            WHERE [m].[RegistrationInstanceId] = [i].[Id] AND [sd].[StartDateTime] > [i].[EndDateTime]
                AND CAST( [sd].[StartDateTime] AS DATE ) >= ISNULL( [i].[PaymentDeadlineDate], CAST( [i].[EndDateTime] AS DATE ) ) ) );

-- check: placement-group-matches-its-placement
-- why: Rock's placement screen lists an instance's groups for a placement by the RelatedEntity's qualifier, the template placement's Id, and places people only in groups of that placement's group type. A group of another type, or a placement of another template, would show in the wrong column or not at all.
SELECT COUNT(*)
FROM [RelatedEntity] AS [r]
INNER JOIN [EntityType] AS [se] ON [se].[Id] = [r].[SourceEntityTypeId]
INNER JOIN [EntityType] AS [te] ON [te].[Id] = [r].[TargetEntityTypeId]
LEFT JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[SourceEntityId]
LEFT JOIN [Group] AS [g] ON [g].[Id] = [r].[TargetEntityId]
LEFT JOIN [RegistrationTemplatePlacement] AS [p] ON CAST( [p].[Id] AS NVARCHAR( 200 ) ) = [r].[QualifierValue]
WHERE [r].[ForeignKey] = @ForeignKey AND [r].[PurposeKey] = 'PLACEMENT'
    AND ( [se].[Name] <> 'Rock.Model.RegistrationInstance' OR [te].[Name] <> 'Rock.Model.Group'
        OR [i].[Id] IS NULL OR [g].[Id] IS NULL OR [p].[Id] IS NULL
        OR [p].[RegistrationTemplateId] <> [i].[RegistrationTemplateId] OR [g].[GroupTypeId] <> [p].[GroupTypeId] );

-- check: linked-group-is-an-event-attendees-group
-- why: Rock adds registrants to the linked group with the template's group type and role, so the group must be of the template's type. Every sample linked group is an Event Attendees group, under the Guid Rock reserves for that type.
SELECT COUNT(*)
FROM [EventItemOccurrenceGroupMap] AS [m]
INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [m].[RegistrationInstanceId]
INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [i].[RegistrationTemplateId]
INNER JOIN [Group] AS [g] ON [g].[Id] = [m].[GroupId]
INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
WHERE [m].[ForeignKey] = @ForeignKey
    AND ( [g].[GroupTypeId] <> ISNULL( [t].[GroupTypeId], 0 ) OR [gt].[Guid] <> '3311132B-268D-44E9-811A-A56A0835E50A' );

-- check: registration-category-has-the-admin-role
-- why: Rock's v19 rollup gave the RSR - Event Registration Administration role Edit and Administrate on every top-level template category that existed then. Categories added later need the same, or that role cannot manage their templates.
SELECT COUNT(*)
FROM [Category] AS [c]
INNER JOIN [EntityType] AS [et] ON [et].[Id] = [c].[EntityTypeId]
WHERE [c].[ForeignKey] = @ForeignKey AND [et].[Name] = 'Rock.Model.RegistrationTemplate'
    AND ( SELECT COUNT( DISTINCT [a].[Action] )
        FROM [Auth] AS [a]
        INNER JOIN [Group] AS [r] ON [r].[Id] = [a].[GroupId]
        INNER JOIN [EntityType] AS [ae] ON [ae].[Id] = [a].[EntityTypeId]
        WHERE [ae].[Name] = 'Rock.Model.Category' AND [a].[EntityId] = [c].[Id] AND [a].[AllowOrDeny] = 'A' AND [a].[Action] IN ( 'Edit', 'Administrate' )
            AND [r].[Guid] = '2A92086B-DFF0-4B9C-46CB-4DAD805615AF' ) <> 2;

-- check: seven-registrations-are-open-today
-- why: Decision 190. Camp, the mission trip, the fall retreat, Parents' Night Out, Money Matters, Date Night, and the Women's Retreat are open on every install day, so every registration feature can be tried. Rock's calendar item page shows Register for an active instance of an active template, inside its dates, linked to the occurrence (EventItemOccurrenceLava).
SELECT CASE WHEN (
        SELECT COUNT( DISTINCT [o].[EventItemId] )
        FROM [EventItemOccurrenceGroupMap] AS [m]
        INNER JOIN [EventItemOccurrence] AS [o] ON [o].[Id] = [m].[EventItemOccurrenceId]
        INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [m].[RegistrationInstanceId]
        INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [i].[RegistrationTemplateId]
        WHERE [m].[ForeignKey] = @ForeignKey AND [i].[IsActive] = 1 AND [t].[IsActive] = 1
            AND [i].[StartDateTime] <= GETDATE() AND [i].[EndDateTime] >= GETDATE() ) >= 7 THEN 0 ELSE 1 END;

-- check: registrant-template-matches
-- why: Rock reads a registrant's forms, fees, and attributes from its template. A registrant, its registration, and the registration's instance all name one template.
SELECT COUNT(*)
FROM [RegistrationRegistrant] AS [rr]
INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
WHERE [rr].[ForeignKey] = @ForeignKey
    AND ( [rr].[RegistrationTemplateId] <> [r].[RegistrationTemplateId] OR [r].[RegistrationTemplateId] <> [i].[RegistrationTemplateId] );

-- check: registrant-is-in-one-instance-per-event
-- why: A person goes to an event once. A family registers at its own campus, so nobody is a registrant in two instances of one template that open and close on the same days, such as VBS at two campuses the same week.
SELECT COUNT(*)
FROM (
    SELECT [pa].[PersonId], [i].[RegistrationTemplateId], [i].[StartDateTime], [i].[EndDateTime]
    FROM [RegistrationRegistrant] AS [rr]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
    INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
    INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
    WHERE [rr].[ForeignKey] = @ForeignKey
    GROUP BY [pa].[PersonId], [i].[RegistrationTemplateId], [i].[StartDateTime], [i].[EndDateTime]
    HAVING COUNT( DISTINCT [i].[Id] ) > 1
) AS [twice];

-- check: registrant-required-answers-are-filled
-- why: Rock's registration form does not save until every required question it shows is answered. A registrant has an answer to each required registrant question on its template, except one on the wait list that the wait list form does not show.
SELECT COUNT(*)
FROM [RegistrationRegistrant] AS [rr]
INNER JOIN [RegistrationTemplateForm] AS [f] ON [f].[RegistrationTemplateId] = [rr].[RegistrationTemplateId]
INNER JOIN [RegistrationTemplateFormField] AS [ff] ON [ff].[RegistrationTemplateFormId] = [f].[Id] AND [ff].[FieldSource] = 4 AND [ff].[IsRequired] = 1
LEFT JOIN [AttributeValue] AS [av] ON [av].[AttributeId] = [ff].[AttributeId] AND [av].[EntityId] = [rr].[Id]
WHERE [rr].[ForeignKey] = @ForeignKey
    AND ( [rr].[OnWaitList] = 0 OR [ff].[ShowOnWaitlist] = 1 )
    AND ISNULL( [av].[Value], '' ) = '';

-- check: registrant-is-in-the-grades
-- why: Rock checks a registrant's grade against the template's on the day they register (RegistrantEligibilityEvaluator), and the event is for those grades, so every registrant is in them on both days. A grade moves up each June 1, as check-in moves it.
WITH [Registrants] AS (
    SELECT [p].[GraduationYear], CAST( [r].[CreatedDateTime] AS DATE ) AS [Registered]
        , ( SELECT CAST( MIN( [sd].[StartDateTime] ) AS DATE )
            FROM [EventItemOccurrenceGroupMap] AS [m]
            INNER JOIN [EventItemOccurrence] AS [o] ON [o].[Id] = [m].[EventItemOccurrenceId]
            INNER JOIN [ScheduleDate] AS [sd] ON [sd].[ScheduleId] = [o].[ScheduleId]
            WHERE [m].[RegistrationInstanceId] = [i].[Id] AND [sd].[StartDateTime] > [i].[EndDateTime] ) AS [EventDay]
        , 12 - CAST( JSON_VALUE( [t].[AdditionalSettingsJson], '$.RegistrantEligibilitySettings.MaximumGradeOffset' ) AS INT ) AS [MinGrade]
        , 12 - CAST( JSON_VALUE( [t].[AdditionalSettingsJson], '$.RegistrantEligibilitySettings.MinimumGradeOffset' ) AS INT ) AS [MaxGrade]
    FROM [RegistrationRegistrant] AS [rr]
    INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
    INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
    INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [i].[RegistrationTemplateId]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
    WHERE [rr].[ForeignKey] = @ForeignKey AND JSON_VALUE( [t].[AdditionalSettingsJson], '$.RegistrantEligibilitySettings.MaximumGradeOffset' ) IS NOT NULL
), [Grades] AS (
    SELECT [MinGrade], [MaxGrade], [EventDay]
        , 12 - ( [GraduationYear] - ( YEAR( [Registered] ) + CASE WHEN CAST( [Registered] AS DATE ) > DATEFROMPARTS( YEAR( [Registered] ), 6, 1 ) THEN 1 ELSE 0 END ) ) AS [GradeRegistered]
        , 12 - ( [GraduationYear] - ( YEAR( [EventDay] ) + CASE WHEN CAST( [EventDay] AS DATE ) > DATEFROMPARTS( YEAR( [EventDay] ), 6, 1 ) THEN 1 ELSE 0 END ) ) AS [GradeAtEvent]
    FROM [Registrants]
)
SELECT COUNT(*)
FROM [Grades]
WHERE [EventDay] IS NULL OR [GradeRegistered] IS NULL
    OR [GradeRegistered] NOT BETWEEN [MinGrade] AND [MaxGrade] OR [GradeAtEvent] NOT BETWEEN [MinGrade] AND [MaxGrade];

-- check: registration-is-inside-its-window
-- why: A registration is made while its instance is open, and before the install day. One dated outside its window could never have been made in Rock.
SELECT COUNT(*)
FROM [Registration] AS [r]
INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
WHERE [r].[ForeignKey] = @ForeignKey
    AND ( [r].[CreatedDateTime] < [i].[StartDateTime] OR [r].[CreatedDateTime] > [i].[EndDateTime] OR [r].[CreatedDateTime] >= CAST( GETDATE() AS DATE )
        OR [r].[CreatedDateKey] <> CAST( CONVERT( CHAR( 8 ), [r].[CreatedDateTime], 112 ) AS INT ) );

-- check: registrants-fit-the-limit-and-join-the-group
-- why: Rock lets no more registrants than MaxAttendees off the wait list, and adds each of them to the linkage's group with the template's role and status (AddRegistrantsToGroup), pointing the registrant at that member. Wait-listed registrants are in no group.
SELECT ( SELECT COUNT(*)
        FROM [RegistrationInstance] AS [i]
        WHERE [i].[ForeignKey] = @ForeignKey AND [i].[MaxAttendees] IS NOT NULL
            AND ( SELECT COUNT(*) FROM [RegistrationRegistrant] AS [rr] INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
                WHERE [r].[RegistrationInstanceId] = [i].[Id] AND [rr].[OnWaitList] = 0 ) > [i].[MaxAttendees] )
    + ( SELECT COUNT(*)
        FROM [RegistrationRegistrant] AS [rr]
        INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
        INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [r].[RegistrationTemplateId]
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
        LEFT JOIN [GroupMember] AS [gm] ON [gm].[Id] = [rr].[GroupMemberId]
        WHERE [rr].[ForeignKey] = @ForeignKey
            AND ( ( [rr].[OnWaitList] = 1 AND [rr].[GroupMemberId] IS NOT NULL )
                OR ( [rr].[OnWaitList] = 0 AND [r].[GroupId] IS NOT NULL
                    AND ( [gm].[Id] IS NULL OR [gm].[GroupId] <> [r].[GroupId] OR [gm].[PersonId] <> [pa].[PersonId]
                        OR [gm].[GroupRoleId] <> [t].[GroupMemberRoleId] OR [gm].[GroupMemberStatus] <> [t].[GroupMemberStatus] ) ) ) );

-- check: wait-list-only-when-full
-- why: Rock puts a registrant on the wait list only when the instance has no spots left. A wait list beside an open spot would never be moved up.
SELECT COUNT(*)
FROM [RegistrationInstance] AS [i]
WHERE [i].[ForeignKey] = @ForeignKey
    AND EXISTS ( SELECT 1 FROM [RegistrationRegistrant] AS [rr] INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId] WHERE [r].[RegistrationInstanceId] = [i].[Id] AND [rr].[OnWaitList] = 1 )
    AND ( [i].[MaxAttendees] IS NULL
        OR ( SELECT COUNT(*) FROM [RegistrationRegistrant] AS [rr] INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
            WHERE [r].[RegistrationInstanceId] = [i].[Id] AND [rr].[OnWaitList] = 0 ) < [i].[MaxAttendees]
        OR NOT EXISTS ( SELECT 1 FROM [RegistrationTemplate] AS [t] WHERE [t].[Id] = [i].[RegistrationTemplateId] AND [t].[WaitListEnabled] = 1 ) );

-- check: placed-person-is-a-registrant
-- why: Rock's placement board lists an instance's registrants and the groups linked to it for each placement. A placed person who is not a registrant, or is on the wait list, of an instance linked to the group would show in a group with no registration behind them.
SELECT COUNT(*)
FROM [GroupMember] AS [gm]
WHERE [gm].[ForeignKey] = @ForeignKey
    AND EXISTS ( SELECT 1 FROM [RelatedEntity] AS [re] WHERE [re].[PurposeKey] = 'PLACEMENT' AND [re].[TargetEntityId] = [gm].[GroupId] AND [re].[ForeignKey] = @ForeignKey )
    AND NOT EXISTS (
        SELECT 1
        FROM [RelatedEntity] AS [re]
        INNER JOIN [Registration] AS [r] ON [r].[RegistrationInstanceId] = [re].[SourceEntityId]
        INNER JOIN [RegistrationRegistrant] AS [rr] ON [rr].[RegistrationId] = [r].[Id] AND [rr].[OnWaitList] = 0
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
        WHERE [re].[PurposeKey] = 'PLACEMENT' AND [re].[TargetEntityId] = [gm].[GroupId] AND [pa].[PersonId] = [gm].[PersonId] );

-- check: registration-payment-is-rocks
-- why: Rock's registration entry saves a payment as an Event Registration transaction whose detail points at the registration and posts to its instance's account. A payment of another type, account, or registration would be missing from the balance, or counted as a gift.
SELECT COUNT(*)
FROM [FinancialTransactionDetail] AS [d]
INNER JOIN [FinancialTransaction] AS [ft] ON [ft].[Id] = [d].[TransactionId]
LEFT JOIN [EntityType] AS [et] ON [et].[Id] = [d].[EntityTypeId]
LEFT JOIN [Registration] AS [r] ON [r].[Id] = [d].[EntityId] AND [et].[Name] = 'Rock.Model.Registration'
LEFT JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
WHERE [d].[ForeignKey] = @ForeignKey
    AND ( [ft].[TransactionTypeValueId] = ( SELECT [Id] FROM [DefinedValue] WHERE [Guid] = '33CB96DD-8752-4BEE-A142-88DB7DE538F0' ) OR [et].[Name] = 'Rock.Model.Registration' )
    AND ( [r].[Id] IS NULL OR [i].[AccountId] <> [d].[AccountId]
        OR [ft].[TransactionTypeValueId] <> ( SELECT [Id] FROM [DefinedValue] WHERE [Guid] = '33CB96DD-8752-4BEE-A142-88DB7DE538F0' )
        OR [ft].[AuthorizedPersonAliasId] <> [r].[PersonAliasId] );

-- check: registration-is-never-overpaid
-- why: Rock's balance is the discounted cost (Registration.DiscountedCost) less the payments. More paid than owed is a refund nobody made. Once the payment deadline has passed, every balance is paid.
WITH [Registrants] AS (
    SELECT [r].[Id], [i].[PaymentDeadlineDate]
        , CASE WHEN [rr].[OnWaitList] = 1 THEN 0 ELSE
            [rr].[Cost] - CASE WHEN [rr].[DiscountApplies] = 1 THEN [rr].[Cost] * [r].[DiscountPercentage] ELSE 0 END
            + ISNULL( [fees].[Other], 0 ) + ISNULL( [fees].[Discountable], 0 ) * CASE WHEN [rr].[DiscountApplies] = 1 THEN 1 - [r].[DiscountPercentage] ELSE 1 END
            - CASE WHEN [rr].[DiscountApplies] = 1 THEN [r].[DiscountAmount] ELSE 0 END END AS [Cost]
    FROM [Registration] AS [r]
    INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
    INNER JOIN [RegistrationRegistrant] AS [rr] ON [rr].[RegistrationId] = [r].[Id]
    OUTER APPLY (
        SELECT SUM( CASE WHEN [tf].[DiscountApplies] = 1 THEN [f].[Quantity] * [f].[Cost] ELSE 0 END ) AS [Discountable]
            , SUM( CASE WHEN [tf].[DiscountApplies] = 0 THEN [f].[Quantity] * [f].[Cost] ELSE 0 END ) AS [Other]
        FROM [RegistrationRegistrantFee] AS [f]
        INNER JOIN [RegistrationTemplateFee] AS [tf] ON [tf].[Id] = [f].[RegistrationTemplateFeeId]
        WHERE [f].[RegistrationRegistrantId] = [rr].[Id] ) AS [fees]
    WHERE [r].[ForeignKey] = @ForeignKey
), [Costs] AS (
    SELECT [Id], [PaymentDeadlineDate], SUM( CASE WHEN [Cost] > 0 THEN [Cost] ELSE 0 END ) AS [Cost]
    FROM [Registrants]
    GROUP BY [Id], [PaymentDeadlineDate]
), [Paid] AS (
    SELECT [c].[Id], [c].[Cost], [c].[PaymentDeadlineDate]
        , ISNULL( ( SELECT SUM( [d].[Amount] ) FROM [FinancialTransactionDetail] AS [d] INNER JOIN [EntityType] AS [et] ON [et].[Id] = [d].[EntityTypeId]
            WHERE [et].[Name] = 'Rock.Model.Registration' AND [d].[EntityId] = [c].[Id] ), 0 ) AS [Paid]
    FROM [Costs] AS [c]
)
SELECT COUNT(*)
FROM [Paid]
WHERE [Paid] > [Cost] OR ( [PaymentDeadlineDate] < CAST( GETDATE() AS DATE ) AND [Paid] < [Cost] );

-- check: payment-plan-fits-its-template
-- why: Rock offers a payment plan only at the template's frequencies, as an Event Registration scheduled transaction from the registrar, with one detail that points at the registration, and its last payment on or before the payment deadline. Each payment it made is one of its transactions.
WITH [Plans] AS (
    SELECT [s].*, [r].[Id] AS [RegistrationId], [r].[PersonAliasId] AS [RegistrarAliasId], [t].[PaymentPlanFrequencyValueIds], [i].[PaymentDeadlineDate], [f].[Guid] AS [FrequencyGuid]
        , [s].[NumberOfPayments] - 1 AS [LastIndex]
    FROM [Registration] AS [r]
    INNER JOIN [FinancialScheduledTransaction] AS [s] ON [s].[Id] = [r].[PaymentPlanFinancialScheduledTransactionId]
    INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
    INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [i].[RegistrationTemplateId]
    INNER JOIN [DefinedValue] AS [f] ON [f].[Id] = [s].[TransactionFrequencyValueId]
    WHERE [r].[ForeignKey] = @ForeignKey
)
SELECT COUNT(*)
FROM [Plans] AS [p]
WHERE [p].[NumberOfPayments] IS NULL OR [p].[NumberOfPayments] < 2 OR [p].[PaymentDeadlineDate] IS NULL
    OR CONCAT( ',', [p].[PaymentPlanFrequencyValueIds], ',' ) NOT LIKE CONCAT( '%,', [p].[TransactionFrequencyValueId], ',%' )
    OR [p].[TransactionTypeValueId] <> ( SELECT [Id] FROM [DefinedValue] WHERE [Guid] = '33CB96DD-8752-4BEE-A142-88DB7DE538F0' )
    OR [p].[AuthorizedPersonAliasId] <> [p].[RegistrarAliasId]
    OR ( SELECT COUNT(*) FROM [FinancialScheduledTransactionDetail] AS [d] INNER JOIN [EntityType] AS [et] ON [et].[Id] = [d].[EntityTypeId]
        WHERE [d].[ScheduledTransactionId] = [p].[Id] AND [et].[Name] = 'Rock.Model.Registration' AND [d].[EntityId] = [p].[RegistrationId] ) <> 1
    OR ( SELECT COUNT(*) FROM [FinancialTransaction] AS [ft] WHERE [ft].[ScheduledTransactionId] = [p].[Id] ) > [p].[NumberOfPayments]
    OR CASE
        WHEN [p].[FrequencyGuid] = '1400753C-A0F9-4A45-8A1D-81C98450BD1F' THEN DATEADD( MONTH, [p].[LastIndex], [p].[StartDate] )
        WHEN DAY( [p].[StartDate] ) = 1 THEN DATEADD( DAY, CASE WHEN [p].[LastIndex] % 2 = 1 THEN 14 ELSE 0 END, DATEADD( MONTH, [p].[LastIndex] / 2, [p].[StartDate] ) )
        ELSE DATEADD( DAY, CASE WHEN [p].[LastIndex] % 2 = 1 THEN -14 ELSE 0 END, DATEADD( MONTH, ( [p].[LastIndex] + 1 ) / 2, [p].[StartDate] ) )
        END > [p].[PaymentDeadlineDate];

-- check: discount-code-within-its-uses
-- why: Rock stops taking a code once as many registrations of an instance have used it as its MaxUsage allows (GetDiscountByCodeIfValid).
SELECT COUNT(*)
FROM [RegistrationTemplateDiscount] AS [d]
INNER JOIN [RegistrationInstance] AS [i] ON [i].[RegistrationTemplateId] = [d].[RegistrationTemplateId]
WHERE [d].[ForeignKey] = @ForeignKey AND [d].[MaxUsage] IS NOT NULL
    AND ( SELECT COUNT(*) FROM [Registration] AS [r] WHERE [r].[RegistrationInstanceId] = [i].[Id] AND [r].[DiscountCode] = [d].[Code] ) > [d].[MaxUsage];

-- check: last-camp-matches-camp-steps
-- why: Jon's decision. When the steps part loaded, the people registered for a Student Camp that has ended are exactly the people with a Student Camp step at that camp, both ways, so the registration and the step tell one story.
WITH [CampDays] AS (
    SELECT DISTINCT [i].[Id] AS [InstanceId], CAST( [sd].[StartDateTime] AS DATE ) AS [CampDay]
    FROM [RegistrationInstance] AS [i]
    INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [i].[RegistrationTemplateId]
    INNER JOIN [EventItemOccurrenceGroupMap] AS [m] ON [m].[RegistrationInstanceId] = [i].[Id]
    INNER JOIN [EventItemOccurrence] AS [o] ON [o].[Id] = [m].[EventItemOccurrenceId]
    INNER JOIN [ScheduleDate] AS [sd] ON [sd].[ScheduleId] = [o].[ScheduleId]
    WHERE [i].[ForeignKey] = @ForeignKey AND [t].[Guid] = 'ABF10164-8D9E-4FF0-B4FC-173F35015923'
        AND [sd].[StartDateTime] > [i].[EndDateTime] AND [sd].[EndDateTime] < CAST( GETDATE() AS DATE )
), [Campers] AS (
    SELECT DISTINCT [pa].[PersonId], [c].[CampDay]
    FROM [CampDays] AS [c]
    INNER JOIN [Registration] AS [r] ON [r].[RegistrationInstanceId] = [c].[InstanceId]
    INNER JOIN [RegistrationRegistrant] AS [rr] ON [rr].[RegistrationId] = [r].[Id] AND [rr].[OnWaitList] = 0
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
), [CampSteps] AS (
    SELECT DISTINCT [pa].[PersonId], CAST( [s].[StartDateTime] AS DATE ) AS [CampDay]
    FROM [Step] AS [s]
    INNER JOIN [StepType] AS [st] ON [st].[Id] = [s].[StepTypeId]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [s].[PersonAliasId]
    WHERE [s].[ForeignKey] = @ForeignKey AND [st].[Guid] = 'EED45E21-7602-47A7-A204-6658F2E712A3'
        AND CAST( [s].[StartDateTime] AS DATE ) IN ( SELECT [CampDay] FROM [CampDays] )
)
SELECT CASE WHEN NOT EXISTS ( SELECT 1 FROM [Step] WHERE [ForeignKey] = @ForeignKey ) THEN 0
    ELSE ( SELECT COUNT(*) FROM ( SELECT * FROM [Campers] EXCEPT SELECT * FROM [CampSteps] ) AS [x] )
        + ( SELECT COUNT(*) FROM ( SELECT * FROM [CampSteps] EXCEPT SELECT * FROM [Campers] ) AS [x] ) END;

-- check: free-registration-has-no-payments
-- why: A free template (no cost, no cost on the instance, and no fees) owes nothing, so Rock's registration entry takes no payment and makes no payment plan for it.
SELECT COUNT(*)
FROM [Registration] AS [r]
INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [r].[RegistrationTemplateId]
WHERE [r].[ForeignKey] = @ForeignKey AND ISNULL( [t].[Cost], 0 ) = 0 AND ISNULL( [t].[SetCostOnInstance], 0 ) = 0
    AND NOT EXISTS ( SELECT 1 FROM [RegistrationTemplateFee] AS [f] WHERE [f].[RegistrationTemplateId] = [t].[Id] )
    AND ( [r].[PaymentPlanFinancialScheduledTransactionId] IS NOT NULL
        OR EXISTS ( SELECT 1 FROM [FinancialTransactionDetail] AS [d] INNER JOIN [EntityType] AS [et] ON [et].[Id] = [d].[EntityTypeId]
            WHERE [et].[Name] = 'Rock.Model.Registration' AND [d].[EntityId] = [r].[Id] ) );

-- check: registrant-fee-matches-its-item
-- why: Rock's registration entry saves a fee with its item's cost, and a quantity above one only on a fee that allows several, such as Date Night childcare per child. A fee whose item belongs to another fee would show a choice the person never saw.
SELECT COUNT(*)
FROM [RegistrationRegistrantFee] AS [f]
INNER JOIN [RegistrationTemplateFee] AS [tf] ON [tf].[Id] = [f].[RegistrationTemplateFeeId]
INNER JOIN [RegistrationTemplateFeeItem] AS [fi] ON [fi].[Id] = [f].[RegistrationTemplateFeeItemId]
WHERE [f].[ForeignKey] = @ForeignKey
    AND ( [f].[Quantity] < 1 OR ( [f].[Quantity] > 1 AND [tf].[AllowMultiple] = 0 ) OR [f].[Cost] <> [fi].[Cost] OR [fi].[RegistrationTemplateFeeId] <> [tf].[Id] );

-- check: quantity-fee-is-in-the-balance
-- why: A template with no minimum payment takes the whole cost when a person registers, and a fee with a quantity costs its item's cost times the quantity. So a registration with such a fee that paid has paid exactly its cost, fees and discount included, as Rock's Registration.DiscountedCost adds it up.
WITH [Registrants] AS (
    SELECT [r].[Id]
        , CASE WHEN [rr].[OnWaitList] = 1 THEN 0 ELSE
            [rr].[Cost] - CASE WHEN [rr].[DiscountApplies] = 1 THEN [rr].[Cost] * [r].[DiscountPercentage] ELSE 0 END
            + ISNULL( [fees].[Other], 0 ) + ISNULL( [fees].[Discountable], 0 ) * CASE WHEN [rr].[DiscountApplies] = 1 THEN 1 - [r].[DiscountPercentage] ELSE 1 END
            - CASE WHEN [rr].[DiscountApplies] = 1 THEN [r].[DiscountAmount] ELSE 0 END END AS [Cost]
    FROM [Registration] AS [r]
    INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [r].[RegistrationTemplateId]
    INNER JOIN [RegistrationRegistrant] AS [rr] ON [rr].[RegistrationId] = [r].[Id]
    OUTER APPLY (
        SELECT SUM( CASE WHEN [tf].[DiscountApplies] = 1 THEN [f].[Quantity] * [f].[Cost] ELSE 0 END ) AS [Discountable]
            , SUM( CASE WHEN [tf].[DiscountApplies] = 0 THEN [f].[Quantity] * [f].[Cost] ELSE 0 END ) AS [Other]
        FROM [RegistrationRegistrantFee] AS [f]
        INNER JOIN [RegistrationTemplateFee] AS [tf] ON [tf].[Id] = [f].[RegistrationTemplateFeeId]
        WHERE [f].[RegistrationRegistrantId] = [rr].[Id] ) AS [fees]
    WHERE [r].[ForeignKey] = @ForeignKey AND [t].[MinimumInitialPayment] IS NULL AND ISNULL( [t].[SetCostOnInstance], 0 ) = 0
        AND EXISTS ( SELECT 1 FROM [RegistrationRegistrantFee] AS [q] INNER JOIN [RegistrationRegistrant] AS [qr] ON [qr].[Id] = [q].[RegistrationRegistrantId]
            WHERE [qr].[RegistrationId] = [r].[Id] AND [q].[Quantity] > 1 )
), [Paid] AS (
    SELECT [c].[Id], SUM( CASE WHEN [c].[Cost] > 0 THEN [c].[Cost] ELSE 0 END ) AS [Cost]
        , ISNULL( ( SELECT SUM( [d].[Amount] ) FROM [FinancialTransactionDetail] AS [d] INNER JOIN [EntityType] AS [et] ON [et].[Id] = [d].[EntityTypeId]
            WHERE [et].[Name] = 'Rock.Model.Registration' AND [d].[EntityId] = [c].[Id] ), 0 ) AS [Paid]
    FROM [Registrants] AS [c]
    GROUP BY [c].[Id]
)
SELECT COUNT(*)
FROM [Paid]
WHERE [Paid] > 0 AND [Paid] <> [Cost];

-- check: fee-item-within-its-usage-limit
-- why: Rock hides a fee item, such as the Women's Retreat single room, once an instance's registrants have used it MaximumUsageCount times (RegistrationTemplateFeeItem.GetUsageCountRemaining), so no instance holds more.
SELECT COUNT(*)
FROM (
    SELECT [r].[RegistrationInstanceId], [fi].[Id], [fi].[MaximumUsageCount], SUM( [f].[Quantity] ) AS [Used]
    FROM [RegistrationRegistrantFee] AS [f]
    INNER JOIN [RegistrationTemplateFeeItem] AS [fi] ON [fi].[Id] = [f].[RegistrationTemplateFeeItemId]
    INNER JOIN [RegistrationRegistrant] AS [rr] ON [rr].[Id] = [f].[RegistrationRegistrantId]
    INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
    WHERE [f].[ForeignKey] = @ForeignKey AND [fi].[MaximumUsageCount] IS NOT NULL
    GROUP BY [r].[RegistrationInstanceId], [fi].[Id], [fi].[MaximumUsageCount]
    HAVING SUM( [f].[Quantity] ) > [fi].[MaximumUsageCount] ) AS [x];

-- check: required-fee-is-chosen
-- why: Rock's registration form does not save until a registrant picks every required fee, such as the Women's Retreat room. A registrant off the wait list has one.
SELECT COUNT(*)
FROM [RegistrationRegistrant] AS [rr]
INNER JOIN [RegistrationTemplateFee] AS [tf] ON [tf].[RegistrationTemplateId] = [rr].[RegistrationTemplateId] AND [tf].[IsRequired] = 1 AND [tf].[IsActive] = 1
WHERE [rr].[ForeignKey] = @ForeignKey AND [rr].[OnWaitList] = 0
    AND NOT EXISTS ( SELECT 1 FROM [RegistrationRegistrantFee] AS [f] WHERE [f].[RegistrationRegistrantId] = [rr].[Id] AND [f].[RegistrationTemplateFeeId] = [tf].[Id] );

-- check: registrants-share-a-family-when-the-template-says-so
-- why: A template whose registrants must be in the same family (RegistrantsSameFamily is Yes) only offers the registrar's family members, so everyone on one of its registrations shares a family, such as a Date Night couple or VBS siblings.
SELECT COUNT(*)
FROM [Registration] AS [r]
INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [r].[RegistrationTemplateId]
WHERE [r].[ForeignKey] = @ForeignKey AND [t].[RegistrantsSameFamily] = 1
    AND ( SELECT COUNT( DISTINCT [p].[PrimaryFamilyId] )
        FROM [RegistrationRegistrant] AS [rr]
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
        INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
        WHERE [rr].[RegistrationId] = [r].[Id] ) > 1;

-- check: date-night-registers-a-married-couple
-- why: Marriage Date Night is for married couples, two people on one registration. Each of its registrations holds exactly two people, and both are married.
SELECT COUNT(*)
FROM [Registration] AS [r]
INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [r].[RegistrationTemplateId]
OUTER APPLY (
    SELECT COUNT(*) AS [People], SUM( CASE WHEN [m].[Guid] = '5FE5A540-7D9F-433E-B47E-4229D1472248' THEN 1 ELSE 0 END ) AS [Married]
    FROM [RegistrationRegistrant] AS [rr]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
    LEFT JOIN [DefinedValue] AS [m] ON [m].[Id] = [p].[MaritalStatusValueId]
    WHERE [rr].[RegistrationId] = [r].[Id] ) AS [c]
WHERE [r].[ForeignKey] = @ForeignKey AND [t].[Guid] = '84E166F7-F218-47AA-97C0-1410C914D0F7'
    AND ( [c].[People] <> 2 OR [c].[Married] <> 2 );

-- check: registrant-meets-the-age-and-gender-rules
-- why: Rock checks a registrant's age and gender against the template's on the day they register (RegistrantEligibilityEvaluator), and the event is for those people, so every registrant meets them on both days: a VBS kid is 4 to 11, and every Women's Retreat guest is a woman.
WITH [Registrants] AS (
    SELECT [p].[BirthDate], [p].[Gender], CAST( [r].[CreatedDateTime] AS DATE ) AS [Registered]
        , ( SELECT CAST( MIN( [sd].[StartDateTime] ) AS DATE )
            FROM [EventItemOccurrenceGroupMap] AS [m]
            INNER JOIN [EventItemOccurrence] AS [o] ON [o].[Id] = [m].[EventItemOccurrenceId]
            INNER JOIN [ScheduleDate] AS [sd] ON [sd].[ScheduleId] = [o].[ScheduleId]
            WHERE [m].[RegistrationInstanceId] = [i].[Id] AND [sd].[StartDateTime] > [i].[EndDateTime] ) AS [EventDay]
        , CAST( JSON_VALUE( [t].[AdditionalSettingsJson], '$.RegistrantEligibilitySettings.MinimumAge' ) AS DECIMAL( 6, 2 ) ) AS [MinAge]
        , CAST( JSON_VALUE( [t].[AdditionalSettingsJson], '$.RegistrantEligibilitySettings.MaximumAge' ) AS DECIMAL( 6, 2 ) ) AS [MaxAge]
        , CAST( JSON_VALUE( [t].[AdditionalSettingsJson], '$.RegistrantEligibilitySettings.Gender' ) AS INT ) AS [RuleGender]
    FROM [RegistrationRegistrant] AS [rr]
    INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
    INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
    INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [i].[RegistrationTemplateId]
    INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
    INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
    WHERE [rr].[ForeignKey] = @ForeignKey
), [Ages] AS (
    SELECT *
        , DATEDIFF( YEAR, [BirthDate], [Registered] ) - CASE WHEN DATEADD( YEAR, DATEDIFF( YEAR, [BirthDate], [Registered] ), [BirthDate] ) > [Registered] THEN 1 ELSE 0 END AS [AgeRegistered]
        , DATEDIFF( YEAR, [BirthDate], [EventDay] ) - CASE WHEN DATEADD( YEAR, DATEDIFF( YEAR, [BirthDate], [EventDay] ), [BirthDate] ) > [EventDay] THEN 1 ELSE 0 END AS [AgeAtEvent]
    FROM [Registrants]
)
SELECT COUNT(*)
FROM [Ages]
WHERE ( [RuleGender] IS NOT NULL AND [Gender] <> [RuleGender] )
    OR ( ( [MinAge] IS NOT NULL OR [MaxAge] IS NOT NULL ) AND ( [BirthDate] IS NULL OR [EventDay] IS NULL ) )
    OR [AgeRegistered] < [MinAge] OR [AgeAtEvent] < [MinAge] OR [AgeRegistered] > [MaxAge] OR [AgeAtEvent] > [MaxAge];

-- check: closed-instance-registrants-are-placed
-- why: Once registration closes, the staff place everyone before the event: last summer's campers, VBS kids in crews, and Serve Day volunteers on project teams. Each registrant off the wait list of a closed instance is in a group of every placement linked to it.
SELECT COUNT(*)
FROM [RegistrationRegistrant] AS [rr]
INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
CROSS APPLY ( SELECT DISTINCT [re].[QualifierValue] FROM [RelatedEntity] AS [re] WHERE [re].[PurposeKey] = 'PLACEMENT' AND [re].[SourceEntityId] = [i].[Id] AND [re].[ForeignKey] = @ForeignKey ) AS [placement]
WHERE [rr].[ForeignKey] = @ForeignKey AND [rr].[OnWaitList] = 0 AND [i].[EndDateTime] < CAST( GETDATE() AS DATE )
    AND NOT EXISTS (
        SELECT 1
        FROM [RelatedEntity] AS [re]
        INNER JOIN [GroupMember] AS [gm] ON [gm].[GroupId] = [re].[TargetEntityId] AND [gm].[PersonId] = [pa].[PersonId]
        WHERE [re].[PurposeKey] = 'PLACEMENT' AND [re].[SourceEntityId] = [i].[Id] AND [re].[QualifierValue] = [placement].[QualifierValue] );

-- check: vbs-crew-matches-the-grade
-- why: VBS places each kid in the crew for their grade on the event day, Pre-K first and 5th grade last. A kid with no grade yet is in the Pre-K crew.
SELECT COUNT(*)
FROM [GroupMember] AS [gm]
INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId]
INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId] AND [gt].[Guid] = '62BF4230-6636-4CDE-ACCE-05329DB91D07'
INNER JOIN [Person] AS [p] ON [p].[Id] = [gm].[PersonId]
CROSS APPLY (
    SELECT CAST( MIN( [sd].[StartDateTime] ) AS DATE ) AS [EventDay]
    FROM [RelatedEntity] AS [re]
    INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [re].[SourceEntityId]
    INNER JOIN [EventItemOccurrenceGroupMap] AS [m] ON [m].[RegistrationInstanceId] = [i].[Id]
    INNER JOIN [EventItemOccurrence] AS [o] ON [o].[Id] = [m].[EventItemOccurrenceId]
    INNER JOIN [ScheduleDate] AS [sd] ON [sd].[ScheduleId] = [o].[ScheduleId]
    WHERE [re].[PurposeKey] = 'PLACEMENT' AND [re].[TargetEntityId] = [g].[Id] AND [sd].[StartDateTime] > [i].[EndDateTime] ) AS [e]
CROSS APPLY ( SELECT CASE WHEN [p].[GraduationYear] IS NULL THEN -1
    ELSE 12 - ( [p].[GraduationYear] - ( YEAR( [e].[EventDay] ) + CASE WHEN CAST( [e].[EventDay] AS DATE ) > DATEFROMPARTS( YEAR( [e].[EventDay] ), 6, 1 ) THEN 1 ELSE 0 END ) ) END AS [Grade] ) AS [k]
WHERE [gm].[ForeignKey] = @ForeignKey
    AND [g].[Order] <> CASE WHEN [k].[Grade] < -1 THEN 0 WHEN [k].[Grade] > 5 THEN 6 ELSE [k].[Grade] + 1 END;

-- check: together-placement-keeps-a-registration-together
-- why: Serve Day places everyone who signed up on one registration, such as a family, on one project team.
SELECT COUNT(*)
FROM [Registration] AS [r]
WHERE [r].[ForeignKey] = @ForeignKey
    AND ( SELECT COUNT( DISTINCT [gm].[GroupId] )
        FROM [RegistrationRegistrant] AS [rr]
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
        INNER JOIN [RelatedEntity] AS [re] ON [re].[PurposeKey] = 'PLACEMENT' AND [re].[SourceEntityId] = [r].[RegistrationInstanceId]
        INNER JOIN [Group] AS [g] ON [g].[Id] = [re].[TargetEntityId]
        INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId] AND [gt].[Guid] = '17C4AC27-120D-4088-995E-94DCAFC600A5'
        INNER JOIN [GroupMember] AS [gm] ON [gm].[GroupId] = [g].[Id] AND [gm].[PersonId] = [pa].[PersonId]
        WHERE [rr].[RegistrationId] = [r].[Id] ) > 1;

-- check: person-attribute-answer-comes-from-a-registration
-- why: The loader writes Rock's Allergy person attribute only as a registration form saves it: for a registrant, off the wait list, of a template whose form asks for it. Kids check-in shows it, so a stray value would put an allergy on a child's label that no one entered.
SELECT COUNT(*)
FROM [AttributeValue] AS [av]
INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId] AND [a].[Guid] = 'DBD192C9-0AA1-46EC-92AB-A3DA8E056D31'
WHERE [av].[ForeignKey] = @ForeignKey
    AND NOT EXISTS (
        SELECT 1
        FROM [RegistrationRegistrant] AS [rr]
        INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
        INNER JOIN [RegistrationTemplateForm] AS [f] ON [f].[RegistrationTemplateId] = [rr].[RegistrationTemplateId]
        INNER JOIN [RegistrationTemplateFormField] AS [ff] ON [ff].[RegistrationTemplateFormId] = [f].[Id] AND [ff].[FieldSource] = 1 AND [ff].[AttributeId] = [a].[Id]
        WHERE [pa].[PersonId] = [av].[EntityId] AND [rr].[OnWaitList] = 0 );

-- check: release-is-signed-for-every-registrant
-- why: Decision 212. Rock's template screen saves the Embed action, so on a template that requires a release, every registrant signs while registering, and Rock links the registrant to that signed release, for that person, from that template. None is left pending.
SELECT COUNT(*)
FROM [RegistrationRegistrant] AS [rr]
INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [rr].[RegistrationTemplateId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
LEFT JOIN [SignatureDocument] AS [sd] ON [sd].[Id] = [rr].[SignatureDocumentId]
LEFT JOIN [PersonAlias] AS [applies] ON [applies].[Id] = [sd].[AppliesToPersonAliasId]
WHERE [rr].[ForeignKey] = @ForeignKey
    AND [t].[RequiredSignatureDocumentTemplateId] IS NOT NULL
    AND ( [t].[SignatureDocumentAction] <> 1 OR [t].[AllowExternalRegistrationUpdates] = 1
        OR [sd].[Id] IS NULL OR [sd].[Status] <> 2 OR [sd].[SignatureDocumentTemplateId] <> [t].[RequiredSignatureDocumentTemplateId]
        OR [applies].[PersonId] IS NULL OR [applies].[PersonId] <> [pa].[PersonId] );

-- check: signed-release-is-whole
-- why: A signed release opens from the registrant list and the person's documents. Each has a PDF in its release's file type with the release as parent, the registrar as signer and the one it is assigned to, a typed name and a reserved email, a reserved IP address, a signed time a few minutes before the registration was saved on the same day, its status date at the signing, and Rock's verification hash, which this check recomputes (SHA-1 over the UTF-8 fields joined by "|" and CRLF, as SignatureDocumentService does).
WITH [Documents] AS (
    SELECT [sd].[Id], [sd].[SignatureDocumentTemplateId], [sd].[BinaryFileId], [sd].[SignedByPersonAliasId], [sd].[AssignedToPersonAliasId], [sd].[SignedName], [sd].[SignedByEmail],
        [sd].[SignedClientIp], [sd].[SignedDateTime], [sd].[LastStatusDate], [sd].[SignatureVerificationHash], [r].[PersonAliasId] AS [RegistrarAliasId], [r].[CreatedDateTime] AS [Registered],
        CONVERT( VARCHAR(MAX), CONCAT( [sd].[SignedDocumentText], N'|' + NCHAR(13) + NCHAR(10), [sd].[SignedClientIp], N'|' + NCHAR(13) + NCHAR(10), [sd].[SignedClientUserAgent], N'|' + NCHAR(13) + NCHAR(10),
            CONVERT( NCHAR(19), [sd].[SignedDateTime], 126 ) + N'.' + RIGHT( N'000' + CAST( DATEPART( MILLISECOND, [sd].[SignedDateTime] ) AS NVARCHAR(3) ), 3 ) + N'0000', N'|' + NCHAR(13) + NCHAR(10),
            [sd].[SignedByPersonAliasId], N'|' + NCHAR(13) + NCHAR(10), [sd].[SignatureDataEncrypted], N'|' + NCHAR(13) + NCHAR(10), [sd].[SignedName] ) COLLATE Latin1_General_100_CI_AS_SC_UTF8 ) AS [Signed]
    FROM [SignatureDocument] AS [sd]
    INNER JOIN [RegistrationRegistrant] AS [rr] ON [rr].[SignatureDocumentId] = [sd].[Id]
    INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
    WHERE [sd].[ForeignKey] = @ForeignKey
)
SELECT COUNT(*) + ( SELECT COUNT(*) FROM [SignatureDocument] AS [sd] WHERE [sd].[ForeignKey] = @ForeignKey AND NOT EXISTS ( SELECT 1 FROM [RegistrationRegistrant] AS [rr] WHERE [rr].[SignatureDocumentId] = [sd].[Id] ) )
FROM [Documents] AS [d]
INNER JOIN [SignatureDocumentTemplate] AS [sdt] ON [sdt].[Id] = [d].[SignatureDocumentTemplateId]
LEFT JOIN [BinaryFile] AS [bf] ON [bf].[Id] = [d].[BinaryFileId]
LEFT JOIN [BinaryFileData] AS [bfd] ON [bfd].[Id] = [bf].[Id]
CROSS APPLY ( SELECT HASHBYTES( 'SHA1', [d].[Signed] ) AS [h] ) AS [x]
CROSS APPLY ( SELECT 'A' + REPLACE( REPLACE( REPLACE( CAST( '' AS XML ).value( 'xs:base64Binary(sql:column("x.h"))', 'VARCHAR(100)' ), '/', 'x' ), '+', 'x' ), '=', 'x' ) AS [Hash] ) AS [computed]
WHERE [bf].[Id] IS NULL OR [bfd].[Id] IS NULL OR [bf].[MimeType] <> 'application/pdf' OR [bf].[IsTemporary] = 1 OR [bf].[BinaryFileTypeId] <> [sdt].[BinaryFileTypeId]
    OR [bf].[ParentEntityId] <> [sdt].[Id] OR SUBSTRING( [bfd].[Content], 1, 5 ) <> 0x255044462D OR [bf].[FileSize] <> DATALENGTH( [bfd].[Content] )
    OR [d].[SignedByPersonAliasId] <> [d].[RegistrarAliasId] OR [d].[AssignedToPersonAliasId] <> [d].[RegistrarAliasId]
    OR ISNULL( [d].[SignedName], '' ) = '' OR ( [d].[SignedByEmail] NOT LIKE '%@example.org' AND [d].[SignedByEmail] NOT LIKE '%@rocksolidchurchdemo.com' ) OR [d].[SignedByEmail] IS NULL
    OR [d].[SignedDateTime] IS NULL OR [d].[SignedDateTime] > [d].[Registered] OR CAST( [d].[SignedDateTime] AS DATE ) <> CAST( [d].[Registered] AS DATE )
    OR [d].[LastStatusDate] <> [d].[SignedDateTime]
    OR ( [d].[SignedClientIp] NOT LIKE '192.0.2.%' AND [d].[SignedClientIp] NOT LIKE '198.51.100.%' AND [d].[SignedClientIp] NOT LIKE '203.0.113.%' )
    OR [d].[SignatureVerificationHash] IS NULL OR [d].[SignatureVerificationHash] <> [computed].[Hash];

-- check: registration-has-its-notes-and-history
-- why: Decision 213. Rock's registration entry writes the registration's own "Added Registration" history, and on a template with Add Person Note, a note of its Event Registration type and a "Registered" history line on each registrant, tied to the registration, all as it saves. A registration without them looks entered by hand.
SELECT COUNT(*)
FROM [RegistrationRegistrant] AS [rr]
INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
INNER JOIN [RegistrationInstance] AS [i] ON [i].[Id] = [r].[RegistrationInstanceId]
INNER JOIN [RegistrationTemplate] AS [t] ON [t].[Id] = [r].[RegistrationTemplateId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
WHERE [rr].[ForeignKey] = @ForeignKey
    AND ( NOT EXISTS (
            SELECT 1
            FROM [History] AS [h]
            INNER JOIN [Category] AS [c] ON [c].[Id] = [h].[CategoryId] AND [c].[Guid] = '813DF1A5-ADBD-481C-AC1D-884F0FA7AE77'
            WHERE [h].[EntityId] = [r].[Id] AND [h].[Verb] = 'ADD' AND [h].[ValueName] = 'Registration' AND [h].[CreatedByPersonAliasId] = [r].[PersonAliasId] )
        OR ( [t].[AddPersonNote] = 1 AND (
            NOT EXISTS (
                SELECT 1
                FROM [Note] AS [n]
                INNER JOIN [NoteType] AS [nt] ON [nt].[Id] = [n].[NoteTypeId] AND [nt].[Guid] = 'BBADA8EF-23FC-4B46-B7A7-0F6D31F8C045'
                WHERE [n].[EntityId] = [pa].[PersonId] AND [n].[Caption] = [i].[Name] AND [n].[Text] LIKE 'Registered for ' + [i].[Name] + '%'
                    AND [n].[CreatedDateTime] BETWEEN [r].[CreatedDateTime] AND DATEADD( MINUTE, 1, [r].[CreatedDateTime] ) )
            OR NOT EXISTS (
                SELECT 1
                FROM [History] AS [h]
                INNER JOIN [Category] AS [c] ON [c].[Id] = [h].[CategoryId] AND [c].[Guid] = 'DA9C0CC7-7B31-4E1E-BBA5-50405B2D9EFE'
                WHERE [h].[EntityId] = [pa].[PersonId] AND [h].[Verb] = 'REGISTERED' AND [h].[RelatedEntityId] = [r].[Id] AND [h].[Caption] = [i].[Name] ) ) ) );

-- check: registration-data-view-is-filed-under-registrations
-- why: Decision 185. A data view about registrations, one that filters on a registration instance or that a template names as its eligibility rule, sits in the Registrations child of the church's category, so staff find them together.
SELECT COUNT(*)
FROM [DataView] AS [dv]
LEFT JOIN [Category] AS [c] ON [c].[Id] = [dv].[CategoryId]
WHERE [dv].[ForeignKey] = @ForeignKey
    AND ( [c].[Guid] IS NULL OR [c].[Guid] <> '38176452-3090-434B-A2C7-01B34039DFCA' )
    AND ( EXISTS (
            SELECT 1
            FROM [DataViewFilter] AS [f]
            INNER JOIN [EntityType] AS [et] ON [et].[Id] = [f].[EntityTypeId]
            WHERE [f].[DataViewId] = [dv].[Id] AND [et].[Name] = 'Rock.Reporting.DataFilter.Person.InRegistrationInstanceFilter' )
        OR EXISTS (
            SELECT 1
            FROM [RegistrationTemplate] AS [t]
            WHERE [t].[ForeignKey] = @ForeignKey
                AND TRY_CAST( JSON_VALUE( [t].[AdditionalSettingsJson], '$.RegistrantEligibilitySettings.EligibilityDataViewGuid' ) AS UNIQUEIDENTIFIER ) = [dv].[Guid] ) );

-- check: registrant-is-in-the-eligibility-data-view
-- why: Decision 215. Rock lets someone register only when they are in the template's eligibility data view at that moment. This replays the view's grade filter (a grade moves up the day after the grade transition date, as Rock counts it) and its check-in count over the "Last N days" window on each registration day. It needs the attendance the view counts, so a load without attendance skips it.
WITH [Views] AS (
    SELECT [t].[Id] AS [TemplateId],
        ( SELECT TOP 1 '["' + REPLACE( [f].[Selection], '|', '","' ) + '"]' FROM [DataViewFilter] AS [f] INNER JOIN [EntityType] AS [et] ON [et].[Id] = [f].[EntityTypeId]
            WHERE [f].[DataViewId] = [dv].[Id] AND [et].[Name] = 'Rock.Reporting.DataFilter.Person.GroupTypeAttendanceFilter' ) AS [Attendance],
        ( SELECT TOP 1 '["' + REPLACE( [f].[Selection], '|', '","' ) + '"]' FROM [DataViewFilter] AS [f] INNER JOIN [EntityType] AS [et] ON [et].[Id] = [f].[EntityTypeId]
            WHERE [f].[DataViewId] = [dv].[Id] AND [et].[Name] = 'Rock.Reporting.DataFilter.Person.GradeFilter' ) AS [Grade]
    FROM [RegistrationTemplate] AS [t]
    INNER JOIN [DataView] AS [dv] ON [dv].[Guid] = TRY_CAST( JSON_VALUE( [t].[AdditionalSettingsJson], '$.RegistrantEligibilitySettings.EligibilityDataViewGuid' ) AS UNIQUEIDENTIFIER )
    WHERE [t].[ForeignKey] = @ForeignKey
), [Rules] AS (
    SELECT [v].[TemplateId], JSON_VALUE( [v].[Attendance], '$[0]' ) AS [GroupTypes], CAST( JSON_VALUE( [v].[Attendance], '$[2]' ) AS INT ) AS [CheckIns],
        CAST( JSON_VALUE( '["' + REPLACE( JSON_VALUE( [v].[Attendance], '$[3]' ), ',', '","' ) + '"]', '$[1]' ) AS INT ) AS [Days],
        ( SELECT CAST( [gv].[Value] AS INT ) FROM [DefinedValue] AS [gv] WHERE [gv].[Guid] = TRY_CAST( JSON_VALUE( [v].[Grade], '$[1]' ) AS UNIQUEIDENTIFIER ) ) AS [GradeOffset]
    FROM [Views] AS [v]
)
SELECT COUNT(*)
FROM [RegistrationRegistrant] AS [rr]
INNER JOIN [Registration] AS [r] ON [r].[Id] = [rr].[RegistrationId]
INNER JOIN [Rules] AS [rule] ON [rule].[TemplateId] = [rr].[RegistrationTemplateId]
INNER JOIN [PersonAlias] AS [pa] ON [pa].[Id] = [rr].[PersonAliasId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [pa].[PersonId]
CROSS APPLY ( SELECT CAST( [r].[CreatedDateTime] AS DATE ) AS [Day] ) AS [d]
WHERE [rr].[ForeignKey] = @ForeignKey
    AND EXISTS ( SELECT 1 FROM [Attendance] WHERE [ForeignKey] = @ForeignKey )
    AND ( ( [rule].[GradeOffset] IS NOT NULL AND ( [p].[GraduationYear] IS NULL
            OR [p].[GraduationYear] - ( YEAR( [d].[Day] ) + CASE WHEN DATEFROMPARTS( YEAR( [d].[Day] ), 6, 1 ) < [d].[Day] THEN 1 ELSE 0 END ) NOT BETWEEN 0 AND [rule].[GradeOffset] ) )
        OR ( [rule].[GroupTypes] IS NOT NULL AND (
            SELECT COUNT(*)
            FROM [Attendance] AS [a]
            INNER JOIN [AttendanceOccurrence] AS [o] ON [o].[Id] = [a].[OccurrenceId]
            INNER JOIN [Group] AS [g] ON [g].[Id] = [o].[GroupId]
            INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId]
            INNER JOIN [PersonAlias] AS [apa] ON [apa].[Id] = [a].[PersonAliasId]
            WHERE [apa].[PersonId] = [p].[Id] AND [a].[DidAttend] = 1
                AND [gt].[Guid] IN ( SELECT TRY_CAST( [value] AS UNIQUEIDENTIFIER ) FROM STRING_SPLIT( [rule].[GroupTypes], ',' ) )
                AND [o].[OccurrenceDate] >= DATEADD( DAY, 1 - [rule].[Days], [d].[Day] ) AND [o].[OccurrenceDate] <= [d].[Day] ) < [rule].[CheckIns] ) );

-- check: staff-attributes-belong-to-staff
-- why: Decision 201. The Staff person attributes describe staff, so every value belongs to an active person in RSR - Staff Workers. A value on anyone else would show a stranger as staff on a staff page.
SELECT COUNT(*)
FROM [AttributeValue] AS [av]
INNER JOIN [AttributeCategory] AS [ac] ON [ac].[AttributeId] = [av].[AttributeId]
INNER JOIN [Category] AS [c] ON [c].[Id] = [ac].[CategoryId]
INNER JOIN [Person] AS [p] ON [p].[Id] = [av].[EntityId]
LEFT JOIN [DefinedValue] AS [rs] ON [rs].[Id] = [p].[RecordStatusValueId]
WHERE [c].[Guid] = '6A3F015A-6CE1-401F-9C5F-8F29D33C99E9' AND [av].[ForeignKey] = @ForeignKey
    AND ( ISNULL( [rs].[Guid], '00000000-0000-0000-0000-000000000000' ) <> '618F906C-C33D-4FA3-8AEF-E58CB7B63F1E'
        OR NOT EXISTS ( SELECT 1 FROM [GroupMember] AS [gm] INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId] WHERE [gm].[PersonId] = [p].[Id] AND [g].[Guid] = '2C112948-FF4C-46E7-981A-0257681EADF4' ) );

-- check: every-staff-member-has-title-department-and-start
-- why: Decision 201. A staff page lists people by title and department, and staff screens sort by start date. A staff member with one value but not the others would show half a card.
SELECT COUNT(*)
FROM ( SELECT DISTINCT [av].[EntityId]
    FROM [AttributeValue] AS [av]
    INNER JOIN [AttributeCategory] AS [ac] ON [ac].[AttributeId] = [av].[AttributeId]
    INNER JOIN [Category] AS [c] ON [c].[Id] = [ac].[CategoryId]
    WHERE [c].[Guid] = '6A3F015A-6CE1-401F-9C5F-8F29D33C99E9' AND [av].[ForeignKey] = @ForeignKey ) AS [s]
WHERE ( SELECT COUNT( DISTINCT [a].[Key] )
    FROM [AttributeValue] AS [av]
    INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId]
    WHERE [av].[EntityId] = [s].[EntityId] AND [a].[Key] IN ( 'StaffTitle', 'StaffDepartment', 'StaffStartDate' ) AND [a].[EntityTypeId] = ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Person' )
        AND ISNULL( [av].[Value], '' ) <> '' ) <> 3;

-- check: staff-supervisor-is-staff
-- why: Decision 201. A supervisor is a staff member's alias, and ValueAsPersonId is that person, as Rock's save hook sets it. A supervisor who is not staff, or a person Id that does not match the alias, would put the wrong boss on the profile.
SELECT COUNT(*)
FROM [AttributeValue] AS [av]
INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId]
LEFT JOIN [PersonAlias] AS [pa] ON CONVERT( nvarchar(50), [pa].[Guid] ) = [av].[Value]
WHERE [a].[Key] = 'StaffSupervisor' AND [a].[EntityTypeId] = ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Person' ) AND [av].[ForeignKey] = @ForeignKey
    AND ( [pa].[Id] IS NULL OR ISNULL( [av].[ValueAsPersonId], 0 ) <> [pa].[PersonId]
        OR NOT EXISTS ( SELECT 1 FROM [AttributeValue] AS [t] INNER JOIN [Attribute] AS [ta] ON [ta].[Id] = [t].[AttributeId] WHERE [ta].[Key] = 'StaffTitle' AND [ta].[EntityTypeId] = [a].[EntityTypeId] AND [t].[EntityId] = [pa].[PersonId] ) );

-- check: every-campus-has-one-campus-pastor
-- why: Decisions 198 and 201. Each campus in the dataset has exactly one named campus pastor, in the Campus Pastors department with that campus, who is in RSR - Pastoral Workers to read the notes they write. A load with staff attributes and a campus with none, or two, breaks the calendar, welcomes, and notes that name them.
SELECT COUNT(*)
FROM [Campus] AS [cm]
WHERE EXISTS ( SELECT 1 FROM [Category] WHERE [Guid] = '6A3F015A-6CE1-401F-9C5F-8F29D33C99E9' )
    AND EXISTS ( SELECT 1 FROM [Person] AS [p] WHERE [p].[PrimaryCampusId] = [cm].[Id] AND [p].[ForeignKey] = @ForeignKey )
    AND ( SELECT COUNT(*)
        FROM [AttributeValue] AS [d]
        INNER JOIN [Attribute] AS [da] ON [da].[Id] = [d].[AttributeId] AND [da].[Key] = 'StaffDepartment'
        INNER JOIN [AttributeValue] AS [k] ON [k].[EntityId] = [d].[EntityId]
        INNER JOIN [Attribute] AS [ka] ON [ka].[Id] = [k].[AttributeId] AND [ka].[Key] = 'StaffCampus' AND [ka].[EntityTypeId] = [da].[EntityTypeId]
        WHERE [da].[EntityTypeId] = ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Person' ) AND [d].[Value] = 'campuses' AND [k].[Value] = LOWER( CONVERT( nvarchar(50), [cm].[Guid] ) )
            AND EXISTS ( SELECT 1 FROM [GroupMember] AS [gm] INNER JOIN [Group] AS [g] ON [g].[Id] = [gm].[GroupId] WHERE [gm].[PersonId] = [d].[EntityId] AND [g].[Guid] = '26E7148C-2059-4F45-BCFE-32230A12F0DC' ) ) <> 1;

-- check: org-chart-members-are-staff
-- why: Decision 202. Rock's Org Chart page shows the Organization Unit groups, so everyone on them is a staff member with a Staff Title. A volunteer there would read as staff.
SELECT COUNT(*)
FROM [GroupMember] AS [gm]
INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [gm].[GroupTypeId]
WHERE [gt].[Guid] = 'AAB2E9F4-E828-4FEE-8467-73DC9DAB784C' AND [gm].[ForeignKey] = @ForeignKey
    AND NOT EXISTS ( SELECT 1 FROM [AttributeValue] AS [av] INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId]
        WHERE [a].[Key] = 'StaffTitle' AND [a].[EntityTypeId] = ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Person' ) AND [av].[EntityId] = [gm].[PersonId] );

-- check: every-staff-member-is-on-the-org-chart
-- why: Decision 202. Every staff member in the dataset belongs to at least one Organization Unit group, so the Org Chart page shows the whole staff.
SELECT COUNT(*)
FROM [AttributeValue] AS [av]
INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId]
WHERE [a].[Key] = 'StaffTitle' AND [a].[EntityTypeId] = ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Person' ) AND [av].[ForeignKey] = @ForeignKey
    AND EXISTS ( SELECT 1 FROM [Group] AS [g] INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [g].[GroupTypeId] WHERE [gt].[Guid] = 'AAB2E9F4-E828-4FEE-8467-73DC9DAB784C' AND [g].[ForeignKey] = @ForeignKey )
    AND NOT EXISTS ( SELECT 1 FROM [GroupMember] AS [gm] INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [gm].[GroupTypeId]
        WHERE [gt].[Guid] = 'AAB2E9F4-E828-4FEE-8467-73DC9DAB784C' AND [gm].[PersonId] = [av].[EntityId] );

-- check: org-unit-leader-supervises-its-staff
-- why: Decisions 201 and 202. A unit's staff report to its leader, so each Staff-role member's Supervisor is a Leader of that unit. Otherwise the Org Chart and the profile would disagree about who someone reports to.
SELECT COUNT(*)
FROM [GroupMember] AS [gm]
INNER JOIN [GroupType] AS [gt] ON [gt].[Id] = [gm].[GroupTypeId]
INNER JOIN [GroupTypeRole] AS [r] ON [r].[Id] = [gm].[GroupRoleId]
WHERE [gt].[Guid] = 'AAB2E9F4-E828-4FEE-8467-73DC9DAB784C' AND [gm].[ForeignKey] = @ForeignKey AND [r].[IsLeader] = 0
    AND NOT EXISTS (
        SELECT 1
        FROM [AttributeValue] AS [sv]
        INNER JOIN [Attribute] AS [sa] ON [sa].[Id] = [sv].[AttributeId] AND [sa].[Key] = 'StaffSupervisor'
        INNER JOIN [GroupMember] AS [lead] ON [lead].[GroupId] = [gm].[GroupId] AND [lead].[PersonId] = [sv].[ValueAsPersonId]
        INNER JOIN [GroupTypeRole] AS [lr] ON [lr].[Id] = [lead].[GroupRoleId] AND [lr].[IsLeader] = 1
        WHERE [sa].[EntityTypeId] = ( SELECT [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Person' ) AND [sv].[EntityId] = [gm].[PersonId] );

-- check: data-view-filter-names-its-related-view
-- why: Decision 225. Rock's data view editor saves the view a filter points at in RelatedDataViewId, and Rock's data view page lists the views built on this one from it. An Existing Data View filter whose related view is missing or differs from its selection would leave Active People showing no views that use it.
SELECT COUNT(*)
FROM [DataViewFilter] AS [f]
INNER JOIN [EntityType] AS [et] ON [et].[Id] = [f].[EntityTypeId]
WHERE [f].[ForeignKey] = @ForeignKey
    AND [et].[Name] IN ( 'Rock.Reporting.DataFilter.OtherDataViewFilter', 'Rock.Reporting.DataFilter.NotInOtherDataViewFilter' )
    AND ISNULL( CASE WHEN ISJSON( [f].[Selection] ) = 1 THEN TRY_CAST( JSON_VALUE( [f].[Selection], '$.DataViewId' ) AS int ) END, -1 ) <> ISNULL( [f].[RelatedDataViewId], -2 );

-- check: reporting-run-history-is-whole
-- why: Decision 233. Rock shows "Runs Since" and "Last Run" on each data view and report. Every report, and each data view with a history, has run at least once after it was made, and a view's run count starts when it was made, as Rock's save hook sets it. A view with half a history, or a last run before it existed, would read as broken.
SELECT
    ( SELECT COUNT(*) FROM [Report]
      WHERE [ForeignKey] = @ForeignKey
        AND ( [RunCount] IS NULL OR [RunCount] < 1 OR [LastRunDateTime] IS NULL OR [LastRunDateTime] < [CreatedDateTime] ) )
    + ( SELECT COUNT(*) FROM [DataView]
        WHERE [ForeignKey] = @ForeignKey
            AND ( [RunCount] IS NOT NULL OR [LastRunDateTime] IS NOT NULL )
            AND ( [RunCount] IS NULL OR [RunCount] < 1 OR [LastRunDateTime] IS NULL OR [LastRunDateTime] < [CreatedDateTime]
                OR [RunCountLastRefreshDateTime] IS NULL OR [RunCountLastRefreshDateTime] <> [CreatedDateTime] ) );

-- check: reporting-made-after-what-it-uses
-- why: Decision 233. Staff build a view after the views it points at, and a report after its view. A view older than the view it uses would show a history no church could have.
SELECT
    ( SELECT COUNT(*)
      FROM [DataViewFilter] AS [f]
      INNER JOIN [DataView] AS [dv] ON [dv].[Id] = [f].[DataViewId]
      INNER JOIN [DataView] AS [used] ON [used].[Id] = [f].[RelatedDataViewId]
      WHERE [dv].[ForeignKey] = @ForeignKey AND [dv].[LastRunDateTime] IS NOT NULL AND [used].[LastRunDateTime] IS NOT NULL
        AND [dv].[CreatedDateTime] <= [used].[CreatedDateTime] )
    + ( SELECT COUNT(*)
        FROM [Report] AS [r]
        INNER JOIN [DataView] AS [dv] ON [dv].[Id] = [r].[DataViewId]
        WHERE [r].[ForeignKey] = @ForeignKey AND [dv].[LastRunDateTime] IS NOT NULL AND [r].[CreatedDateTime] <= [dv].[CreatedDateTime] );

-- check: one-retired-data-view
-- why: Decision 233. Exactly one church data view has not run in over a year, so Rock's advice to remove unused data views has one clear example. Every other view ran in the two weeks before install. The year counts back from the newest run, which is the install's last few days.
SELECT CASE WHEN (
        SELECT COUNT(*) FROM [DataView]
        WHERE [ForeignKey] = @ForeignKey
            AND [LastRunDateTime] < DATEADD( day, -365, ( SELECT MAX( [LastRunDateTime] ) FROM [DataView] WHERE [ForeignKey] = @ForeignKey ) ) ) = 1
    THEN 0 ELSE 1 END;

-- check: attribute-value-has-rock-derived-columns
-- why: Decision 249. Rock's save hook (AttributeValue.UpdateValueAsProperties, the same in Rock 20 and 21) fills ValueAsBoolean, ValueAsNumeric, ValueAsDateTime, and ValueAsPersonId from Value, and a data view filter on an attribute reads those columns, not Value. Every value a load wrote has them as Rock would: the boolean for a value of 1 to 5 characters, the number whenever SQL Server reads Value as one, the date whenever Value is an ISO date, and the person behind a Person attribute's alias.
SELECT COUNT(*)
FROM [AttributeValue] AS [av]
INNER JOIN [Attribute] AS [a] ON [a].[Id] = [av].[AttributeId]
INNER JOIN [FieldType] AS [ft] ON [ft].[Id] = [a].[FieldTypeId]
LEFT JOIN [PersonAlias] AS [pa] ON [ft].[Guid] = 'E4EAB7B2-0B76-429B-AFE4-AD86D7428C70' AND [pa].[Guid] = TRY_CONVERT( UNIQUEIDENTIFIER, [av].[Value] )
CROSS APPLY ( SELECT
    CASE WHEN DATALENGTH( [av].[Value] ) BETWEEN 2 AND 10 THEN CASE WHEN LOWER( [av].[Value] ) IN ( '1', 'y', 't', 'yes', 'true' ) THEN 1 ELSE 0 END END AS [Boolean],
    CASE WHEN LEN( [av].[Value] ) <= 24 THEN TRY_CONVERT( DECIMAL( 18, 2 ), [av].[Value] ) END AS [Numeric],
    CASE WHEN [av].[Value] LIKE '[12][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]%' THEN TRY_CONVERT( DATETIME2, [av].[Value] ) END AS [Date] ) AS [rock]
WHERE [av].[ForeignKey] = @ForeignKey
    AND ( ISNULL( CAST( [av].[ValueAsBoolean] AS INT ), -1 ) <> ISNULL( [rock].[Boolean], -1 )
        OR ( [rock].[Numeric] IS NOT NULL AND ( [av].[ValueAsNumeric] IS NULL OR [av].[ValueAsNumeric] <> [rock].[Numeric] ) )
        OR ( [rock].[Date] IS NOT NULL AND ( [av].[ValueAsDateTime] IS NULL OR ABS( DATEDIFF_BIG( MILLISECOND, [av].[ValueAsDateTime], [rock].[Date] ) ) > 4 ) )
        OR ISNULL( [av].[ValueAsPersonId], -1 ) <> ISNULL( [pa].[PersonId], -1 ) );