frontend/src/constants/fallbacks.js

// Fallback data for all demo tabs when Gemini API is unavailable

export const FALLBACK_NL2SQL = {
  sql: `WITH sentiment_baseline AS (
  -- Step 1: Calculate each customer's average sentiment score
  SELECT
    r.customer_id,
    AVG(r.sentiment_score) AS avg_sentiment,
    COUNT(*) AS total_reviews,
    MIN(r.sentiment_score) AS worst_review
  FROM reviews r
  GROUP BY r.customer_id
  HAVING AVG(r.sentiment_score) < -0.4  -- angry threshold
),
purchase_behavior AS (
  -- Step 2: Get post-complaint order stats
  SELECT
    o.customer_id,
    COUNT(*) AS orders_after_complaint,
    SUM(o.amount) AS spend_after_complaint,
    MAX(o.created_at) AS last_purchase
  FROM orders o
  JOIN sentiment_baseline sb ON sb.customer_id = o.customer_id
  WHERE o.created_at > (
    SELECT MIN(r.created_at) FROM reviews r
    WHERE r.customer_id = o.customer_id
    AND r.sentiment_score < -0.4
  )
  GROUP BY o.customer_id
  HAVING COUNT(*) >= 1
),
ranked_loyal_complainers AS (
  -- Step 3: Join and rank by lifetime value
  SELECT
    c.id, c.name, c.email, c.lifetime_value,
    sb.avg_sentiment,
    pb.orders_after_complaint,
    pb.spend_after_complaint,
    RANK() OVER (ORDER BY c.lifetime_value DESC) AS ltv_rank,
    DENSE_RANK() OVER (ORDER BY sb.avg_sentiment ASC) AS angriest_rank,
    LAG(pb.spend_after_complaint) OVER (
      ORDER BY c.lifetime_value DESC
    ) AS prev_customer_spend,
    c.segment,
    c.ai_metadata->>'cohort_label' AS ai_cohort
  FROM customers c
  JOIN sentiment_baseline sb ON sb.customer_id = c.id
  JOIN purchase_behavior pb ON pb.customer_id = c.id
)
SELECT
  name, email, segment, ai_cohort,
  ROUND(lifetime_value, 2) AS lifetime_value_usd,
  ROUND(avg_sentiment, 3) AS avg_sentiment_score,
  orders_after_complaint,
  ROUND(spend_after_complaint, 2) AS spend_after_complaint_usd,
  ltv_rank, angriest_rank,
  last_purchase::DATE AS last_active
FROM ranked_loyal_complainers
ORDER BY ltv_rank
LIMIT 50;
-- Run EXPLAIN ANALYZE for execution plan`,
  explanation: `This query identifies your highest-value customers who complained but stayed loyal — a critical retention segment. It uses three CTEs to separate concerns: sentiment aggregation, purchase behavior filtering, and final ranking. The two window functions (RANK and DENSE_RANK) provide dual-axis sorting — by lifetime value and by anger severity — letting the business prioritize outreach. The JSONB operator extracts AI-computed cohort labels stored by the trigger system, avoiding a JOIN. On the reviews table, PostgreSQL will use the HNSW-adjacent GIN index on ai_metadata for the JSONB predicate.`
};

export const FALLBACK_TRIGGER_REVIEW = {
  score: -0.89,
  label: "NEGATIVE",
  confidence: 0.97,
  key_phrases: ["battery died", "no support", "11 days"]
};

export const FALLBACK_TRIGGER_PRODUCT = {
  category: "Electronics",
  subcategory: "Audio > Headphones",
  tags: ["wireless", "premium", "over-ear"],
  quality_score: 0.88,
  similar_categories: ["Bluetooth Speakers", "Earbuds"]
};

export const FALLBACK_TRIGGER_TRANSACTION = {
  fraud_score: 0.91,
  risk_level: "CRITICAL",
  risk_factors: ["Amount 54x above customer average", "New geographic location", "High-value electronics merchant"],
  recommended_action: "BLOCK",
  confidence: 0.94,
  inference_ms: 12.42,
  model_auc: 0.9455,
  model_version: "XGBoost-v1.0-IEEE-CIS",
  shap_attributions: [
    { feature: "Transaction Amount", shap_value: 0.35 },
    { feature: "Customer Historic Risk", shap_value: 0.15 },
    { feature: "Merchant Profile Risk", shap_value: 0.25 },
    { feature: "IP Location Anomaly", shap_value: 0.30 },
    { feature: "Transaction Velocity", shap_value: 0.05 }
  ],
  source: "fallback_static"
};

export const FALLBACK_SEARCH_RESULTS = [
  { name: "Sony WH-1000XM5", category: "Electronics > Headphones", price: "$349.99", vector_score: 0.96, text_score: 0.88, rrf_score: 0.94, why_matched: "Exact semantic match for noise cancelling + travel headphones", in_stock: true },
  { name: "Bose QuietComfort Ultra", category: "Electronics > Headphones", price: "$429.00", vector_score: 0.94, text_score: 0.82, rrf_score: 0.91, why_matched: "Premium noise cancelling, strong brand association with travel", in_stock: true },
  { name: "Apple AirPods Max", category: "Electronics > Headphones", price: "$549.00", vector_score: 0.89, text_score: 0.71, rrf_score: 0.84, why_matched: "High-end active noise cancellation, over-ear design", in_stock: true },
  { name: "Jabra Elite 85h", category: "Electronics > Headphones", price: "$199.99", vector_score: 0.85, text_score: 0.76, rrf_score: 0.82, why_matched: "Noise cancelling headphones with long battery life for travel", in_stock: false },
  { name: "Sennheiser Momentum 4", category: "Electronics > Headphones", price: "$299.95", vector_score: 0.82, text_score: 0.68, rrf_score: 0.78, why_matched: "Premium over-ear headphones, adaptive noise cancellation", in_stock: true },
  { name: "Anker Soundcore Space Q45", category: "Electronics > Headphones", price: "$99.99", vector_score: 0.78, text_score: 0.73, rrf_score: 0.76, why_matched: "Budget noise cancelling option, foldable travel design", in_stock: true }
];