# Inventory Velocity & MACD Momentum Specification

## Overview

This specification details the implementation of inventory velocity tracking with MACD-style momentum indicators for category trend analysis.

---

## Part 1: Days-to-Sale Velocity Analysis

### Data Source

```sql
-- Primary data from kiosk_sales.sales
SELECT
    typeNum,
    buyDate,      -- When item was purchased from seller
    salesDate,    -- When item was sold to customer
    price,
    cost,
    DATEDIFF(salesDate, buyDate) as days_to_sale,
    (price - cost) as margin
FROM kiosk_sales.sales
WHERE salesDate IS NOT NULL
```

### Velocity Buckets

| Bucket | Days Range | Business Meaning |
|--------|-----------|------------------|
| Lightning | 0-7 | Hot items, potentially underpriced |
| Fast | 8-14 | Optimal pricing and demand |
| Good | 15-30 | Healthy turnover |
| Average | 31-60 | Normal range |
| Slow | 61-90 | Consider markdown |
| Stale | 91-180 | Needs attention |
| Dead | 180+ | Clearance candidates |

### Observed Margin Erosion Pattern

```
Days to Sale → Margin Impact
0-7 days:    $9.41 avg margin (baseline)
8-14 days:   $9.23 (-2% from baseline)
15-30 days:  $9.22 (-2%)
31-60 days:  $9.05 (-4%)
61-90 days:  $8.88 (-6%)
91-180 days: $8.28 (-12%)
180+ days:   $4.23 (-55%!)
```

### Daily Aggregation Query

```sql
-- Run daily via TaskEngine to populate analytics_store_velocity
INSERT INTO analytics_store_velocity
    (typeNum, date, items_sold, avg_days_to_sale, avg_margin,
     days_0_7, days_8_14, days_15_30, days_31_60,
     days_61_90, days_91_180, days_180_plus)
SELECT
    typeNum,
    salesDate as date,
    COUNT(*) as items_sold,
    ROUND(AVG(DATEDIFF(salesDate, buyDate)), 2) as avg_days_to_sale,
    ROUND(AVG(price - cost), 2) as avg_margin,
    SUM(CASE WHEN DATEDIFF(salesDate, buyDate) <= 7 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEDIFF(salesDate, buyDate) BETWEEN 8 AND 14 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEDIFF(salesDate, buyDate) BETWEEN 15 AND 30 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEDIFF(salesDate, buyDate) BETWEEN 31 AND 60 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEDIFF(salesDate, buyDate) BETWEEN 61 AND 90 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEDIFF(salesDate, buyDate) BETWEEN 91 AND 180 THEN 1 ELSE 0 END),
    SUM(CASE WHEN DATEDIFF(salesDate, buyDate) > 180 THEN 1 ELSE 0 END)
FROM kiosk_sales.sales
WHERE salesDate = CURDATE() - INTERVAL 1 DAY
GROUP BY typeNum, salesDate
ON DUPLICATE KEY UPDATE
    items_sold = VALUES(items_sold),
    avg_days_to_sale = VALUES(avg_days_to_sale),
    avg_margin = VALUES(avg_margin),
    days_0_7 = VALUES(days_0_7),
    days_8_14 = VALUES(days_8_14),
    days_15_30 = VALUES(days_15_30),
    days_31_60 = VALUES(days_31_60),
    days_61_90 = VALUES(days_61_90),
    days_91_180 = VALUES(days_91_180),
    days_180_plus = VALUES(days_180_plus);
```

---

## Part 2: MACD Momentum Indicators

### MACD Theory (Adapted for Retail)

MACD (Moving Average Convergence Divergence) is a trend-following momentum indicator that shows the relationship between two moving averages of sales data.

**Standard MACD Components**:
1. **MACD Line** = Fast EMA - Slow EMA
2. **Signal Line** = EMA of MACD Line
3. **Histogram** = MACD Line - Signal Line

### Retail Adaptation

Instead of stock prices, we track **daily sales velocity** (items sold per day) for each category.

**Default Parameters**:
- Fast EMA Period: 12 days
- Slow EMA Period: 26 days
- Signal Period: 9 days

**Customizable Ranges**:
- Fast: 5-15 days
- Slow: 20-35 days
- Signal: 5-12 days

