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

CREATE TABLE education.progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Relaciones
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  lesson_id UUID NOT NULL REFERENCES education.lessons(id) ON DELETE CASCADE,
  enrollment_id UUID NOT NULL REFERENCES education.enrollments(id) ON DELETE CASCADE,

  -- Estado
  is_completed BOOLEAN DEFAULT false,

  -- Progreso de video
  last_position_seconds INTEGER DEFAULT 0,
  total_watch_time_seconds INTEGER DEFAULT 0, -- Tiempo total visto
  watch_percentage DECIMAL(5,2) DEFAULT 0.00,

  -- Tracking
  first_viewed_at TIMESTAMPTZ,
  last_viewed_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,

  -- Metadata
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT unique_user_lesson UNIQUE(user_id, lesson_id),
  CONSTRAINT valid_watch_percentage CHECK (watch_percentage >= 0 AND watch_percentage <= 100),
  CONSTRAINT completion_requires_date CHECK (
    (NOT is_completed) OR (completed_at IS NOT NULL)
  )
);

-- Índices
CREATE INDEX idx_progress_user ON education.progress(user_id);
CREATE INDEX idx_progress_lesson ON education.progress(lesson_id);
CREATE INDEX idx_progress_enrollment ON education.progress(enrollment_id);
CREATE INDEX idx_progress_completed ON education.progress(is_completed) WHERE is_completed = true;
CREATE INDEX idx_progress_user_enrollment ON education.progress(user_id, enrollment_id);

-- Comentarios
COMMENT ON TABLE education.progress IS 'Progreso individual del usuario en cada lección';
COMMENT ON COLUMN education.progress.last_position_seconds IS 'Última posición del video en segundos';
COMMENT ON COLUMN education.progress.total_watch_time_seconds IS 'Tiempo total de visualización acumulado';
COMMENT ON COLUMN education.progress.watch_percentage IS 'Porcentaje de la lección completada';