-- ============================================
-- UNIFIED SMART SEARCH MIGRATION
-- Supports: Videos, Playlists, Channels
-- ============================================

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS vector; -- For semantic search embeddings

-- ============================================
-- 1. FUZZY SEARCH INDEXES (pg_trgm)
-- ============================================

-- Videos fuzzy search indexes
CREATE INDEX IF NOT EXISTS idx_videos_title_trgm 
ON videos USING gin (title gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_videos_description_trgm 
ON videos USING gin (description gin_trgm_ops);

-- Playlists fuzzy search indexes (SAME PATTERN)
CREATE INDEX IF NOT EXISTS idx_playlists_title_trgm 
ON playlists USING gin (title gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_playlists_description_trgm 
ON playlists USING gin (description gin_trgm_ops);

-- Users/Channels fuzzy search indexes
CREATE INDEX IF NOT EXISTS idx_users_name_trgm 
ON users USING gin (name gin_trgm_ops);

-- ============================================
-- 2. FULL-TEXT SEARCH (tsvector)
-- ============================================

-- Videos full-text search
ALTER TABLE videos 
ADD COLUMN IF NOT EXISTS search_vector tsvector;

-- Update existing videos
UPDATE videos 
SET search_vector = to_tsvector('english', title || ' ' || COALESCE(description, ''))
WHERE search_vector IS NULL;

-- Create GIN index for full-text search
CREATE INDEX IF NOT EXISTS idx_videos_search_vector 
ON videos USING gin(search_vector);

-- Playlists full-text search (SAME PATTERN)
ALTER TABLE playlists 
ADD COLUMN IF NOT EXISTS search_vector tsvector;

-- Update existing playlists
UPDATE playlists 
SET search_vector = to_tsvector('english', title || ' ' || COALESCE(description, ''))
WHERE search_vector IS NULL;

-- Create GIN index for full-text search
CREATE INDEX IF NOT EXISTS idx_playlists_search_vector 
ON playlists USING gin(search_vector);

-- ============================================
-- 3. UNIFIED EMBEDDINGS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS content_embeddings (
    id SERIAL PRIMARY KEY,
    content_type VARCHAR(20) NOT NULL CHECK (content_type IN ('video', 'playlist')),
    content_id INTEGER NOT NULL,
    title TEXT,
    description TEXT,
    embedding VECTOR(384),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(content_type, content_id)
);

-- Indexes for embeddings table
CREATE INDEX IF NOT EXISTS idx_embeddings_type 
ON content_embeddings(content_type);

CREATE INDEX IF NOT EXISTS idx_embeddings_content 
ON content_embeddings(content_type, content_id);

-- Vector similarity index (for fast semantic search)
CREATE INDEX IF NOT EXISTS idx_embeddings_vector 
ON content_embeddings 
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- ============================================
-- 4. TRIGGERS FOR AUTO-UPDATE
-- ============================================

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

-- Trigger for videos
DROP TRIGGER IF EXISTS video_search_vector_update ON videos;
CREATE TRIGGER video_search_vector_update
BEFORE INSERT OR UPDATE OF title, description ON videos
FOR EACH ROW EXECUTE FUNCTION update_video_search_vector();

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

-- Trigger for playlists
DROP TRIGGER IF EXISTS playlist_search_vector_update ON playlists;
CREATE TRIGGER playlist_search_vector_update
BEFORE INSERT OR UPDATE OF title, description ON playlists
FOR EACH ROW EXECUTE FUNCTION update_playlist_search_vector();

-- ============================================
-- 5. PERFORMANCE INDEXES
-- ============================================

-- Active content indexes
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;

-- 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;

-- ============================================
-- 6. COMMENTS
-- ============================================

COMMENT ON TABLE content_embeddings IS 'Unified embeddings table for semantic search across videos and playlists';
COMMENT ON COLUMN content_embeddings.content_type IS 'Type of content: video or playlist';
COMMENT ON COLUMN content_embeddings.embedding IS '384-dimensional vector embedding for semantic search';

