-- ============================================
-- SMART SEARCH MIGRATION
-- Adds pg_trgm extension and indexes for fuzzy, fulltext, and semantic search
-- ============================================

-- Step 1: Enable pg_trgm extension for fuzzy matching
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Step 2: Add tsvector columns for full-text search on videos
ALTER TABLE videos 
ADD COLUMN IF NOT EXISTS search_vector tsvector;

-- Step 3: Add tsvector columns for full-text search on playlists
ALTER TABLE playlists 
ADD COLUMN IF NOT EXISTS search_vector tsvector;

-- Step 4: Create function to update video search_vector
CREATE OR REPLACE FUNCTION update_video_search_vector() RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector := 
    setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Step 5: Create trigger to auto-update video search_vector
DROP TRIGGER IF EXISTS video_search_vector_update ON videos;
CREATE TRIGGER video_search_vector_update
  BEFORE INSERT OR UPDATE ON videos
  FOR EACH ROW
  EXECUTE FUNCTION update_video_search_vector();

-- Step 6: Create function to update playlist search_vector
CREATE OR REPLACE FUNCTION update_playlist_search_vector() RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector := 
    setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Step 7: Create trigger to auto-update playlist search_vector
DROP TRIGGER IF EXISTS playlist_search_vector_update ON playlists;
CREATE TRIGGER playlist_search_vector_update
  BEFORE INSERT OR UPDATE ON playlists
  FOR EACH ROW
  EXECUTE FUNCTION update_playlist_search_vector();

-- Step 8: Update existing videos with search_vector
UPDATE videos 
SET search_vector = 
  setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
  setweight(to_tsvector('english', COALESCE(description, '')), 'B')
WHERE search_vector IS NULL;

-- Step 9: Update existing playlists with search_vector
UPDATE playlists 
SET search_vector = 
  setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
  setweight(to_tsvector('english', COALESCE(description, '')), 'B')
WHERE search_vector IS NULL;

-- Step 10: Create GIN indexes for full-text search (videos)
CREATE INDEX IF NOT EXISTS idx_videos_search_vector 
ON videos USING GIN(search_vector);

-- Step 11: Create GIN indexes for full-text search (playlists)
CREATE INDEX IF NOT EXISTS idx_playlists_search_vector 
ON playlists USING GIN(search_vector);

-- Step 12: Create trigram indexes for fuzzy matching (videos title)
CREATE INDEX IF NOT EXISTS idx_videos_title_trgm 
ON videos USING GIN(title gin_trgm_ops);

-- Step 13: Create trigram indexes for fuzzy matching (videos description)
CREATE INDEX IF NOT EXISTS idx_videos_description_trgm 
ON videos USING GIN(description gin_trgm_ops);

-- Step 14: Create trigram indexes for fuzzy matching (playlists title)
CREATE INDEX IF NOT EXISTS idx_playlists_title_trgm 
ON playlists USING GIN(title gin_trgm_ops);

-- Step 15: Create trigram indexes for fuzzy matching (playlists description)
CREATE INDEX IF NOT EXISTS idx_playlists_description_trgm 
ON playlists USING GIN(description gin_trgm_ops);

-- Step 16: Add columns for storing embeddings (for semantic search)
-- These will be populated by the AI service
ALTER TABLE videos 
ADD COLUMN IF NOT EXISTS embedding vector(384);

ALTER TABLE playlists 
ADD COLUMN IF NOT EXISTS embedding vector(384);

-- Step 17: Create indexes for vector similarity search (using pgvector if available)
-- Note: If pgvector extension is not installed, these will fail gracefully
-- You can install it with: CREATE EXTENSION IF NOT EXISTS vector;
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'vector') THEN
    CREATE INDEX IF NOT EXISTS idx_videos_embedding 
    ON videos USING ivfflat(embedding vector_cosine_ops) 
    WITH (lists = 100);
    
    CREATE INDEX IF NOT EXISTS idx_playlists_embedding 
    ON playlists USING ivfflat(embedding vector_cosine_ops) 
    WITH (lists = 100);
  ELSE
    RAISE NOTICE 'pgvector extension not found. Semantic search indexes skipped. Install with: CREATE EXTENSION vector;';
  END IF;
END $$;

-- Step 18: Add index on isActive for faster filtering
CREATE INDEX IF NOT EXISTS idx_videos_isactive 
ON videos("isActive") 
WHERE "isActive" = true;

CREATE INDEX IF NOT EXISTS idx_playlists_isactive 
ON playlists("isActive") 
WHERE "isActive" = true;

-- Step 19: Add composite indexes for common queries
CREATE INDEX IF NOT EXISTS idx_videos_category_active 
ON videos("categoryId", "isActive") 
WHERE "isActive" = true;

CREATE INDEX IF NOT EXISTS idx_playlists_category_active 
ON playlists("categoryId", "isActive") 
WHERE "isActive" = true;

-- ============================================
-- VERIFICATION QUERIES (for testing)
-- ============================================

-- Check if extension is enabled
-- SELECT * FROM pg_extension WHERE extname = 'pg_trgm';

-- Check if indexes exist
-- SELECT indexname FROM pg_indexes WHERE tablename IN ('videos', 'playlists') AND indexname LIKE '%trgm%' OR indexname LIKE '%search%';

-- Test fuzzy search
-- SELECT title, similarity(title, 'javscript') as sim FROM videos WHERE title % 'javscript' ORDER BY sim DESC LIMIT 5;

-- Test full-text search
-- SELECT title, ts_rank(search_vector, to_tsquery('english', 'python')) as rank FROM videos WHERE search_vector @@ to_tsquery('english', 'python') ORDER BY rank DESC LIMIT 5;

