number_services_same_type = selected_service_compare
  .rollup({
      count: op.count()
  })
   .objects()[0].count
   
// NUMBERS OF SERVICES WITH NO GRADE
number_services_same_type_no_grade = selected_service_compare
 .filter(d => op.includes("No grade yet", d.MinGrade))
  .rollup({
      count: op.count()
  })
   .objects()[0].count
   
// TEXT STRING SUBTITLE
selected_serv_type_subtitle  = {
  if (service_selected.CareService) {
    return ( "There are " + number_services_same_type + " of these services, " + number_services_same_type_no_grade + " have not yet received grades" );
  } else {
    return "";
  }
}

perc_services_same_grade = selected_service_compare
.params({
  // define parameters
    selected_MinGrade: service_selected.MinGrade
  })
  // first filter out those that don't have any grades
  .filter(d  => d.MinGrade_numeric >= 1)
  // group by each grade
  .groupby("MinGrade")
  // calculate counts of services with each mingrade
  .rollup({
      count: op.count()
  })
  // create new column for proportion of services with each grade
  .derive({ percent: d => op.round((d.count / op.sum(d.count) || 0)*100) }) // include `|| 0` in case sum is zero
  // filter out match selected grade
  .filter((d, $) => op.includes($.selected_MinGrade, d.MinGrade))
  // pull out percent value
   .objects()[0].percent
  
   
// PERCENTAGE OF SERVICES WITH HIGHER GRADE
perc_services_higher_grade = selected_service_compare
.params({
  // define parameters
     selected_MinGrade_numeric: service_selected.MinGrade_numeric
  })
  // first filter out those that don't have any grades
  .filter(d  => d.MinGrade_numeric >= 1)
  // group by each grade
  .groupby("MinGrade_numeric")
  // calculate counts of services with each mingrade
  .rollup({
      count: op.count()
  })
  // create new column for proportion of services with each grade
  .derive({ percent: d => op.round((d.count / op.sum(d.count) || 0)*100) }) // include `|| 0` in case sum is zero
  // Ungroup by minimum grade
  .ungroup()
  // filter out match selected grade
  .filter((d, $) => d.MinGrade_numeric > $.selected_MinGrade_numeric)
  .derive({total: d => op.sum(d.percent)})
  // pull out total value
  .object(1)?.total ?? 0
   
// TEXT STRING SUBTITLE
selected_serv_grade_subtitle  = {
  if (service_selected.CareService) {
    return ( "Same as " + perc_services_same_grade + "% of services of the same type, whilst " + perc_services_higher_grade + "% have a higher minimum grade" );
  } else {
    return "";
  }
}

inspection_check = {
  if(service_selected.months_since_inspection >=0) {
    return (service_selected.months_since_inspection + " month(s) since last inspection");
  } else {
    return "No inspection yet";
  }
}

// SELECTED SERVICE LAST INSPECTION
selected_serv_last_inspection  = {
  if (service_selected.CareService) {
    return inspection_check; 
  } else {
    return "Select a service";
  }
}

median_time_last_inspection = selected_service_compare 
  // filter out those that haven't yet been inspected
 .filter(d => d.months_since_inspection >= 0)
  .rollup({
    count: op.count(),
    median_months_since_inspection: op.median('months_since_inspection')
   })
   .objects()[0].median_months_since_inspection
   
// TEXT STRING SUBTITLE
selected_serv_last_inspection_subtitle  = {
  if (service_selected.CareService) {
    return "The average (median) time since the last inspection for services of this type is " + median_time_last_inspection + " month(s)";
  } else{
    return "";
  }
}

staff_check = {
  if(service_selected.staff_per_place >=0) {
    return (service_selected.staff_per_place.toFixed(2) + " staff per registered place");
  } else {
    return "No staff recorded";
  }
}

// SELECTED SERVICE LAST INSPECTION
staff_per_place  = {
  if (service_selected.CareService) {
    return staff_check; 
  } else {
    return "Select a service";
  }
}

median_staff = selected_service_compare 
 .filter(d => d.staff_per_place > 0)
  .rollup({
    count: op.count(),
    median_staff_per_place: op.median('staff_per_place')
   })
  .objects()[0]?.median_staff_per_place ?? "No staff recorded"
  
  
