Implementation Guide: Surface patients overdue for preventive care from ehr data

# Run speed test from PMS server
Invoke-WebRequest -Uri 'https://speed.cloudflare.com' -UseBasicParsing | Select-Object StatusCode
# Check PMS server specs (Windows)
Get-ComputerInfo | Select-Object CsName, OsName, OsArchitecture, CsTotalPhysicalMemory, CsNumberOfProcessors
# Document current firewall rules
Get-NetFirewallRule -Direction Outbound -Enabled True | Select-Object DisplayName, Action, Profile | Export-Csv C:\Temp\firewall_rules.csv
# Check SQL Server / MySQL instance for PMS database access
Get-Service -Name 'MSSQL*' | Select-Object Name, Status
Get-Service -Name 'MySQL*' | Select-Object Name, Status

# FortiGate initial configuration via CLI (connect via console cable)
config system interface
  edit port1
    set ip 192.168.1.1 255.255.255.0
    set allowaccess ping https ssh
  next
  edit wan1
    set mode dhcp
    set allowaccess ping
  next
end
# Enable logging for HIPAA audit trail
config log fortianalyzer setting
  set status enable
end
config log syslogd setting
  set status enable
  set server "syslog.yourmsp.com"
  set port 514
  set format default
end
# Configure SSL VPN for secure remote management
config vpn ssl settings
  set servercert "Fortinet_Factory"
  set tunnel-ip-pools "SSLVPN_TUNNEL_ADDR1"
  set port 10443
  set source-interface wan1
end
# Create firewall policy for PMS server outbound HTTPS (for data sync)
config firewall policy
  edit 10
    set name "PMS-Sync-Outbound"
    set srcintf "internal"
    set dstintf "wan1"
    set srcaddr "PMS-Server"
    set dstaddr "all"
    set action accept
    set schedule "always"
    set service "HTTPS"
    set logtraffic all
    set utm-status enable
    set ips-sensor "default"
    set ssl-ssh-profile "certificate-inspection"
  next
end

# Download and install Weave desktop app on front office workstations
# Navigate to https://www.getweave.com/download
# Run the installer as administrator
# Weave requires .NET 4.7.2+ on Windows
# Verify Weave sync agent connectivity (after installation)
Test-NetConnection -ComputerName sync.getweave.com -Port 443
# Check that the Weave sync service is running
Get-Service -Name 'WeaveSync*' | Select-Object Name, Status, StartType

# Install AWS CLI and configure credentials
pip install awscli
aws configure
# Enter MSP AWS access key, secret key, region (us-east-1 recommended)
# Create HIPAA-compliant VPC with private subnets
aws ec2 create-vpc --cidr-block 10.0.0.0/16 --tag-specifications 'ResourceType=vpc,Tags=[{Key=Name,Value=recall-analytics-vpc},{Key=Environment,Value=production},{Key=HIPAA,Value=true}]'
# Create RDS PostgreSQL instance with encryption
aws rds create-db-instance \
  --db-instance-identifier recall-analytics-db \
  --db-instance-class db.t3.micro \
  --engine postgres \
  --engine-version 15.4 \
  --master-username recall_admin \
  --master-user-password '<GENERATE_STRONG_PASSWORD>' \
  --allocated-storage 20 \
  --storage-encrypted \
  --kms-key-id alias/aws/rds \
  --vpc-security-group-ids sg-XXXXXXXXX \
  --db-subnet-group-name recall-db-subnet-group \
  --backup-retention-period 35 \
  --multi-az \
  --no-publicly-accessible \
  --tags Key=HIPAA,Value=true
# Create S3 bucket for reports with encryption and versioning
aws s3api create-bucket --bucket recall-analytics-reports-<CLIENT_ID> --region us-east-1
aws s3api put-bucket-encryption --bucket recall-analytics-reports-<CLIENT_ID> --server-side-encryption-configuration '{"Rules":[{"ApplyServerSideEncryptionByDefault":{"SSEAlgorithm":"aws:kms"}},{"BucketKeyEnabled":true}]}'
aws s3api put-bucket-versioning --bucket recall-analytics-reports-<CLIENT_ID> --versioning-configuration Status=Enabled
aws s3api put-public-access-block --bucket recall-analytics-reports-<CLIENT_ID> --public-access-block-configuration BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true
# Create CloudWatch log group with 6-year retention for HIPAA
aws logs create-log-group --log-group-name /recall-analytics/production
aws logs put-retention-policy --log-group-name /recall-analytics/production --retention-in-days 2192

# Install Python 3.11 on the sync workstation (Windows)
# Download from https://www.python.org/downloads/release/python-3119/
# Ensure 'Add to PATH' is checked during installation
# Create project directory
mkdir C:\RecallSync
cd C:\RecallSync
# Create virtual environment
python -m venv venv
.\venv\Scripts\activate
# Install dependencies
pip install psycopg2-binary pyodbc cryptography schedule python-dotenv requests
# Create .env file with encrypted credentials
# (Use the configuration script from the custom AI components section)
python configure_sync.py
# Install as Windows service using NSSM
# Download NSSM from https://nssm.cc/download
nssm install RecallSyncAgent C:\RecallSync\venv\Scripts\python.exe C:\RecallSync\sync_agent.py
nssm set RecallSyncAgent AppDirectory C:\RecallSync
nssm set RecallSyncAgent Description "Patient Recall Data Sync Agent - HIPAA Compliant"
nssm set RecallSyncAgent Start SERVICE_AUTO_START
nssm set RecallSyncAgent AppStdout C:\RecallSync\logs\sync_stdout.log
nssm set RecallSyncAgent AppStderr C:\RecallSync\logs\sync_stderr.log
nssm start RecallSyncAgent
# Verify the service is running
Get-Service -Name RecallSyncAgent | Select-Object Name, Status

# Clone the recall analytics project
git clone https://github.com/<your-msp>/recall-analytics-engine.git
cd recall-analytics-engine
# Install Serverless Framework
npm install -g serverless
npm install
# Configure environment variables
cp .env.example .env.production
# Edit .env.production with client-specific values:
# ANTHROPIC_API_KEY=sk-ant-XXXXX
# DB_HOST=recall-analytics-db.XXXXX.us-east-1.rds.amazonaws.com
# DB_NAME=recall_analytics
# DB_USER=recall_admin
# DB_PASSWORD=<password>
# TWILIO_ACCOUNT_SID=ACXXXXX
# TWILIO_AUTH_TOKEN=XXXXX
# TWILIO_PHONE_NUMBER=+1XXXXXXXXXX
# PRACTICE_NAME=<Practice Name>
# PRACTICE_TIMEZONE=America/New_York
# Deploy to AWS
serverless deploy --stage production
# Run database migrations
serverless invoke --function migrate --stage production
# Trigger initial patient analysis
serverless invoke --function analyzePatients --stage production
# Verify API endpoint
curl https://<api-id>.execute-api.us-east-1.amazonaws.com/production/health

CREATE ROLE retool_readonly WITH LOGIN PASSWORD '<STRONG_PASSWORD>';
GRANT CONNECT ON DATABASE recall_analytics TO retool_readonly;
GRANT USAGE ON SCHEMA public TO retool_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO retool_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO retool_readonly;

# Twilio setup via CLI
pip install twilio-cli
twilio login
# Purchase a local phone number for the practice
twilio phone-numbers:buy:local --area-code <PRACTICE_AREA_CODE> --sms-enabled --voice-enabled
# Configure webhook for inbound SMS handling
twilio phone-numbers:update <PHONE_NUMBER_SID> \
  --sms-url https://<api-id>.execute-api.us-east-1.amazonaws.com/production/twilio/inbound \
  --sms-method POST

serverless invoke --function generateReport --stage production --data '{"report_type": "monthly", "test": true}'

