Implementation Guide: Auto-schedule follow-up appointments based on visit type and protocol

# For Open Dental: Query current recall types and intervals
# Connect to MySQL database (Open Dental)
mysql -u root -p opendental
SELECT RecallTypeNum, Description, DefaultInterval, TimePattern FROM recalltype;
# Query patients overdue for recall
SELECT p.PatNum, p.LName, p.FName, r.DateDue, rt.Description FROM recall r INNER JOIN patient p ON r.PatNum = p.PatNum INNER JOIN recalltype rt ON r.RecallTypeNum = rt.RecallTypeNum WHERE r.DateDue < CURDATE() AND r.IsDisabled = 0 ORDER BY r.DateDue ASC LIMIT 100;
# For Dentrix: Use Dentrix API Exchange to pull continuing care data
# Endpoint: GET /v1/continuingcare?patientId={id}
# Requires Dentrix Developer API key - obtain from Henry Schein One partner portal

# For on-premises server - verify specs
systeminfo | findstr /C:"Total Physical Memory" /C:"OS Name" /C:"System Type"
wmic cpu get Name, NumberOfCores, MaxClockSpeed
wmic diskdrive get Size, Model

# Check available disk space
wmic logicaldisk get Caption, FreeSpace, Size

# Verify Windows Server version
winver

# Apply all pending Windows Updates
Install-WindowsUpdate -MicrosoftUpdate -AcceptAll -AutoReboot

# Configure Windows Firewall - allow outbound HTTPS for SaaS platforms
New-NetFirewallRule -DisplayName "Allow HTTPS Outbound" -Direction Outbound -Protocol TCP -RemotePort 443 -Action Allow

# Verify DNS resolution to engagement platform
nslookup api.nexhealth.com
nslookup app.nexhealth.com

# Test internet bandwidth (from server)
# Use speedtest-cli or browser-based test - minimum 25/10 Mbps

# Install MSP RMM agent (example: ConnectWise Automate)
# Download agent from MSP's RMM portal and run installer
msiexec /i "Agent_Install.msi" /quiet SERVERADDRESS=rmm.yourmsp.com

# Configure UPS monitoring (if APC Smart-UPS installed)
# Install PowerChute Business Edition on server
# Set graceful shutdown threshold to 5 minutes remaining battery

# OPEN DENTAL: Configure Recall Types via MySQL (or use GUI: Setup > Appointments > Recall Types)
# Example: Ensure D1110 (Adult Prophy) triggers 6-month recall
# In Open Dental GUI: Setup > Appointments > Recall Types > Prophy
# Set Interval to 6 months, Trigger Procedures to D1110
# Set Appointment Type to 'Prophy/Recall'

# Verify recall types via SQL:
SELECT rt.RecallTypeNum, rt.Description, rt.DefaultInterval, 
       GROUP_CONCAT(rtt.CodeNum) AS TriggerCodes
FROM recalltype rt
LEFT JOIN recalltrigger rtt ON rt.RecallTypeNum = rtt.RecallTypeNum
GROUP BY rt.RecallTypeNum;

# OPEN DENTAL: Configure Perio Maintenance recall
# Trigger procedure: D4341 (SRP), D4342 (SRP 1-3 teeth)
# Interval: 3 months (after initial 4-6 week post-op)
# In GUI: Setup > Appointments > Recall Types > Add New
# Description: 'Perio Maintenance'
# Default Interval: 3 months
# Trigger Procedures: D4341, D4342

# DENTRIX: Configure Continuing Care
# Navigate to: Office Manager > Maintenance > Practice Setup > Continuing Care Types
# For each visit type, set:
#   - Type name (e.g., 'Prophy Recall')
#   - Interval (e.g., 6 months)
#   - Attached procedure codes
#   - Auto-generate preference: ON

# EAGLESOFT: Configure Recall
# Navigate to: Activities > Recall Setup
# Define recall types with procedure codes and intervals

# Document all configurations in the Protocol Mapping spreadsheet
# Template columns: Procedure_Code | Description | Follow_Up_Type | Interval_Days | Provider_Constraint | Operatory_Constraint | Time_Block_Preference

# OPEN DENTAL: Verify schedule exists for future dates
SELECT ScheduleDate, ProvNum, StartTime, StopTime, SchedType
FROM schedule
WHERE ScheduleDate > CURDATE()
AND ScheduleDate < DATE_ADD(CURDATE(), INTERVAL 180 DAY)
ORDER BY ScheduleDate, ProvNum
LIMIT 50;