### EMA Calculation

Exponential Moving Average gives more weight to recent data:

```
EMA_today = (Value_today × k) + (EMA_yesterday × (1 - k))
where k = 2 / (period + 1)

For 12-day EMA: k = 2/13 = 0.1538
For 26-day EMA: k = 2/27 = 0.0741
For 9-day Signal: k = 2/10 = 0.2000
```

### PHP Implementation

```php
<?php

namespace BuyerKiosk\Analytics\Services;

class MACDCalculator
{
    private float $fastPeriod;
    private float $slowPeriod;
    private float $signalPeriod;

    public function __construct(
        int $fastPeriod = 12,
        int $slowPeriod = 26,
        int $signalPeriod = 9
    ) {
        $this->fastPeriod = $fastPeriod;
        $this->slowPeriod = $slowPeriod;
        $this->signalPeriod = $signalPeriod;
    }

    /**
     * Calculate EMA multiplier (k factor)
     */
    private function getEMAMultiplier(int $period): float
    {
        return 2.0 / ($period + 1);
    }

    /**
     * Calculate EMA for a series of values
     *
     * @param array $values Ordered array of values (oldest first)
     * @param int $period EMA period
     * @return array EMA values for each point
     */
    public function calculateEMA(array $values, int $period): array
    {
        if (count($values) < $period) {
            return [];
        }

        $k = $this->getEMAMultiplier($period);
        $emaValues = [];

        // First EMA is SMA of first 'period' values
        $firstSMA = array_sum(array_slice($values, 0, $period)) / $period;
        $emaValues[$period - 1] = $firstSMA;

        // Calculate subsequent EMAs
        for ($i = $period; $i < count($values); $i++) {
            $ema = ($values[$i] * $k) + ($emaValues[$i - 1] * (1 - $k));
            $emaValues[$i] = $ema;
        }

        return $emaValues;
    }

    /**
     * Calculate full MACD data for a category
     *
     * @param array $dailyData Array of ['date' => 'Y-m-d', 'items_sold' => int]
     * @return array MACD analysis results
     */
    public function calculate(array $dailyData): array
    {
        // Extract values in date order
        usort($dailyData, fn($a, $b) => $a['date'] <=> $b['date']);
        $values = array_column($dailyData, 'items_sold');
        $dates = array_column($dailyData, 'date');

        if (count($values) < $this->slowPeriod + $this->signalPeriod) {
            return ['error' => 'Insufficient data points'];
        }

        // Calculate EMAs
        $fastEMA = $this->calculateEMA($values, $this->fastPeriod);
        $slowEMA = $this->calculateEMA($values, $this->slowPeriod);

        // Calculate MACD Line (Fast EMA - Slow EMA)
        $macdLine = [];
        $startIndex = $this->slowPeriod - 1;

        for ($i = $startIndex; $i < count($values); $i++) {
            if (isset($fastEMA[$i]) && isset($slowEMA[$i])) {
                $macdLine[$i] = $fastEMA[$i] - $slowEMA[$i];
            }
        }

        // Calculate Signal Line (9-day EMA of MACD)
        $macdValues = array_values($macdLine);
        $signalEMA = $this->calculateEMA($macdValues, $this->signalPeriod);

        // Build result set
        $results = [];
        $signalIndex = 0;

        foreach ($macdLine as $i => $macd) {
            $signal = $signalEMA[$signalIndex] ?? null;
            $histogram = $signal !== null ? $macd - $signal : null;

            $results[] = [
                'date' => $dates[$i],
                'value' => $values[$i],
                'fast_ema' => round($fastEMA[$i] ?? 0, 4),
                'slow_ema' => round($slowEMA[$i] ?? 0, 4),
                'macd_line' => round($macd, 4),
                'signal_line' => $signal !== null ? round($signal, 4) : null,
                'histogram' => $histogram !== null ? round($histogram, 4) : null,
                'trend' => $this->interpretTrend($macd, $signal, $histogram)
            ];

            $signalIndex++;
        }

        return $results;
    }

    /**
     * Interpret MACD signals
     */
    private function interpretTrend(?float $macd, ?float $signal, ?float $histogram): string
    {
        if ($signal === null || $histogram === null) {
            return 'insufficient_data';
        }

        // Strong bullish: MACD above signal and histogram positive/growing
        if ($macd > $signal && $histogram > 0) {
            return $histogram > 0.5 ? 'strong_bullish' : 'bullish';
        }

        // Strong bearish: MACD below signal and histogram negative
        if ($macd < $signal && $histogram < 0) {
            return $histogram < -0.5 ? 'strong_bearish' : 'bearish';
        }

        // Crossover detection would need previous values
        return 'neutral';
    }

    /**
     * Detect crossover events (buy/sell signals)
     */
    public function detectCrossovers(array $macdResults): array
    {
        $crossovers = [];

        for ($i = 1; $i < count($macdResults); $i++) {
            $prev = $macdResults[$i - 1];
            $curr = $macdResults[$i];

            if ($prev['signal_line'] === null || $curr['signal_line'] === null) {
                continue;
            }

            // Bullish crossover: MACD crosses above signal
            if ($prev['macd_line'] <= $prev['signal_line'] &&
                $curr['macd_line'] > $curr['signal_line']) {
                $crossovers[] = [
                    'date' => $curr['date'],
                    'type' => 'bullish_crossover',
                    'action' => 'BUY_MORE',
                    'strength' => abs($curr['histogram'])
                ];
            }

            // Bearish crossover: MACD crosses below signal
            if ($prev['macd_line'] >= $prev['signal_line'] &&
                $curr['macd_line'] < $curr['signal_line']) {
                $crossovers[] = [
                    'date' => $curr['date'],
                    'type' => 'bearish_crossover',
                    'action' => 'REDUCE_BUYING',
                    'strength' => abs($curr['histogram'])
                ];
            }
        }

        return $crossovers;
    }
}
```

