Piko Admin Dashboard & Analytics

// Complex SQL with CTEs and Window Functions
@Query("""
    WITH monthly_stats AS (
        SELECT 
            DATE_FORMAT(created_at, '%Y-%m') as month,
            COUNT(DISTINCT user_id) as active_users,
            COUNT(*) as total_posts,
            LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m')) as prev_month_users
        FROM activities
        WHERE created_at >= :startDate
        GROUP BY month
    )
    SELECT 
        month,
        active_users,
        total_posts,
        ROUND(((active_users - prev_month_users) / prev_month_users) * 100, 2) as growth_rate
    FROM monthly_stats
    ORDER BY month DESC
    """)
List getMonthlyGrowth(@Param("startDate") LocalDateTime startDate);

// Spring Security Configuration
@Configuration
@EnableWebSecurity
public class SecurityConfig {
    
    @Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
        http
            .csrf(AbstractHttpConfigurer::disable)
            .sessionManagement(session -> 
                session.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
            .authorizeHttpRequests(auth -> auth
                .requestMatchers("/api/auth/**").permitAll()
                .requestMatchers("/api/admin/**").hasAnyRole("ADMIN", "SUPER_ADMIN")
                .requestMatchers("/api/users/**").hasRole("SUPER_ADMIN")
                .anyRequest().authenticated()
            )
            .addFilterBefore(jwtAuthenticationFilter, 
                UsernamePasswordAuthenticationFilter.class);
        
        return http.build();
    }
}

// User Management Service with Pagination
@Service
public class UserService {
    
    public Page getUsers(
            String search,
            Role role,
            UserStatus status,
            Pageable pageable) {
        
        Specification spec = Specification
            .where(UserSpecification.hasSearchTerm(search))
            .and(UserSpecification.hasRole(role))
            .and(UserSpecification.hasStatus(status));
        
        return userRepository.findAll(spec, pageable)
            .map(this::mapToUserResponse);
    }
    
    @Transactional
    public UserResponse updateUserRole(Long userId, Role newRole) {
        User user = findUserById(userId);
        
        // Audit log
        auditService.log(
            AuditAction.UPDATE_USER_ROLE,
            userId,
            getCurrentAdmin(),
            Map.of("oldRole", user.getRole(), "newRole", newRole)
        );
        
        user.setRole(newRole);
        return mapToUserResponse(userRepository.save(user));
    }
}