-- =====================================================
-- TABLE: education.user_activity_log
-- =====================================================
-- Proyecto: OrbiQuant IA (Trading Platform)
-- Módulo: OQI-002 - Education
-- Especificación: Tabla adicional para tracking de actividad
-- =====================================================

CREATE TABLE education.user_activity_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

  -- Tipo de actividad
  activity_type VARCHAR(50) NOT NULL, -- lesson_view, quiz_complete, course_enroll, etc.

  -- Referencias opcionales
  course_id UUID REFERENCES education.courses(id) ON DELETE SET NULL,
  lesson_id UUID REFERENCES education.lessons(id) ON DELETE SET NULL,
  quiz_id UUID REFERENCES education.quizzes(id) ON DELETE SET NULL,

  -- Metadata
  metadata JSONB DEFAULT '{}',
  xp_earned INTEGER DEFAULT 0,

  -- Contexto
  ip_address INET,
  user_agent TEXT,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Índices
CREATE INDEX idx_activity_user ON education.user_activity_log(user_id);
CREATE INDEX idx_activity_type ON education.user_activity_log(activity_type);
CREATE INDEX idx_activity_created ON education.user_activity_log(created_at DESC);
CREATE INDEX idx_activity_user_date ON education.user_activity_log(user_id, created_at DESC);
CREATE INDEX idx_activity_course ON education.user_activity_log(course_id) WHERE course_id IS NOT NULL;

-- Comentarios
COMMENT ON TABLE education.user_activity_log IS 'Log de actividades del usuario en el módulo educativo';
COMMENT ON COLUMN education.user_activity_log.activity_type IS 'Tipos: lesson_view, lesson_complete, quiz_start, quiz_complete, course_enroll, etc.';
COMMENT ON COLUMN education.user_activity_log.metadata IS 'Información adicional específica del tipo de actividad';
COMMENT ON COLUMN education.user_activity_log.xp_earned IS 'XP ganado en esta actividad (si aplica)';