### Signal Interpretation for Buyers

| Signal | MACD Condition | Action |
|--------|---------------|--------|
| Strong Buy | Bullish crossover + positive histogram growing | Increase buying in category |
| Buy | MACD above signal | Category trending up |
| Hold | MACD near signal, histogram near zero | Maintain current levels |
| Reduce | MACD below signal | Category cooling |
| Strong Reduce | Bearish crossover + negative histogram | Decrease buying in category |

### Daily Aggregation Job

```php
<?php

namespace BuyerKiosk\TaskEngine\Jobs;

class CalculateCategoryMACDJob extends AbstractJob
{
    public function execute(array $context): void
    {
        $calculator = new MACDCalculator(
            $context['fastPeriod'] ?? 12,
            $context['slowPeriod'] ?? 26,
            $context['signalPeriod'] ?? 9
        );

        // Get categories with sufficient data
        $categories = $this->getCategoriesWithData($context['typeNum']);

        foreach ($categories as $category) {
            // Get last 60 days of daily sales
            $dailyData = $this->getCategoryDailySales(
                $context['typeNum'],
                $category,
                60
            );

            $macdResults = $calculator->calculate($dailyData);

            if (isset($macdResults['error'])) {
                continue;
            }

            // Store latest MACD values
            $latest = end($macdResults);
            $this->storeMACDData($context['typeNum'], $category, $latest);

            // Detect and log crossovers
            $crossovers = $calculator->detectCrossovers($macdResults);
            foreach ($crossovers as $crossover) {
                $this->logCrossoverAlert($context['typeNum'], $category, $crossover);
            }
        }
    }
}
```

---

## Part 3: Additional Momentum Indicators (Future)

### RSI (Relative Strength Index)

Measures overbought/oversold conditions:
- RSI > 70: Category may be overbought (too much inventory)
- RSI < 30: Category may be oversold (opportunity)

### Bollinger Bands

Shows volatility:
- Price/velocity outside bands indicates unusual activity
- Band squeeze indicates low volatility (breakout coming)

### Volume-Weighted Analysis

Weight momentum by actual revenue, not just item counts.

---

## Part 4: UI Components

### Velocity Dashboard Card