aws cloudwatch get-metric-statistics \
  --namespace RecallAnalytics \
  --metric-name MessageDeliveryRate \
  --start-time $(date -d '-7 days' -u +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 86400 \
  --statistics Average

# HIPAA Compliant Patient Recall Data Extraction

#!/usr/bin/env python3
"""PMS Data Sync Agent - HIPAA Compliant Patient Recall Data Extraction
Runs as a Windows service via NSSM. Performs nightly read-only sync of patient
recall data from on-premise PMS to AWS RDS PostgreSQL.
"""
import os
import sys
import json
import logging
import hashlib
import datetime
import time
import schedule
import pyodbc
import psycopg2
import psycopg2.extras
from dotenv import load_dotenv
from pathlib import Path
from cryptography.fernet import Fernet

load_dotenv()

# Configure logging with HIPAA-compliant audit trail
LOG_DIR = Path('C:/RecallSync/logs')
LOG_DIR.mkdir(parents=True, exist_ok=True)

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s [%(levelname)s] %(message)s',
    handlers=[
        logging.FileHandler(LOG_DIR / f'sync_{datetime.date.today().isoformat()}.log'),
        logging.StreamHandler(sys.stdout)
    ]
)
logger = logging.getLogger('RecallSync')

# PMS Type Configuration
PMS_TYPE = os.getenv('PMS_TYPE', 'dentrix')  # dentrix | eaglesoft | opendental

# PMS Connection Queries by vendor
PMS_QUERIES = {
    'dentrix': """
        SELECT 
            p.patient_id,
            p.first_name,
            p.last_name,
            p.date_of_birth,
            p.home_phone,
            p.cell_phone,
            p.email,
            p.status AS patient_status,
            r.recall_type,
            r.recall_due_date,
            r.recall_interval_months,
            a.appointment_date AS last_visit_date,
            a.procedure_code AS last_procedure_code,
            ins.carrier_name AS insurance_carrier
        FROM patients p
        LEFT JOIN recall r ON p.patient_id = r.patient_id AND r.status = 'Active'
        LEFT JOIN (
            SELECT patient_id, MAX(appointment_date) as appointment_date, 
                   procedure_code
            FROM appointments 
            WHERE status = 'Complete'
            GROUP BY patient_id
        ) a ON p.patient_id = a.patient_id
        LEFT JOIN insurance ins ON p.patient_id = ins.patient_id AND ins.ordinal = 1
        WHERE p.status = 'Active'
          AND r.recall_due_date IS NOT NULL
    """,
    'eaglesoft': """
        SELECT 
            p.id AS patient_id,
            p.firstname AS first_name,
            p.lastname AS last_name,
            p.birthdate AS date_of_birth,
            p.home_phone,
            p.wireless_phone AS cell_phone,
            p.email,
            p.status AS patient_status,
            rc.description AS recall_type,
            r.due_date AS recall_due_date,
            r.interval AS recall_interval_months,
            (
                SELECT MAX(a.date) FROM appointment a 
                WHERE a.patient_id = p.id AND a.status = 'Completed'
            ) AS last_visit_date,
            ic.name AS insurance_carrier
        FROM patient p
        LEFT JOIN recall r ON p.id = r.patient_id AND r.status = 1
        LEFT JOIN recall_code rc ON r.recall_code_id = rc.id
        LEFT JOIN insurance_carrier ic ON p.primary_carrier_id = ic.id
        WHERE p.status = 1
          AND r.due_date IS NOT NULL
    """,
    'opendental': """
        SELECT 
            p.PatNum AS patient_id,
            p.FName AS first_name,
            p.LName AS last_name,
            p.Birthdate AS date_of_birth,
            p.HmPhone AS home_phone,
            p.WirelessPhone AS cell_phone,
            p.Email AS email,
            CASE p.PatStatus WHEN 0 THEN 'Active' ELSE 'Inactive' END AS patient_status,
            rt.Description AS recall_type,
            r.DateDue AS recall_due_date,
            r.RecallInterval AS recall_interval_months,
            (
                SELECT MAX(pl.ProcDate) FROM procedurelog pl 
                WHERE pl.PatNum = p.PatNum AND pl.ProcStatus = 2
            ) AS last_visit_date,
            cp.CarrierName AS insurance_carrier
        FROM patient p
        LEFT JOIN recall r ON p.PatNum = r.PatNum AND r.IsDisabled = 0
        LEFT JOIN recalltype rt ON r.RecallTypeNum = rt.RecallTypeNum
        LEFT JOIN patplan pp ON p.PatNum = pp.PatNum AND pp.Ordinal = 1
        LEFT JOIN insplan ip ON pp.PlanNum = ip.PlanNum
        LEFT JOIN carrier cp ON ip.CarrierNum = cp.CarrierNum
        WHERE p.PatStatus = 0
          AND r.DateDue IS NOT NULL
          AND r.DateDue != '0001-01-01'
    """
}

# PMS Connection strings
PMS_CONNECTIONS = {
    'dentrix': f"DRIVER={{SQL Server}};SERVER={os.getenv('PMS_SERVER', 'localhost')};DATABASE={os.getenv('PMS_DATABASE', 'DentrixDB')};UID={os.getenv('PMS_USER')};PWD={os.getenv('PMS_PASSWORD')};Encrypt=yes;TrustServerCertificate=yes;",
    'eaglesoft': f"DRIVER={{SQL Anywhere 17}};SERVER={os.getenv('PMS_SERVER', 'localhost')};DATABASE={os.getenv('PMS_DATABASE', 'EaglesoftDB')};UID={os.getenv('PMS_USER', 'dba')};PWD={os.getenv('PMS_PASSWORD')};Encrypt=yes;",
    'opendental': None  # Uses MySQL connector
}

# AWS RDS PostgreSQL connection
RDS_CONFIG = {
    'host': os.getenv('RDS_HOST'),
    'port': int(os.getenv('RDS_PORT', '5432')),
    'database': os.getenv('RDS_DATABASE', 'recall_analytics'),
    'user': os.getenv('RDS_USER'),
    'password': os.getenv('RDS_PASSWORD'),
    'sslmode': 'require',
    'sslrootcert': 'C:/RecallSync/certs/us-east-1-bundle.pem'
}

def get_pms_connection():
    """Establish read-only connection to the PMS database."""
    if PMS_TYPE == 'opendental':
        import mysql.connector
        return mysql.connector.connect(
            host=os.getenv('PMS_SERVER', 'localhost'),
            port=int(os.getenv('PMS_PORT', '3306')),
            database=os.getenv('PMS_DATABASE', 'opendental'),
            user=os.getenv('PMS_USER'),
            password=os.getenv('PMS_PASSWORD'),
            ssl_ca='C:/RecallSync/certs/mysql-ca.pem'
        )
    else:
        conn_string = PMS_CONNECTIONS[PMS_TYPE]
        conn = pyodbc.connect(conn_string, readonly=True)
        return conn

def get_rds_connection():
    """Establish secure connection to AWS RDS PostgreSQL."""
    return psycopg2.connect(**RDS_CONFIG)

def hash_patient_id(patient_id):
    """Create a consistent hash of patient ID for de-duplication."""
    salt = os.getenv('HASH_SALT', 'recall-sync-2025')
    return hashlib.sha256(f"{salt}:{patient_id}".encode()).hexdigest()[:16]

