-- R557 Phase 53 — Leading-indicator customer health signals.
-- AR aging alone is a *lagging* indicator. By the time AR moves, the
-- customer has already disengaged. This migration adds the substrate for
-- *leading* signals so we can predict churn or escalation 30-60 days
-- earlier. Mike's high-leverage move #3 — Driscoll is 36.4% of revenue,
-- so a single early-warning on a top-5 customer pays this 100x.

CREATE TABLE IF NOT EXISTS customer_health_signals (
  signal_id          INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id        TEXT NOT NULL,            -- NS entity ID, joinable to customers
  signal_type        TEXT NOT NULL,
    -- 'order_velocity_decline'      | order count last-30 vs trailing-90 baseline
    -- 'order_recency_gap'           | days since last order vs typical interval (median of last-12)
    -- 'email_response_latency'      | mean reply time (us→them) trend over last-90
    -- 'dispute_frequency'           | dispute / credit-memo count last-30
    -- 'inbound_sentiment_trend'     | AI-classified email sentiment slope
    -- 'order_spread_narrowing'      | distinct SKU count vs typical mix
    -- 'call_to_order_ratio'         | inbound calls without orders (when call data exists)
    -- 'reflexion_friction'          | count of reflexion entries tagged 'friction' for this customer
    -- 'ar_aging_lagging'            | the lagging signal — kept for completeness
  signal_value       REAL,                     -- normalized to a 0-1 risk contribution
  signal_raw_value   TEXT,                     -- raw measured value (string so any type fits)
  direction          TEXT,                     -- 'risk_up' | 'risk_down' | 'stable'
  computed_at        TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  window_start       TEXT,
  window_end         TEXT,
  source             TEXT,                     -- 'compute_signal_cron' | 'event_stream' | 'manual'
  notes              TEXT
);
CREATE INDEX IF NOT EXISTS idx_chs_customer ON customer_health_signals(customer_id, computed_at DESC);
CREATE INDEX IF NOT EXISTS idx_chs_type ON customer_health_signals(signal_type, computed_at DESC);

-- Composite score (nightly): weighted sum of signals → 0-100 risk score.
CREATE TABLE IF NOT EXISTS customer_health_scores (
  customer_id        TEXT PRIMARY KEY,
  health_score       REAL NOT NULL,            -- 0-100 (lower = healthier)
  risk_tier          TEXT NOT NULL,            -- 'green' | 'yellow' | 'orange' | 'red'
  top_signals_json   TEXT,                     -- JSON array of top-3 signal contributions
  computed_at        TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  prior_score        REAL,
  prior_tier         TEXT,
  trend_arrow        TEXT,                     -- 'up' | 'down' | 'flat'
  notes              TEXT
);
CREATE INDEX IF NOT EXISTS idx_chs_score ON customer_health_scores(health_score DESC);
CREATE INDEX IF NOT EXISTS idx_chs_tier ON customer_health_scores(risk_tier);

-- Score history for trend analysis. One row per nightly compute per customer.
CREATE TABLE IF NOT EXISTS customer_health_score_history (
  history_id    INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id   TEXT NOT NULL,
  health_score  REAL NOT NULL,
  risk_tier     TEXT NOT NULL,
  computed_at   TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_chs_hist_customer ON customer_health_score_history(customer_id, computed_at DESC);
CREATE INDEX IF NOT EXISTS idx_chs_hist_date ON customer_health_score_history(computed_at DESC);

INSERT OR IGNORE INTO migration_history (migration_id, description) VALUES
  ('126_customer_health_signals', 'R557 Phase 53 — leading-indicator customer health signals + composite score + history (Mike high-leverage #3)');