```html
<div class="card velocity-card">
    <div class="card-header">
        <h5>Inventory Velocity</h5>
        <div class="date-range-selector">
            <!-- Date picker -->
        </div>
    </div>
    <div class="card-body">
        <!-- Stacked bar chart: items by velocity bucket -->
        <canvas id="velocityChart"></canvas>

        <!-- Summary stats -->
        <div class="velocity-stats">
            <div class="stat">
                <span class="label">Avg Days to Sale</span>
                <span class="value">{{ avgDaysToSale }}</span>
                <span class="trend {{ trend }}">{{ trendPct }}%</span>
            </div>
            <div class="stat">
                <span class="label">Fast Movers (0-14d)</span>
                <span class="value">{{ fastPct }}%</span>
            </div>
            <div class="stat">
                <span class="label">Stale (180+d)</span>
                <span class="value warning">{{ stalePct }}%</span>
            </div>
        </div>
    </div>
</div>
```

### MACD Chart Component

```html
<div class="card macd-card">
    <div class="card-header">
        <h5>Category Momentum</h5>
        <select id="categorySelect">
            <!-- Categories -->
        </select>
    </div>
    <div class="card-body">
        <!-- MACD line chart with signal -->
        <canvas id="macdChart"></canvas>

        <!-- Histogram below -->
        <canvas id="macdHistogram"></canvas>

        <!-- Signal interpretation -->
        <div class="macd-signal {{ signalClass }}">
            <i class="fas fa-{{ signalIcon }}"></i>
            <span>{{ signalText }}</span>
        </div>
    </div>
</div>
```

### Category Heat Map

```html
<div class="card momentum-heatmap">
    <div class="card-header">
        <h5>All Categories - Momentum Overview</h5>
    </div>
    <div class="card-body">
        <div class="heatmap-grid">
            {% for category in categories %}
            <div class="heatmap-cell {{ category.momentumClass }}"
                 data-category="{{ category.name }}"
                 title="{{ category.name }}: {{ category.trend }}">
                <span class="name">{{ category.shortName }}</span>
                <span class="indicator">
                    {% if category.trend == 'strong_bullish' %}
                        <i class="fas fa-arrow-up"></i><i class="fas fa-arrow-up"></i>
                    {% elseif category.trend == 'bullish' %}
                        <i class="fas fa-arrow-up"></i>
                    {% elseif category.trend == 'bearish' %}
                        <i class="fas fa-arrow-down"></i>
                    {% elseif category.trend == 'strong_bearish' %}
                        <i class="fas fa-arrow-down"></i><i class="fas fa-arrow-down"></i>
                    {% else %}
                        <i class="fas fa-minus"></i>
                    {% endif %}
                </span>
            </div>
            {% endfor %}
        </div>
    </div>
</div>
```

---

## Part 5: API Endpoints

### Velocity Endpoints

```
GET /api/analytics/:typeNum/velocity
    ?start_date=2024-01-01
    ?end_date=2024-12-31
    ?compare=yoy  // year-over-year comparison

GET /api/analytics/:typeNum/velocity/distribution
    ?period=30  // days

GET /api/analytics/:typeNum/velocity/trend
    ?metric=avg_days_to_sale
    ?granularity=weekly
```

### MACD Endpoints

```
GET /api/analytics/:typeNum/momentum/macd
    ?category=womens_tops
    ?fast=12
    ?slow=26
    ?signal=9

GET /api/analytics/:typeNum/momentum/overview
    // Returns all categories with current momentum status

GET /api/analytics/:typeNum/momentum/alerts
    // Returns recent crossover events
```

---

## Part 6: Aggregation Schedule

| Job | Frequency | Data Processed |
|-----|-----------|----------------|
| VelocityDailyAggregationJob | Daily 2am | Previous day's sales |
| CategoryMACDCalculationJob | Daily 3am | Last 60 days per category |
| MomentumAlertJob | Daily 4am | Check for crossovers |
| VelocityMonthlyRollupJob | 1st of month | Monthly summaries |

---

## Implementation Checklist

- [ ] Create database tables for analytics storage
- [ ] Implement MACDCalculator service class
- [ ] Create VelocityService for data retrieval
- [ ] Build TaskEngine aggregation jobs
- [ ] Design API endpoints
- [ ] Create frontend dashboard components
- [ ] Implement Chart.js visualizations
- [ ] Add export functionality
- [ ] Write unit tests for MACD calculations
- [ ] Create documentation and user guide