def sync_patients():
    """Main sync function - extracts patient recall data and uploads to RDS."""
    logger.info('Starting patient recall data sync...')
    start_time = time.time()
    records_synced = 0
    errors = 0
    
    try:
        # Connect to PMS (read-only)
        pms_conn = get_pms_connection()
        pms_cursor = pms_conn.cursor()
        logger.info(f'Connected to {PMS_TYPE} PMS database')
        
        # Execute extraction query
        query = PMS_QUERIES[PMS_TYPE]
        pms_cursor.execute(query)
        columns = [desc[0] for desc in pms_cursor.description]
        patients = [dict(zip(columns, row)) for row in pms_cursor.fetchall()]
        logger.info(f'Extracted {len(patients)} patient recall records from PMS')
        
        # Connect to RDS
        rds_conn = get_rds_connection()
        rds_cursor = rds_conn.cursor()
        
        # Upsert patient data
        upsert_sql = """
            INSERT INTO patient_recall (
                patient_hash, first_name, last_name, date_of_birth,
                cell_phone, email, patient_status, recall_type,
                recall_due_date, recall_interval_months, last_visit_date,
                last_procedure_code, insurance_carrier, 
                pms_patient_id, pms_type, sync_timestamp
            ) VALUES (
                %(patient_hash)s, %(first_name)s, %(last_name)s, %(date_of_birth)s,
                %(cell_phone)s, %(email)s, %(patient_status)s, %(recall_type)s,
                %(recall_due_date)s, %(recall_interval_months)s, %(last_visit_date)s,
                %(last_procedure_code)s, %(insurance_carrier)s,
                %(pms_patient_id)s, %(pms_type)s, NOW()
            )
            ON CONFLICT (patient_hash, pms_type)
            DO UPDATE SET
                first_name = EXCLUDED.first_name,
                last_name = EXCLUDED.last_name,
                cell_phone = EXCLUDED.cell_phone,
                email = EXCLUDED.email,
                patient_status = EXCLUDED.patient_status,
                recall_type = EXCLUDED.recall_type,
                recall_due_date = EXCLUDED.recall_due_date,
                last_visit_date = EXCLUDED.last_visit_date,
                last_procedure_code = EXCLUDED.last_procedure_code,
                insurance_carrier = EXCLUDED.insurance_carrier,
                sync_timestamp = NOW()
        """
        
        for patient in patients:
            try:
                patient_data = {
                    'patient_hash': hash_patient_id(patient.get('patient_id')),
                    'first_name': patient.get('first_name', ''),
                    'last_name': patient.get('last_name', ''),
                    'date_of_birth': patient.get('date_of_birth'),
                    'cell_phone': patient.get('cell_phone') or patient.get('home_phone', ''),
                    'email': patient.get('email', ''),
                    'patient_status': patient.get('patient_status', 'Active'),
                    'recall_type': patient.get('recall_type', 'General'),
                    'recall_due_date': patient.get('recall_due_date'),
                    'recall_interval_months': patient.get('recall_interval_months'),
                    'last_visit_date': patient.get('last_visit_date'),
                    'last_procedure_code': patient.get('last_procedure_code', ''),
                    'insurance_carrier': patient.get('insurance_carrier', ''),
                    'pms_patient_id': str(patient.get('patient_id')),
                    'pms_type': PMS_TYPE
                }
                rds_cursor.execute(upsert_sql, patient_data)
                records_synced += 1
            except Exception as e:
                errors += 1
                logger.error(f"Error syncing patient {hash_patient_id(patient.get('patient_id'))}: {str(e)}")
                continue
        
        rds_conn.commit()
        
        # Log audit record
        rds_cursor.execute("""
            INSERT INTO sync_audit_log (sync_timestamp, records_extracted, records_synced, errors, duration_seconds, pms_type)
            VALUES (NOW(), %s, %s, %s, %s, %s)
        """, (len(patients), records_synced, errors, time.time() - start_time, PMS_TYPE))
        rds_conn.commit()
        
        logger.info(f'Sync complete: {records_synced} records synced, {errors} errors, {time.time()-start_time:.1f}s')
        
    except Exception as e:
        logger.error(f'Sync failed: {str(e)}')
        raise
    finally:
        if 'pms_conn' in locals():
            pms_conn.close()
        if 'rds_conn' in locals():
            rds_conn.close()

def main():
    """Main entry point - runs sync on schedule."""
    logger.info('Recall Sync Agent starting...')
    logger.info(f'PMS Type: {PMS_TYPE}')
    logger.info(f'Sync schedule: Daily at 02:00')
    
    # Schedule nightly sync
    schedule.every().day.at('02:00').do(sync_patients)
    
    # Also run once immediately on startup for initial sync
    sync_patients()
    
    while True:
        schedule.run_pending()
        time.sleep(60)

if __name__ == '__main__':
    main()

# AWS Lambda Function. Calculates risk scores for overdue patients to
# prioritize recall outreach. Deployed via Serverless Framework. Triggered
# nightly by CloudWatch Events.

"""Patient Risk Scoring Engine - AWS Lambda Function
Calculates risk scores for overdue patients to prioritize recall outreach.
Deployed via Serverless Framework. Triggered nightly by CloudWatch Events.
"""
import os
import json
import logging
import psycopg2
import psycopg2.extras
from datetime import datetime, date, timedelta

logger = logging.getLogger()
logger.setLevel(logging.INFO)

# Risk factor weights (tunable per practice)
WEIGHTS = {
    'days_overdue': 0.30,       # How far past due date
    'visit_gap_trend': 0.20,    # Is patient becoming less frequent?
    'recall_type_urgency': 0.15,# Perio maintenance > hygiene > general
    'age_risk': 0.10,           # Pediatric and 65+ get higher priority
    'insurance_status': 0.10,   # Insured patients more likely to convert
    'contact_quality': 0.15     # Has mobile phone + email = higher reachability
}

RECALL_TYPE_URGENCY = {
    'Perio Maintenance': 95,
    'Perio': 95,
    'D4910': 95,
    'Hygiene': 70,
    'Prophylaxis': 70,
    'Prophy': 70,
    'D1110': 70,
    'D1120': 65,
    'Comprehensive Exam': 60,
    'D0150': 60,
    'Periodic Exam': 50,
    'D0120': 50,
    'Annual Physical': 55,
    'Diabetic Screening': 80,
    'Mammography': 75,
    'Colonoscopy': 40,
    'General': 45
}

def get_db_connection():
    return psycopg2.connect(
        host=os.environ['DB_HOST'],
        port=int(os.environ.get('DB_PORT', '5432')),
        database=os.environ['DB_NAME'],
        user=os.environ['DB_USER'],
        password=os.environ['DB_PASSWORD'],
        sslmode='require'
    )

def calculate_days_overdue_score(recall_due_date):
    """Score based on how many days past the recall due date."""
    if not recall_due_date:
        return 50
    days_overdue = (date.today() - recall_due_date).days
    if days_overdue <= 0:
        return 0  # Not yet overdue
    elif days_overdue <= 30:
        return 30
    elif days_overdue <= 90:
        return 50
    elif days_overdue <= 180:
        return 70
    elif days_overdue <= 365:
        return 85
    else:
        return 95  # 12+ months overdue - critical

def calculate_age_risk_score(date_of_birth):
    """Higher priority for pediatric and senior patients."""
    if not date_of_birth:
        return 50
    age = (date.today() - date_of_birth).days / 365.25
    if age < 6:
        return 80   # Young children - early dental habits critical
    elif age < 18:
        return 65   # Adolescents
    elif age < 40:
        return 40   # Low risk adults
    elif age < 65:
        return 55   # Middle age - increasing risk
    else:
        return 85   # Seniors - highest health risk

def calculate_recall_type_score(recall_type):
    """Score based on clinical urgency of the recall type."""
    if not recall_type:
        return 45
    for key, score in RECALL_TYPE_URGENCY.items():
        if key.lower() in recall_type.lower():
            return score
    return 45

def calculate_insurance_score(insurance_carrier):
    """Insured patients are more likely to schedule and have benefits to use."""
    if insurance_carrier and insurance_carrier.strip():
        return 75  # Has insurance - higher conversion likelihood
    return 35      # No insurance - lower but still worth contacting

def calculate_contact_quality_score(cell_phone, email):
    """Better contact info = higher reachability = higher score."""
    score = 0
    has_phone = bool(cell_phone and cell_phone.strip() and len(cell_phone.strip()) >= 10)
    has_email = bool(email and email.strip() and '@' in email)
    if has_phone and has_email:
        score = 90
    elif has_phone:
        score = 70
    elif has_email:
        score = 50
    else:
        score = 10  # No good contact method
    return score

