create_student_feedback_table.sql

-- Create student_feedback table for storing AI-generated feedback during sessions
CREATE TABLE IF NOT EXISTS student_feedback (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    session_id TEXT NOT NULL,
    student_id TEXT NOT NULL,
    timestamp_seconds NUMERIC NOT NULL,
    feedback_type TEXT NOT NULL,
    message TEXT NOT NULL,
    learning_outcome TEXT NOT NULL,
    performance_rating INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_student_feedback_session_id ON student_feedback (session_id);
CREATE INDEX IF NOT EXISTS idx_student_feedback_student_id ON student_feedback (student_id);
CREATE INDEX IF NOT EXISTS idx_student_feedback_timestamp ON student_feedback (timestamp_seconds);

-- Enable Row Level Security (RLS) if needed
ALTER TABLE student_feedback ENABLE ROW LEVEL SECURITY;

-- Create policy to allow authenticated users to read and write their own data
-- Adjust this policy based on your authentication requirements
CREATE POLICY "Users can manage feedback" ON student_feedback
    FOR ALL USING (true);

-- Grant permissions
GRANT ALL ON student_feedback TO authenticated;
GRANT ALL ON student_feedback TO anon;