# Check for gaps in provider schedules
SELECT DISTINCT ScheduleDate FROM schedule
WHERE ScheduleDate > CURDATE()
AND ScheduleDate < DATE_ADD(CURDATE(), INTERVAL 90 DAY)
AND ProvNum = [PROVIDER_NUM]
ORDER BY ScheduleDate;

# OPEN DENTAL: Verify operatory configuration
SELECT OperatoryNum, OpName, ProvDentist, ProvHygienist, IsHygiene
FROM operatory
WHERE IsHidden = 0;

# Verify appointment type durations are correctly set
# OPEN DENTAL: Setup > Appointments > Appointment Types
SELECT AppointmentTypeNum, AppointmentTypeName, CodeStr, Pattern
FROM appointmenttype;

# NexHealth API verification (for MSPs using the API directly):
curl -X GET 'https://nexhealth.com/api/v1/appointments?location_id={location_id}&start_date=2025-01-01&end_date=2025-12-31' \
  -H 'Authorization: Bearer {api_key}' \
  -H 'Accept: application/json'

# Verify patient data sync:
curl -X GET 'https://nexhealth.com/api/v1/patients?location_id={location_id}&page=1&per_page=10' \
  -H 'Authorization: Bearer {api_key}' \
  -H 'Accept: application/json'

<iframe src='https://booking.nexhealth.com/{practice_slug}'
  width='100%' height='700' frameborder='0'></iframe>

https://booking.nexhealth.com/{practice_slug}?appointment_type={type_id}

# Create test patients in PMS (use clearly marked test names)
# OPEN DENTAL: Add test patients via GUI or API
curl -X POST 'https://api.opendental.com/api/v1/patients' \
  -H 'Authorization: ODSESSIONID={session_id}' \
  -H 'Content-Type: application/json' \
  -d '{
    "LName": "TESTPATIENT",
    "FName": "AutoScheduleTest",
    "Birthdate": "1990-01-01",
    "Gender": 1
  }'

# Test Scenario 1: Prophy Recall Auto-Schedule
# 1. Set procedure D1110 to 'Complete' for test patient
# 2. Verify recall due date calculated (should be +6 months)
# 3. Verify NexHealth received the trigger event
# 4. Verify patient received SMS/email notification
# 5. Click self-schedule link - verify available slots shown
# 6. Book appointment via portal
# 7. Verify appointment appears in PMS within 60 seconds

# Test Scenario 2: Post-Op Auto-Book
# 1. Set procedure D7210 to 'Complete' for test patient
# 2. Verify post-op appointment auto-created in PMS (+7 days)
# 3. Verify confirmation SMS sent to test phone number
# 4. Verify appointment details correct (provider, duration, type)

# Test Scenario 3: Edge Cases
# 1. Complete a procedure when no provider slots available in the interval window
#    - Expected: System queues the request and alerts front desk
# 2. Complete a procedure for patient who already has a future appointment of that type
#    - Expected: System does not create duplicate; logs the skip
# 3. Patient reschedules via SMS reply
#    - Expected: Original slot freed, new slot booked, PMS updated
# 4. Patient opts out of SMS
#    - Expected: No further automated messages; front desk alerted for manual follow-up

# Verify all test results and document in Testing Log spreadsheet

curl -X GET 'https://nexhealth.com/api/v1/analytics/appointments?location_id={location_id}&start_date={start}&end_date={end}' \
  -H 'Authorization: Bearer {api_key}' \
  -H 'Accept: application/json'

-- query auto-created appointments from the last 7 days

SELECT a.AptNum, a.AptDateTime, a.AptStatus, p.LName, p.FName, a.Note
FROM appointment a
INNER JOIN patient p ON a.PatNum = p.PatNum
WHERE a.DateTStamp > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND a.Note LIKE '%auto%'
ORDER BY a.AptDateTime DESC;

# Configuration Specification. Implemented as a configuration within the
# engagement platform (NexHealth/Dental Intelligence) or as a Keragon
# workflow for custom requirements.

