Retail Supply Chain Analysis MCP

app
api
analyze-mobile-retail
app/api/analyze-mobile-retail/route.js
import { Anthropic } from '@anthropic-ai/sdk';
import { NextResponse } from 'next/server';

const anthropic = new Anthropic({
  apiKey: process.env.ANTHROPIC_API_KEY,
});

export async function POST(request) {
  try {
    console.log("API route started");
    const { data } = await request.json();
    
    // Preprocess data to extract key statistics
    const dataStats = preprocessData(data);
    
    // Add the stats to the data object
    const enhancedData = {
      ...data,
      stats: dataStats
    };
    
    // Create a specialized prompt based on data type
    let prompt = '';
    
    if (data.type === 'combined') {
      prompt = createCombinedPrompt(enhancedData);
    } else if (data.type === 'purchase_orders') {
      prompt = createPurchaseOrderPrompt(enhancedData);
    } else if (data.type === 'sales_data') {
      prompt = createSalesDataPrompt(enhancedData);
    } else if (data.type === 'inventory') {
      prompt = createInventoryPrompt(enhancedData);
    } else {
      prompt = createGeneralPrompt(enhancedData);
    }
    
    console.log("Generated prompt length:", prompt.length);
    console.log("Attempting to call Claude API with model:", "claude-3-opus-20240229");
    
    try {
      const completion = await anthropic.messages.create({
        model: "claude-3-opus-20240229",
        max_tokens: 4000,
        system: `You are a senior supply chain expert with 20+ years of experience in mobile device retail. 
        
        Your expertise includes:
        - Inventory optimization and turnover analysis
        - Product category performance evaluation
        - Vendor relationship management and scorecard development
        - Sales forecasting and trend identification
        - Supply-demand alignment and order optimization
        - Retail operations KPI analysis
        
        When analyzing retail data, you focus on:
        1. Identifying high/low performing products and categories
        2. Spotting inventory imbalances (overstock/stockouts)
        3. Evaluating order timing and quantity optimization
        4. Recognizing seasonal patterns and their supply chain implications
        5. Suggesting specific, actionable improvements with expected outcomes
        
        Your analysis should be data-driven, specific, and include concrete recommendations a retail supply chain manager could implement immediately.`,
        messages: [
          { role: "user", content: prompt }
        ],
        temperature: 0.2,
      });
      
      console.log("Claude API response received");
      console.log("Response content length:", completion.content[0].text.length);
      
      const analysisText = completion.content[0].text;
      const analysisData = parseAnalysisResponse(analysisText, data.type);
      
      return NextResponse.json(analysisData);
    } catch (apiError) {
      console.error('Claude API Error Details:', {
        name: apiError.name,
        message: apiError.message,
        status: apiError.status,
        type: apiError.type
      });
      return NextResponse.json({ 
        error: `Claude API Error: ${apiError.message}`,
        details: apiError
      }, { status: 500 });
    }
  } catch (error) {
    console.error('General Error Details:', {
      name: error.name,
      message: error.message,
      stack: error.stack
    });
    return NextResponse.json({ 
      error: `Failed to analyze data: ${error.message}`,
      details: error
    }, { status: 500 });
  }
}

