Merge branch 'master' into fix/analytics-v2-queries

- Add authMiddleware to analytics-v2.ts to require authentication
- Add permanent rule #6 to CLAUDE.md: "ALL API ROUTES REQUIRE AUTHENTICATION"
- Add forbidden action #19: "Creating API routes without authMiddleware"
- Document authentication flow and trusted origins

🤖 Generated with [Claude Code](https://claude.com/claude-code)

Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>

.woodpecker/.ci.yml

@@ -90,10 +90,10 @@ steps:
       platforms: linux/amd64
       provenance: false
       build_args:
-        - APP_BUILD_VERSION=${CI_COMMIT_SHA:0:8}
+        APP_BUILD_VERSION: ${CI_COMMIT_SHA:0:8}
-        - APP_GIT_SHA=${CI_COMMIT_SHA}
+        APP_GIT_SHA: ${CI_COMMIT_SHA}
-        - APP_BUILD_TIME=${CI_PIPELINE_CREATED}
+        APP_BUILD_TIME: ${CI_PIPELINE_CREATED}
-        - CONTAINER_IMAGE_TAG=${CI_COMMIT_SHA:0:8}
+        CONTAINER_IMAGE_TAG: ${CI_COMMIT_SHA:0:8}
     depends_on: []
     when:
       branch: master

CLAUDE.md

@@ -119,7 +119,42 @@ npx tsx src/db/migrate.ts
 - Importing it at runtime causes startup crashes if env vars aren't perfect
 - `pool.ts` uses lazy initialization - only validates when first query is made
 
-### 6. LOCAL DEVELOPMENT BY DEFAULT
+### 6. ALL API ROUTES REQUIRE AUTHENTICATION — NO EXCEPTIONS
+
+**Every API router MUST apply `authMiddleware` at the router level.**
+
+```typescript
+import { authMiddleware } from '../auth/middleware';
+
+const router = Router();
+router.use(authMiddleware);  // REQUIRED - first line after router creation
+```
+
+**Authentication flow (see `src/auth/middleware.ts`):**
+1. Check Bearer token (JWT or API token) → grant access if valid
+2. Check trusted origins (cannaiq.co, findadispo.com, localhost, etc.) → grant access
+3. Check trusted IPs (127.0.0.1, ::1, internal pod IPs) → grant access
+4. **Return 401 Unauthorized** if none of the above
+
+**NEVER create API routes without auth middleware:**
+- No "public" endpoints that bypass authentication
+- No "read-only" exceptions
+- No "analytics-only" exceptions
+- If an endpoint exists under `/api/*`, it MUST be protected
+
+**When creating new route files:**
+1. Import `authMiddleware` from `../auth/middleware`
+2. Add `router.use(authMiddleware)` immediately after creating the router
+3. Document security requirements in file header comments
+
+**Trusted origins (defined in middleware):**
+- `https://cannaiq.co`
+- `https://findadispo.com`
+- `https://findagram.co`
+- `*.cannabrands.app` domains
+- `localhost:*` for development
+
+### 7. LOCAL DEVELOPMENT BY DEFAULT
 
 **Quick Start:**
 ```bash
@@ -452,6 +487,7 @@ const result = await pool.query(`
 16. **Running `lsof -ti:PORT | xargs kill`** or similar process-killing commands
 17. **Using hardcoded database names** in code or comments
 18. **Creating or connecting to a second database**
+19. **Creating API routes without authMiddleware** (all `/api/*` routes MUST be protected)
 
 ---
 

backend/docs/BRAND_INTELLIGENCE_API.md

@@ -0,0 +1,394 @@
+# Brand Intelligence API
+
+## Endpoint
+
+```
+GET /api/analytics/v2/brand/:name/intelligence
+```
+
+## Query Parameters
+
+| Param | Type | Default | Description |
+|-------|------|---------|-------------|
+| `window` | `7d\|30d\|90d` | `30d` | Time window for trend calculations |
+| `state` | string | - | Filter by state code (e.g., `AZ`) |
+| `category` | string | - | Filter by category (e.g., `Flower`) |
+
+## Response Payload Schema
+
+```typescript
+interface BrandIntelligenceResult {
+  brand_name: string;
+  window: '7d' | '30d' | '90d';
+  generated_at: string;  // ISO timestamp when data was computed
+
+  performance_snapshot: PerformanceSnapshot;
+  alerts: Alerts;
+  sku_performance: SkuPerformance[];
+  retail_footprint: RetailFootprint;
+  competitive_landscape: CompetitiveLandscape;
+  inventory_health: InventoryHealth;
+  promo_performance: PromoPerformance;
+}
+```
+
+---
+
+## Section 1: Performance Snapshot
+
+Summary cards with key brand metrics.
+
+```typescript
+interface PerformanceSnapshot {
+  active_skus: number;              // Total products in catalog
+  total_revenue_30d: number | null; // Estimated from qty × price
+  total_stores: number;             // Active retail partners
+  new_stores_30d: number;           // New distribution in window
+  market_share: number | null;      // % of category SKUs
+  avg_wholesale_price: number | null;
+  price_position: 'premium' | 'value' | 'competitive';
+}
+```
+
+**UI Label Mapping:**
+| Field | User-Facing Label | Helper Text |
+|-------|-------------------|-------------|
+| `active_skus` | Active Products | X total in catalog |
+| `total_revenue_30d` | Monthly Revenue | Estimated from sales |
+| `total_stores` | Retail Distribution | Active retail partners |
+| `new_stores_30d` | New Opportunities | X new in last 30 days |
+| `market_share` | Category Position | % of category |
+| `avg_wholesale_price` | Avg Wholesale | Per unit |
+| `price_position` | Pricing Tier | Premium/Value/Market Rate |
+
+---
+
+## Section 2: Alerts
+
+Issues requiring attention.
+
+```typescript
+interface Alerts {
+  lost_stores_30d_count: number;
+  lost_skus_30d_count: number;
+  competitor_takeover_count: number;
+  avg_oos_duration_days: number | null;
+  avg_reorder_lag_days: number | null;
+  items: AlertItem[];
+}
+
+interface AlertItem {
+  type: 'lost_store' | 'delisted_sku' | 'shelf_loss' | 'extended_oos';
+  severity: 'critical' | 'warning';
+  store_name?: string;
+  product_name?: string;
+  competitor_brand?: string;
+  days_since?: number;
+  state_code?: string;
+}
+```
+
+**UI Label Mapping:**
+| Field | User-Facing Label |
+|-------|-------------------|
+| `lost_stores_30d_count` | Accounts at Risk |
+| `lost_skus_30d_count` | Delisted SKUs |
+| `competitor_takeover_count` | Shelf Losses |
+| `avg_oos_duration_days` | Avg Stockout Length |
+| `avg_reorder_lag_days` | Avg Restock Time |
+| `severity: critical` | Urgent |
+| `severity: warning` | Watch |
+
+---
+
+## Section 3: SKU Performance (Product Velocity)
+
+How fast each SKU sells.
+
+```typescript
+interface SkuPerformance {
+  store_product_id: number;
+  product_name: string;
+  category: string | null;
+  daily_velocity: number;        // Units/day estimate
+  velocity_status: 'hot' | 'steady' | 'slow' | 'stale';
+  retail_price: number | null;
+  on_sale: boolean;
+  stores_carrying: number;
+  stock_status: 'in_stock' | 'low_stock' | 'out_of_stock';
+}
+```
+
+**UI Label Mapping:**
+| Field | User-Facing Label |
+|-------|-------------------|
+| `daily_velocity` | Daily Rate |
+| `velocity_status` | Momentum |
+| `velocity_status: hot` | Hot |
+| `velocity_status: steady` | Steady |
+| `velocity_status: slow` | Slow |
+| `velocity_status: stale` | Stale |
+| `retail_price` | Retail Price |
+| `on_sale` | Promo (badge) |
+
+**Velocity Thresholds:**
+- `hot`: >= 5 units/day
+- `steady`: >= 1 unit/day
+- `slow`: >= 0.1 units/day
+- `stale`: < 0.1 units/day
+
+---
+
+## Section 4: Retail Footprint
+
+Store placement and coverage.
+
+```typescript
+interface RetailFootprint {
+  total_stores: number;
+  in_stock_count: number;
+  out_of_stock_count: number;
+  penetration_by_region: RegionPenetration[];
+  whitespace_stores: WhitespaceStore[];
+}
+
+interface RegionPenetration {
+  state_code: string;
+  store_count: number;
+  percent_reached: number;    // % of state's dispensaries
+  in_stock: number;
+  out_of_stock: number;
+}
+
+interface WhitespaceStore {
+  store_id: number;
+  store_name: string;
+  state_code: string;
+  city: string | null;
+  category_fit: number;       // How many competing brands they carry
+  competitor_brands: string[];
+}
+```
+
+**UI Label Mapping:**
+| Field | User-Facing Label |
+|-------|-------------------|
+| `penetration_by_region` | Market Coverage by Region |
+| `percent_reached` | X% reached |
+| `in_stock` | X stocked |
+| `out_of_stock` | X out |
+| `whitespace_stores` | Expansion Opportunities |
+| `category_fit` | X fit |
+
+---
+
+## Section 5: Competitive Landscape
+
+Market positioning vs competitors.
+
+```typescript
+interface CompetitiveLandscape {
+  brand_price_position: 'premium' | 'value' | 'competitive';
+  market_share_trend: MarketSharePoint[];
+  competitors: Competitor[];
+  head_to_head_skus: HeadToHead[];
+}
+
+interface MarketSharePoint {
+  date: string;
+  share_percent: number;
+}
+
+interface Competitor {
+  brand_name: string;
+  store_overlap_percent: number;
+  price_position: 'premium' | 'value' | 'competitive';
+  avg_price: number | null;
+  sku_count: number;
+}
+
+interface HeadToHead {
+  product_name: string;
+  brand_price: number;
+  competitor_brand: string;
+  competitor_price: number;
+  price_diff_percent: number;
+}
+```
+
+**UI Label Mapping:**
+| Field | User-Facing Label |
+|-------|-------------------|
+| `price_position: premium` | Premium Tier |
+| `price_position: value` | Value Leader |
+| `price_position: competitive` | Market Rate |
+| `market_share_trend` | Share of Shelf Trend |
+| `head_to_head_skus` | Price Comparison |
+| `store_overlap_percent` | X% store overlap |
+
+---
+
+## Section 6: Inventory Health
+
+Stock projections and risk levels.
+
+```typescript
+interface InventoryHealth {
+  critical_count: number;      // <7 days stock
+  warning_count: number;       // 7-14 days stock
+  healthy_count: number;       // 14-90 days stock
+  overstocked_count: number;   // >90 days stock
+  skus: InventorySku[];
+  overstock_alert: OverstockItem[];
+}
+
+interface InventorySku {
+  store_product_id: number;
+  product_name: string;
+  store_name: string;
+  days_of_stock: number | null;
+  risk_level: 'critical' | 'elevated' | 'moderate' | 'healthy';
+  current_quantity: number | null;
+  daily_sell_rate: number | null;
+}
+
+interface OverstockItem {
+  product_name: string;
+  store_name: string;
+  excess_units: number;
+  days_of_stock: number;
+}
+```
+
+**UI Label Mapping:**
+| Field | User-Facing Label |
+|-------|-------------------|
+| `risk_level: critical` | Reorder Now |
+| `risk_level: elevated` | Low Stock |
+| `risk_level: moderate` | Monitor |
+| `risk_level: healthy` | Healthy |
+| `critical_count` | Urgent (<7 days) |
+| `warning_count` | Low (7-14 days) |
+| `overstocked_count` | Excess (>90 days) |
+| `days_of_stock` | X days remaining |
+| `overstock_alert` | Overstock Alert |
+| `excess_units` | X excess units |
+
+---
+
+## Section 7: Promotion Effectiveness
+
+How promotions impact sales.
+
+```typescript
+interface PromoPerformance {
+  avg_baseline_velocity: number | null;
+  avg_promo_velocity: number | null;
+  avg_velocity_lift: number | null;     // % increase during promo
+  avg_efficiency_score: number | null;  // ROI proxy
+  promotions: Promotion[];
+}
+
+interface Promotion {
+  product_name: string;
+  store_name: string;
+  status: 'active' | 'scheduled' | 'ended';
+  start_date: string;
+  end_date: string | null;
+  regular_price: number;
+  promo_price: number;
+  discount_percent: number;
+  baseline_velocity: number | null;
+  promo_velocity: number | null;
+  velocity_lift: number | null;
+  efficiency_score: number | null;
+}
+```
+
+**UI Label Mapping:**
+| Field | User-Facing Label |
+|-------|-------------------|
+| `avg_baseline_velocity` | Normal Rate |
+| `avg_promo_velocity` | During Promos |
+| `avg_velocity_lift` | Avg Sales Lift |
+| `avg_efficiency_score` | ROI Score |
+| `velocity_lift` | Sales Lift |
+| `efficiency_score` | ROI Score |
+| `status: active` | Live |
+| `status: scheduled` | Scheduled |
+| `status: ended` | Ended |
+
+---
+
+## Example Queries
+
+### Get full payload
+```javascript
+const response = await fetch('/api/analytics/v2/brand/Wyld/intelligence?window=30d');
+const data = await response.json();
+```
+
+### Extract summary cards (flattened)
+```javascript
+const { performance_snapshot: ps, alerts } = data;
+
+const summaryCards = {
+  activeProducts: ps.active_skus,
+  monthlyRevenue: ps.total_revenue_30d,
+  retailDistribution: ps.total_stores,
+  newOpportunities: ps.new_stores_30d,
+  categoryPosition: ps.market_share,
+  avgWholesale: ps.avg_wholesale_price,
+  pricingTier: ps.price_position,
+  accountsAtRisk: alerts.lost_stores_30d_count,
+  delistedSkus: alerts.lost_skus_30d_count,
+  shelfLosses: alerts.competitor_takeover_count,
+};
+```
+
+### Get top 10 fastest selling SKUs
+```javascript
+const topSkus = data.sku_performance
+  .filter(sku => sku.velocity_status === 'hot' || sku.velocity_status === 'steady')
+  .sort((a, b) => b.daily_velocity - a.daily_velocity)
+  .slice(0, 10);
+```
+
+### Get critical inventory alerts only
+```javascript
+const criticalInventory = data.inventory_health.skus
+  .filter(sku => sku.risk_level === 'critical');
+```
+
+### Get states with <50% penetration
+```javascript
+const underPenetrated = data.retail_footprint.penetration_by_region
+  .filter(region => region.percent_reached < 50)
+  .sort((a, b) => a.percent_reached - b.percent_reached);
+```
+
+### Get active promotions with positive lift
+```javascript
+const effectivePromos = data.promo_performance.promotions
+  .filter(p => p.status === 'active' && p.velocity_lift > 0)
+  .sort((a, b) => b.velocity_lift - a.velocity_lift);
+```
+
+### Build chart data for market share trend
+```javascript
+const chartData = data.competitive_landscape.market_share_trend.map(point => ({
+  x: new Date(point.date),
+  y: point.share_percent,
+}));
+```
+
+---
+
+## Notes for Frontend Implementation
+
+1. **All fields are snake_case** - transform to camelCase if needed
+2. **Null values are possible** - handle gracefully in UI
+3. **Arrays may be empty** - show appropriate empty states
+4. **Timestamps are ISO format** - parse with `new Date()`
+5. **Percentages are already computed** - no need to multiply by 100
+6. **The `window` parameter affects trend calculations** - 7d/30d/90d

backend/package.json

@@ -1,6 +1,6 @@
 {
   "name": "dutchie-menus-backend",
-  "version": "1.5.1",
+  "version": "1.6.0",
   "description": "Backend API for Dutchie Menus scraper and management",
   "main": "dist/index.js",
   "scripts": {

backend/public/downloads/cannaiq-menus-latest.zip

@@ -0,0 +1 @@
+cannaiq-menus-1.6.0.zip

backend/src/auth/middleware.ts

@@ -32,6 +32,7 @@ const TRUSTED_ORIGINS = [
 // Pattern-based trusted origins (wildcards)
 const TRUSTED_ORIGIN_PATTERNS = [
   /^https:\/\/.*\.cannabrands\.app$/,  // *.cannabrands.app
+  /^https:\/\/.*\.cannaiq\.co$/,       // *.cannaiq.co
 ];
 
 // Trusted IPs for internal pod-to-pod communication
@@ -152,7 +153,53 @@ export async function authenticateUser(email: string, password: string): Promise
 }
 
 export async function authMiddleware(req: AuthRequest, res: Response, next: NextFunction) {
-  // Allow trusted origins/IPs to bypass auth (internal services, same-origin)
+  const authHeader = req.headers.authorization;
+
+  // If a Bearer token is provided, always try to use it first (logged-in user)
+  if (authHeader && authHeader.startsWith('Bearer ')) {
+    const token = authHeader.substring(7);
+
+    // Try JWT first
+    const jwtUser = verifyToken(token);
+
+    if (jwtUser) {
+      req.user = jwtUser;
+      return next();
+    }
+
+    // If JWT fails, try API token
+    try {
+      const result = await pool.query(`
+        SELECT id, name, rate_limit, active, expires_at, allowed_endpoints
+        FROM api_tokens
+        WHERE token = $1
+      `, [token]);
+
+      if (result.rows.length > 0) {
+        const apiToken = result.rows[0];
+        if (!apiToken.active) {
+          return res.status(401).json({ error: 'API token is inactive' });
+        }
+        if (apiToken.expires_at && new Date(apiToken.expires_at) < new Date()) {
+          return res.status(401).json({ error: 'API token has expired' });
+        }
+        req.user = {
+          id: 0,
+          email: `api:${apiToken.name}`,
+          role: 'api_token'
+        };
+        req.apiToken = apiToken;
+        return next();
+      }
+    } catch (err) {
+      console.error('API token lookup error:', err);
+    }
+
+    // Token provided but invalid
+    return res.status(401).json({ error: 'Invalid token' });
+  }
+
+  // No token provided - check trusted origins for API access (WordPress, etc.)
   if (isTrustedRequest(req)) {
     req.user = {
       id: 0,
@@ -162,80 +209,10 @@ export async function authMiddleware(req: AuthRequest, res: Response, next: Next
     return next();
   }
 
-  const authHeader = req.headers.authorization;
+  return res.status(401).json({ error: 'No token provided' });
-
-  if (!authHeader || !authHeader.startsWith('Bearer ')) {
-    return res.status(401).json({ error: 'No token provided' });
-  }
-
-  const token = authHeader.substring(7);
-
-  // Try JWT first
-  const jwtUser = verifyToken(token);
-
-  if (jwtUser) {
-    req.user = jwtUser;
-    return next();
-  }
-
-  // If JWT fails, try API token
-  try {
-    const result = await pool.query(`
-      SELECT id, name, rate_limit, active, expires_at, allowed_endpoints
-      FROM api_tokens
-      WHERE token = $1
-    `, [token]);
-
-    if (result.rows.length === 0) {
-      return res.status(401).json({ error: 'Invalid token' });
-    }
-
-    const apiToken = result.rows[0];
-
-    // Check if token is active
-    if (!apiToken.active) {
-      return res.status(401).json({ error: 'Token is disabled' });
-    }
-
-    // Check if token is expired
-    if (apiToken.expires_at && new Date(apiToken.expires_at) < new Date()) {
-      return res.status(401).json({ error: 'Token has expired' });
-    }
-
-    // Check allowed endpoints
-    if (apiToken.allowed_endpoints && apiToken.allowed_endpoints.length > 0) {
-      const isAllowed = apiToken.allowed_endpoints.some((pattern: string) => {
-        // Simple wildcard matching
-        const regex = new RegExp('^' + pattern.replace('*', '.*') + '$');
-        return regex.test(req.path);
-      });
-
-      if (!isAllowed) {
-        return res.status(403).json({ error: 'Endpoint not allowed for this token' });
-      }
-    }
-
-    // Set API token on request for tracking
-    req.apiToken = {
-      id: apiToken.id,
-      name: apiToken.name,
-      rate_limit: apiToken.rate_limit
-    };
-
-    // Set a generic user for compatibility with existing code
-    req.user = {
-      id: apiToken.id,
-      email: `api-token-${apiToken.id}@system`,
-      role: 'api'
-    };
-
-    next();
-  } catch (error) {
-    console.error('Error verifying API token:', error);
-    return res.status(500).json({ error: 'Authentication failed' });
-  }
 }
 
+
 /**
  * Require specific role(s) to access endpoint.
  *

backend/src/middleware/trustedDomains.ts

@@ -5,8 +5,8 @@ import { Request, Response, NextFunction } from 'express';
  * These are our own frontends that should have unrestricted access.
  */
 const TRUSTED_DOMAINS = [
-  'cannaiq.co',
+  '*.cannaiq.co',
-  'www.cannaiq.co',
+  '*.cannabrands.app',
   'findagram.co',
   'www.findagram.co',
   'findadispo.com',
@@ -32,6 +32,24 @@ function extractDomain(header: string): string | null {
   }
 }
 
+/**
+ * Checks if a domain matches any trusted domain (supports *.domain.com wildcards)
+ */
+function isTrustedDomain(domain: string): boolean {
+  for (const trusted of TRUSTED_DOMAINS) {
+    if (trusted.startsWith('*.')) {
+      // Wildcard: *.example.com matches example.com and any subdomain
+      const baseDomain = trusted.slice(2);
+      if (domain === baseDomain || domain.endsWith('.' + baseDomain)) {
+        return true;
+      }
+    } else if (domain === trusted) {
+      return true;
+    }
+  }
+  return false;
+}
+
 /**
  * Checks if the request comes from a trusted domain
  */
@@ -42,7 +60,7 @@ function isRequestFromTrustedDomain(req: Request): boolean {
   // Check Origin header first (preferred for CORS requests)
   if (origin) {
     const domain = extractDomain(origin);
-    if (domain && TRUSTED_DOMAINS.includes(domain)) {
+    if (domain && isTrustedDomain(domain)) {
       return true;
     }
   }
@@ -50,7 +68,7 @@ function isRequestFromTrustedDomain(req: Request): boolean {
   // Fallback to Referer header
   if (referer) {
     const domain = extractDomain(referer);
-    if (domain && TRUSTED_DOMAINS.includes(domain)) {
+    if (domain && isTrustedDomain(domain)) {
       return true;
     }
   }

backend/src/routes/analytics-v2.ts

@@ -7,15 +7,23 @@
  * Routes are prefixed with /api/analytics/v2
  *
  * Phase 3: Analytics Engine + Rec/Med by State
+ *
+ * SECURITY: All routes require authentication via authMiddleware.
+ * Access is granted to:
+ * - Trusted origins (cannaiq.co, findadispo.com, etc.)
+ * - Trusted IPs (localhost, internal pods)
+ * - Valid JWT or API tokens
  */
 
 import { Router, Request, Response } from 'express';
 import { Pool } from 'pg';
+import { authMiddleware } from '../auth/middleware';
 import { PriceAnalyticsService } from '../services/analytics/PriceAnalyticsService';
 import { BrandPenetrationService } from '../services/analytics/BrandPenetrationService';
 import { CategoryAnalyticsService } from '../services/analytics/CategoryAnalyticsService';
 import { StoreAnalyticsService } from '../services/analytics/StoreAnalyticsService';
 import { StateAnalyticsService } from '../services/analytics/StateAnalyticsService';
+import { BrandIntelligenceService } from '../services/analytics/BrandIntelligenceService';
 import { TimeWindow, LegalType } from '../services/analytics/types';
 
 function parseTimeWindow(window?: string): TimeWindow {
@@ -35,12 +43,17 @@ function parseLegalType(legalType?: string): LegalType {
 export function createAnalyticsV2Router(pool: Pool): Router {
   const router = Router();
 
+  // SECURITY: Apply auth middleware to ALL routes
+  // This gate ensures only authenticated requests can access analytics data
+  router.use(authMiddleware);
+
   // Initialize services
   const priceService = new PriceAnalyticsService(pool);
   const brandService = new BrandPenetrationService(pool);
   const categoryService = new CategoryAnalyticsService(pool);
   const storeService = new StoreAnalyticsService(pool);
   const stateService = new StateAnalyticsService(pool);
+  const brandIntelligenceService = new BrandIntelligenceService(pool);
 
   // ============================================================
   // PRICE ANALYTICS
@@ -259,6 +272,48 @@ export function createAnalyticsV2Router(pool: Pool): Router {
     }
   });
 
+  /**
+   * GET /brand/:name/intelligence
+   * Get comprehensive B2B brand intelligence dashboard data
+   *
+   * Returns all brand metrics in a single unified response:
+   *   - Performance Snapshot (active SKUs, revenue, stores, market share)
+   *   - Alerts/Slippage (lost stores, delisted SKUs, competitor takeovers)
+   *   - Product Velocity (daily rates, velocity status)
+   *   - Retail Footprint (penetration, whitespace opportunities)
+   *   - Competitive Landscape (price position, market share trend)
+   *   - Inventory Health (days of stock, risk levels)
+   *   - Promotion Effectiveness (baseline vs promo velocity, ROI)
+   *
+   * Query params:
+   *   - window: 7d|30d|90d (default: 30d)
+   *   - state: state code filter (e.g., AZ)
+   *   - category: category filter (e.g., Flower)
+   */
+  router.get('/brand/:name/intelligence', async (req: Request, res: Response) => {
+    try {
+      const brandName = decodeURIComponent(req.params.name);
+      const window = parseTimeWindow(req.query.window as string);
+      const stateCode = req.query.state as string | undefined;
+      const category = req.query.category as string | undefined;
+
+      const result = await brandIntelligenceService.getBrandIntelligence(brandName, {
+        window,
+        stateCode,
+        category,
+      });
+
+      if (!result) {
+        return res.status(404).json({ error: 'Brand not found' });
+      }
+
+      res.json(result);
+    } catch (error) {
+      console.error('[AnalyticsV2] Brand intelligence error:', error);
+      res.status(500).json({ error: 'Failed to fetch brand intelligence' });
+    }
+  });
+
   // ============================================================
   // CATEGORY ANALYTICS
   // ============================================================

backend/src/routes/public-api.ts

@@ -130,6 +130,12 @@ const CONSUMER_TRUSTED_ORIGINS = [
   'http://localhost:3002',
 ];
 
+// Wildcard trusted origin patterns (*.domain.com)
+const CONSUMER_TRUSTED_PATTERNS = [
+  /^https:\/\/([a-z0-9-]+\.)?cannaiq\.co$/,
+  /^https:\/\/([a-z0-9-]+\.)?cannabrands\.app$/,
+];
+
 // Trusted IPs for local development (bypass API key auth)
 const TRUSTED_IPS = ['127.0.0.1', '::1', '::ffff:127.0.0.1'];
 
@@ -150,8 +156,17 @@ function isConsumerTrustedRequest(req: Request): boolean {
     return true;
   }
   const origin = req.headers.origin;
-  if (origin && CONSUMER_TRUSTED_ORIGINS.includes(origin)) {
+  if (origin) {
-    return true;
+    // Check exact matches
+    if (CONSUMER_TRUSTED_ORIGINS.includes(origin)) {
+      return true;
+    }
+    // Check wildcard patterns
+    for (const pattern of CONSUMER_TRUSTED_PATTERNS) {
+      if (pattern.test(origin)) {
+        return true;
+      }
+    }
   }
   const referer = req.headers.referer;
   if (referer) {
@@ -160,6 +175,18 @@ function isConsumerTrustedRequest(req: Request): boolean {
         return true;
       }
     }
+    // Check wildcard patterns against referer origin
+    try {
+      const refererUrl = new URL(referer);
+      const refererOrigin = refererUrl.origin;
+      for (const pattern of CONSUMER_TRUSTED_PATTERNS) {
+        if (pattern.test(refererOrigin)) {
+          return true;
+        }
+      }
+    } catch {
+      // Invalid referer URL, ignore
+    }
   }
   return false;
 }

backend/src/services/analytics/CategoryAnalyticsService.ts

@@ -43,14 +43,14 @@ export class CategoryAnalyticsService {
     // Get current category metrics
     const currentResult = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         COUNT(*) AS sku_count,
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
         AVG(sp.price_rec) AS avg_price
       FROM store_products sp
-      WHERE sp.category = $1
+      WHERE sp.category_raw = $1
         AND sp.is_in_stock = TRUE
-      GROUP BY sp.category
+      GROUP BY sp.category_raw
     `, [category]);
 
     if (currentResult.rows.length === 0) {
@@ -70,7 +70,7 @@ export class CategoryAnalyticsService {
         COUNT(DISTINCT sps.dispensary_id) AS dispensary_count,
         AVG(sps.price_rec) AS avg_price
       FROM store_product_snapshots sps
-      WHERE sps.category = $1
+      WHERE sps.category_raw = $1
         AND sps.captured_at >= $2
         AND sps.captured_at <= $3
         AND sps.is_in_stock = TRUE
@@ -111,8 +111,9 @@ export class CategoryAnalyticsService {
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
         AVG(sp.price_rec) AS avg_price
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
-      WHERE sp.category = $1
+      JOIN states s ON s.id = d.state_id
+      WHERE sp.category_raw = $1
         AND sp.is_in_stock = TRUE
       GROUP BY s.code, s.name, s.recreational_legal
       ORDER BY sku_count DESC
@@ -154,24 +155,25 @@ export class CategoryAnalyticsService {
 
     const result = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         COUNT(*) AS sku_count,
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
-        COUNT(DISTINCT sp.brand_name) AS brand_count,
+        COUNT(DISTINCT sp.brand_name_raw) AS brand_count,
         AVG(sp.price_rec) AS avg_price,
         COUNT(DISTINCT s.code) AS state_count
       FROM store_products sp
-      LEFT JOIN states s ON s.id = sp.state_id
+      LEFT JOIN dispensaries d ON d.id = sp.dispensary_id
-      WHERE sp.category IS NOT NULL
+      JOIN states s ON s.id = d.state_id
+      WHERE sp.category_raw IS NOT NULL
         AND sp.is_in_stock = TRUE
         ${stateFilter}
-      GROUP BY sp.category
+      GROUP BY sp.category_raw
       ORDER BY sku_count DESC
       LIMIT $1
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       sku_count: parseInt(row.sku_count),
       dispensary_count: parseInt(row.dispensary_count),
       brand_count: parseInt(row.brand_count),
@@ -188,14 +190,14 @@ export class CategoryAnalyticsService {
     let categoryFilter = '';
 
     if (category) {
-      categoryFilter = 'WHERE sp.category = $1';
+      categoryFilter = 'WHERE sp.category_raw = $1';
       params.push(category);
     }
 
     const result = await this.pool.query(`
       WITH category_stats AS (
         SELECT
-          sp.category,
+          sp.category_raw,
           CASE WHEN s.recreational_legal = TRUE THEN 'recreational' ELSE 'medical_only' END AS legal_type,
           COUNT(DISTINCT s.code) AS state_count,
           COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
@@ -203,13 +205,14 @@ export class CategoryAnalyticsService {
           AVG(sp.price_rec) AS avg_price,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec) AS median_price
         FROM store_products sp
-        JOIN states s ON s.id = sp.state_id
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
         ${categoryFilter}
-        ${category ? 'AND' : 'WHERE'} sp.category IS NOT NULL
+        ${category ? 'AND' : 'WHERE'} sp.category_raw IS NOT NULL
           AND sp.is_in_stock = TRUE
           AND sp.price_rec IS NOT NULL
           AND (s.recreational_legal = TRUE OR s.medical_legal = TRUE)
-        GROUP BY sp.category, CASE WHEN s.recreational_legal = TRUE THEN 'recreational' ELSE 'medical_only' END
+        GROUP BY sp.category_raw, CASE WHEN s.recreational_legal = TRUE THEN 'recreational' ELSE 'medical_only' END
       ),
       rec_stats AS (
         SELECT * FROM category_stats WHERE legal_type = 'recreational'
@@ -218,7 +221,7 @@ export class CategoryAnalyticsService {
         SELECT * FROM category_stats WHERE legal_type = 'medical_only'
       )
       SELECT
-        COALESCE(r.category, m.category) AS category,
+        COALESCE(r.category_raw, m.category_raw) AS category,
         r.state_count AS rec_state_count,
         r.dispensary_count AS rec_dispensary_count,
         r.sku_count AS rec_sku_count,
@@ -235,7 +238,7 @@ export class CategoryAnalyticsService {
           ELSE NULL
         END AS price_diff_percent
       FROM rec_stats r
-      FULL OUTER JOIN med_stats m ON r.category = m.category
+      FULL OUTER JOIN med_stats m ON r.category_raw = m.category_raw
       ORDER BY COALESCE(r.sku_count, 0) + COALESCE(m.sku_count, 0) DESC
     `, params);
 
@@ -282,7 +285,7 @@ export class CategoryAnalyticsService {
           COUNT(*) AS sku_count,
           COUNT(DISTINCT sps.dispensary_id) AS dispensary_count
         FROM store_product_snapshots sps
-        WHERE sps.category = $1
+        WHERE sps.category_raw = $1
           AND sps.captured_at >= $2
           AND sps.captured_at <= $3
           AND sps.is_in_stock = TRUE
@@ -335,31 +338,33 @@ export class CategoryAnalyticsService {
       WITH category_total AS (
         SELECT COUNT(*) AS total
         FROM store_products sp
-        LEFT JOIN states s ON s.id = sp.state_id
+        LEFT JOIN dispensaries d ON d.id = sp.dispensary_id
-        WHERE sp.category = $1
+      JOIN states s ON s.id = d.state_id
+        WHERE sp.category_raw = $1
           AND sp.is_in_stock = TRUE
-          AND sp.brand_name IS NOT NULL
+          AND sp.brand_name_raw IS NOT NULL
           ${stateFilter}
       )
       SELECT
-        sp.brand_name,
+        sp.brand_name_raw,
         COUNT(*) AS sku_count,
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
         AVG(sp.price_rec) AS avg_price,
         ROUND(COUNT(*)::NUMERIC * 100 / NULLIF((SELECT total FROM category_total), 0), 2) AS category_share_percent
       FROM store_products sp
-      LEFT JOIN states s ON s.id = sp.state_id
+      LEFT JOIN dispensaries d ON d.id = sp.dispensary_id
-      WHERE sp.category = $1
+      JOIN states s ON s.id = d.state_id
+      WHERE sp.category_raw = $1
         AND sp.is_in_stock = TRUE
-        AND sp.brand_name IS NOT NULL
+        AND sp.brand_name_raw IS NOT NULL
         ${stateFilter}
-      GROUP BY sp.brand_name
+      GROUP BY sp.brand_name_raw
       ORDER BY sku_count DESC
       LIMIT $2
     `, params);
 
     return result.rows.map((row: any) => ({
-      brand_name: row.brand_name,
+      brand_name: row.brand_name_raw,
       sku_count: parseInt(row.sku_count),
       dispensary_count: parseInt(row.dispensary_count),
       avg_price: row.avg_price ? parseFloat(row.avg_price) : null,
@@ -421,7 +426,7 @@ export class CategoryAnalyticsService {
     `, [start, end, limit]);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       start_sku_count: parseInt(row.start_sku_count),
       end_sku_count: parseInt(row.end_sku_count),
       growth: parseInt(row.growth),

backend/src/services/analytics/PriceAnalyticsService.ts

@@ -43,9 +43,9 @@ export class PriceAnalyticsService {
     const productResult = await this.pool.query(`
       SELECT
         sp.id,
-        sp.name,
+        sp.name_raw,
-        sp.brand_name,
+        sp.brand_name_raw,
-        sp.category,
+        sp.category_raw,
         sp.dispensary_id,
         sp.price_rec,
         sp.price_med,
@@ -53,7 +53,7 @@ export class PriceAnalyticsService {
         s.code AS state_code
       FROM store_products sp
       JOIN dispensaries d ON d.id = sp.dispensary_id
-      LEFT JOIN states s ON s.id = sp.state_id
+      JOIN states s ON s.id = d.state_id
       WHERE sp.id = $1
     `, [storeProductId]);
 
@@ -133,7 +133,7 @@ export class PriceAnalyticsService {
 
     const result = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         s.code AS state_code,
         s.name AS state_name,
         CASE
@@ -148,18 +148,18 @@ export class PriceAnalyticsService {
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count
       FROM store_products sp
       JOIN dispensaries d ON d.id = sp.dispensary_id
-      JOIN states s ON s.id = sp.state_id
+      JOIN states s ON s.id = d.state_id
-      WHERE sp.category = $1
+      WHERE sp.category_raw = $1
         AND sp.price_rec IS NOT NULL
         AND sp.is_in_stock = TRUE
         AND (s.recreational_legal = TRUE OR s.medical_legal = TRUE)
         ${stateFilter}
-      GROUP BY sp.category, s.code, s.name, s.recreational_legal
+      GROUP BY sp.category_raw, s.code, s.name, s.recreational_legal
       ORDER BY state_code
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       state_code: row.state_code,
       state_name: row.state_name,
       legal_type: row.legal_type,
@@ -189,7 +189,7 @@ export class PriceAnalyticsService {
 
     const result = await this.pool.query(`
       SELECT
-        sp.brand_name AS category,
+        sp.brand_name_raw AS category,
         s.code AS state_code,
         s.name AS state_name,
         CASE
@@ -204,18 +204,18 @@ export class PriceAnalyticsService {
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count
       FROM store_products sp
       JOIN dispensaries d ON d.id = sp.dispensary_id
-      JOIN states s ON s.id = sp.state_id
+      JOIN states s ON s.id = d.state_id
-      WHERE sp.brand_name = $1
+      WHERE sp.brand_name_raw = $1
         AND sp.price_rec IS NOT NULL
         AND sp.is_in_stock = TRUE
         AND (s.recreational_legal = TRUE OR s.medical_legal = TRUE)
         ${stateFilter}
-      GROUP BY sp.brand_name, s.code, s.name, s.recreational_legal
+      GROUP BY sp.brand_name_raw, s.code, s.name, s.recreational_legal
       ORDER BY state_code
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       state_code: row.state_code,
       state_name: row.state_name,
       legal_type: row.legal_type,
@@ -254,7 +254,7 @@ export class PriceAnalyticsService {
     }
 
     if (category) {
-      filters += ` AND sp.category = $${paramIdx}`;
+      filters += ` AND sp.category_raw = $${paramIdx}`;
       params.push(category);
       paramIdx++;
     }
@@ -288,15 +288,16 @@ export class PriceAnalyticsService {
       )
       SELECT
         v.store_product_id,
-        sp.name AS product_name,
+        sp.name_raw AS product_name,
-        sp.brand_name,
+        sp.brand_name_raw,
         v.change_count,
         v.avg_change_pct,
         v.max_change_pct,
         v.last_change_at
       FROM volatility v
       JOIN store_products sp ON sp.id = v.store_product_id
-      LEFT JOIN states s ON s.id = sp.state_id
+      LEFT JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       WHERE 1=1 ${filters}
       ORDER BY v.change_count DESC, v.avg_change_pct DESC
       LIMIT $3
@@ -305,7 +306,7 @@ export class PriceAnalyticsService {
     return result.rows.map((row: any) => ({
       store_product_id: row.store_product_id,
       product_name: row.product_name,
-      brand_name: row.brand_name,
+      brand_name: row.brand_name_raw,
       change_count: parseInt(row.change_count),
       avg_change_percent: row.avg_change_pct ? parseFloat(row.avg_change_pct) : 0,
       max_change_percent: row.max_change_pct ? parseFloat(row.max_change_pct) : 0,
@@ -327,13 +328,13 @@ export class PriceAnalyticsService {
     let categoryFilter = '';
 
     if (category) {
-      categoryFilter = 'WHERE sp.category = $1';
+      categoryFilter = 'WHERE sp.category_raw = $1';
       params.push(category);
     }
 
     const result = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         AVG(sp.price_rec) FILTER (WHERE s.recreational_legal = TRUE) AS rec_avg,
         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec)
           FILTER (WHERE s.recreational_legal = TRUE) AS rec_median,
@@ -343,17 +344,18 @@ export class PriceAnalyticsService {
         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec)
           FILTER (WHERE s.medical_legal = TRUE AND (s.recreational_legal = FALSE OR s.recreational_legal IS NULL)) AS med_median
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       ${categoryFilter}
       ${category ? 'AND' : 'WHERE'} sp.price_rec IS NOT NULL
         AND sp.is_in_stock = TRUE
-        AND sp.category IS NOT NULL
+        AND sp.category_raw IS NOT NULL
-      GROUP BY sp.category
+      GROUP BY sp.category_raw
-      ORDER BY sp.category
+      ORDER BY sp.category_raw
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       rec_avg: row.rec_avg ? parseFloat(row.rec_avg) : null,
       rec_median: row.rec_median ? parseFloat(row.rec_median) : null,
       med_avg: row.med_avg ? parseFloat(row.med_avg) : null,

backend/src/services/analytics/StateAnalyticsService.ts

@@ -108,14 +108,14 @@ export class StateAnalyticsService {
       SELECT
         COUNT(DISTINCT d.id) AS dispensary_count,
         COUNT(DISTINCT sp.id) AS product_count,
-        COUNT(DISTINCT sp.brand_name) FILTER (WHERE sp.brand_name IS NOT NULL) AS brand_count,
+        COUNT(DISTINCT sp.brand_name_raw) FILTER (WHERE sp.brand_name_raw IS NOT NULL) AS brand_count,
-        COUNT(DISTINCT sp.category) FILTER (WHERE sp.category IS NOT NULL) AS category_count,
+        COUNT(DISTINCT sp.category_raw) FILTER (WHERE sp.category_raw IS NOT NULL) AS category_count,
         COUNT(sps.id) AS snapshot_count,
         MAX(sps.captured_at) AS last_crawl_at
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       WHERE s.code = $1
     `, [stateCode]);
 
@@ -129,7 +129,8 @@ export class StateAnalyticsService {
         MIN(price_rec) AS min_price,
         MAX(price_rec) AS max_price
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       WHERE s.code = $1
         AND sp.price_rec IS NOT NULL
         AND sp.is_in_stock = TRUE
@@ -140,14 +141,15 @@ export class StateAnalyticsService {
     // Get top categories
     const topCategoriesResult = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         COUNT(*) AS count
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       WHERE s.code = $1
-        AND sp.category IS NOT NULL
+        AND sp.category_raw IS NOT NULL
         AND sp.is_in_stock = TRUE
-      GROUP BY sp.category
+      GROUP BY sp.category_raw
       ORDER BY count DESC
       LIMIT 10
     `, [stateCode]);
@@ -155,14 +157,15 @@ export class StateAnalyticsService {
     // Get top brands
     const topBrandsResult = await this.pool.query(`
       SELECT
-        sp.brand_name AS brand,
+        sp.brand_name_raw AS brand,
         COUNT(*) AS count
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       WHERE s.code = $1
-        AND sp.brand_name IS NOT NULL
+        AND sp.brand_name_raw IS NOT NULL
         AND sp.is_in_stock = TRUE
-      GROUP BY sp.brand_name
+      GROUP BY sp.brand_name_raw
       ORDER BY count DESC
       LIMIT 10
     `, [stateCode]);
@@ -191,7 +194,7 @@ export class StateAnalyticsService {
         max_price: pricing.max_price ? parseFloat(pricing.max_price) : null,
       },
       top_categories: topCategoriesResult.rows.map((row: any) => ({
-        category: row.category,
+        category: row.category_raw,
         count: parseInt(row.count),
       })),
       top_brands: topBrandsResult.rows.map((row: any) => ({
@@ -215,8 +218,8 @@ export class StateAnalyticsService {
         COUNT(sps.id) AS snapshot_count
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       WHERE s.recreational_legal = TRUE
       GROUP BY s.code, s.name
       ORDER BY dispensary_count DESC
@@ -232,8 +235,8 @@ export class StateAnalyticsService {
         COUNT(sps.id) AS snapshot_count
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       WHERE s.medical_legal = TRUE
         AND (s.recreational_legal = FALSE OR s.recreational_legal IS NULL)
       GROUP BY s.code, s.name
@@ -295,46 +298,48 @@ export class StateAnalyticsService {
     let groupBy = 'NULL';
 
     if (category) {
-      categoryFilter = 'AND sp.category = $1';
+      categoryFilter = 'AND sp.category_raw = $1';
       params.push(category);
-      groupBy = 'sp.category';
+      groupBy = 'sp.category_raw';
     } else {
-      groupBy = 'sp.category';
+      groupBy = 'sp.category_raw';
     }
 
     const result = await this.pool.query(`
       WITH rec_prices AS (
         SELECT
-          ${category ? 'sp.category' : 'sp.category'},
+          ${category ? 'sp.category_raw' : 'sp.category_raw'},
           COUNT(DISTINCT s.code) AS state_count,
           COUNT(*) AS product_count,
           AVG(sp.price_rec) AS avg_price,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec) AS median_price
         FROM store_products sp
-        JOIN states s ON s.id = sp.state_id
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
         WHERE s.recreational_legal = TRUE
           AND sp.price_rec IS NOT NULL
           AND sp.is_in_stock = TRUE
-          AND sp.category IS NOT NULL
+          AND sp.category_raw IS NOT NULL
           ${categoryFilter}
-        GROUP BY sp.category
+        GROUP BY sp.category_raw
       ),
       med_prices AS (
         SELECT
-          ${category ? 'sp.category' : 'sp.category'},
+          ${category ? 'sp.category_raw' : 'sp.category_raw'},
           COUNT(DISTINCT s.code) AS state_count,
           COUNT(*) AS product_count,
           AVG(sp.price_rec) AS avg_price,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec) AS median_price
         FROM store_products sp
-        JOIN states s ON s.id = sp.state_id
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
         WHERE s.medical_legal = TRUE
           AND (s.recreational_legal = FALSE OR s.recreational_legal IS NULL)
           AND sp.price_rec IS NOT NULL
           AND sp.is_in_stock = TRUE
-          AND sp.category IS NOT NULL
+          AND sp.category_raw IS NOT NULL
           ${categoryFilter}
-        GROUP BY sp.category
+        GROUP BY sp.category_raw
       )
       SELECT
         COALESCE(r.category, m.category) AS category,
@@ -357,7 +362,7 @@ export class StateAnalyticsService {
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       recreational: {
         state_count: parseInt(row.rec_state_count) || 0,
         product_count: parseInt(row.rec_product_count) || 0,
@@ -395,12 +400,12 @@ export class StateAnalyticsService {
         COALESCE(s.medical_legal, FALSE) AS medical_legal,
         COUNT(DISTINCT d.id) AS dispensary_count,
         COUNT(DISTINCT sp.id) AS product_count,
-        COUNT(DISTINCT sp.brand_name) FILTER (WHERE sp.brand_name IS NOT NULL) AS brand_count,
+        COUNT(DISTINCT sp.brand_name_raw) FILTER (WHERE sp.brand_name_raw IS NOT NULL) AS brand_count,
         MAX(sps.captured_at) AS last_crawl_at
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       GROUP BY s.code, s.name, s.recreational_legal, s.medical_legal
       ORDER BY dispensary_count DESC, s.name
     `);
@@ -451,8 +456,8 @@ export class StateAnalyticsService {
         END AS gap_reason
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       WHERE s.recreational_legal = TRUE OR s.medical_legal = TRUE
       GROUP BY s.code, s.name, s.recreational_legal, s.medical_legal
       HAVING COUNT(DISTINCT d.id) = 0
@@ -499,7 +504,8 @@ export class StateAnalyticsService {
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec) AS median_price,
           COUNT(*) AS product_count
         FROM states s
-        JOIN store_products sp ON sp.state_id = s.id
+        JOIN dispensaries d ON d.state_id = s.id
+        JOIN store_products sp ON sp.dispensary_id = d.id
         WHERE sp.price_rec IS NOT NULL
           AND sp.is_in_stock = TRUE
           AND (s.recreational_legal = TRUE OR s.medical_legal = TRUE)

backend/src/services/analytics/StoreAnalyticsService.ts

@@ -89,22 +89,22 @@ export class StoreAnalyticsService {
     // Get brands added/dropped
     const brandsResult = await this.pool.query(`
       WITH start_brands AS (
-        SELECT DISTINCT brand_name
+        SELECT DISTINCT brand_name_raw
         FROM store_product_snapshots
         WHERE dispensary_id = $1
-          AND captured_at >= $2 AND captured_at < $2 + INTERVAL '1 day'
+          AND captured_at >= $2::timestamp AND captured_at < $2::timestamp + INTERVAL '1 day'
-          AND brand_name IS NOT NULL
+          AND brand_name_raw IS NOT NULL
       ),
       end_brands AS (
-        SELECT DISTINCT brand_name
+        SELECT DISTINCT brand_name_raw
         FROM store_product_snapshots
         WHERE dispensary_id = $1
-          AND captured_at >= $3 - INTERVAL '1 day' AND captured_at <= $3
+          AND captured_at >= $3::timestamp - INTERVAL '1 day' AND captured_at <= $3::timestamp
-          AND brand_name IS NOT NULL
+          AND brand_name_raw IS NOT NULL
       )
       SELECT
-        ARRAY(SELECT brand_name FROM end_brands EXCEPT SELECT brand_name FROM start_brands) AS added,
+        ARRAY(SELECT brand_name_raw FROM end_brands EXCEPT SELECT brand_name_raw FROM start_brands) AS added,
-        ARRAY(SELECT brand_name FROM start_brands EXCEPT SELECT brand_name FROM end_brands) AS dropped
+        ARRAY(SELECT brand_name_raw FROM start_brands EXCEPT SELECT brand_name_raw FROM end_brands) AS dropped
     `, [dispensaryId, start, end]);
 
     const brands = brandsResult.rows[0] || { added: [], dropped: [] };
@@ -184,9 +184,9 @@ export class StoreAnalyticsService {
         -- Products added
         SELECT
           sp.id AS store_product_id,
-          sp.name AS product_name,
+          sp.name_raw AS product_name,
-          sp.brand_name,
+          sp.brand_name_raw,
-          sp.category,
+          sp.category_raw,
           'added' AS event_type,
           sp.first_seen_at AS event_date,
           NULL::TEXT AS old_value,
@@ -201,9 +201,9 @@ export class StoreAnalyticsService {
         -- Stock in/out from snapshots
         SELECT
           sps.store_product_id,
-          sp.name AS product_name,
+          sp.name_raw AS product_name,
-          sp.brand_name,
+          sp.brand_name_raw,
-          sp.category,
+          sp.category_raw,
           CASE
             WHEN sps.is_in_stock = TRUE AND LAG(sps.is_in_stock) OVER w = FALSE THEN 'stock_in'
             WHEN sps.is_in_stock = FALSE AND LAG(sps.is_in_stock) OVER w = TRUE THEN 'stock_out'
@@ -224,9 +224,9 @@ export class StoreAnalyticsService {
         -- Price changes from snapshots
         SELECT
           sps.store_product_id,
-          sp.name AS product_name,
+          sp.name_raw AS product_name,
-          sp.brand_name,
+          sp.brand_name_raw,
-          sp.category,
+          sp.category_raw,
           'price_change' AS event_type,
           sps.captured_at AS event_date,
           LAG(sps.price_rec::TEXT) OVER w AS old_value,
@@ -250,8 +250,8 @@ export class StoreAnalyticsService {
     return result.rows.map((row: any) => ({
       store_product_id: row.store_product_id,
       product_name: row.product_name,
-      brand_name: row.brand_name,
+      brand_name: row.brand_name_raw,
-      category: row.category,
+      category: row.category_raw,
       event_type: row.event_type,
       event_date: row.event_date ? row.event_date.toISOString() : null,
       old_value: row.old_value,
@@ -364,8 +364,8 @@ export class StoreAnalyticsService {
       changes: result.rows.map((row: any) => ({
         store_product_id: row.store_product_id,
         product_name: row.product_name,
-        brand_name: row.brand_name,
+        brand_name: row.brand_name_raw,
-        category: row.category,
+        category: row.category_raw,
         old_quantity: row.old_quantity,
         new_quantity: row.new_quantity,
         quantity_delta: row.qty_delta,
@@ -415,14 +415,14 @@ export class StoreAnalyticsService {
     // Get top brands
     const brandsResult = await this.pool.query(`
       SELECT
-        brand_name AS brand,
+        brand_name_raw AS brand,
         COUNT(*) AS count,
         ROUND(COUNT(*)::NUMERIC * 100 / NULLIF($2, 0), 2) AS percent
       FROM store_products
       WHERE dispensary_id = $1
-        AND brand_name IS NOT NULL
+        AND brand_name_raw IS NOT NULL
         AND is_in_stock = TRUE
-      GROUP BY brand_name
+      GROUP BY brand_name_raw
       ORDER BY count DESC
       LIMIT 20
     `, [dispensaryId, totalProducts]);
@@ -432,7 +432,7 @@ export class StoreAnalyticsService {
       in_stock_count: parseInt(totals.in_stock) || 0,
       out_of_stock_count: parseInt(totals.out_of_stock) || 0,
       categories: categoriesResult.rows.map((row: any) => ({
-        category: row.category,
+        category: row.category_raw,
         count: parseInt(row.count),
         percent: parseFloat(row.percent) || 0,
       })),
@@ -574,23 +574,24 @@ export class StoreAnalyticsService {
       ),
       market_prices AS (
         SELECT
-          sp.category,
+          sp.category_raw,
           AVG(sp.price_rec) AS market_avg
         FROM store_products sp
-        WHERE sp.state_id = $2
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+        WHERE d.state_id = $2
           AND sp.price_rec IS NOT NULL
           AND sp.is_in_stock = TRUE
-          AND sp.category IS NOT NULL
+          AND sp.category_raw IS NOT NULL
-        GROUP BY sp.category
+        GROUP BY sp.category_raw
       )
       SELECT
-        sp.category,
+        sp.category_raw,
         sp.store_avg AS store_avg_price,
         mp.market_avg AS market_avg_price,
         ROUND(((sp.store_avg - mp.market_avg) / NULLIF(mp.market_avg, 0) * 100)::NUMERIC, 2) AS price_vs_market_percent,
         sp.product_count
       FROM store_prices sp
-      LEFT JOIN market_prices mp ON mp.category = sp.category
+      LEFT JOIN market_prices mp ON mp.category = sp.category_raw
       ORDER BY sp.product_count DESC
     `, [dispensaryId, dispensary.state_id]);
 
@@ -602,9 +603,10 @@ export class StoreAnalyticsService {
         WHERE dispensary_id = $1 AND price_rec IS NOT NULL AND is_in_stock = TRUE
       ),
       market_avg AS (
-        SELECT AVG(price_rec) AS avg
+        SELECT AVG(sp.price_rec) AS avg
-        FROM store_products
+        FROM store_products sp
-        WHERE state_id = $2 AND price_rec IS NOT NULL AND is_in_stock = TRUE
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+        WHERE d.state_id = $2 AND sp.price_rec IS NOT NULL AND sp.is_in_stock = TRUE
       )
       SELECT
         ROUND(((sa.avg - ma.avg) / NULLIF(ma.avg, 0) * 100)::NUMERIC, 2) AS price_vs_market
@@ -615,7 +617,7 @@ export class StoreAnalyticsService {
       dispensary_id: dispensaryId,
       dispensary_name: dispensary.name,
       categories: result.rows.map((row: any) => ({
-        category: row.category,
+        category: row.category_raw,
         store_avg_price: parseFloat(row.store_avg_price),
         market_avg_price: row.market_avg_price ? parseFloat(row.market_avg_price) : 0,
         price_vs_market_percent: row.price_vs_market_percent ? parseFloat(row.price_vs_market_percent) : 0,

backend/src/services/analytics/index.ts

@@ -11,3 +11,4 @@ export { BrandPenetrationService } from './BrandPenetrationService';
 export { CategoryAnalyticsService } from './CategoryAnalyticsService';
 export { StoreAnalyticsService } from './StoreAnalyticsService';
 export { StateAnalyticsService } from './StateAnalyticsService';
+export { BrandIntelligenceService } from './BrandIntelligenceService';

wordpress-plugin/cannaiq-menus.php

@@ -46,14 +46,17 @@ class CannaIQ_Menus_Plugin {
         // Initialize plugin
         load_plugin_textdomain('cannaiq-menus', false, dirname(plugin_basename(__FILE__)) . '/languages');
 
-        // Register shortcodes
+        // Register shortcodes - primary CannaIQ shortcodes
         add_shortcode('cannaiq_products', [$this, 'products_shortcode']);
         add_shortcode('cannaiq_product', [$this, 'single_product_shortcode']);
-        // Legacy shortcode support (backward compatibility)
+
-        add_shortcode('crawlsy_products', [$this, 'products_shortcode']);
+        // DEPRECATED: Legacy shortcode aliases for backward compatibility only
-        add_shortcode('crawlsy_product', [$this, 'single_product_shortcode']);
+        // These allow sites that used the old plugin names to continue working
-        add_shortcode('dutchie_products', [$this, 'products_shortcode']);
+        // New implementations should use [cannaiq_products] and [cannaiq_product]
-        add_shortcode('dutchie_product', [$this, 'single_product_shortcode']);
+        add_shortcode('crawlsy_products', [$this, 'products_shortcode']);   // deprecated
+        add_shortcode('crawlsy_product', [$this, 'single_product_shortcode']); // deprecated
+        add_shortcode('dutchie_products', [$this, 'products_shortcode']);   // deprecated
+        add_shortcode('dutchie_product', [$this, 'single_product_shortcode']); // deprecated
     }
 
     /**
@@ -114,7 +117,9 @@ class CannaIQ_Menus_Plugin {
     public function register_settings() {
         register_setting('cannaiq_menus_settings', 'cannaiq_api_token');
 
-        // Migrate old settings if they exist
+        // MIGRATION: Auto-migrate API tokens from old plugin versions
+        // This runs once - if user had crawlsy or dutchie plugin, their token is preserved
+        // Can be removed in a future major version once all users have migrated
         $old_crawlsy_token = get_option('crawlsy_api_token');
         $old_dutchie_token = get_option('dutchie_api_token');