{
  "protocol_version": "1.0",
  "practice_id": "{PRACTICE_ID}",
  "effective_date": "2025-01-01",
  "protocols": [
    {
      "protocol_id": "PROPHY_RECALL_6MO",
      "trigger_codes": ["D1110", "D1120"],
      "trigger_event": "procedure_completed",
      "follow_up": {
        "appointment_type": "Prophy/Recall",
        "interval_days": 180,
        "interval_tolerance_days": 14,
        "duration_minutes": 60,
        "provider_rule": "same_hygienist_or_any_hygienist",
        "operatory_rule": "hygiene_operatory",
        "time_preference": "same_day_and_time",
        "scheduling_method": "self_schedule_link",
        "auto_book_if_no_response_days": 30
      },
      "communication": {
        "initial_notification_days_before_due": 30,
        "reminder_sequence": [30, 14, 0, -14, -28],
        "channels": ["sms", "email"],
        "escalate_to_phone_after_days": 28
      }
    },
    {
      "protocol_id": "PERIO_MAINTENANCE_3MO",
      "trigger_codes": ["D4910"],
      "trigger_event": "procedure_completed",
      "follow_up": {
        "appointment_type": "Perio Maintenance",
        "interval_days": 90,
        "interval_tolerance_days": 7,
        "duration_minutes": 60,
        "provider_rule": "same_hygienist",
        "operatory_rule": "hygiene_operatory",
        "time_preference": "same_day_and_time",
        "scheduling_method": "self_schedule_link",
        "auto_book_if_no_response_days": 14
      },
      "communication": {
        "initial_notification_days_before_due": 21,
        "reminder_sequence": [21, 7, 0, -7],
        "channels": ["sms", "email"],
        "escalate_to_phone_after_days": 14
      }
    },
    {
      "protocol_id": "SRP_POSTOP_4WK",
      "trigger_codes": ["D4341", "D4342"],
      "trigger_event": "procedure_completed",
      "follow_up": {
        "appointment_type": "SRP Post-Op Evaluation",
        "interval_days": 28,
        "interval_tolerance_days": 7,
        "duration_minutes": 30,
        "provider_rule": "same_provider",
        "operatory_rule": "any_available",
        "time_preference": "morning_preferred",
        "scheduling_method": "auto_book",
        "auto_book_if_no_response_days": null
      },
      "communication": {
        "initial_notification_days_before_due": 0,
        "reminder_sequence": [0, -3, -1],
        "channels": ["sms"],
        "escalate_to_phone_after_days": null
      }
    },
    {
      "protocol_id": "CROWN_SEAT_3WK",
      "trigger_codes": ["D2740", "D2750", "D2751", "D2752"],
      "trigger_event": "procedure_completed",
      "follow_up": {
        "appointment_type": "Crown/Bridge Seat",
        "interval_days": 21,
        "interval_tolerance_days": 7,
        "duration_minutes": 45,
        "provider_rule": "same_provider",
        "operatory_rule": "doctor_operatory",
        "time_preference": "any_available",
        "scheduling_method": "auto_book",
        "auto_book_if_no_response_days": null
      },
      "communication": {
        "initial_notification_days_before_due": 0,
        "reminder_sequence": [0, -3, -1],
        "channels": ["sms", "email"],
        "escalate_to_phone_after_days": null
      }
    },
    {
      "protocol_id": "EXTRACTION_POSTOP_1WK",
      "trigger_codes": ["D7210", "D7220", "D7230", "D7240"],
      "trigger_event": "procedure_completed",
      "follow_up": {
        "appointment_type": "Post-Op Check",
        "interval_days": 7,
        "interval_tolerance_days": 2,
        "duration_minutes": 15,
        "provider_rule": "same_provider",
        "operatory_rule": "any_available",
        "time_preference": "first_available",
        "scheduling_method": "auto_book",
        "auto_book_if_no_response_days": null
      },
      "communication": {
        "initial_notification_days_before_due": 0,
        "reminder_sequence": [0, -1],
        "channels": ["sms"],
        "escalate_to_phone_after_days": null
      }
    },
    {
      "protocol_id": "MEDICAL_CHRONIC_DISEASE_QUARTERLY",
      "trigger_codes": ["99213", "99214"],
      "trigger_event": "procedure_completed",
      "condition_filter": {
        "icd10_codes": ["E11.*", "I10", "J45.*"],
        "description": "Diabetes, Hypertension, Asthma"
      },
      "follow_up": {
        "appointment_type": "Chronic Disease Follow-Up",
        "interval_days": 90,
        "interval_tolerance_days": 14,
        "duration_minutes": 30,
        "provider_rule": "same_provider",
        "operatory_rule": "any_exam_room",
        "time_preference": "same_day_and_time",
        "scheduling_method": "self_schedule_link",
        "auto_book_if_no_response_days": 21
      },
      "communication": {
        "initial_notification_days_before_due": 21,
        "reminder_sequence": [21, 7, 0, -7, -14],
        "channels": ["sms", "email", "patient_portal"],
        "escalate_to_phone_after_days": 21
      }
    }
  ],
  "global_rules": {
    "duplicate_prevention": true,
    "duplicate_window_days": 30,
    "max_future_booking_days": 365,
    "business_hours_only": true,
    "respect_patient_preferred_time": true,
    "respect_patient_opt_out": true,
    "insurance_eligibility_check": false,
    "holiday_exclusions": ["2025-01-01", "2025-07-04", "2025-11-27", "2025-12-25"],
    "error_handling": {
      "no_available_slot": "queue_for_front_desk",
      "patient_already_scheduled": "skip_and_log",
      "sync_failure": "retry_3x_then_alert_msp",
      "provider_not_found": "assign_any_available_same_type"
    }
  }
}

