# TODO - TikTok Analytics Platform

## Code Review Findings (Feb 2025)

### High Priority
- [x] **Performance: Redundant Array Sorting** - Virality/Engagement cards sort `timeframeData` inline on every render, but data is already sorted. Use `useMemo` to calculate once.
- [ ] **Type Safety** - Using `any` types in metric calculations. Define `DailyMetric` interface.

### Medium Priority
- [x] **Data Gap Assumption** - Code assumes `sortedDesc[1]` is "yesterday" but could be 2+ days ago if gaps exist. Validate consecutive dates.
- [x] **Engagement Rate Semantics** - Removed confusing day-over-day arrow; now shows only total engagement rate.

### Low Priority
- [ ] **TikTok ID Mismatch** - Same songs have different IDs in Chartmetric vs TIKTOK_TRENDS_DAILY. Need name-based fallback search or ID mapping table.
- [ ] **Magic Colors** - Hardcoded hex colors should be constants
- [ ] **Accessibility** - Color-only indicators need ARIA labels for screen readers
- [ ] **Code Duplication** - Extract shared day-over-day logic into custom hook
- [ ] **Missing JSDoc** - Add documentation for complex metric calculations

## Completed ✅

### History Panel & Caching
- [x] Fix bugs in `TrendAnalysis.tsx` (missing soundId argument)
- [x] IndexedDB caching with HistoryPanel component
- [x] History panel layout fixes (flexShrink: 0)
- [x] Sound name display in trend tracking

## In Progress / TODO

### Snowflake Integration (Priority)
- [ ] Implement direct Snowflake browser connection with SSO OAuth
  - See detailed plan: [`docs/snowflake-integration-plan.md`](snowflake-integration-plan.md)
  - Uses built-in `SNOWFLAKE$LOCAL_APPLICATION` integration (no admin setup)
  - OAuth flow: browser redirect → SSO auth → token exchange → SQL API calls
  - Fetches real historical trend data from `DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK_STAT`
  - Replaces sampled data in trend charts with actual platform-wide daily creations

### Creator Analysis Enhancements
- [ ] Add "Top Music" or "Commonly Used Sounds" section
  - Extract most frequently used sounds from creator's videos
  - Display sound usage frequency/popularity
  - Link to trend analysis for those sounds
  - Show which sounds are trending vs. niche

### Instagram Integration for Trend Analysis
- [ ] Research Apify scrapers for Instagram Reels
  - Find actors that support sound/audio tracking
  - Verify data structure compatibility with TikTok data
  
- [ ] Implement Instagram data fetching
  - Add `fetchIGSoundDetails` in `ui/src/api/apify.ts`
  - Update `calculateTrendMetrics` to handle both platforms
  - Ensure proper error handling for IG API failures
  
- [ ] Update Trend Analysis UI
  - Add platform toggle or side-by-side comparison
  - Show IG vs TT metrics (creations, views, engagement)
  - Implement cross-platform velocity comparison
  - Visualize which platform is leading the trend

### Optional Enhancements
- [ ] Add dashboard summary page
  - Overview of recent analyses
  - Quick stats and trends
  
- [ ] Improve navigation
  - Better routing between Creator and Trend views
  - Breadcrumbs or context indicators
  
- [ ] Error handling
  - Add error boundaries for better UX
  - Improve error messages and recovery options

## Notes

### Development Guidelines
- Follow existing patterns in the codebase
- Use Material-UI components for consistency
- Maintain TypeScript strict typing
- Cache API results in IndexedDB where appropriate
- Keep UI responsive and handle loading states
- Test with real TikTok/IG data before committing

### Known Limitations
- Some sounds may not have `musicMeta.musicName` available
- Apify rate limits may affect concurrent requests
- IndexedDB cache expires after 15 minutes
- Daily creation trends are sampled data (see Research below)

## Research

### Historical Sound/Music Trend Data
**Status:** SOLUTION FOUND - Use Snowflake Chartmetric data

**Problem:** Apify returns a sample of ~50 videos per sound, so daily creation counts in the trend chart are based on sampled data, not actual platform-wide daily creations.

**Solution:** Query existing Snowflake Chartmetric data!

**Database:** `DELPHI_EXPLORATION.CHARTMETRIC`

**Tables:**
- `TIKTOK` - Sound/track metadata (24M rows)
  - `TIKTOK_ID` - TikTok sound ID (matches our app's sound IDs)
  - `TRACK`, `ARTIST`, `ISRC`
  - `POSTS_LATEST` - Current total posts

- `TIKTOK_STAT` - Historical daily stats (570M rows)
  - `TIKTOK` - FK to TIKTOK.ID
  - `TIMESTP` - Date
  - `POSTS` - Cumulative post count on that date

**Sample Query:**
```sql
SELECT
    t.TIKTOK_ID,
    t.TRACK,
    t.ARTIST,
    s.TIMESTP,
    s.POSTS,
    s.POSTS - LAG(s.POSTS) OVER (ORDER BY s.TIMESTP) as DAILY_NEW_POSTS
FROM DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK_STAT s
JOIN DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK t ON s.TIKTOK = t.ID
WHERE t.TIKTOK_ID = '7537176505709808415'
ORDER BY s.TIMESTP DESC
```

**Next Steps:**
- [ ] Add Snowflake connection to UI (via API or direct)
- [ ] Create endpoint to fetch historical sound data
- [ ] Replace sampled data chart with real Chartmetric data
- [ ] Consider hybrid approach: Apify for real-time, Snowflake for historical

**Alternative (if Snowflake not accessible from UI):**
- [Soundcharts API](https://developers.soundcharts.com/documentation/reference/tiktok/summary) - paid B2B service

**Other APIs checked (no historical data):**
- Apify `clockworks/tiktok-sound-scraper` - only total count
- Apify `alien_force/tiktok-trending-sounds-tracker` - trending only, can't query by ID
- TikAPI.io - only total `videoCount`, no daily breakdown