Project 04: Study Group Organizer 👥

-- Users table with study preferences
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    university VARCHAR(255),
    major VARCHAR(255),
    year_of_study INTEGER,
    bio TEXT,
    study_preferences JSONB, -- {"preferred_times": ["evening"], "subjects": ["math", "cs"]}
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Study groups
CREATE TABLE study_groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    subject VARCHAR(100),
    course_code VARCHAR(50),
    icon_emoji VARCHAR(10) DEFAULT '[books]',
    is_public BOOLEAN DEFAULT TRUE,
    max_members INTEGER DEFAULT 10,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    settings JSONB DEFAULT '{"allow_guests": false, "require_approval": false}'
);

-- Group membership
CREATE TABLE group_members (
    group_id UUID REFERENCES study_groups(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(20) DEFAULT 'member', -- admin, moderator, member
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_active TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    contribution_points INTEGER DEFAULT 0,
    PRIMARY KEY (group_id, user_id)
);

-- Real-time chat messages
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID REFERENCES study_groups(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id),
    content TEXT NOT NULL,
    type VARCHAR(20) DEFAULT 'text', -- text, image, file, code
    metadata JSONB, -- {"file_url": "...", "file_name": "..."}
    reply_to UUID REFERENCES messages(id),
    edited_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for performance
CREATE INDEX idx_messages_group_created ON messages(group_id, created_at DESC);
CREATE INDEX idx_group_members_user ON group_members(user_id);

// Socket.io server for real-time features
io.on('connection', (socket) => \{
  socket.on('join-group', async (groupId, userId) => \{
    // Verify membership
    const isMember = await checkGroupMembership(groupId, userId);
    if (!isMember) return socket.emit('error', 'Not a member');
    
    socket.join(`group:${groupId}`);
    
    // Notify others
    socket.to(`group:${groupId}`).emit('user-joined', {
      userId,
      timestamp: new Date()
    });
    
    // Update last active
    await updateLastActive(groupId, userId);
  });
  
  socket.on('send-message', async (data) => {
    const { groupId, userId, content, type } = data;
    
    // Save to database
    const message = await db.query(`
      INSERT INTO messages (group_id, user_id, content, type)
      VALUES ($1, $2, $3, $4)
      RETURNING *, 
        (SELECT username FROM users WHERE id = $2) as username,
        (SELECT avatar_url FROM users WHERE id = $2) as avatar_url
    `, [groupId, userId, content, type]);
    
    // Broadcast to group
    io.to(`group:${groupId}`).emit('new-message', message.rows[0]);
    
    // Update contribution points
    await incrementContribution(groupId, userId, 1);
  });
  
  // Typing indicators
  socket.on('typing', ({ groupId, userId, isTyping }) => {
    socket.to(`group:${groupId}`).emit('user-typing', {
      userId,
      isTyping
    });
  });
});

-- Study sessions
CREATE TABLE study_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID REFERENCES study_groups(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    topic VARCHAR(255),
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    location VARCHAR(255), -- "Library Room 201" or "Online"
    meeting_url VARCHAR(500), -- For online sessions
    max_attendees INTEGER,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    reminder_sent BOOLEAN DEFAULT FALSE
);

-- Session attendance
CREATE TABLE session_attendees (
    session_id UUID REFERENCES study_sessions(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'going', -- going, maybe, not_going
    attended BOOLEAN DEFAULT FALSE,
    notes TEXT,
    PRIMARY KEY (session_id, user_id)
);

-- User availability
CREATE TABLE user_availability (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    day_of_week INTEGER CHECK (day_of_week BETWEEN 0 AND 6),
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    timezone VARCHAR(50) DEFAULT 'UTC',
    is_recurring BOOLEAN DEFAULT TRUE
);

-- Find best meeting times
CREATE OR REPLACE FUNCTION find_common_availability(
    p_group_id UUID,
    p_duration_minutes INTEGER,
    p_date_start DATE,
    p_date_end DATE
) RETURNS TABLE (
    suggested_time TIMESTAMP WITH TIME ZONE,
    available_count INTEGER,
    total_members INTEGER
) AS $
BEGIN
    RETURN QUERY
    WITH group_member_availability AS (
        SELECT 
            ua.user_id,
            ua.day_of_week,
            ua.start_time,
            ua.end_time
        FROM user_availability ua
        JOIN group_members gm ON ua.user_id = gm.user_id
        WHERE gm.group_id = p_group_id
    ),
    time_slots AS (
        SELECT generate_series(
            p_date_start::timestamp,
            p_date_end::timestamp,
            '30 minutes'::interval
        ) AS slot_time
    )
    SELECT 
        ts.slot_time,
        COUNT(DISTINCT gma.user_id) as available_count,
        (SELECT COUNT(*) FROM group_members WHERE group_id = p_group_id) as total_members
    FROM time_slots ts
    JOIN group_member_availability gma 
        ON EXTRACT(DOW FROM ts.slot_time) = gma.day_of_week
        AND ts.slot_time::time >= gma.start_time
        AND (ts.slot_time + (p_duration_minutes || ' minutes')::interval)::time <= gma.end_time
    GROUP BY ts.slot_time
    HAVING COUNT(DISTINCT gma.user_id) >= 
        (SELECT COUNT(*) FROM group_members WHERE group_id = p_group_id) * 0.6
    ORDER BY available_count DESC, ts.slot_time
    LIMIT 10;
END;
$ LANGUAGE plpgsql;

-- Shared resources
CREATE TABLE resources (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID REFERENCES study_groups(id) ON DELETE CASCADE,
    uploaded_by UUID REFERENCES users(id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(50), -- note, document, video, link, quiz
    file_url VARCHAR(500),
    file_size INTEGER,
    mime_type VARCHAR(100),
    tags TEXT[],
    is_pinned BOOLEAN DEFAULT FALSE,
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Collaborative notes with version history
CREATE TABLE notes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    resource_id UUID REFERENCES resources(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    markdown TEXT,
    version INTEGER DEFAULT 1,
    edited_by UUID REFERENCES users(id),
    edit_summary VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Track resource interactions
CREATE TABLE resource_interactions (
    resource_id UUID REFERENCES resources(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    interaction_type VARCHAR(20), -- view, download, like, bookmark
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    PRIMARY KEY (resource_id, user_id, interaction_type)
);

-- Implement collaborative editing
CREATE OR REPLACE FUNCTION create_note_version(
    p_resource_id UUID,
    p_content TEXT,
    p_markdown TEXT,
    p_edited_by UUID,
    p_edit_summary VARCHAR(255)
) RETURNS notes AS $
DECLARE
    v_new_note notes;
    v_last_version INTEGER;
BEGIN
    -- Get the last version number
    SELECT COALESCE(MAX(version), 0) INTO v_last_version
    FROM notes
    WHERE resource_id = p_resource_id;
    
    -- Create new version
    INSERT INTO notes (resource_id, content, markdown, version, edited_by, edit_summary)
    VALUES (p_resource_id, p_content, p_markdown, v_last_version + 1, p_edited_by, p_edit_summary)
    RETURNING * INTO v_new_note;
    
    -- Update contribution points
    UPDATE group_members gm
    SET contribution_points = contribution_points + 5
    FROM resources r
    WHERE r.id = p_resource_id
      AND gm.group_id = r.group_id
      AND gm.user_id = p_edited_by;
    
    RETURN v_new_note;
END;
$ LANGUAGE plpgsql;

-- Study goals
CREATE TABLE study_goals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID REFERENCES study_groups(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    target_date DATE,
    category VARCHAR(50), -- exam_prep, project, assignment, general
    status VARCHAR(20) DEFAULT 'active', -- active, completed, archived
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Goal milestones
CREATE TABLE goal_milestones (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    goal_id UUID REFERENCES study_goals(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    due_date DATE,
    completed BOOLEAN DEFAULT FALSE,
    completed_by UUID REFERENCES users(id),
    completed_at TIMESTAMP WITH TIME ZONE,
    order_index INTEGER
);

-- Study time tracking
CREATE TABLE study_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    group_id UUID REFERENCES study_groups(id),
    duration_minutes INTEGER NOT NULL,
    topic VARCHAR(255),
    productivity_rating INTEGER CHECK (productivity_rating BETWEEN 1 AND 5),
    notes TEXT,
    logged_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Analytics views
CREATE VIEW group_analytics AS
SELECT 
    g.id as group_id,
    g.name as group_name,
    COUNT(DISTINCT gm.user_id) as member_count,
    COUNT(DISTINCT s.id) as total_sessions,
    COUNT(DISTINCT r.id) as resource_count,
    SUM(sl.duration_minutes) as total_study_minutes,
    AVG(sl.productivity_rating) as avg_productivity,
    COUNT(DISTINCT CASE WHEN m.created_at > NOW() - INTERVAL '7 days' 
           THEN m.id END) as messages_last_week
FROM study_groups g
LEFT JOIN group_members gm ON g.id = gm.group_id
LEFT JOIN study_sessions s ON g.id = s.group_id
LEFT JOIN resources r ON g.id = r.group_id
LEFT JOIN study_logs sl ON g.id = sl.group_id
LEFT JOIN messages m ON g.id = m.group_id
GROUP BY g.id, g.name;

-- Personal progress dashboard
CREATE VIEW user_progress AS
SELECT 
    u.id as user_id,
    u.username,
    COUNT(DISTINCT sl.group_id) as active_groups,
    SUM(sl.duration_minutes) as total_study_time,
    AVG(sl.productivity_rating) as avg_productivity,
    COUNT(DISTINCT DATE(sl.logged_at)) as study_days,
    MAX(sl.logged_at) as last_study_session,
    SUM(gm.contribution_points) as total_contribution_points
FROM users u
LEFT JOIN study_logs sl ON u.id = sl.user_id
LEFT JOIN group_members gm ON u.id = gm.user_id
GROUP BY u.id, u.username;

-- Achievements system
CREATE TABLE achievements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(10),
    points INTEGER DEFAULT 10,
    criteria JSONB -- {"type": "study_streak", "days": 7}
);

-- User achievements
CREATE TABLE user_achievements (
    user_id UUID REFERENCES users(id),
    achievement_id UUID REFERENCES achievements(id),
    earned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    group_id UUID REFERENCES study_groups(id),
    PRIMARY KEY (user_id, achievement_id)
);

-- Quiz battles
CREATE TABLE quizzes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID REFERENCES study_groups(id),
    created_by UUID REFERENCES users(id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    questions JSONB NOT NULL, -- Array of questions with answers
    time_limit_seconds INTEGER,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Quiz attempts
CREATE TABLE quiz_attempts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    quiz_id UUID REFERENCES quizzes(id),
    user_id UUID REFERENCES users(id),
    score INTEGER NOT NULL,
    time_taken_seconds INTEGER,
    answers JSONB,
    completed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Leaderboards
CREATE VIEW group_leaderboard AS
SELECT 
    gm.group_id,
    u.id as user_id,
    u.username,
    u.avatar_url,
    gm.contribution_points,
    COUNT(DISTINCT qa.id) as quizzes_completed,
    AVG(qa.score) as avg_quiz_score,
    SUM(sl.duration_minutes) as study_time,
    COUNT(DISTINCT ua.achievement_id) as achievements_earned,
    RANK() OVER (PARTITION BY gm.group_id ORDER BY gm.contribution_points DESC) as rank
FROM group_members gm
JOIN users u ON gm.user_id = u.id
LEFT JOIN quiz_attempts qa ON u.id = qa.user_id
LEFT JOIN study_logs sl ON u.id = sl.user_id AND sl.group_id = gm.group_id
LEFT JOIN user_achievements ua ON u.id = ua.user_id AND ua.group_id = gm.group_id
GROUP BY gm.group_id, u.id, u.username, u.avatar_url, gm.contribution_points;

-- Auto-award achievements
CREATE OR REPLACE FUNCTION check_achievements() RETURNS TRIGGER AS $
BEGIN
    -- Study streak achievement
    IF (SELECT COUNT(DISTINCT DATE(logged_at)) 
        FROM study_logs 
        WHERE user_id = NEW.user_id 
        AND logged_at >= CURRENT_DATE - INTERVAL '7 days') >= 7 THEN
        
        INSERT INTO user_achievements (user_id, achievement_id, group_id)
        SELECT NEW.user_id, id, NEW.group_id
        FROM achievements
        WHERE criteria->>'type' = 'study_streak'
        AND (criteria->>'days')::int = 7
        ON CONFLICT DO NOTHING;
    END IF;
    
    -- Night owl achievement (studying after 10 PM)
    IF EXTRACT(HOUR FROM NEW.logged_at) >= 22 THEN
        INSERT INTO user_achievements (user_id, achievement_id, group_id)
        SELECT NEW.user_id, id, NEW.group_id
        FROM achievements
        WHERE criteria->>'type' = 'night_owl'
        ON CONFLICT DO NOTHING;
    END IF;
    
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER award_achievements
AFTER INSERT ON study_logs
FOR EACH ROW EXECUTE FUNCTION check_achievements();

# Clone the starter
git clone https://github.com/telebort/study-group-starter
cd study-group-starter

# Install dependencies
npm install

# Set up environment
cp .env.example .env
# Add Supabase, Redis, S3 keys

# Run development
npm run dev

// pages/api/groups/[id]/schedule.js
export async function POST(req, res) \{
      const { groupId } = req.query;
    const { duration, dateRange } = req.body;
  
  // Find best times when most members are available
  const suggestions = await db.query(`
    SELECT * FROM find_common_availability($1, $2, $3, $4)
  `, [groupId, duration, dateRange.start, dateRange.end]);
  
  // Create interactive poll
  const poll = await createSchedulePoll(groupId, suggestions.rows);
  
  // Notify members
  await notifyMembers(groupId, \{
    type: 'schedule_poll',
    pollId: poll.id,
    message: 'Vote for the best study session time!'
  \});
  
      res.json({ poll, suggestions: suggestions.rows });
\}

// Real-time collaborative whiteboard
import { Excalidraw } from "@excalidraw/excalidraw";

export function StudyWhiteboard({ groupId, sessionId }) {
  const [elements, setElements] = useState([]);
  
  useEffect(() => {
    // Join whiteboard room
    socket.emit('join-whiteboard', { groupId, sessionId });
    
    // Sync whiteboard state
    socket.on('whiteboard-update', (data) => {
      setElements(data.elements);
    });
    
    return () => socket.emit('leave-whiteboard', { groupId, sessionId });
  }, [groupId, sessionId]);
  
  const handleChange = (elements) => {
    setElements(elements);
    socket.emit('whiteboard-change', { 
      groupId, 
      sessionId, 
      elements 
    });
  };
  
  return (
    <Excalidraw
      elements={elements}
      onChange={handleChange}
      collaborators={collaborators}
    />
  );
\}