# pseudocode implementation for engagement platforms and custom Keragon
# integrations

# Slot Finder Algorithm - Pseudocode Implementation
# This runs within the engagement platform or as a Keragon workflow step.

import datetime
from typing import List, Optional, Dict

def find_optimal_slot(
    patient_id: str,
    protocol: Dict,
    pms_api_client,  # NexHealth API or CRMBridge.ai or Open Dental API
    completion_date: datetime.date
) -> Optional[Dict]:
    """
    Find the optimal appointment slot based on protocol rules.
    Returns slot details or None if no slot found.
    """
    follow_up = protocol['follow_up']
    
    # Calculate target date range
    target_date = completion_date + datetime.timedelta(days=follow_up['interval_days'])
    earliest_date = target_date - datetime.timedelta(days=follow_up['interval_tolerance_days'])
    latest_date = target_date + datetime.timedelta(days=follow_up['interval_tolerance_days'])
    
    # Check for duplicate prevention
    if protocol.get('global_rules', {}).get('duplicate_prevention', True):
        existing = pms_api_client.get_future_appointments(
            patient_id=patient_id,
            appointment_type=follow_up['appointment_type'],
            date_range=(earliest_date, latest_date + datetime.timedelta(days=30))
        )
        if existing:
            return {'status': 'skip', 'reason': 'patient_already_scheduled', 'existing_apt': existing[0]}
    
    # Determine provider constraint
    provider_ids = resolve_provider_rule(
        rule=follow_up['provider_rule'],
        original_provider_id=protocol.get('original_provider_id'),
        pms_api_client=pms_api_client
    )
    
    # Determine time preference
    preferred_time = resolve_time_preference(
        preference=follow_up['time_preference'],
        patient_id=patient_id,
        pms_api_client=pms_api_client
    )
    
    # Search for available slots
    available_slots = pms_api_client.get_available_slots(
        start_date=earliest_date,
        end_date=latest_date,
        provider_ids=provider_ids,
        operatory_type=follow_up['operatory_rule'],
        duration_minutes=follow_up['duration_minutes'],
        exclude_holidays=protocol.get('global_rules', {}).get('holiday_exclusions', [])
    )
    
    if not available_slots:
        # Expand search window by 2x tolerance
        extended_latest = latest_date + datetime.timedelta(days=follow_up['interval_tolerance_days'])
        available_slots = pms_api_client.get_available_slots(
            start_date=earliest_date,
            end_date=extended_latest,
            provider_ids=provider_ids,
            operatory_type=follow_up['operatory_rule'],
            duration_minutes=follow_up['duration_minutes']
        )
    
    if not available_slots:
        return {'status': 'no_slot', 'reason': 'no_availability', 'action': 'queue_for_front_desk'}
    
    # Score and rank slots by preference
    scored_slots = []
    for slot in available_slots:
        score = 0
        # Prefer target date (highest score)
        days_from_target = abs((slot['date'] - target_date).days)
        score += max(0, 100 - (days_from_target * 5))  # Closer to target = higher score
        
        # Prefer patient's usual time
        if preferred_time and slot['time'].hour == preferred_time.hour:
            score += 50
        
        # Prefer same day of week as original appointment
        if slot['date'].weekday() == completion_date.weekday():
            score += 25
        
        # Prefer original provider
        if slot['provider_id'] == protocol.get('original_provider_id'):
            score += 30
        
        scored_slots.append({'slot': slot, 'score': score})
    
    # Sort by score descending
    scored_slots.sort(key=lambda x: x['score'], reverse=True)
    best_slot = scored_slots[0]['slot']
    
    return {
        'status': 'found',
        'slot': best_slot,
        'score': scored_slots[0]['score'],
        'scheduling_method': follow_up['scheduling_method']
    }