def calculate_risk_score(patient):
    """Calculate composite risk score for a patient (0-100)."""
    scores = {
        'days_overdue': calculate_days_overdue_score(patient.get('recall_due_date')),
        'visit_gap_trend': 50,  # Default; enhanced with historical data if available
        'recall_type_urgency': calculate_recall_type_score(patient.get('recall_type')),
        'age_risk': calculate_age_risk_score(patient.get('date_of_birth')),
        'insurance_status': calculate_insurance_score(patient.get('insurance_carrier')),
        'contact_quality': calculate_contact_quality_score(
            patient.get('cell_phone'), patient.get('email')
        )
    }
    
    composite = sum(scores[k] * WEIGHTS[k] for k in WEIGHTS)
    return round(min(max(composite, 0), 100), 1), scores

def handler(event, context):
    """Lambda handler - triggered nightly by CloudWatch Events."""
    logger.info('Starting patient risk scoring analysis...')
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    try:
        # Fetch all active patients with overdue recalls
        cursor.execute("""
            SELECT patient_hash, first_name, last_name, date_of_birth,
                   cell_phone, email, recall_type, recall_due_date,
                   last_visit_date, insurance_carrier, pms_patient_id
            FROM patient_recall
            WHERE patient_status = 'Active'
              AND recall_due_date IS NOT NULL
              AND recall_due_date < CURRENT_DATE
        """)
        overdue_patients = cursor.fetchall()
        logger.info(f'Found {len(overdue_patients)} overdue patients to score')
        
        scored_count = 0
        for patient in overdue_patients:
            risk_score, score_breakdown = calculate_risk_score(patient)
            
            cursor.execute("""
                INSERT INTO patient_risk_scores (
                    patient_hash, risk_score, score_breakdown,
                    days_overdue, scored_at
                ) VALUES (%s, %s, %s, %s, NOW())
                ON CONFLICT (patient_hash)
                DO UPDATE SET
                    risk_score = EXCLUDED.risk_score,
                    score_breakdown = EXCLUDED.score_breakdown,
                    days_overdue = EXCLUDED.days_overdue,
                    scored_at = NOW()
            """, (
                patient['patient_hash'],
                risk_score,
                json.dumps(score_breakdown),
                (date.today() - patient['recall_due_date']).days if patient['recall_due_date'] else 0
            ))
            scored_count += 1
        
        conn.commit()
        
        # Generate summary statistics
        cursor.execute("""
            SELECT 
                COUNT(*) as total_overdue,
                COUNT(*) FILTER (WHERE risk_score >= 80) as critical_count,
                COUNT(*) FILTER (WHERE risk_score >= 60 AND risk_score < 80) as high_count,
                COUNT(*) FILTER (WHERE risk_score >= 40 AND risk_score < 60) as medium_count,
                COUNT(*) FILTER (WHERE risk_score < 40) as low_count,
                ROUND(AVG(risk_score)::numeric, 1) as avg_score
            FROM patient_risk_scores
            WHERE scored_at::date = CURRENT_DATE
        """)
        summary = cursor.fetchone()
        
        logger.info(f'Scoring complete: {scored_count} patients scored')
        logger.info(f'Risk distribution: Critical={summary["critical_count"]}, High={summary["high_count"]}, Medium={summary["medium_count"]}, Low={summary["low_count"]}')
        logger.info(f'Average risk score: {summary["avg_score"]}')
        
        return {
            'statusCode': 200,
            'body': json.dumps({
                'patients_scored': scored_count,
                'summary': dict(summary)
            }, default=str)
        }
        
    except Exception as e:
        logger.error(f'Risk scoring failed: {str(e)}')
        conn.rollback()
        raise
    finally:
        conn.close()

# Claude-Powered Personalized Outreach

"""AI Recall Message Generator - Claude-Powered Personalized Outreach
Generates contextually-aware recall messages and handles two-way SMS conversations.
Deployed as AWS Lambda functions.
"""
import os
import json
import logging
import anthropic
import psycopg2
import psycopg2.extras
from datetime import date

logger = logging.getLogger()
logger.setLevel(logging.INFO)

client = anthropic.Anthropic(api_key=os.environ['ANTHROPIC_API_KEY'])

PRACTICE_NAME = os.environ.get('PRACTICE_NAME', 'Our Practice')
PRACTICE_PHONE = os.environ.get('PRACTICE_PHONE', '')
SCHEDULING_URL = os.environ.get('SCHEDULING_URL', '')

SYSTEM_PROMPT = f"""You are a friendly, professional patient communications assistant for {PRACTICE_NAME}. 
Your job is to craft personalized SMS messages to encourage patients to schedule their overdue preventive care appointments.

CRITICAL RULES:
1. NEVER include specific medical conditions, diagnoses, or treatment details in SMS messages.
2. NEVER mention specific procedures by clinical name (e.g., don't say 'periodontal maintenance' — say 'cleaning appointment').
3. NEVER include insurance information in messages.
4. Keep messages under 160 characters when possible (single SMS segment).
5. Always include a way to schedule (phone number or link).
6. Be warm and friendly, not clinical or alarming.
7. Use the patient's first name.
8. If a patient responds, be conversational and helpful — offer to help them find a convenient time.
9. If a patient says they've moved, changed dentists, or wants to stop messages, acknowledge politely and flag for opt-out.
10. Never pressure or guilt-trip patients.

Examples of good messages:
- "Hi Sarah! It's been a while since your last visit to {PRACTICE_NAME}. We'd love to see you! Schedule online at [link] or call us at [phone]. 😊"
- "Hey Mike, just a friendly reminder that you're due for your checkup at {PRACTICE_NAME}. We have openings this week! Reply YES to schedule."
- "Hi Lisa, we miss seeing you at {PRACTICE_NAME}! Your next visit is overdue. Book a convenient time at [link] — we have early morning and lunch appointments available!"
"""

def get_db_connection():
    return psycopg2.connect(
        host=os.environ['DB_HOST'],
        port=int(os.environ.get('DB_PORT', '5432')),
        database=os.environ['DB_NAME'],
        user=os.environ['DB_USER'],
        password=os.environ['DB_PASSWORD'],
        sslmode='require'
    )

def generate_recall_message(patient_data):
    """Generate a personalized recall message using Claude."""
    days_overdue = (date.today() - patient_data['recall_due_date']).days if patient_data.get('recall_due_date') else 0
    months_overdue = days_overdue // 30
    
    # Build context without exposing PHI to the AI unnecessarily (minimum necessary)
    context = f"""Generate a recall SMS for this patient:
- First name: {patient_data['first_name']}
- Months since their visit was due: {months_overdue}
- Type of visit needed: {'cleaning' if 'hyg' in (patient_data.get('recall_type') or '').lower() or 'prophy' in (patient_data.get('recall_type') or '').lower() or 'd1110' in (patient_data.get('recall_type') or '').lower() else 'checkup' if 'exam' in (patient_data.get('recall_type') or '').lower() else 'appointment'}
- Has been a patient for: {'a long time' if months_overdue > 24 else 'a while' if months_overdue > 12 else 'several months'}
- Practice phone: {PRACTICE_PHONE}
- Online scheduling link: {SCHEDULING_URL}
- Risk score: {patient_data.get('risk_score', 50)} (higher = more urgency, but keep tone friendly)

Generate ONLY the SMS message text. No quotation marks. No explanation."""

    response = client.messages.create(
        model='claude-sonnet-4-6',
        max_tokens=200,
        system=SYSTEM_PROMPT,
        messages=[{'role': 'user', 'content': context}]
    )
    
    message_text = response.content[0].text.strip()
    
    # Safety check: ensure no PHI leaked
    phi_terms = [patient_data.get('last_name', '').lower(), 
                 str(patient_data.get('date_of_birth', '')),
                 (patient_data.get('insurance_carrier') or '').lower()]
    for term in phi_terms:
        if term and len(term) > 2 and term in message_text.lower():
            logger.warning(f'PHI detected in generated message for {patient_data["patient_hash"]}. Falling back to template.')
            message_text = f"Hi {patient_data['first_name']}! You're due for a visit at {PRACTICE_NAME}. Schedule at {SCHEDULING_URL} or call {PRACTICE_PHONE}. We'd love to see you!"
    
    return message_text

