-- Executive KPI Summary Query
SELECT 
  DATE_TRUNC('month', date) as month,
  COUNT(DISTINCT customer_id) as active_customers,
  SUM(arr) as total_arr,
  AVG(health_score) as avg_health_score,
  SUM(CASE WHEN churn_date IS NOT NULL THEN 1 ELSE 0 END) as churned_customers,
  (SUM(expansion_arr) / SUM(arr)) * 100 as net_expansion_rate
FROM customer_metrics 
WHERE date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', date)
ORDER BY month DESC;

# Python health scoring algorithm
def calculate_health_score(customer_data):
    weights = {
        'product_usage': 0.35,
        'engagement': 0.25,
        'support_sentiment': 0.20,
        'payment_history': 0.20
    }

    scores = {
        'product_usage': min(customer_data['daily_active_users'] / customer_data['licensed_users'], 1.0),
        'engagement': customer_data['training_completion_rate'],
        'support_sentiment': customer_data['avg_ticket_sentiment'],
        'payment_history': 1.0 if customer_data['days_overdue'] == 0 else max(0, 1 - customer_data['days_overdue'] / 90)
    }

    health_score = sum(score * weights[metric] for metric, score in scores.items()) * 100

    if health_score >= 80:
        return {'score': health_score, 'status': 'Healthy', 'color': '#22c55e'}
    elif health_score >= 60:
        return {'score': health_score, 'status': 'At Risk', 'color': '#f59e0b'}
    else:
        return {'score': health_score, 'status': 'Critical', 'color': '#ef4444'}

// React component for KPI cards
const MetricCard = ({ title, value, change, trend, target }) => {
  const isPositive = trend === 'up';
  const meetingTarget = value >= target;

  return (
    <div className="bg-white p-6 rounded-lg shadow-sm border">
      <div className="flex items-center justify-between">
        <h3 className="text-sm font-medium text-gray-500">{title}</h3>
        <span className={`text-xs px-2 py-1 rounded ${
          meetingTarget ? 'bg-green-100 text-green-800' : 'bg-red-100 text-red-800'
        }`}>
          Target: {target}%
        </span>
      </div>
      <div className="mt-2">
        <div className="text-3xl font-bold text-gray-900">{value}%</div>
        <div className={`flex items-center text-sm ${
          isPositive ? 'text-green-600' : 'text-red-600'
        }`}>
          <span>{change}% from last month</span>
        </div>
      </div>
    </div>
  );
};

-- Cohort retention analysis
WITH customer_cohorts AS (
  SELECT 
    customer_id,
    DATE_TRUNC('month', first_purchase_date) as cohort_month,
    DATE_TRUNC('month', purchase_date) as purchase_month
  FROM customer_purchases
),
cohort_data AS (
  SELECT 
    cohort_month,
    purchase_month,
    COUNT(DISTINCT customer_id) as customers
  FROM customer_cohorts
  GROUP BY cohort_month, purchase_month
)
SELECT 
  cohort_month,
  purchase_month,
  customers,
  ROUND(100.0 * customers / 
    FIRST_VALUE(customers) OVER (
      PARTITION BY cohort_month 
      ORDER BY purchase_month
    ), 2) as retention_rate
FROM cohort_data
ORDER BY cohort_month, purchase_month;

# Churn prediction integration
import pandas as pd
from sklearn.ensemble import RandomForestClassifier

def generate_churn_alerts(customer_features):
    # Features: health_score, days_since_login, support_tickets, contract_value
    model = RandomForestClassifier(n_estimators=100)

    churn_probability = model.predict_proba(customer_features)[:, 1]

    alerts = []
    for idx, prob in enumerate(churn_probability):
        if prob > 0.7:
            alerts.append({
                'customer_id': customer_features.iloc[idx]['customer_id'],
                'churn_probability': prob,
                'priority': 'High',
                'recommended_action': 'Immediate CSM intervention'
            })
        elif prob > 0.4:
            alerts.append({
                'customer_id': customer_features.iloc[idx]['customer_id'],
                'churn_probability': prob,
                'priority': 'Medium',
                'recommended_action': 'Schedule health check call'
            })

    return sorted(alerts, key=lambda x: x['churn_probability'], reverse=True)

# Alert configuration for CS metrics
alerts:
  health_score_drop:
    metric: "customer_health_score"
    threshold: 20  # 20 point drop
    timeframe: "7d"
    action: "create_task"
    assignee: "account_csm"

  usage_decline:
    metric: "daily_active_users"
    threshold: -30  # 30% decline
    timeframe: "14d"
    action: "send_slack"
    channel: "#customer-success"

  expansion_opportunity:
    metric: "feature_adoption_rate"
    threshold: 80  # 80% adoption of current plan
    timeframe: "30d"
    action: "create_opportunity"
    stage: "qualified"

# ETL pipeline for CS metrics
class CSMetricsETL:
    def __init__(self):
        self.sources = {
            'crm': self.extract_crm_data,
            'product': self.extract_usage_data,
            'support': self.extract_support_data,
            'financial': self.extract_billing_data
        }

    def extract_and_transform(self):
        metrics = {}

        for source, extractor in self.sources.items():
            raw_data = extractor()
            metrics[source] = self.transform_data(raw_data, source)

        # Join and enrich data
        unified_metrics = self.join_customer_data(metrics)
        return self.calculate_derived_metrics(unified_metrics)

    def calculate_derived_metrics(self, data):
        data['health_score'] = self.calculate_health_score(data)
        data['churn_risk'] = self.calculate_churn_risk(data)
        data['expansion_score'] = self.calculate_expansion_score(data)
        return data