// First check for those that are undefined as all services have had no complaints
staff_undefined_check = {
  if(median_staff !== "No staff recorded"){
    return "The average (median) number of staff per registered place for services of this type is " + median_staff.toFixed(2);
  } else {
    return "No services of this type had any staff recorded";
  }
}
  
// TEXT STRING SUBTITLE
median_staff_subtitle  = {
  if (service_selected.CareService) {
    return staff_undefined_check;
  } else{
    return "";
  }
}

complaints_check = {
  if(service_selected.Complaints_upheld_2425 >=0) {
    return (service_selected.Complaints_upheld_2425 + " upheld complaint(s) between 1 April 2024 and 31 March 2025");
  } else {
    return "No upheld complaints between 1 April 2024 and 31 March 2025";
  }
}

// SELECTED SERVICE COMPLAINTS
complaints  = {
  if (service_selected.CareService) {
    return complaints_check ; 
  } else {
    return "Select a service";
  }
}

perc_services_complaint = selected_service_compare
  // group by each grade
  .groupby("Complaints_upheld_2425")
  // calculate counts of services with all the varying numbers of upheld complaints
  .rollup({
      count: op.count()
  })
  // create new column for proportion of services with each number of complaints
  .derive({ percent: d => op.round((d.count / op.sum(d.count) || 0)*100) }) // include `|| 0` in case sum is zero
  // Ungroup by number of complaints
  .ungroup()
  // filter out anything less than 1
  .filter((d) => d.Complaints_upheld_2425 >= 1)
  .derive({total: d => op.sum(d.percent)})
  // pull out total value
  .objects()[0]?.total ?? 0

// First check for those that are undefined as all services have had no complaints
complaints_undefined_check = {
  if(perc_services_complaint !== 0){
    return perc_services_complaint + "% of services of this type had at least one upheld complaint between 1 April 2024 and 31 March 2025";
  } else {
    return "No services of this type had at least one upheld complaint between 1 April 2024 and 31 March 2025";
  }
}
  
// TEXT STRING SUBTITLE
complaints_subtitle  = {
  if (service_selected.CareService) {
    return complaints_undefined_check;
  } else{
    return "";
  }
}

requirements_check = {
  if(service_selected.any_requirements_2425 === "Yes") {
    return ("This service had requirement(s) from inspections between 1 April 2024 and 31 March 2025");
  } else  if(service_selected.any_requirements_2425 === "Inspected but no requirements") {
    return "This service had no requirement(s) from inspections between 1 April 2024 and 31 March 2025";
  } else {
  return "This service was not inspected between 1 April 2024 and 31 March 2025"
  }
}

// SELECTED SERVICE REQUIREMENTS
requirements  = {
  if (service_selected.CareService) {
    return requirements_check ; 
  } else {
    return "Select a service";
  }
}

perc_services_requirement = selected_service_compare
  // First filter out those that have not been inspected (i.e. blank field)
  .filter((d) => d.any_requirements_2425 !== "")
  // group by each grade
  .groupby("any_requirements_2425")
  // calculate counts of services with all the varying categories of requirements (yes, inspected but none, and blank)
  .rollup({
      count: op.count()
  })
  // create new column for proportion of services with each number of requirements
  .derive({ percent: d => op.round((d.count / op.sum(d.count) || 0)*100) }) // include `|| 0` in case sum is zero
  // Ungroup by number of requirements
  .ungroup()
  // filter only those that are "Yes"
  .filter((d) => d.any_requirements_2425 === "Yes")
  .derive({total: d => op.sum(d.percent)})
  // pull out total value
  .objects()[0]?.total ?? 0

// First check for those that are undefined as all services have had no complaints
requirements_undefined_check = {
  if(perc_services_requirement !== 0){
    return perc_services_requirement + "% of services of this type that had been inspected between 1 April 2024 and 31 March 2025 had at least one requirement";
  } else {
    return "No services of this type that had been inspected between 1 April 2024 and 31 March 2025 had any requirements";
  }
}
  
// TEXT STRING SUBTITLE
requirements_subtitle  = {
  if (service_selected.CareService) {
    return requirements_undefined_check;
  } else{
    return "";
  }
}