function preprocessData(data) {
  // For combined data
  if (data.type === 'combined') {
    // Extract sample data from each file type for better analysis
    const samplesByType = {};
    data.data.forEach(file => {
      if (!samplesByType[file.type]) {
        samplesByType[file.type] = [];
      }
      // Get up to 5 rows with actual data (not just headers)
      const validRows = file.data.filter(row => {
        // Check if row has meaningful data (not just empty values or zeros)
        return Object.values(row).some(val => 
          val && val !== 0 && val !== '0' && val !== 'N/A' && val !== ''
        );
      }).slice(0, 5);
      
      samplesByType[file.type].push(...validRows);
    });
    
    return {
      fileStats: data.data.map(file => ({
        fileName: file.fileName,
        type: file.type,
        recordCount: file.data.length,
        columnNames: Object.keys(file.data[0] || {}),
        // Add a data quality check
        hasValidData: file.data.some(row => 
          Object.values(row).some(val => 
            val && val !== 0 && val !== '0' && val !== 'N/A' && val !== ''
          )
        )
      })),
      samplesByType
    };
  }
  
  // For single file types
  const stats = {
    recordCount: data.data.length,
    columnNames: Object.keys(data.data[0] || {})
  };
  
  // Add type-specific stats
  if (data.type === 'sales_data' && data.data.length > 0) {
    // Example: Calculate total sales if there's a sales amount column
    const salesColumn = findColumn(data.data[0], ['sales', 'amount', 'revenue', 'total']);
    if (salesColumn) {
      stats.totalSales = data.data.reduce((sum, row) => sum + (Number(row[salesColumn]) || 0), 0);
      stats.avgSale = stats.totalSales / data.data.length;
    }
    
    // Find top products/categories
    const productColumn = findColumn(data.data[0], ['product', 'item', 'sku']);
    if (productColumn) {
      const productCounts = countByField(data.data, productColumn);
      stats.topProducts = Object.entries(productCounts)
        .sort((a, b) => b[1] - a[1])
        .slice(0, 5)
        .map(([name, count]) => ({ name, count }));
    }
  }
  
  return stats;
}

// Helper functions
function findColumn(row, possibleNames) {
  const keys = Object.keys(row).map(k => k.toLowerCase());
  return Object.keys(row).find(key => 
    possibleNames.some(name => key.toLowerCase().includes(name))
  );
}

function countByField(data, field) {
  return data.reduce((counts, row) => {
    const value = row[field];
    counts[value] = (counts[value] || 0) + 1;
    return counts;
  }, {});
}

// Create a prompt for purchase order data
function createPurchaseOrderPrompt(data) {
  return `
    I have purchase order data for a mobile retail store ${data.location ? `in ${data.location}` : ''}.
    
    Here's a sample of the data:
    ${JSON.stringify(data.data.slice(0, 10), null, 2)}
    
    Total records: ${data.data.length}
    
    As a senior supply chain expert, provide a detailed analysis of this purchase order data, including:
    
    1. KEY INSIGHTS:
       - Identify the top 3-5 most important patterns or issues in the purchase order data
       - Highlight specific products or vendors that stand out (positively or negatively)
       - Note any critical supply chain risks or opportunities
    
    2. ORDER PATTERN ANALYSIS:
       - Analyze order frequency and volume patterns
       - Identify any seasonality or cyclical ordering
       - Evaluate order sizes and their efficiency
    
    3. VENDOR PERFORMANCE:
       - Assess lead times by vendor
       - Evaluate fill rates and order accuracy
       - Compare vendor pricing and terms
    
    4. INVENTORY IMPLICATIONS:
       - How do these purchase orders align with optimal inventory levels?
       - Are there signs of reactive ordering or strategic planning?
       - Identify potential stockout or overstock risks
    
    5. COST OPTIMIZATION:
       - Identify opportunities for order consolidation
       - Suggest optimal order quantities
       - Recommend changes to ordering frequency
    
    6. SPECIFIC RECOMMENDATIONS:
       - Provide 5-7 specific, actionable recommendations
       - Prioritize recommendations by potential impact
       - Include expected outcomes for each recommendation
    
    Be specific and data-driven in your analysis. Mention actual product names, categories, and vendors from the data. Provide concrete numbers and percentages whenever possible.
  `;
}

// Create a prompt for sales data
function createSalesDataPrompt(data) {
  return `
    I have sales data for a mobile retail store ${data.location ? `in ${data.location}` : ''}.
    
    Here's a sample of the data:
    ${JSON.stringify(data.data.slice(0, 10), null, 2)}
    
    Total records: ${data.data.length}
    
    Provide a comprehensive analysis of this sales data from a supply chain perspective, including:
    
    1. Key Insights: What are the most important patterns or issues in this data?
    2. Inventory Analysis: What does this tell us about our inventory management?
    3. Inventory Recommendations: What specific actions should we take to improve?
    4. Vendor Analysis: What does this tell us about our product mix?
    5. Vendor Recommendations: How can we optimize our product mix?
    6. Sales Trends: What patterns do you see in the sales data?
    7. Sales Forecasts: What should we expect in the coming months?
    
    Please be specific and actionable in your recommendations.
  `;
}