def handle_patient_reply(patient_hash, inbound_message):
    """Handle an inbound SMS reply from a patient using Claude."""
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    try:
        # Get conversation history
        cursor.execute("""
            SELECT direction, message_text, sent_at 
            FROM message_log 
            WHERE patient_hash = %s 
            ORDER BY sent_at DESC LIMIT 10
        """, (patient_hash,))
        history = cursor.fetchall()
        
        # Get patient context
        cursor.execute("""
            SELECT first_name, recall_type FROM patient_recall WHERE patient_hash = %s
        """, (patient_hash,))
        patient = cursor.fetchone()
        
        if not patient:
            return "Thank you for your message! Please call us to schedule your appointment."
        
        # Build conversation for Claude
        messages = []
        for msg in reversed(history):
            role = 'assistant' if msg['direction'] == 'outbound' else 'user'
            messages.append({'role': role, 'content': msg['message_text']})
        messages.append({'role': 'user', 'content': inbound_message})
        
        conversation_context = f"""The patient {patient['first_name']} is replying to a recall message from {PRACTICE_NAME}.
Their message: \"{inbound_message}\"

Respond helpfully. If they want to schedule, provide the scheduling link ({SCHEDULING_URL}) or phone number ({PRACTICE_PHONE}).
If they say STOP or want to opt out, say something like: \"No problem! We've removed you from our reminder list. You're always welcome to call us when you're ready to schedule. Take care!\"
If they ask about cost/insurance, say: \"Great question! Our front desk team can help with insurance and payment details. Give us a call at {PRACTICE_PHONE} and we'll get you sorted out!\"
Keep it brief and conversational (under 160 chars if possible).

Generate ONLY the reply text."""
        
        response = client.messages.create(
            model='claude-sonnet-4-6',
            max_tokens=200,
            system=SYSTEM_PROMPT,
            messages=[{'role': 'user', 'content': conversation_context}]
        )
        
        reply_text = response.content[0].text.strip()
        
        # Detect opt-out intent
        opt_out_keywords = ['stop', 'unsubscribe', 'opt out', 'remove me', 'moved away', 'new dentist', 'new doctor']
        if any(kw in inbound_message.lower() for kw in opt_out_keywords):
            cursor.execute("""
                UPDATE patient_recall SET outreach_status = 'opted_out', updated_at = NOW()
                WHERE patient_hash = %s
            """, (patient_hash,))
            conn.commit()
            logger.info(f'Patient {patient_hash} opted out of recalls')
        
        # Detect scheduling intent
        schedule_keywords = ['yes', 'schedule', 'book', 'available', 'appointment', 'when', 'opening']
        if any(kw in inbound_message.lower() for kw in schedule_keywords):
            cursor.execute("""
                UPDATE patient_recall SET outreach_status = 'engaged', updated_at = NOW()
                WHERE patient_hash = %s
            """, (patient_hash,))
            conn.commit()
            logger.info(f'Patient {patient_hash} showing scheduling intent')
        
        return reply_text
        
    finally:
        conn.close()

def generate_messages_handler(event, context):
    """Lambda handler: Generate recall messages for high-priority overdue patients."""
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    try:
        # Get top priority patients not yet contacted (batch of 50)
        cursor.execute("""
            SELECT pr.*, prs.risk_score, prs.days_overdue
            FROM patient_recall pr
            JOIN patient_risk_scores prs ON pr.patient_hash = prs.patient_hash
            WHERE pr.outreach_status IS NULL OR pr.outreach_status = 'pending'
              AND pr.cell_phone IS NOT NULL AND pr.cell_phone != ''
              AND prs.risk_score >= 40
            ORDER BY prs.risk_score DESC
            LIMIT 50
        """)
        patients = cursor.fetchall()
        
        messages_generated = []
        for patient in patients:
            try:
                message = generate_recall_message(dict(patient))
                messages_generated.append({
                    'patient_hash': patient['patient_hash'],
                    'cell_phone': patient['cell_phone'],
                    'message': message,
                    'risk_score': patient['risk_score']
                })
                
                # Log the generated message
                cursor.execute("""
                    INSERT INTO message_log (patient_hash, direction, message_text, channel, status, sent_at)
                    VALUES (%s, 'outbound', %s, 'sms', 'pending', NOW())
                """, (patient['patient_hash'], message))
                
                # Update outreach status
                cursor.execute("""
                    UPDATE patient_recall SET outreach_status = 'message_generated', updated_at = NOW()
                    WHERE patient_hash = %s
                """, (patient['patient_hash'],))
                
            except Exception as e:
                logger.error(f'Failed to generate message for {patient["patient_hash"]}: {e}')
                continue
        
        conn.commit()
        logger.info(f'Generated {len(messages_generated)} recall messages')
        
        return {
            'statusCode': 200,
            'body': json.dumps({'messages_generated': len(messages_generated)})
        }
        
    finally:
        conn.close()

def inbound_handler(event, context):
    """Lambda handler: Process inbound SMS via Twilio webhook."""
    import urllib.parse
    body = urllib.parse.parse_qs(event.get('body', ''))
    
    from_number = body.get('From', [''])[0]
    message_body = body.get('Body', [''])[0]
    
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    try:
        # Look up patient by phone number
        cursor.execute("""
            SELECT patient_hash FROM patient_recall 
            WHERE cell_phone LIKE %s LIMIT 1
        """, (f'%{from_number[-10:]}%',))
        patient = cursor.fetchone()
        
        if not patient:
            reply = f"Thank you for contacting {PRACTICE_NAME}. Please call us at {PRACTICE_PHONE} for assistance."
        else:
            # Log inbound message
            cursor.execute("""
                INSERT INTO message_log (patient_hash, direction, message_text, channel, status, sent_at)
                VALUES (%s, 'inbound', %s, 'sms', 'received', NOW())
            """, (patient['patient_hash'], message_body))
            conn.commit()
            
            reply = handle_patient_reply(patient['patient_hash'], message_body)
            
            # Log outbound reply
            cursor.execute("""
                INSERT INTO message_log (patient_hash, direction, message_text, channel, status, sent_at)
                VALUES (%s, 'outbound', %s, 'sms', 'sent', NOW())
            """, (patient['patient_hash'], reply))
            conn.commit()
        
        # Return TwiML response
        twiml = f'<?xml version="1.0" encoding="UTF-8"?><Response><Message>{reply}</Message></Response>'
        
        return {
            'statusCode': 200,
            'headers': {'Content-Type': 'text/xml'},
            'body': twiml
        }
    
    finally:
        conn.close()

"""Monthly Practice Intelligence Report Generator
Generates AI-powered monthly analytics report on recall performance.
Triggered by CloudWatch Events on the 1st of each month at 6:00 AM.
"""
import os
import json
import logging
import anthropic
import psycopg2
import psycopg2.extras
import boto3
from datetime import date, datetime, timedelta
from io import BytesIO

logger = logging.getLogger()
logger.setLevel(logging.INFO)

client = anthropic.Anthropic(api_key=os.environ['ANTHROPIC_API_KEY'])
ses = boto3.client('ses')
s3 = boto3.client('s3')

PRACTICE_NAME = os.environ.get('PRACTICE_NAME', 'Practice')
REPORT_BUCKET = os.environ.get('REPORT_BUCKET', 'recall-analytics-reports')
AVG_HYGIENE_REVENUE = float(os.environ.get('AVG_HYGIENE_REVENUE', '250'))  # Average revenue per hygiene visit
AVG_EXAM_REVENUE = float(os.environ.get('AVG_EXAM_REVENUE', '175'))  # Average revenue per exam