def resolve_provider_rule(rule: str, original_provider_id: str, pms_api_client) -> List[str]:
    """Resolve provider rule to list of acceptable provider IDs."""
    if rule == 'same_provider':
        return [original_provider_id]
    elif rule == 'same_hygienist_or_any_hygienist':
        hygienists = pms_api_client.get_providers(provider_type='hygienist')
        # Put original first for preference scoring
        ids = [original_provider_id] + [h['id'] for h in hygienists if h['id'] != original_provider_id]
        return ids
    elif rule == 'same_hygienist':
        return [original_provider_id]
    elif rule == 'any_available_same_type':
        provider = pms_api_client.get_provider(original_provider_id)
        all_same_type = pms_api_client.get_providers(provider_type=provider['type'])
        return [p['id'] for p in all_same_type]
    else:
        return [original_provider_id]


def resolve_time_preference(preference: str, patient_id: str, pms_api_client) -> Optional[datetime.time]:
    """Resolve time preference to a target time."""
    if preference == 'same_day_and_time':
        last_apt = pms_api_client.get_last_appointment(patient_id)
        if last_apt:
            return last_apt['time']
    elif preference == 'morning_preferred':
        return datetime.time(9, 0)
    elif preference == 'first_available':
        return None  # No time preference, take first
    return None

# NexHealth API implementation for slot search:
# GET /api/v1/availabilities
# Parameters: location_id, provider_id, start_date, end_date, appointment_type_id
# Returns available time slots matching criteria

# Open Dental API implementation for slot search:
# GET /api/v1/appointments?AptStatus=0 (open timeslots)
# Filter by ProvNum, Op, AptDateTime range
# Or use the built-in recall system which handles this natively

-- Check if patient already has a future appointment of the specified type
-- Run this BEFORE creating a new auto-scheduled appointment

SELECT COUNT(*) AS existing_count
FROM appointment a
INNER JOIN appointmenttype at ON a.AppointmentTypeNum = at.AppointmentTypeNum
WHERE a.PatNum = @PatientNum
  AND at.AppointmentTypeName = @AppointmentType
  AND a.AptDateTime >= @EarliestDate
  AND a.AptDateTime <= @LatestDate
  AND a.AptStatus IN (1, 4)  -- 1=Scheduled, 4=ASAP
;

-- If existing_count > 0, DO NOT create new appointment
-- Log: 'Duplicate prevented for PatNum {X}, existing apt on {date}'

# Keragon Workflow or Platform-Native Configuration