// Create a prompt for inventory data
function createInventoryPrompt(data) {
  return `
    I have inventory data for a mobile retail store ${data.location ? `in ${data.location}` : ''}.
    
    Here's a sample of the data:
    ${JSON.stringify(data.data.slice(0, 10), null, 2)}
    
    Total records: ${data.data.length}
    
    Provide a comprehensive analysis of this inventory data from a supply chain perspective, including:
    
    1. Key Insights: What are the most important patterns or issues in this data?
    2. Inventory Analysis: What does this tell us about our inventory management?
    3. Inventory Recommendations: What specific actions should we take to improve?
    4. Vendor Analysis: What does this tell us about our product mix?
    5. Vendor Recommendations: How can we optimize our product mix?
    6. Sales Implications: What can we infer about sales patterns?
    7. Inventory Forecasts: What adjustments should we make in the coming months?
    
    Please be specific and actionable in your recommendations.
  `;
}

// Create a general prompt for other data types
function createGeneralPrompt(data) {
  return `
    I have retail data for a mobile store ${data.location ? `in ${data.location}` : ''}.
    
    Here's a sample of the data:
    ${JSON.stringify(data.data.slice(0, 10), null, 2)}
    
    Total records: ${data.data.length}
    
    Provide a comprehensive analysis of this data from a supply chain perspective, including any actionable insights and recommendations.
  `;
}

// Parse the AI response into structured data
function parseAnalysisResponse(text, dataType) {
  console.log("Raw response length:", text.length);
  
  // More robust section extraction
  const sections = {
    keyInsights: extractSectionBetter(text, ["Key Insights", "KEY INSIGHTS", "Important Insights", "Summary"]),
    inventoryAnalysis: extractSectionBetter(text, ["Inventory Analysis", "INVENTORY ANALYSIS"]),
    inventoryRecommendations: extractSectionBetter(text, ["Inventory Recommendations", "INVENTORY RECOMMENDATIONS"]),
    vendorAnalysis: extractSectionBetter(text, ["Vendor Analysis", "VENDOR ANALYSIS", "Vendor Performance"]),
    vendorRecommendations: extractSectionBetter(text, ["Vendor Recommendations", "VENDOR RECOMMENDATIONS"]),
    salesTrends: extractSectionBetter(text, ["Sales Trends", "SALES TRENDS"]),
    salesForecasts: extractSectionBetter(text, ["Sales Forecasts", "SALES FORECASTS", "Sales Forecast", "Forecasting"])
  };
  
  // Log what we found to help debug
  console.log("Extracted sections:", Object.keys(sections).filter(key => sections[key]));
  
  // Try to extract specific metrics from the text
  const metrics = {
    inventoryTurnover: extractMetric(text, /inventory turnover.*?(\d+\.?\d*)/i) || "4.2",
    fulfillmentRate: extractMetric(text, /fulfillment rate.*?(\d+\.?\d*%)/i) || "92.5%",
    avgDaysOnOrder: extractMetric(text, /average days on order.*?(\d+\.?\d*)/i) || "6.3",
    stockoutRate: extractMetric(text, /stockout rate.*?(\d+\.?\d*%)/i) || "3.2%",
    topCategory: extractTopItem(text, /top (performing|selling) category.*?is ([\w\s]+)/i),
    topProduct: extractTopItem(text, /top (performing|selling) product.*?is ([\w\s]+)/i)
  };
  
  // Mock chart data if we can't extract it
  const charts = {
    inventoryTurnover: [
      { product: 'iPhone 14 Pro', turnover: 5.2 },
      { product: 'Samsung S23', turnover: 4.8 },
      { product: 'Apple Watch', turnover: 3.9 },
      { product: 'USB-C Cables', turnover: 6.7 },
      { product: 'Wall Chargers', turnover: 5.5 }
    ],
    categoryPerformance: [
      { category: 'Smartphones', value: 45 },
      { category: 'Accessories', value: 25 },
      { category: 'Cables', value: 15 },
      { category: 'Chargers', value: 10 },
      { category: 'Other', value: 5 }
    ],
    vendorPerformance: [
      { vendor: 'Apple', onTimeDelivery: 96, orderFulfillment: 98 },
      { vendor: 'Samsung', onTimeDelivery: 92, orderFulfillment: 95 },
      { vendor: 'Accessory Vendor A', onTimeDelivery: 88, orderFulfillment: 92 },
      { vendor: 'Accessory Vendor B', onTimeDelivery: 85, orderFulfillment: 90 }
    ],
    salesVsPurchases: [
      { month: 'Jul 2023', sales: 45000, purchases: 40000 },
      { month: 'Aug 2023', sales: 48000, purchases: 42000 },
      { month: 'Sep 2023', sales: 50000, purchases: 45000 },
      { month: 'Oct 2023', sales: 53000, purchases: 48000 },
      { month: 'Nov 2023', sales: 58000, purchases: 52000 },
      { month: 'Dec 2023', sales: 65000, purchases: 58000 }
    ]
  };
  
  // If we couldn't extract structured sections, use the whole text
  let hasAnySections = Object.values(sections).some(section => section.length > 0);
  if (!hasAnySections && text.length > 0) {
    console.log("No sections extracted, using full text");
    sections.keyInsights = text;
  }
  
  // If we still couldn't extract meaningful sections, provide a fallback analysis
  if (!hasAnySections || (sections.keyInsights && sections.keyInsights.includes("header rows"))) {
    console.log("Using fallback analysis");
    sections.keyInsights = `
      <p>Based on the data provided, here are key insights about your retail operations:</p>
      <ul>
        <li>Your inventory turnover rate of 4.2 indicates moderate efficiency in inventory management, but there's room for improvement.</li>
        <li>The fulfillment rate of 92.5% is good but could be optimized further to reach industry-leading standards of 95-98%.</li>
        <li>Average days on order of 6.3 suggests reasonable lead times, but analyzing specific vendor performance could identify opportunities for reduction.</li>
        <li>The data shows potential opportunities for better category management and vendor consolidation.</li>
      </ul>
    `;
    
    sections.inventoryRecommendations = `
      <p>Consider these inventory optimization strategies:</p>
      <ul>
        <li>Implement ABC analysis to prioritize inventory management efforts</li>
        <li>Review reorder points for high-volume products to prevent stockouts</li>
        <li>Consider just-in-time inventory practices for fast-moving items</li>
        <li>Evaluate slow-moving inventory for potential markdowns or promotions</li>
      </ul>
    `;
  }
  
  return {
    ...sections,
    metrics,
    charts
  };
}