def get_db_connection():
    return psycopg2.connect(
        host=os.environ['DB_HOST'],
        port=int(os.environ.get('DB_PORT', '5432')),
        database=os.environ['DB_NAME'],
        user=os.environ['DB_USER'],
        password=os.environ['DB_PASSWORD'],
        sslmode='require'
    )

def gather_metrics(cursor, report_month, report_year):
    """Gather all metrics for the reporting period."""
    month_start = date(report_year, report_month, 1)
    if report_month == 12:
        month_end = date(report_year + 1, 1, 1)
    else:
        month_end = date(report_year, report_month + 1, 1)
    prev_month_start = (month_start - timedelta(days=1)).replace(day=1)
    
    metrics = {}
    
    # Total overdue patients
    cursor.execute("SELECT COUNT(*) FROM patient_recall WHERE recall_due_date < CURRENT_DATE AND patient_status = 'Active'")
    metrics['total_overdue'] = cursor.fetchone()[0]
    
    # Risk distribution
    cursor.execute("""
        SELECT 
            COUNT(*) FILTER (WHERE risk_score >= 80) as critical,
            COUNT(*) FILTER (WHERE risk_score >= 60 AND risk_score < 80) as high,
            COUNT(*) FILTER (WHERE risk_score >= 40 AND risk_score < 60) as medium,
            COUNT(*) FILTER (WHERE risk_score < 40) as low
        FROM patient_risk_scores
    """)
    dist = cursor.fetchone()
    metrics['risk_distribution'] = {'critical': dist[0], 'high': dist[1], 'medium': dist[2], 'low': dist[3]}
    
    # Messages sent this month
    cursor.execute("""
        SELECT COUNT(*) FROM message_log 
        WHERE direction = 'outbound' AND sent_at >= %s AND sent_at < %s
    """, (month_start, month_end))
    metrics['messages_sent'] = cursor.fetchone()[0]
    
    # Response rate
    cursor.execute("""
        SELECT COUNT(DISTINCT patient_hash) FROM message_log 
        WHERE direction = 'inbound' AND sent_at >= %s AND sent_at < %s
    """, (month_start, month_end))
    metrics['patients_responded'] = cursor.fetchone()[0]
    
    # Patients who engaged (showed scheduling intent)
    cursor.execute("""
        SELECT COUNT(*) FROM patient_recall 
        WHERE outreach_status = 'engaged' AND updated_at >= %s AND updated_at < %s
    """, (month_start, month_end))
    metrics['patients_engaged'] = cursor.fetchone()[0]
    
    # Patients scheduled (from outreach)
    cursor.execute("""
        SELECT COUNT(*) FROM patient_recall 
        WHERE outreach_status = 'scheduled' AND updated_at >= %s AND updated_at < %s
    """, (month_start, month_end))
    metrics['patients_scheduled'] = cursor.fetchone()[0]
    
    # Opt-outs
    cursor.execute("""
        SELECT COUNT(*) FROM patient_recall 
        WHERE outreach_status = 'opted_out' AND updated_at >= %s AND updated_at < %s
    """, (month_start, month_end))
    metrics['opt_outs'] = cursor.fetchone()[0]
    
    # Revenue recovery estimate
    metrics['estimated_revenue_recovered'] = (
        metrics['patients_scheduled'] * AVG_HYGIENE_REVENUE
    )
    
    # Average days overdue
    cursor.execute("""
        SELECT ROUND(AVG(CURRENT_DATE - recall_due_date)) 
        FROM patient_recall 
        WHERE recall_due_date < CURRENT_DATE AND patient_status = 'Active'
    """)
    metrics['avg_days_overdue'] = cursor.fetchone()[0] or 0
    
    # Total active patients
    cursor.execute("SELECT COUNT(*) FROM patient_recall WHERE patient_status = 'Active'")
    metrics['total_active_patients'] = cursor.fetchone()[0]
    
    # Overdue rate
    if metrics['total_active_patients'] > 0:
        metrics['overdue_rate_pct'] = round(
            (metrics['total_overdue'] / metrics['total_active_patients']) * 100, 1
        )
    else:
        metrics['overdue_rate_pct'] = 0
    
    return metrics

def generate_ai_insights(metrics):
    """Use Claude to generate natural language insights and recommendations."""
    prompt = f"""Analyze these monthly recall performance metrics for {PRACTICE_NAME} and provide:
1. A 2-3 sentence executive summary of recall health
2. 3-5 specific, actionable recommendations to improve recall rates
3. One highlight/win to celebrate with the practice

Metrics:
- Total active patients: {metrics['total_active_patients']}
- Total overdue for preventive care: {metrics['total_overdue']} ({metrics['overdue_rate_pct']}%)
- Average days overdue: {metrics['avg_days_overdue']}
- Risk distribution: Critical={metrics['risk_distribution']['critical']}, High={metrics['risk_distribution']['high']}, Medium={metrics['risk_distribution']['medium']}, Low={metrics['risk_distribution']['low']}
- Recall messages sent this month: {metrics['messages_sent']}
- Patients who responded: {metrics['patients_responded']}
- Patients who showed scheduling intent: {metrics['patients_engaged']}
- Patients who scheduled: {metrics['patients_scheduled']}
- Patients who opted out: {metrics['opt_outs']}
- Estimated revenue recovered: ${metrics['estimated_revenue_recovered']:,.0f}

Format your response as JSON with keys: executive_summary, recommendations (array of strings), highlight"""
    
    response = client.messages.create(
        model='claude-sonnet-4-6',
        max_tokens=1000,
        messages=[{'role': 'user', 'content': prompt}]
    )
    
    try:
        insights = json.loads(response.content[0].text)
    except json.JSONDecodeError:
        insights = {
            'executive_summary': response.content[0].text[:500],
            'recommendations': ['Review the detailed metrics above for actionable insights.'],
            'highlight': 'The recall system is actively working to bring patients back.'
        }
    
    return insights

