-- =====================================================
-- VIEWS - Schema Education
-- =====================================================
-- Proyecto: OrbiQuant IA (Trading Platform)
-- Módulo: OQI-002 - Education
-- Especificación: ET-EDU-001-database.md
-- =====================================================

-- Vista: Cursos con estadísticas completas
CREATE OR REPLACE VIEW education.v_courses_with_stats AS
SELECT
  c.*,
  cat.name as category_name,
  cat.slug as category_slug,
  COUNT(DISTINCT m.id) as modules_count,
  COUNT(DISTINCT l.id) as lessons_count,
  SUM(l.video_duration_seconds) as total_duration_seconds,
  COUNT(DISTINCT e.id) as enrollments_count,
  COUNT(DISTINCT CASE WHEN e.status = 'completed' THEN e.id END) as completions_count
FROM education.courses c
LEFT JOIN education.categories cat ON c.category_id = cat.id
LEFT JOIN education.modules m ON c.id = m.course_id
LEFT JOIN education.lessons l ON m.id = l.module_id
LEFT JOIN education.enrollments e ON c.id = e.course_id
GROUP BY c.id, cat.name, cat.slug;

COMMENT ON VIEW education.v_courses_with_stats IS 'Cursos con estadísticas agregadas de módulos, lecciones y enrollments';

-- Vista: Progreso del usuario por curso
CREATE OR REPLACE VIEW education.v_user_course_progress AS
SELECT
  e.user_id,
  e.course_id,
  c.title as course_title,
  c.slug as course_slug,
  c.thumbnail_url,
  e.status as enrollment_status,
  e.progress_percentage,
  e.enrolled_at,
  e.completed_at,
  e.total_xp_earned,
  COUNT(DISTINCT p.id) as lessons_viewed,
  COUNT(DISTINCT CASE WHEN p.is_completed THEN p.id END) as lessons_completed
FROM education.enrollments e
JOIN education.courses c ON e.course_id = c.id
LEFT JOIN education.progress p ON e.id = p.enrollment_id
GROUP BY e.id, e.user_id, e.course_id, c.title, c.slug, c.thumbnail_url;

COMMENT ON VIEW education.v_user_course_progress IS 'Progreso detallado del usuario en cada curso enrollado';

-- Vista: Leaderboard de usuarios
CREATE OR REPLACE VIEW education.v_leaderboard_weekly AS
SELECT
  ugp.user_id,
  ugp.weekly_xp,
  ugp.current_level,
  ugp.current_streak_days,
  RANK() OVER (ORDER BY ugp.weekly_xp DESC) as rank
FROM education.user_gamification_profile ugp
WHERE ugp.weekly_xp > 0
ORDER BY ugp.weekly_xp DESC
LIMIT 100;

COMMENT ON VIEW education.v_leaderboard_weekly IS 'Top 100 usuarios por XP semanal';

CREATE OR REPLACE VIEW education.v_leaderboard_monthly AS
SELECT
  ugp.user_id,
  ugp.monthly_xp,
  ugp.current_level,
  ugp.current_streak_days,
  RANK() OVER (ORDER BY ugp.monthly_xp DESC) as rank
FROM education.user_gamification_profile ugp
WHERE ugp.monthly_xp > 0
ORDER BY ugp.monthly_xp DESC
LIMIT 100;

COMMENT ON VIEW education.v_leaderboard_monthly IS 'Top 100 usuarios por XP mensual';

CREATE OR REPLACE VIEW education.v_leaderboard_alltime AS
SELECT
  ugp.user_id,
  ugp.total_xp,
  ugp.current_level,
  ugp.total_courses_completed,
  RANK() OVER (ORDER BY ugp.total_xp DESC) as rank
FROM education.user_gamification_profile ugp
WHERE ugp.total_xp > 0
ORDER BY ugp.total_xp DESC
LIMIT 100;

COMMENT ON VIEW education.v_leaderboard_alltime IS 'Top 100 usuarios por XP total histórico';

-- Vista: Estadísticas del usuario
CREATE OR REPLACE VIEW education.v_user_statistics AS
SELECT
  ugp.user_id,
  ugp.total_xp,
  ugp.current_level,
  ugp.xp_to_next_level,
  ugp.current_streak_days,
  ugp.longest_streak_days,
  ugp.total_courses_completed,
  ugp.total_lessons_completed,
  ugp.total_quizzes_passed,
  ugp.total_certificates_earned,
  ugp.average_quiz_score,
  COUNT(DISTINCT e.id) as total_enrollments,
  COUNT(DISTINCT CASE WHEN e.status = 'active' THEN e.id END) as active_enrollments,
  COUNT(DISTINCT ua.id) as total_achievements
FROM education.user_gamification_profile ugp
LEFT JOIN education.enrollments e ON ugp.user_id = e.user_id
LEFT JOIN education.user_achievements ua ON ugp.user_id = ua.user_id
GROUP BY ugp.user_id, ugp.total_xp, ugp.current_level, ugp.xp_to_next_level,
         ugp.current_streak_days, ugp.longest_streak_days, ugp.total_courses_completed,
         ugp.total_lessons_completed, ugp.total_quizzes_passed, ugp.total_certificates_earned,
         ugp.average_quiz_score;

COMMENT ON VIEW education.v_user_statistics IS 'Estadísticas completas del usuario (gamificación + progreso)';

-- Vista: Cursos populares
CREATE OR REPLACE VIEW education.v_popular_courses AS
SELECT
  c.id,
  c.title,
  c.slug,
  c.thumbnail_url,
  c.difficulty_level,
  c.avg_rating,
  c.total_reviews,
  c.total_enrollments,
  COUNT(DISTINCT e.id) as recent_enrollments_30d,
  COUNT(DISTINCT CASE WHEN e.status = 'completed' THEN e.id END) as completions
FROM education.courses c
LEFT JOIN education.enrollments e ON c.id = e.course_id
  AND e.enrolled_at >= NOW() - INTERVAL '30 days'
WHERE c.status = 'published'
GROUP BY c.id
ORDER BY recent_enrollments_30d DESC, c.avg_rating DESC
LIMIT 50;

COMMENT ON VIEW education.v_popular_courses IS 'Top 50 cursos más populares (enrollments últimos 30 días)';