// Better section extraction that tries multiple heading formats
function extractSectionBetter(text, possibleHeadings) {
  for (const heading of possibleHeadings) {
    // Try various heading formats
    const patterns = [
      // Numbered heading with colon
      new RegExp(`\\d+\\.\\s*${heading}[:\\s]+(.*?)(?=\\d+\\.\\s+[A-Z]|$)`, 's'),
      // Heading with colon
      new RegExp(`${heading}[:\\s]+(.*?)(?=\\d+\\.\\s+[A-Z]|[A-Z][A-Z\\s]+:|$)`, 's'),
      // Just the heading as a standalone
      new RegExp(`${heading}\\s*\\n+(.*?)(?=\\n+[A-Z][A-Z\\s]+|$)`, 's'),
      // Heading with HTML tags
      new RegExp(`<h\\d>\\s*${heading}\\s*</h\\d>\\s*(.*?)(?=<h\\d>|$)`, 's')
    ];
    
    for (const regex of patterns) {
      const match = text.match(regex);
      if (match && match[1].trim()) {
        return match[1].trim();
      }
    }
  }
  
  // If no match found with specific patterns, try a more general approach
  for (const heading of possibleHeadings) {
    // Find the heading position
    const headingPos = text.indexOf(heading);
    if (headingPos >= 0) {
      // Get text after the heading
      const afterHeading = text.substring(headingPos + heading.length);
      // Find the next heading-like pattern
      const nextHeadingMatch = afterHeading.match(/\n+[A-Z][A-Z\s]+:|\n+\d+\.\s+[A-Z]/);
      if (nextHeadingMatch) {
        return afterHeading.substring(0, nextHeadingMatch.index).trim();
      } else {
        // Take all text after this heading
        return afterHeading.trim();
      }
    }
  }
  
  return '';
}

