database.sql

-- Base de datos para UTP Class AI
CREATE DATABASE IF NOT EXISTS utp_class_db;
USE utp_class_db;

-- Tabla de usuarios
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    role ENUM('student', 'teacher', 'admin') DEFAULT 'student',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de cursos
CREATE TABLE IF NOT EXISTS courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    teacher_id INT,
    is_live BOOLEAN DEFAULT FALSE,
    total_weeks INT DEFAULT 18,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Tabla de inscripciones
CREATE TABLE IF NOT EXISTS enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    progress INT DEFAULT 0,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    UNIQUE KEY unique_enrollment (user_id, course_id)
);

-- Tabla de semanas del curso
CREATE TABLE IF NOT EXISTS course_weeks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    week_number INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    UNIQUE KEY unique_week (course_id, week_number)
);

-- Tabla de materiales
CREATE TABLE IF NOT EXISTS materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    week_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    type ENUM('video', 'pdf', 'ppt', 'word', 'excel', 'link', 'other') NOT NULL,
    file_path VARCHAR(500),
    file_url VARCHAR(500),
    duration_minutes INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (week_id) REFERENCES course_weeks(id) ON DELETE CASCADE
);

-- Tabla de foros
CREATE TABLE IF NOT EXISTS forums (
    id INT AUTO_INCREMENT PRIMARY KEY,
    week_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (week_id) REFERENCES course_weeks(id) ON DELETE CASCADE
);

-- Tabla de posts en foros
CREATE TABLE IF NOT EXISTS forum_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    forum_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    parent_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (forum_id) REFERENCES forums(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES forum_posts(id) ON DELETE CASCADE
);

-- Tabla de tareas
CREATE TABLE IF NOT EXISTS assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    week_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    due_date DATETIME,
    max_score DECIMAL(5,2) DEFAULT 20.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (week_id) REFERENCES course_weeks(id) ON DELETE CASCADE
);

-- Tabla de entregas de tareas
CREATE TABLE IF NOT EXISTS submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assignment_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT,
    file_path VARCHAR(500),
    score DECIMAL(5,2),
    feedback TEXT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    graded_at TIMESTAMP NULL,
    FOREIGN KEY (assignment_id) REFERENCES assignments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_submission (assignment_id, user_id)
);

-- Tabla de publicaciones generales
CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    category VARCHAR(100),
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Tabla de conversaciones con IA
CREATE TABLE IF NOT EXISTS ai_conversations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    user_message TEXT NOT NULL,
    ai_response TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Tabla de resúmenes generados por IA
CREATE TABLE IF NOT EXISTS ai_summaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    material_id INT NOT NULL,
    summary TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE
);

-- Tabla de recomendaciones de IA
CREATE TABLE IF NOT EXISTS ai_recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    recommendation_type VARCHAR(50),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

-- Insertar datos de ejemplo
INSERT INTO users (username, password, full_name, email, role) VALUES
('CH71054448', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Estudiante Demo', 'estudiante@demo.com', 'student'),
('PROF001', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Profesor Demo', 'profesor@demo.com', 'teacher');

INSERT INTO courses (name, code, description, teacher_id, is_live, total_weeks) VALUES
('Calidad de Software - Sección 33330', 'CS-33330', 'Curso de calidad de software enfocado en metodologías y herramientas modernas', 2, TRUE, 18),
('Desarrollo Web Avanzado', 'DWA-001', 'Curso avanzado de desarrollo web con tecnologías modernas', 2, FALSE, 16),
('Inteligencia Artificial', 'IA-001', 'Introducción a la inteligencia artificial y machine learning', 2, TRUE, 18);

INSERT INTO enrollments (user_id, course_id, progress) VALUES
(1, 1, 35),
(1, 2, 60),
(1, 3, 15);

INSERT INTO course_weeks (course_id, week_number, title, description) VALUES
(1, 1, 'Semana 01', 'Introducción a la calidad de software'),
(1, 2, 'Semana 02', 'Metodologías de calidad'),
(1, 3, 'Semana 03', 'Pruebas de software');

INSERT INTO materials (week_id, title, description, type, file_url) VALUES
(1, 'Introducción al curso', 'Video de bienvenida y presentación del curso', 'video', 'https://example.com/video1.mp4'),
(1, 'Syllabus del curso', 'Documento con el plan de estudios completo', 'pdf', 'https://example.com/syllabus.pdf'),
(1, 'Presentación Semana 1', 'Slides de la primera clase', 'ppt', 'https://example.com/week1.pptx');

INSERT INTO forums (week_id, title, description) VALUES
(1, 'Foro de dudas - Semana 1', 'Espacio para consultas sobre los temas de la primera semana'),
(2, 'Foro de dudas - Semana 2', 'Espacio para consultas sobre los temas de la segunda semana');

INSERT INTO posts (user_id, category, title, content) VALUES
(2, 'Empleabilidad', 'Convocatoria FORTEL Lima', '¡Atención estudiantes y egresados! FORTEL busca a sus colaboradores part time y full time. Postula hasta el 8 de octubre.'),
(2, 'Empleabilidad', 'Taller de Empleabilidad', 'Desde el área de Empleabilidad, queremos invitarte a ser parte de nuestro taller: "Tu energía es tu ventaja: Motívate para alcanzar tus metas profesionales"'),
(2, 'Anuncios', 'Bienvenida al ciclo', '¡Bienvenidos al nuevo ciclo académico! Les deseamos mucho éxito en sus estudios.');