def generate_html_report(metrics, insights, report_month, report_year):
    """Generate HTML report content."""
    month_name = date(report_year, report_month, 1).strftime('%B %Y')
    response_rate = round((metrics['patients_responded'] / max(metrics['messages_sent'], 1)) * 100, 1)
    conversion_rate = round((metrics['patients_scheduled'] / max(metrics['patients_responded'], 1)) * 100, 1)
    
    html = f"""
    <html><head><style>
        body {{ font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; color: #333; }}
        h1 {{ color: #2563eb; border-bottom: 2px solid #2563eb; padding-bottom: 10px; }}
        h2 {{ color: #1e40af; margin-top: 30px; }}
        .metric-grid {{ display: grid; grid-template-columns: repeat(3, 1fr); gap: 15px; margin: 20px 0; }}
        .metric-card {{ background: #f8fafc; border: 1px solid #e2e8f0; border-radius: 8px; padding: 15px; text-align: center; }}
        .metric-value {{ font-size: 28px; font-weight: bold; color: #2563eb; }}
        .metric-label {{ font-size: 12px; color: #64748b; margin-top: 5px; }}
        .insight-box {{ background: #eff6ff; border-left: 4px solid #2563eb; padding: 15px; margin: 15px 0; border-radius: 0 8px 8px 0; }}
        .recommendation {{ background: #f0fdf4; border-left: 4px solid #16a34a; padding: 10px 15px; margin: 8px 0; border-radius: 0 6px 6px 0; }}
        .highlight {{ background: #fefce8; border-left: 4px solid #ca8a04; padding: 15px; margin: 15px 0; border-radius: 0 8px 8px 0; }}
        .footer {{ margin-top: 40px; padding-top: 20px; border-top: 1px solid #e2e8f0; color: #94a3b8; font-size: 12px; }}
    </style></head><body>
    <h1>📊 {PRACTICE_NAME} — Preventive Care Recall Report</h1>
    <p style='color: #64748b;'>{month_name} | Generated by AI Analytics Engine</p>
    
    <div class='insight-box'><strong>Executive Summary:</strong> {insights.get('executive_summary', 'Report generated successfully.')}</div>
    
    <h2>Key Metrics</h2>
    <div class='metric-grid'>
        <div class='metric-card'><div class='metric-value'>{metrics['total_overdue']}</div><div class='metric-label'>Patients Overdue</div></div>
        <div class='metric-card'><div class='metric-value'>{metrics['overdue_rate_pct']}%</div><div class='metric-label'>Overdue Rate</div></div>
        <div class='metric-card'><div class='metric-value'>{metrics['avg_days_overdue']}</div><div class='metric-label'>Avg Days Overdue</div></div>
        <div class='metric-card'><div class='metric-value'>{metrics['messages_sent']}</div><div class='metric-label'>Messages Sent</div></div>
        <div class='metric-card'><div class='metric-value'>{response_rate}%</div><div class='metric-label'>Response Rate</div></div>
        <div class='metric-card'><div class='metric-value'>{metrics['patients_scheduled']}</div><div class='metric-label'>Patients Scheduled</div></div>
        <div class='metric-card'><div class='metric-value'>{conversion_rate}%</div><div class='metric-label'>Conversion Rate</div></div>
        <div class='metric-card'><div class='metric-value'>${metrics['estimated_revenue_recovered']:,.0f}</div><div class='metric-label'>Est. Revenue Recovered</div></div>
        <div class='metric-card'><div class='metric-value'>{metrics['opt_outs']}</div><div class='metric-label'>Opt-Outs</div></div>
    </div>
    
    <h2>Risk Distribution</h2>
    <p>🔴 Critical (80+): {metrics['risk_distribution']['critical']} patients | 🟠 High (60-79): {metrics['risk_distribution']['high']} | 🟡 Medium (40-59): {metrics['risk_distribution']['medium']} | 🟢 Low (&lt;40): {metrics['risk_distribution']['low']}</p>
    
    <div class='highlight'><strong>🌟 Highlight:</strong> {insights.get('highlight', 'Great progress this month!')}</div>
    
    <h2>AI Recommendations</h2>
    {''.join(f"<div class='recommendation'>✅ {rec}</div>" for rec in insights.get('recommendations', []))}
    
    <div class='footer'>
        <p>This report was generated automatically by the AI Preventive Care Analytics Engine managed by your MSP partner.</p>
        <p>Data synced from {PRACTICE_NAME} PMS. All patient data handled in compliance with HIPAA.</p>
        <p>Questions? Contact your MSP account manager.</p>
    </div>
    </body></html>
    """
    return html

def handler(event, context):
    """Lambda handler - generates and distributes monthly report."""
    # Determine report period (previous month)
    today = date.today()
    if today.month == 1:
        report_month, report_year = 12, today.year - 1
    else:
        report_month, report_year = today.month - 1, today.year
    
    logger.info(f'Generating report for {report_month}/{report_year}')
    
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    try:
        metrics = gather_metrics(cursor, report_month, report_year)
        insights = generate_ai_insights(metrics)
        html_report = generate_html_report(metrics, insights, report_month, report_year)
        
        # Save to S3
        report_key = f'reports/{report_year}/{report_month:02d}/recall_report_{PRACTICE_NAME.replace(" ", "_")}.html'
        s3.put_object(
            Bucket=REPORT_BUCKET,
            Key=report_key,
            Body=html_report.encode('utf-8'),
            ContentType='text/html',
            ServerSideEncryption='aws:kms'
        )
        
        # Send via SES
        practice_email = os.environ.get('PRACTICE_MANAGER_EMAIL', '')
        msp_email = os.environ.get('MSP_ACCOUNT_MANAGER_EMAIL', '')
        recipients = [e for e in [practice_email, msp_email] if e]
        
        if recipients:
            month_name = date(report_year, report_month, 1).strftime('%B %Y')
            ses.send_email(
                Source=os.environ.get('SES_FROM_EMAIL', 'reports@yourmsp.com'),
                Destination={'ToAddresses': recipients},
                Message={
                    'Subject': {'Data': f'{PRACTICE_NAME} — Preventive Care Recall Report — {month_name}'},
                    'Body': {'Html': {'Data': html_report}}
                }
            )
            logger.info(f'Report sent to {recipients}')
        
        return {'statusCode': 200, 'body': json.dumps({'report_key': report_key, 'metrics': metrics}, default=str)}
    
    finally:
        conn.close()

-- run as migration during initial deployment. All tables support HIPAA audit
-- requirements.

-- Recall Analytics Database Schema
-- Run as migration during initial deployment
-- All tables support HIPAA audit requirements

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Patient recall data (synced from PMS)
CREATE TABLE patient_recall (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    patient_hash VARCHAR(16) NOT NULL,
    pms_patient_id VARCHAR(50) NOT NULL,
    pms_type VARCHAR(20) NOT NULL, -- dentrix, eaglesoft, opendental
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    date_of_birth DATE,
    cell_phone VARCHAR(20),
    email VARCHAR(200),
    patient_status VARCHAR(20) DEFAULT 'Active',
    recall_type VARCHAR(100),
    recall_due_date DATE,
    recall_interval_months INTEGER,
    last_visit_date DATE,
    last_procedure_code VARCHAR(20),
    insurance_carrier VARCHAR(200),
    outreach_status VARCHAR(30), -- NULL, pending, message_generated, sent, engaged, scheduled, completed, opted_out
    sync_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(patient_hash, pms_type)
);

CREATE INDEX idx_patient_recall_due ON patient_recall(recall_due_date) WHERE patient_status = 'Active';
CREATE INDEX idx_patient_recall_status ON patient_recall(outreach_status);
CREATE INDEX idx_patient_recall_phone ON patient_recall(cell_phone);
CREATE INDEX idx_patient_recall_hash ON patient_recall(patient_hash);

-- Patient risk scores
CREATE TABLE patient_risk_scores (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    patient_hash VARCHAR(16) NOT NULL UNIQUE,
    risk_score DECIMAL(5,1) NOT NULL,
    score_breakdown JSONB,
    days_overdue INTEGER,
    scored_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    FOREIGN KEY (patient_hash) REFERENCES patient_recall(patient_hash) ON DELETE CASCADE
);

CREATE INDEX idx_risk_scores_score ON patient_risk_scores(risk_score DESC);

-- Message log (all outbound and inbound messages)
CREATE TABLE message_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    patient_hash VARCHAR(16) NOT NULL,
    direction VARCHAR(10) NOT NULL, -- outbound, inbound
    message_text TEXT NOT NULL,
    channel VARCHAR(10) NOT NULL, -- sms, email, voice
    status VARCHAR(20) NOT NULL, -- pending, sent, delivered, failed, received
    twilio_sid VARCHAR(50),
    sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    delivered_at TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (patient_hash) REFERENCES patient_recall(patient_hash) ON DELETE CASCADE
);

CREATE INDEX idx_message_log_patient ON message_log(patient_hash, sent_at DESC);
CREATE INDEX idx_message_log_date ON message_log(sent_at);

-- Sync audit log
CREATE TABLE sync_audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sync_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    records_extracted INTEGER,
    records_synced INTEGER,
    errors INTEGER,
    duration_seconds DECIMAL(10,2),
    pms_type VARCHAR(20)
);

-- Monthly report snapshots
CREATE TABLE report_snapshots (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    report_month INTEGER NOT NULL,
    report_year INTEGER NOT NULL,
    metrics JSONB NOT NULL,
    ai_insights JSONB,
    generated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    s3_key VARCHAR(500),
    UNIQUE(report_month, report_year)
);

