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

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

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

  -- Información del certificado
  certificate_number VARCHAR(50) NOT NULL UNIQUE, -- OQI-CERT-XXXX-YYYY

  -- Datos del certificado (snapshot)
  user_name VARCHAR(200) NOT NULL,
  course_title VARCHAR(200) NOT NULL,
  instructor_name VARCHAR(200),
  completion_date DATE NOT NULL,

  -- Metadata de logro
  final_score DECIMAL(5,2), -- Promedio de quizzes
  total_xp_earned INTEGER,

  -- URL del PDF generado
  certificate_url VARCHAR(500),

  -- Verificación
  verification_code VARCHAR(100) UNIQUE, -- Para verificar autenticidad
  is_verified BOOLEAN DEFAULT true,

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

  CONSTRAINT unique_user_course_cert UNIQUE(user_id, course_id)
);

-- Índices
CREATE INDEX idx_certificates_user ON education.certificates(user_id);
CREATE INDEX idx_certificates_course ON education.certificates(course_id);
CREATE INDEX idx_certificates_number ON education.certificates(certificate_number);
CREATE INDEX idx_certificates_verification ON education.certificates(verification_code);

-- Comentarios
COMMENT ON TABLE education.certificates IS 'Certificados emitidos al completar cursos';
COMMENT ON COLUMN education.certificates.certificate_number IS 'Número único formato: OQI-CERT-YYYY-NNNNN';
COMMENT ON COLUMN education.certificates.verification_code IS 'Código para verificar autenticidad del certificado';
COMMENT ON COLUMN education.certificates.final_score IS 'Promedio de todos los quizzes del curso';