fix-database-schema.sql

-- Clear Database Schema Cache and Verify Structure
-- Run this in Supabase SQL Editor

-- First, let's see what columns actually exist in the courses table
SELECT 
  column_name, 
  data_type, 
  is_nullable,
  column_default
FROM information_schema.columns 
WHERE table_name = 'courses' 
AND table_schema = 'public'
ORDER BY ordinal_position;

-- Check if the table exists and has data
SELECT COUNT(*) as course_count FROM courses;

-- If the table is empty or missing columns, let's recreate it properly
-- Drop and recreate the courses table with the correct structure
DROP TABLE IF EXISTS study_sessions CASCADE;
DROP TABLE IF EXISTS course_files CASCADE;
DROP TABLE IF EXISTS courses CASCADE;

-- Create courses table with the exact structure our app expects
CREATE TABLE courses (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  difficulty TEXT NOT NULL CHECK (difficulty IN ('easy', 'medium', 'hard')),
  priority INTEGER NOT NULL DEFAULT 5,
  color TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  total_slides INTEGER DEFAULT 0,
  completed_slides INTEGER DEFAULT 0,
  processed_chunks JSONB DEFAULT '[]'::jsonb,
  key_concepts TEXT[] DEFAULT '{}',
  total_estimated_time INTEGER DEFAULT 0,
  processing_status TEXT DEFAULT 'pending' CHECK (processing_status IN ('pending', 'processing', 'completed', 'failed')),
  last_processed TIMESTAMP WITH TIME ZONE
);

-- Create course_files table
CREATE TABLE course_files (
  id TEXT PRIMARY KEY,
  course_id TEXT REFERENCES courses(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  size INTEGER NOT NULL,
  uri TEXT,
  uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create study_sessions table
CREATE TABLE study_sessions (
  id TEXT PRIMARY KEY,
  course_id TEXT REFERENCES courses(id) ON DELETE CASCADE NOT NULL,
  date TIMESTAMP WITH TIME ZONE NOT NULL,
  slides INTEGER DEFAULT 0,
  completed BOOLEAN DEFAULT FALSE,
  completed_slides INTEGER DEFAULT 0,
  chunks JSONB DEFAULT '[]'::jsonb,
  total_estimated_time INTEGER DEFAULT 0,
  completed_chunks INTEGER DEFAULT 0,
  current_chunk_index INTEGER DEFAULT 0,
  session_progress INTEGER DEFAULT 0,
  learning_objectives TEXT[] DEFAULT '{}',
  assessment_questions TEXT[] DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE courses ENABLE ROW LEVEL SECURITY;
ALTER TABLE course_files ENABLE ROW LEVEL SECURITY;
ALTER TABLE study_sessions ENABLE ROW LEVEL SECURITY;

-- Create RLS policies
CREATE POLICY "Users can view own courses" ON courses
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own courses" ON courses
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own courses" ON courses
  FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own courses" ON courses
  FOR DELETE USING (auth.uid() = user_id);

-- Create policies for course_files
CREATE POLICY "Users can manage files of own courses" ON course_files
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM courses 
      WHERE courses.id = course_files.course_id 
      AND courses.user_id = auth.uid()
    )
  );

-- Create policies for study_sessions
CREATE POLICY "Users can manage sessions of own courses" ON study_sessions
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM courses 
      WHERE courses.id = study_sessions.course_id 
      AND courses.user_id = auth.uid()
    )
  );

-- Verify the final structure
SELECT 'Database setup completed successfully!' as status;
SELECT 'Final courses table structure:' as info;
SELECT column_name FROM information_schema.columns WHERE table_name = 'courses' AND table_schema = 'public' ORDER BY ordinal_position;