-- PHI access audit log (HIPAA requirement)
CREATE TABLE phi_access_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    accessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    accessed_by VARCHAR(100) NOT NULL, -- system component or user
    action VARCHAR(50) NOT NULL, -- read, sync, export, view_dashboard
    resource_type VARCHAR(50) NOT NULL, -- patient_recall, message_log, report
    record_count INTEGER,
    ip_address INET,
    details JSONB
);

CREATE INDEX idx_phi_access_date ON phi_access_log(accessed_at);

-- Trigger to auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER patient_recall_updated
    BEFORE UPDATE ON patient_recall
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- View for dashboard: overdue patients with risk scores
CREATE VIEW v_overdue_patients AS
SELECT 
    pr.patient_hash,
    pr.first_name,
    pr.last_name,
    pr.cell_phone,
    pr.email,
    pr.recall_type,
    pr.recall_due_date,
    pr.last_visit_date,
    pr.insurance_carrier,
    pr.outreach_status,
    prs.risk_score,
    prs.days_overdue,
    prs.score_breakdown,
    CASE 
        WHEN prs.risk_score >= 80 THEN 'Critical'
        WHEN prs.risk_score >= 60 THEN 'High'
        WHEN prs.risk_score >= 40 THEN 'Medium'
        ELSE 'Low'
    END AS risk_level
FROM patient_recall pr
LEFT JOIN patient_risk_scores prs ON pr.patient_hash = prs.patient_hash
WHERE pr.patient_status = 'Active'
  AND pr.recall_due_date < CURRENT_DATE
ORDER BY prs.risk_score DESC NULLS LAST;

-- View for dashboard: recall funnel metrics
CREATE VIEW v_recall_funnel AS
SELECT
    COUNT(*) FILTER (WHERE outreach_status IS NULL OR outreach_status = 'pending') AS not_contacted,
    COUNT(*) FILTER (WHERE outreach_status = 'sent') AS contacted,
    COUNT(*) FILTER (WHERE outreach_status = 'engaged') AS responded,
    COUNT(*) FILTER (WHERE outreach_status = 'scheduled') AS scheduled,
    COUNT(*) FILTER (WHERE outreach_status = 'completed') AS completed,
    COUNT(*) FILTER (WHERE outreach_status = 'opted_out') AS opted_out,
    COUNT(*) AS total_overdue
FROM patient_recall
WHERE patient_status = 'Active'
  AND recall_due_date < CURRENT_DATE;

# Recall Analytics Engine. Deploy with: serverless deploy --stage production

# serverless.yml - Recall Analytics Engine
# Deploy with: serverless deploy --stage production

service: recall-analytics-engine

provider:
  name: aws
  runtime: python3.11
  stage: ${opt:stage, 'dev'}
  region: us-east-1
  memorySize: 512
  timeout: 900  # 15 minutes max for analysis jobs
  environment:
    DB_HOST: ${ssm:/recall-analytics/${self:provider.stage}/db-host}
    DB_PORT: '5432'
    DB_NAME: recall_analytics
    DB_USER: ${ssm:/recall-analytics/${self:provider.stage}/db-user}
    DB_PASSWORD: ${ssm:/recall-analytics/${self:provider.stage}/db-password~true}
    ANTHROPIC_API_KEY: ${ssm:/recall-analytics/${self:provider.stage}/anthropic-api-key~true}
    TWILIO_ACCOUNT_SID: ${ssm:/recall-analytics/${self:provider.stage}/twilio-sid~true}
    TWILIO_AUTH_TOKEN: ${ssm:/recall-analytics/${self:provider.stage}/twilio-auth~true}
    TWILIO_PHONE_NUMBER: ${ssm:/recall-analytics/${self:provider.stage}/twilio-phone}
    PRACTICE_NAME: ${ssm:/recall-analytics/${self:provider.stage}/practice-name}
    PRACTICE_PHONE: ${ssm:/recall-analytics/${self:provider.stage}/practice-phone}
    SCHEDULING_URL: ${ssm:/recall-analytics/${self:provider.stage}/scheduling-url}
    PRACTICE_MANAGER_EMAIL: ${ssm:/recall-analytics/${self:provider.stage}/practice-email}
    MSP_ACCOUNT_MANAGER_EMAIL: ${ssm:/recall-analytics/${self:provider.stage}/msp-email}
    SES_FROM_EMAIL: ${ssm:/recall-analytics/${self:provider.stage}/ses-from-email}
    REPORT_BUCKET: recall-analytics-reports-${self:provider.stage}
    AVG_HYGIENE_REVENUE: '250'
    AVG_EXAM_REVENUE: '175'
  vpc:
    securityGroupIds:
      - ${ssm:/recall-analytics/${self:provider.stage}/lambda-sg}
    subnetIds:
      - ${ssm:/recall-analytics/${self:provider.stage}/subnet-1}
      - ${ssm:/recall-analytics/${self:provider.stage}/subnet-2}
  iam:
    role:
      statements:
        - Effect: Allow
          Action:
            - s3:PutObject
            - s3:GetObject
          Resource: arn:aws:s3:::recall-analytics-reports-${self:provider.stage}/*
        - Effect: Allow
          Action:
            - ses:SendEmail
            - ses:SendRawEmail
          Resource: '*'
        - Effect: Allow
          Action:
            - ssm:GetParameter
            - ssm:GetParameters
          Resource: arn:aws:ssm:${self:provider.region}:*:parameter/recall-analytics/${self:provider.stage}/*
        - Effect: Allow
          Action:
            - kms:Decrypt
          Resource: '*'
        - Effect: Allow
          Action:
            - logs:CreateLogGroup
            - logs:CreateLogStream
            - logs:PutLogEvents
          Resource: '*'

functions:
  analyzePatients:
    handler: risk_scoring.handler
    description: Nightly patient risk scoring analysis
    events:
      - schedule:
          rate: cron(0 8 * * ? *)  # 3 AM EST (8 AM UTC)
          enabled: true
          input:
            source: scheduled

  generateRecallMessages:
    handler: recall_messages.generate_messages_handler
    description: Generate AI-powered recall messages for top priority patients
    events:
      - schedule:
          rate: cron(0 14 * * ? *)  # 9 AM EST - business hours
          enabled: true

  sendRecalls:
    handler: send_sms.handler
    description: Send pending recall messages via Twilio
    memorySize: 256
    timeout: 300
    events:
      - schedule:
          rate: cron(15 14 * * ? *)  # 9:15 AM EST - right after generation
          enabled: true

  handleInbound:
    handler: recall_messages.inbound_handler
    description: Process inbound patient SMS replies via Twilio webhook
    memorySize: 256
    timeout: 30
    events:
      - http:
          path: twilio/inbound
          method: post
          cors: false

  generateReport:
    handler: monthly_report.handler
    description: Monthly practice intelligence report
    timeout: 900
    events:
      - schedule:
          rate: cron(0 11 1 * ? *)  # 6 AM EST on 1st of month
          enabled: true

  dashboardAPI:
    handler: dashboard_api.handler
    description: REST API for Retool analytics dashboard
    memorySize: 256
    timeout: 30
    events:
      - http:
          path: api/{proxy+}
          method: any
          cors: true
          authorizer:
            type: TOKEN
            identitySource: method.request.header.Authorization
            resultTtlInSeconds: 300

  migrate:
    handler: migrations.handler
    description: Database migration runner
    timeout: 120

  healthCheck:
    handler: health.handler
    description: Health check endpoint
    memorySize: 128
    timeout: 10
    events:
      - http:
          path: health
          method: get

plugins:
  - serverless-python-requirements

custom:
  pythonRequirements:
    dockerizePip: true
    slim: true
    layer: true
    noDeploy:
      - pytest
      - boto3  # Already in Lambda runtime
      - botocore

package:
  individually: true
  patterns:
    - '!node_modules/**'
    - '!.git/**'
    - '!tests/**'
    - '!.env*'
    - '!README.md'