Implement Core Dashboard Metrics

Ticket ID: DASH-METRICS-001
Priority: High

**Feature Description**

1. Implement backend endpoint to serve core dashboard metrics for admin panel:

- Active Members count

- Active Projects count with trend percentage

- Upcoming Events count with trend percentage

- Blog Posts count with trend percentage

**Acceptance Criteria**
1. Database Updates

- Add last_active_at timestamp column to Users table

- Add last_updated timestamp column to Projects table

- Add status column (default: 'draft') to Blog table

- Ensure all tables have proper indexes for date-based queries

2. Redis Caching Implementation ✅

- Implement Redis cache with 5-minute TTL for metrics

- Cache key: metrics:dashboard:v1

- Cache invalidation on significant events:

- New user registration

- Project creation/update

- Event creation/update

- Blog publish/unpublish

- Graceful fallback to database if Redis fails

- Response time < 50ms for cache hits

3. Endpoint Implementation ✅

- Single Optimized Endpoint: GET /api/metrics/dashboard

Response Format:
```
json

{
"activeMembers": {
"count": 2540,
"trend": 0,
"description": "Users active in last 7 days"
},
"activeProjects": {
"count": 156,
"trend": -7,
"description": "Projects with recent activity"
},
"upcomingEvents": {
"count": 25,
"trend": 12,
"description": "Events starting in next 14 days"
},
"blogPosts": {
"count": 101,
"trend": 2,
"description": "Total published posts"
},
"lastUpdated": "2024-01-15T10:30:00Z"
}
```

4. Metric Definitions ✅

- Active Members: Distinct users with last_active_at within last 7 days

- Active Projects: Projects with last_updated within last 30 days

- Upcoming Events: Events where start_date is within next 14 days

- Blog Posts: Published posts only (status = 'published')

5. Trend Calculation ✅

- Calculate percentage change from previous period

- Current vs previous 7 days for Active Members

- Current vs previous 30 days for Active Projects

- Current vs previous 14 days for Upcoming Events

- Current vs previous month for Blog Posts

- Return integer percentages (e.g., -7, +12, +2)

6. Performance Requirements ✅

- Response time < 150ms (production)

- Implement Redis caching with 5-minute TTL

- Use optimized Sequelize queries with minimal joins

- Single database round-trip preferred

7. Testing ✅

- Unit tests for each metric calculation

- Integration test for endpoint response structure

8. Success Metrics

- Dashboard loads all metrics in < 200ms (cached)

- Counts match SQL verification queries

- Trend calculations are mathematically correct

- Cache properly invalidates on data changes

📝 Notes

- Use Sequelize scopes for reusable query logic

- Add database indexes on date columns for performance

- Consider cron job to pre-calculate metrics if queries become heavy

- Coordinate with frontend on error handling for missing data