-- =====================================================
-- Study Buddy Database Setup Script
-- =====================================================
-- This script creates the complete database schema for
-- the Study Buddy course resource sharing platform
-- =====================================================

-- Drop existing tables if they exist (for clean setup)
DROP TABLE IF EXISTS materials;
DROP TABLE IF EXISTS study_groups;
DROP TABLE IF EXISTS courses;

-- =====================================================
-- Create Courses Table
-- =====================================================
-- Stores information about available courses
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    description TEXT,
    instructor VARCHAR(100),
    duration_weeks INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- Create Materials Table
-- =====================================================
-- Stores study materials linked to courses
CREATE TABLE materials (
    material_id INT PRIMARY KEY AUTO_INCREMENT,
    material_name VARCHAR(200) NOT NULL,
    course_id INT,
    file_type VARCHAR(10),
    upload_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

-- =====================================================
-- Create Study Groups Table
-- =====================================================
-- Stores study group information for each course
CREATE TABLE study_groups (
    group_id INT PRIMARY KEY AUTO_INCREMENT,
    group_name VARCHAR(150) NOT NULL,
    course_id INT,
    meeting_time VARCHAR(100),
    member_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

-- =====================================================
-- Insert Sample Data - Courses
-- =====================================================
INSERT INTO courses (course_name, description, instructor, duration_weeks) VALUES
('Database Systems', 'Comprehensive course covering relational database management systems, SQL programming, database design, normalization, transactions, and advanced topics.', 'Dr. Sarah Johnson', 12),
('Data Structures', 'Master fundamental and advanced data structures including arrays, linked lists, stacks, queues, trees, graphs, and hash tables.', 'Prof. Michael Chen', 10),
('Web Development', 'Learn to build modern, responsive web applications from scratch. Cover HTML5, CSS3, JavaScript, and front-end frameworks.', 'Dr. Emily Rodriguez', 14);

-- =====================================================
-- Insert Sample Data - Materials
-- =====================================================
INSERT INTO materials (material_name, course_id, file_type, upload_date) VALUES
('Introduction to Databases', 1, 'PDF', '2024-01-15'),
('SQL Query Basics', 1, 'PPT', '2024-01-20'),
('Normalization and Design', 1, 'PDF', '2024-01-25'),
('Transactions and Concurrency', 1, 'PPT', '2024-02-01'),
('Arrays and Linked Lists', 2, 'PDF', '2024-01-18'),
('Trees and Graphs', 2, 'PPT', '2024-01-28'),
('Algorithm Complexity Analysis', 2, 'PDF', '2024-02-05'),
('HTML5 and CSS3 Fundamentals', 3, 'PDF', '2024-01-22'),
('JavaScript ES6+', 3, 'PPT', '2024-02-03'),
('Responsive Design Principles', 3, 'PDF', '2024-02-10');

-- =====================================================
-- Insert Sample Data - Study Groups
-- =====================================================
INSERT INTO study_groups (group_name, course_id, meeting_time, member_count) VALUES
('Database Design Masters', 1, 'Monday & Thursday 6:00 PM', 12),
('SQL Query Wizards', 1, 'Wednesday 7:00 PM', 15),
('Database Systems Study Circle', 1, 'Tuesday & Friday 5:30 PM', 18),
('Algorithm Analysis Group', 2, 'Monday & Wednesday 6:30 PM', 14),
('Data Structures Mastery', 2, 'Thursday 7:30 PM', 16),
('Full Stack Developers', 3, 'Tuesday & Saturday 6:00 PM', 20),
('Frontend Design Studio', 3, 'Wednesday & Friday 5:00 PM', 17);

-- =====================================================
-- Useful Sample Queries
-- =====================================================

-- Query 1: Get all materials with their course names
-- SELECT 
--     m.material_name,
--     m.file_type,
--     c.course_name,
--     c.instructor
-- FROM materials m
-- INNER JOIN courses c ON m.course_id = c.course_id
-- ORDER BY c.course_name, m.material_name;

-- Query 2: Count materials per course
-- SELECT 
--     c.course_name,
--     COUNT(m.material_id) AS material_count
-- FROM courses c
-- LEFT JOIN materials m ON c.course_id = m.course_id
-- GROUP BY c.course_id, c.course_name
-- ORDER BY material_count DESC;

-- Query 3: Find courses with more than 2 study groups
-- SELECT 
--     c.course_name,
--     COUNT(sg.group_id) AS group_count,
--     SUM(sg.member_count) AS total_members
-- FROM courses c
-- INNER JOIN study_groups sg ON c.course_id = sg.course_id
-- GROUP BY c.course_id, c.course_name
-- HAVING COUNT(sg.group_id) > 2
-- ORDER BY group_count DESC;

-- Query 4: Get complete course information with materials and groups
-- SELECT 
--     c.course_name,
--     c.instructor,
--     m.material_name,
--     m.file_type,
--     sg.group_name,
--     sg.meeting_time,
--     sg.member_count
-- FROM courses c
-- LEFT JOIN materials m ON c.course_id = m.course_id
-- LEFT JOIN study_groups sg ON c.course_id = sg.course_id
-- ORDER BY c.course_name, m.material_name, sg.group_name;

-- Query 5: Get study groups with most members per course
-- SELECT 
--     c.course_name,
--     sg.group_name,
--     sg.meeting_time,
--     sg.member_count
-- FROM study_groups sg
-- INNER JOIN courses c ON sg.course_id = c.course_id
-- WHERE sg.member_count >= (
--     SELECT AVG(member_count)
--     FROM study_groups
--     WHERE course_id = sg.course_id
-- )
-- ORDER BY sg.member_count DESC;

-- =====================================================
-- Create Indexes for Better Performance
-- =====================================================
CREATE INDEX idx_materials_course ON materials(course_id);
CREATE INDEX idx_study_groups_course ON study_groups(course_id);
CREATE INDEX idx_materials_type ON materials(file_type);

-- =====================================================
-- Database Setup Complete!
-- =====================================================
-- You can now use these tables and queries for the
-- Study Buddy application.
-- =====================================================