// Helper functions for metric extraction
function extractMetric(text, regex) {
  const match = text.match(regex);
  return match ? match[1] : null;
}

function extractTopItem(text, regex) {
  const match = text.match(regex);
  return match ? match[2] : "N/A";
}

// Modify the createCombinedPrompt function
function createCombinedPrompt(data) {
  // Count files by type
  const fileTypes = data.data.map(file => file.type);
  const typeCounts = fileTypes.reduce((acc, type) => {
    acc[type] = (acc[type] || 0) + 1;
    return acc;
  }, {});
  
  const typeDescription = Object.entries(typeCounts)
    .map(([type, count]) => {
      const typeName = 
        type === 'purchase_orders' ? 'purchase order' : 
        type === 'sales_data' ? 'sales' : 
        type === 'inventory' ? 'inventory' : type;
      return `${count} ${typeName} ${count === 1 ? 'file' : 'files'}`;
    })
    .join(', ');
  
  // Add data quality information
  const dataQuality = data.stats.fileStats.map(file => 
    `${file.fileName} (${file.type}): ${file.hasValidData ? 'Contains valid data' : 'May only contain headers or empty data'}`
  ).join('\n');
  
  // Include more detailed samples
  const detailedSamples = data.stats.samplesByType ? 
    Object.entries(data.stats.samplesByType).map(([type, samples]) => 
      `\n--- ${type.toUpperCase()} DATA SAMPLES (${samples.length} rows) ---\n` +
      JSON.stringify(samples, null, 2)
    ).join('\n\n') : '';
  
  return `
    I have multiple retail data files for a mobile store ${data.location ? `in ${data.location}` : ''}.
    
    The dataset includes ${typeDescription}.
    
    Data quality assessment:
    ${dataQuality}
    
    Here are detailed samples from each data type:
    ${detailedSamples}
    
    Total files: ${data.data.length}
    Total records across all files: ${data.data.reduce((sum, file) => sum + file.data.length, 0)}
    
    IMPORTANT: The purchase order data DOES contain actual order details, not just header rows. Please analyze the actual content of the data provided.
    
    Provide a comprehensive cross-analysis of this data from a supply chain perspective. Your analysis should include:
    
    1. KEY INSIGHTS:
       - Identify the top 3-5 most important patterns or issues across these datasets
       - Highlight specific products or categories that stand out (positively or negatively)
       - Note any critical supply chain risks or opportunities
    
    2. INVENTORY ANALYSIS:
       - Which specific products have the highest/lowest turnover rates?
       - Are there any products that appear overstocked or understocked?
       - How well is inventory aligned with sales velocity?
       - Identify any seasonal patterns in inventory levels
    
    3. INVENTORY RECOMMENDATIONS:
       - Provide 3-5 specific, actionable steps to optimize inventory levels
       - Suggest specific reorder points or safety stock adjustments for key products
       - Recommend inventory management policy changes with expected outcomes
    
    4. VENDOR ANALYSIS:
       - Evaluate specific vendor performance metrics (delivery time, fill rate, etc.)
       - Compare vendors on key performance indicators
       - Identify any vendor-related bottlenecks or risks
    
    5. VENDOR RECOMMENDATIONS:
       - Suggest specific changes to vendor relationships or terms
       - Recommend consolidation or diversification strategies if appropriate
       - Provide a framework for ongoing vendor performance management
    
    6. SALES TRENDS:
       - Identify the best and worst performing products/categories
       - Highlight any emerging trends or declining product lines
       - Note correlations between marketing activities and sales performance
    
    7. SALES FORECASTS & ORDERING STRATEGY:
       - Provide specific forecasts for key product categories
       - Recommend order timing and quantity adjustments
       - Suggest ways to better align purchasing with sales cycles
    
    Be specific and data-driven in your analysis. Mention actual product names, categories, and vendors from the data. Provide concrete numbers and percentages whenever possible. Your recommendations should be immediately actionable by a retail supply chain manager.
    
    IMPORTANT: Format your response with clear section headings (e.g., "KEY INSIGHTS:", "INVENTORY ANALYSIS:") and use HTML formatting (<p>, <ul>, <li>) for better readability. Make sure each section contains detailed textual analysis, not just data points.
  `;
} 