database_updates.sql

-- ============================================
-- DATABASE UPDATES FOR NEW FEATURES
-- Run these in your Supabase SQL Editor
-- ============================================

-- ============================================
-- 1. ADD NEW COLUMNS TO NOTES TABLE
-- ============================================

-- Add year column for past papers
ALTER TABLE notes 
ADD COLUMN IF NOT EXISTS year INTEGER;

-- Add chapter/unit column for organizing by syllabus
ALTER TABLE notes 
ADD COLUMN IF NOT EXISTS chapter TEXT;

-- Add tags array for better searchability
ALTER TABLE notes 
ADD COLUMN IF NOT EXISTS tags TEXT[];

-- Add views counter for popularity tracking
ALTER TABLE notes 
ADD COLUMN IF NOT EXISTS views INTEGER DEFAULT 0;

-- ============================================
-- 2. UPDATE EXISTING NOTES TABLE (if needed)
-- ============================================

-- Make sure downloads column exists with default
ALTER TABLE notes 
ALTER COLUMN downloads SET DEFAULT 0;

-- ============================================
-- 3. ADD POLICY FOR UPDATES (if not exists)
-- ============================================

-- Policy for Notes: Everyone can update (for download/view counts)
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_policies 
        WHERE tablename = 'notes' 
        AND policyname = 'Public Notes Update'
    ) THEN
        CREATE POLICY "Public Notes Update"
        ON notes FOR UPDATE
        USING (true);
    END IF;
END $$;

-- ============================================
-- 4. CREATE INDEX FOR BETTER PERFORMANCE
-- ============================================

-- Index for grade filtering
CREATE INDEX IF NOT EXISTS idx_notes_grade ON notes(grade);

-- Index for medium filtering
CREATE INDEX IF NOT EXISTS idx_notes_medium ON notes(medium);

-- Index for type filtering
CREATE INDEX IF NOT EXISTS idx_notes_type ON notes(type);

-- Index for year filtering
CREATE INDEX IF NOT EXISTS idx_notes_year ON notes(year);

-- Index for subject filtering
CREATE INDEX IF NOT EXISTS idx_notes_subject ON notes(subject);

-- Composite index for common filter combinations
CREATE INDEX IF NOT EXISTS idx_notes_grade_medium ON notes(grade, medium);
CREATE INDEX IF NOT EXISTS idx_notes_grade_type ON notes(grade, type);

-- ============================================
-- 5. OPTIONAL: CREATE BOOKMARKS TABLE
-- (For future bookmarks/favorites feature)
-- ============================================

CREATE TABLE IF NOT EXISTS bookmarks (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
    user_id TEXT NOT NULL,  -- Can be device ID or user ID if you add auth
    note_id BIGINT NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
    UNIQUE(user_id, note_id)
);

-- Enable RLS for bookmarks
ALTER TABLE bookmarks ENABLE ROW LEVEL SECURITY;

-- Policy for Bookmarks: Users can manage their own bookmarks
CREATE POLICY "Users can manage bookmarks"
ON bookmarks FOR ALL
USING (true)
WITH CHECK (true);

-- ============================================
-- 6. OPTIONAL: CREATE RATINGS TABLE
-- (For future rating feature)
-- ============================================

CREATE TABLE IF NOT EXISTS ratings (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
    user_id TEXT NOT NULL,
    note_id BIGINT NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    UNIQUE(user_id, note_id)
);

-- Enable RLS for ratings
ALTER TABLE ratings ENABLE ROW LEVEL SECURITY;

-- Policy for Ratings
CREATE POLICY "Public Ratings Access"
ON ratings FOR ALL
USING (true)
WITH CHECK (true);

-- ============================================
-- 7. OPTIONAL: CREATE COMMENTS TABLE
-- (For future comments feature)
-- ============================================

CREATE TABLE IF NOT EXISTS comments (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
    user_name TEXT NOT NULL,
    note_id BIGINT NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    likes INTEGER DEFAULT 0
);

-- Enable RLS for comments
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

-- Policy for Comments
CREATE POLICY "Public Comments Access"
ON comments FOR SELECT
USING (true);

CREATE POLICY "Public Comments Insert"
ON comments FOR INSERT
WITH CHECK (true);

-- ============================================
-- 8. CREATE THANK YOU NOTES TABLE
-- (For community appreciation feature)
-- ============================================

CREATE TABLE IF NOT EXISTS thank_you_notes (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
    student_name TEXT NOT NULL,
    message TEXT NOT NULL,
    contributor_name TEXT DEFAULT 'All Contributors'
);

-- Enable RLS for thank_you_notes
ALTER TABLE thank_you_notes ENABLE ROW LEVEL SECURITY;

-- Policy for Thank You Notes: Everyone can read
CREATE POLICY "Public Thank You Notes Read"
ON thank_you_notes FOR SELECT
USING (true);

-- Policy for Thank You Notes: Everyone can insert
CREATE POLICY "Public Thank You Notes Insert"
ON thank_you_notes FOR INSERT
WITH CHECK (true);

-- Index for faster queries
CREATE INDEX IF NOT EXISTS idx_thank_you_notes_created ON thank_you_notes(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_thank_you_notes_contributor ON thank_you_notes(contributor_name);

-- ============================================
-- 9. HELPFUL VIEWS (OPTIONAL)
-- ============================================

-- View for popular materials (most downloaded)
CREATE OR REPLACE VIEW popular_notes AS
SELECT * FROM notes
ORDER BY downloads DESC
LIMIT 10;

-- View for recent materials
CREATE OR REPLACE VIEW recent_notes AS
SELECT * FROM notes
ORDER BY created_at DESC
LIMIT 10;

-- View for O/L materials
CREATE OR REPLACE VIEW ol_notes AS
SELECT * FROM notes
WHERE grade = 'Grade 11 (O/L)'
ORDER BY created_at DESC;

-- View for A/L materials
CREATE OR REPLACE VIEW al_notes AS
SELECT * FROM notes
WHERE grade = 'Grade 12-13 (A/L)'
ORDER BY created_at DESC;

-- ============================================
-- 9. FUNCTION TO GET SUBJECTS LIST
-- ============================================

CREATE OR REPLACE FUNCTION get_unique_subjects()
RETURNS TABLE(subject TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT DISTINCT n.subject
    FROM notes n
    WHERE n.subject IS NOT NULL
    ORDER BY n.subject;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- VERIFICATION QUERIES
-- Run these to verify the updates worked
-- ============================================

-- Check notes table structure
-- SELECT column_name, data_type, column_default
-- FROM information_schema.columns
-- WHERE table_name = 'notes';

-- Check existing data
-- SELECT id, title, grade, medium, type, year, downloads FROM notes LIMIT 10;

-- Check indexes
-- SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'notes';