{
  "exception_handlers": [
    {
      "exception_type": "no_available_slot",
      "severity": "medium",
      "actions": [
        {
          "action": "create_front_desk_task",
          "task_title": "Manual scheduling needed: {patient_name} - {appointment_type}",
          "task_description": "Auto-scheduler could not find an available slot for {patient_name}'s {appointment_type} follow-up (target date: {target_date}). Please schedule manually or extend provider schedule.",
          "task_due_date": "target_date - 7 days",
          "task_assignee": "front_desk_lead"
        },
        {
          "action": "log_event",
          "log_level": "warning",
          "message": "No slot found for patient {patient_id}, protocol {protocol_id}, target {target_date}"
        }
      ]
    },
    {
      "exception_type": "patient_already_scheduled",
      "severity": "low",
      "actions": [
        {
          "action": "log_event",
          "log_level": "info",
          "message": "Duplicate prevented: patient {patient_id} already has {appointment_type} on {existing_date}"
        }
      ]
    },
    {
      "exception_type": "sync_failure",
      "severity": "high",
      "actions": [
        {
          "action": "retry",
          "max_retries": 3,
          "retry_interval_minutes": 5
        },
        {
          "action": "alert_msp",
          "method": "email_and_sms",
          "recipient": "msp_oncall@yourmsp.com",
          "message": "ALERT: PMS sync failure at {practice_name}. Auto-scheduling paused. Error: {error_message}"
        },
        {
          "action": "create_front_desk_task",
          "task_title": "SYSTEM ALERT: Auto-scheduling temporarily unavailable",
          "task_description": "The auto-scheduling system has encountered a sync error. Your MSP has been notified and is investigating. Please schedule follow-ups manually until resolved.",
          "task_assignee": "office_manager"
        }
      ]
    },
    {
      "exception_type": "patient_opted_out",
      "severity": "low",
      "actions": [
        {
          "action": "create_front_desk_task",
          "task_title": "Manual follow-up needed: {patient_name} (opted out of auto-messages)",
          "task_description": "Patient {patient_name} has opted out of automated communications. Please call to schedule their {appointment_type} follow-up (due: {target_date}).",
          "task_due_date": "target_date - 14 days",
          "task_assignee": "front_desk"
        },
        {
          "action": "log_event",
          "log_level": "info",
          "message": "Opt-out patient {patient_id} routed to manual follow-up queue"
        }
      ]
    },
    {
      "exception_type": "provider_schedule_not_found",
      "severity": "medium",
      "actions": [
        {
          "action": "try_alternative_provider",
          "fallback_rule": "any_available_same_type"
        },
        {
          "action": "alert_msp",
          "method": "email",
          "message": "Provider schedule not built for {provider_name} beyond {last_scheduled_date}. Practice needs to extend schedule templates."
        }
      ]
    }
  ],
  "daily_exception_report": {
    "enabled": true,
    "send_time": "07:00",
    "recipients": ["office_manager@practice.com", "msp_team@yourmsp.com"],
    "include": ["unresolved_tasks", "sync_status", "scheduling_stats"]
  }
}

INSERT INTO task (TaskListNum, DateTask, Descript, TaskStatus, UserNum)
VALUES (@FrontDeskTaskList, @DueDate, @TaskDescription, 0, @OfficeManagerUserNum);

-- KPI 1: Recall compliance rate (current month)
SELECT 
  COUNT(CASE WHEN a.AptNum IS NOT NULL THEN 1 END) AS scheduled_count,
  COUNT(*) AS total_due,
  ROUND(COUNT(CASE WHEN a.AptNum IS NOT NULL THEN 1 END) / COUNT(*) * 100, 1) AS compliance_pct
FROM recall r
INNER JOIN patient p ON r.PatNum = p.PatNum
LEFT JOIN appointment a ON r.PatNum = a.PatNum 
  AND a.AptDateTime >= CURDATE()
  AND a.AptStatus IN (1, 4)
INNER JOIN recalltype rt ON r.RecallTypeNum = rt.RecallTypeNum
WHERE r.DateDue BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') 
  AND LAST_DAY(CURDATE())
AND r.IsDisabled = 0
AND p.PatStatus = 0;

-- KPI 4: No-show rate comparison (last 90 days)
SELECT 
  CASE WHEN a.Note LIKE '%auto%' OR a.Note LIKE '%nexhealth%' THEN 'Auto-Scheduled' ELSE 'Manual' END AS booking_method,
  COUNT(*) AS total_appointments,
  COUNT(CASE WHEN a.AptStatus = 5 THEN 1 END) AS broken_appointments,
  ROUND(COUNT(CASE WHEN a.AptStatus = 5 THEN 1 END) / COUNT(*) * 100, 1) AS noshow_pct
FROM appointment a
WHERE a.AptDateTime >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
AND a.AptDateTime < CURDATE()
GROUP BY booking_method;

-- Monthly scheduling volume trend
SELECT 
  DATE_FORMAT(a.AptDateTime, '%Y-%m') AS month,
  COUNT(*) AS total_recall_apts,
  COUNT(CASE WHEN a.Note LIKE '%auto%' THEN 1 END) AS auto_scheduled
FROM appointment a
INNER JOIN appointmenttype at ON a.AppointmentTypeNum = at.AppointmentTypeNum
WHERE at.AppointmentTypeName IN ('Prophy/Recall', 'Perio Maintenance', 'Post-Op Check')
AND a.AptDateTime >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(a.AptDateTime, '%Y-%m')
ORDER BY month;

GET /api/v1/analytics/appointments
GET /api/v1/analytics/communications
GET /api/v1/analytics/online_booking