-- C/C++ Learning System Database Setup
-- This file will create the database and all required tables

-- Users table (students and instructors)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    user_type ENUM('student', 'instructor') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_user_type (user_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Subjects table (programming courses)
CREATE TABLE IF NOT EXISTS subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL,
    subject_code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    instructor_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (instructor_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_instructor (instructor_id),
    INDEX idx_subject_code (subject_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Enrollments table (student enrollments in subjects)
CREATE TABLE IF NOT EXISTS enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    subject_id INT,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    UNIQUE KEY unique_enrollment (student_id, subject_id),
    INDEX idx_student (student_id),
    INDEX idx_subject (subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Activities table (programming assignments)
CREATE TABLE IF NOT EXISTS activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subject_id INT,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    starter_code TEXT,
    expected_output TEXT,
    due_date DATETIME,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_subject (subject_id),
    INDEX idx_created_by (created_by),
    INDEX idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Submissions table (student code submissions)
CREATE TABLE IF NOT EXISTS submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    activity_id INT,
    student_id INT,
    code TEXT,
    output TEXT,
    score DECIMAL(5,2) DEFAULT NULL,
    feedback TEXT,
    status ENUM('pending', 'graded') DEFAULT 'pending',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    graded_at DATETIME,
    FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_activity (activity_id),
    INDEX idx_student (student_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Notifications table (system notifications)
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    message TEXT NOT NULL,
    category ENUM('activity', 'grade', 'system', 'announcement') DEFAULT 'system',
    activity_id INT,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_read_status (is_read),
    INDEX idx_created (created_at),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample data (optional - for testing)

-- Sample instructor account (password: instructor123)
INSERT INTO users (username, password, email, full_name, user_type) VALUES
('instructor1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'instructor@school.com', 'John Instructor', 'instructor');

-- Sample student account (password: student123)
INSERT INTO users (username, password, email, full_name, user_type) VALUES
('student1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'student@school.com', 'Jane Student', 'student');

-- Sample subject
INSERT INTO subjects (subject_name, subject_code, description, instructor_id) VALUES
('Introduction to C Programming', 'CPP101', 'Learn the fundamentals of C programming including variables, loops, functions, and pointers.', 1);

-- Sample enrollment
INSERT INTO enrollments (student_id, subject_id) VALUES (2, 1);

-- Sample activity
INSERT INTO activities (subject_id, title, description, starter_code, expected_output, due_date, created_by) VALUES
(1, 'Hello World Program',
'Write a C program that prints "Hello, World!" to the console.',
'#include <stdio.h>\n\nint main() {\n    // Write your code here\n    \n    return 0;\n}',
'Hello, World!',
DATE_ADD(NOW(), INTERVAL 7 DAY),
1);

-- Display success message
SELECT 'Database setup completed successfully!' AS message;
SELECT 'Default Accounts Created:' AS info;
SELECT 'Instructor - Username: instructor1, Password: instructor123' AS account;
SELECT 'Student - Username: student1, Password: student123' AS account;
