-- ============================================
-- PERFORMANCE OPTIMIZATION INDEXES
-- ============================================
-- Additional indexes for frequently queried columns
-- to improve API response times
-- ============================================

-- ============================================
-- 1. VIDEOS TABLE INDEXES
-- ============================================

-- Index for uploadedBy (user videos)
CREATE INDEX IF NOT EXISTS idx_videos_uploaded_by 
ON videos("uploadedBy", "isActive") 
WHERE "isActive" = true;

-- Index for playlistId (standalone videos)
CREATE INDEX IF NOT EXISTS idx_videos_playlist_id 
ON videos("playlistId") 
WHERE "playlistId" IS NULL;

-- Index for createdAt (latest videos)
CREATE INDEX IF NOT EXISTS idx_videos_created_at 
ON videos("createdAt" DESC) 
WHERE "isActive" = true;

-- Index for views (popular videos)
CREATE INDEX IF NOT EXISTS idx_videos_views 
ON videos("views" DESC) 
WHERE "isActive" = true;

-- Composite index for category + subcategory queries
CREATE INDEX IF NOT EXISTS idx_videos_category_subcategory 
ON videos("categoryId", "subcategoryId", "isActive") 
WHERE "isActive" = true;

-- Index for youtubeId (duplicate checks)
CREATE INDEX IF NOT EXISTS idx_videos_youtube_id 
ON videos("youtubeId") 
WHERE "youtubeId" IS NOT NULL;

-- ============================================
-- 2. PLAYLISTS TABLE INDEXES
-- ============================================

-- Index for addedBy (user playlists)
CREATE INDEX IF NOT EXISTS idx_playlists_added_by 
ON playlists("addedBy", "isActive") 
WHERE "isActive" = true AND "isPrivate" = false;

-- Index for createdAt (latest playlists)
CREATE INDEX IF NOT EXISTS idx_playlists_created_at 
ON playlists("createdAt" DESC) 
WHERE "isActive" = true AND "isPrivate" = false;

-- Composite index for category + subcategory queries
CREATE INDEX IF NOT EXISTS idx_playlists_category_subcategory 
ON playlists("categoryId", "subcategoryId", "isActive") 
WHERE "isActive" = true AND "isPrivate" = false;

-- ============================================
-- 3. PLAYLIST_VIDEOS TABLE INDEXES
-- ============================================

-- Index for playlistId (videos in playlist)
CREATE INDEX IF NOT EXISTS idx_playlist_videos_playlist_id 
ON playlist_videos("playlistId");

-- Index for videoId (video in playlists)
CREATE INDEX IF NOT EXISTS idx_playlist_videos_video_id 
ON playlist_videos("videoId");

-- Composite index for playlist + video lookup
CREATE INDEX IF NOT EXISTS idx_playlist_videos_composite 
ON playlist_videos("playlistId", "videoId");

-- ============================================
-- 4. WATCH_HISTORY TABLE INDEXES
-- ============================================

-- Index for userId (user history)
CREATE INDEX IF NOT EXISTS idx_watch_history_user_id 
ON watch_history("userId", "watchedAt" DESC);

-- Index for videoId (video history)
CREATE INDEX IF NOT EXISTS idx_watch_history_video_id 
ON watch_history("videoId");

-- Composite index for user + video lookup
CREATE INDEX IF NOT EXISTS idx_watch_history_user_video 
ON watch_history("userId", "videoId");

-- ============================================
-- 5. VIDEO_LIKES TABLE INDEXES
-- ============================================

-- Index for userId (user likes)
CREATE INDEX IF NOT EXISTS idx_video_likes_user_id 
ON video_likes("userId", "createdAt" DESC);

-- Index for videoId (video likes count)
CREATE INDEX IF NOT EXISTS idx_video_likes_video_id 
ON video_likes("videoId");

-- Composite index for user + video lookup
CREATE INDEX IF NOT EXISTS idx_video_likes_user_video 
ON video_likes("userId", "videoId");

-- ============================================
-- 6. FOLLOWS TABLE INDEXES
-- ============================================

-- Index for followerId (who follows)
CREATE INDEX IF NOT EXISTS idx_follows_follower_id 
ON follows("followerId");

-- Index for followingId (who is followed)
CREATE INDEX IF NOT EXISTS idx_follows_following_id 
ON follows("followingId");

-- Composite index for follower + following lookup
CREATE INDEX IF NOT EXISTS idx_follows_composite 
ON follows("followerId", "followingId");

-- ============================================
-- 7. COMMENTS TABLE INDEXES
-- ============================================

-- Index for videoId (video comments)
CREATE INDEX IF NOT EXISTS idx_comments_video_id 
ON comments("videoId", "createdAt" DESC);

-- Index for userId (user comments)
CREATE INDEX IF NOT EXISTS idx_comments_user_id 
ON comments("userId", "createdAt" DESC);

-- ============================================
-- 8. SAVED_VIDEOS TABLE INDEXES
-- ============================================

-- Index for userId (user saved videos)
CREATE INDEX IF NOT EXISTS idx_saved_videos_user_id 
ON saved_videos("userId", "createdAt" DESC);

-- Index for videoId (video saved count)
CREATE INDEX IF NOT EXISTS idx_saved_videos_video_id 
ON saved_videos("videoId");

-- Composite index for user + video lookup
CREATE INDEX IF NOT EXISTS idx_saved_videos_user_video 
ON saved_videos("userId", "videoId");

-- ============================================
-- MIGRATION COMPLETE
-- ============================================
-- These indexes will significantly improve query performance
-- for frequently accessed data
-- ============================================

