# Configuration Structure

### Team Configuration:

{
    Id: "Guid",
    ConfigurationOrigin: "String",
    TeamMemberTypes: "ICollection<TeamMemberType>",
    CustomFields: "ICollection<CustomField>",
    LogCategories: "ICollection<LogCategory>",
    Abilities: "ICollection<Ability>",
    FormMemberships: "ICollection<FormMembership>",
    AppointmentTypes: "ICollection<AppointmentType",
}

### Team Member Type:

{
    Id: "Guid",
    ConfigurationOrigin: "String",
    Name: "String",
    Description: "String",
    LabelSingular: "String",
    LabelPlural: "String",
    LogConfigId: "Guid FK=LogConfigs.Id",
    AppointmentsConfigId: "Guid FK=AppointmentsConfig.Id"
}

### Log Config:

{
    Enabled: "Bool",
    ViewAbilityId: "Guid FK=Abilities.Id",
    ARAdd: "Guid FK=Abilities.Id",
    AREditAny: "Guid FK=Abilities.Id",
    AREditOwn: "Guid FK=Abilities.Id"
},

### Appointments Config:

{
    Enabled: "Bool"
},

### Custom Field:

{
    Id: "Guid",
    TeamMemberTypeId: "Guid FK=TeamMemberTypes.Id",
    ChartType: "String, one of: TeamMember, TeamMemberTeamEnrollment, or Service"
    ConfigurationOrigin: "String",
    Label: "String",
    DataType: "String", //One of: Int (aka 4), Bool, String, DateTime, Date, Double (aka 4.5), *future: something nested?*
    FieldType: "String", //One of: Text, DatePicker, DateTimePicker, Options, Checkboxes, Switch, Select
    DataLevel: "String", //Nominal, Ordinal, Interval, Ratio
    ChapterLocation: "String",
    SectionLocation: "String",
    OrderWeight: "Int",
    Required: "String",
    ARView: "Guid? FK=Abilities.Id",
    AREdit: "Guid? FK=Abilities.Id"
}

### Log Category:

{
    Id: "Guid",
    TeamMemberTypeId: "Guid FK=TeamMemberType.Id",
    ConfigurationOrigin: "String",
    GroupingLabel: "String",
    CategoryLabel: "String",
}

### Ability:

{
    Id: "Guid",
    TeamMemberTypeId: "Guid FK=TeamMemberType.Id",
    ConfigurationOrigin: "String",
    AbilitySource: "String: one of: System, User"
    Name: "String"
}

### Security Role:

{
    Id: "Guid",
    TeamMemberTypeId: "Guid FK=TeamMemberType.Id",
    ConfigurationOrigin: "String",
    Name: "String",
    Abilities: "Array of AbilityId"
}

### Forms Membership:

{
    Id: "Guid",
    TeamMemberTypeId: "Guid FK=TeamMemberType.Id",
    ConfigurationOrigin: "String",
    FormId: "String (from DMS)",
    StartDate: "Date",
    EndDate: "Date",
    CreateDocumentAbilityId: "Guid FK=Abilities.Id",
    EditAnyDocumentAbilityId: "Guid FK=Abilities.Id",
    EditOwnDocumentAbilityId: "Guid FK=Abilities.Id",
    ViewAnyDocumentAbilityId: "Guid FK=Abilities.Id",
    ViewOwnDocumentAbilityId: "Guid FK=Abilities.Id",
}

### Appointment Type:

{
    Id: "Guid",
    TeamMemberTypeId: "Guid FK=TeamMemberType.Id",
    ConfigurationOrigin: "String",
    StartDate: "Date",
    EndDate: "Date",
    Label: "String",
    OccurredFormMembershipId: "Guid FK=FormMemberships.Id",
    CancelledFormMembershipId: "Guid FK=FormMemberships.Id",
    ARCreateAppointment: "Guid FK=Abilities.Id",
    AREditAnyAppointment: "Guid FK=Abilities.Id",
    AREditOwnAppointment: "Guid FK=Abilities.Id",
    ARViewAnyAppointment: "Guid FK=Abilities.Id",
    ARViewOwnAppointment: "Guid FK=Abilities.Id",
}

### Custom Field Value:

{
    Id: "Guid",
    TeamMemberId: "Guid?",
    TeamId: "Guid?",
    ServiceId: "Guid?",
    CustomFieldId: "Guid",
    CustomField: {
        Id: "Guid",
        TeamMemberTypeId: "Guid FK=TeamMemberType.Id",
        ConfigurationOrigin: "String",
        Label: "String",
        DataType: "String", //One of: Int (aka 4), Bool, String, DateTime, Date, Double (aka 4.5), *future: something nested?*
        FieldType: "String", //One of: Text, DatePicker, DateTimePicker, Options, Checkboxes, Switch, Select
        DataLevel: "String", //Nominal, Ordinal, Interval, Ratio
        ChapterLocation: "String",
        SectionLocation: "String",
        OrderWeight: "Int",
        Required: "String",
        ViewAbilityId: "Guid FK=Abilities.Id",
        EditAbilityId: "Guid FK=Abilities.Id"
    }
    FieldValue: "String" //always stored in a standard parsable way as a string for example for a date:  yyyy-MM-dd Monday
}

### Team Member:

{
    Id: "Guid",
    TeamMemberTypeId: "Guid",
    ...
    CustomFieldValues: "ICollection<CustomFieldValue>"
}

### Reporting Example:
#### Filtering:

//If we want to apply the following filters:
//    DOB > 2015-01-01 (custom field id = "123")
//    FirstName != "TEST" (custom field id = "555")
IQueryable<TeamMember> teamMembersFilteredQuery = dbContext.TeamMembers
    .Where(tm => tm.Id == {teamId});

//Filter by Team Members who have a DOB After 2015-01-01
teamMembersFilteredQuery = TeamMembersFilteredQuery
    .Where(tm => tm.CustomFieldValues
        .Where(cfv => 
            cfv.CustomFieldId == "123" && 
            cfv.FieldValue > '2015-01-01')
        .Count() > 0);

//Filter by Team Members who have First Name != 'Test'
teamMembersFilteredQuery = TeamMembersFilteredQuery
    .Where(tm => tm.CustomFieldValues
        .Where(cfv => 
            cfv.CustomFieldId == "555" && 
            cfv.FieldValue != 'TEST')
        .Count() > 0);

//Get the Data:
TeamMember[] teamMembers = teamMembersFilterQuery
    .Include(tm => tm.CustomFieldValues).ThenInclude(cfv => cfv.CustomField)
    .ToArray();

CustomFieldValue[] customFieldValuesFlat = teamMembers
    .SelectMany(tm => tm.CustomFieldValues)
    .ToArray();
    

#### Aggregating:

//Split By (AKA Group By) by Last Name (CFId = 333) and First Name (CFId = 555) with the following columns:
//Height (Mean) (CFId = 999) (FieldType = int AKA height in inches)
//DOB (Min) (CFId = 123)

string[] uniqueLastNames = customFieldValuesFlat
    .Where(cfv => cfv.CustomFieldId == 333)
    .Select(cfv => cfv.FieldValue)
    .Distinct();
string[] uniqueFirstNames = customFieldValuesFlat
    .Where(cfv => cfv.CustomFieldId == 555)
    .Select(cfv => cfv.FieldValue)
    .Distinct();
// uniqueLastNames should look like this: ['Smith',Stickney','Hranek', 'Niu', 'Vergilio']

foreach(string lastName in uniqueLastNames)
{
    foreach(string firstName in uniqueFirstNames)
    {
        var meanHeight = teamMembers
            .Where(tm =>
                tm.CustomFieldValues
                    .Where(cfv => 
                        cfv.CustomFieldId == 333 && 
                        cfv.FieldValue == lastName)
                    .Count() > 0 &&
                tm.CustomFieldValues
                    .Where(cfv => 
                        cfv.CustomFieldId == 333 && 
                        cfv.FieldValue == firstName)
                    .Count() > 0)
            .SelectMany(tm => tm.CustomFieldValues)
            .Where(cfv => cfv.CustomFieldId == 999)
            .Average(cfv => int.Parse(cfv.FieldValue));
    }
}

//or like this for dynamic:
IQueryable<TeamMember> teamMemberQueryForAggregate = teamMembers
    .Where(tm => tm.Id != null);
foreach(string lastName in uniqueLastNames)
{
    teamMemberQueryForAggregate = teamMemberQueryForAggregate
            .Where(tm =>
                tm.CustomFieldValues
                    .Where(cfv => 
                        cfv.CustomFieldId == 333 && 
                        cfv.FieldValue == lastName)
                    .Count() > 0);
    foreach(string firstName in uniqueFirstNames)
    { 
        
                    
            teamMemberQueryForAggregate = teamMemberQueryForAggregate
            .Where(tm =>
                tm.CustomFieldValues
                    .Where(cfv => 
                        cfv.CustomFieldId == 999 && 
                        cfv.FieldValue == firstName)
                    .Count() > 0);
            
            var meanHeight = teamMemberQueryForAggregate
                .SelectMany(tm => tm.CustomFieldValues)
                .Where(cfv => cfv.CustomFieldId == 999)
                .Average(cfv => int.Parse(cfv.FieldValue));
    }
}