-- ============================================
-- UNIFIED SEARCH SETUP MIGRATION
-- ============================================
-- This migration sets up unified search infrastructure
-- for videos, playlists, and channels (users)
-- ============================================

-- ============================================
-- 1. ENABLE EXTENSIONS
-- ============================================
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Note: pgvector extension is optional for semantic search
-- If not installed, embeddings will be computed at runtime
-- To install: https://github.com/pgvector/pgvector
-- CREATE EXTENSION IF NOT EXISTS vector;

-- ============================================
-- 2. FUZZY SEARCH INDEXES (UNIFIED PATTERN)
-- ============================================

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

CREATE INDEX IF NOT EXISTS idx_users_channel_description_trgm 
ON users USING gin ("channelDescription" gin_trgm_ops)
WHERE "channelDescription" IS NOT NULL;

-- ============================================
-- 3. FULL-TEXT SEARCH COLUMNS & INDEXES
-- ============================================

-- 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 index
CREATE INDEX IF NOT EXISTS idx_videos_search_vector 
ON videos USING gin(search_vector);

-- Trigger function to auto-update 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();

-- 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 index
CREATE INDEX IF NOT EXISTS idx_playlists_search_vector 
ON playlists USING gin(search_vector);

-- Trigger function for playlists
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();

-- ============================================
-- 4. UNIFIED EMBEDDINGS TABLE
-- ============================================
-- Note: This table uses TEXT for embeddings if pgvector is not installed
-- Embeddings will be stored as JSON array strings and computed at runtime

DO $$
BEGIN
    -- Check if vector extension exists
    IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'vector') THEN
        -- Create table with vector type
        EXECUTE '
        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)
        );
        
        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);
        CREATE INDEX IF NOT EXISTS idx_embeddings_vector ON content_embeddings USING ivfflat (embedding vector_cosine_ops)
        WITH (lists = 100);
        ';
    ELSE
        -- Create table with TEXT for embeddings (computed at runtime)
        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 TEXT, -- JSON array string if pgvector not available
            created_at TIMESTAMP DEFAULT NOW(),
            updated_at TIMESTAMP DEFAULT NOW(),
            UNIQUE(content_type, content_id)
        );
        
        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);
    END IF;
END $$;

-- ============================================
-- 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 (using quoted column names for case sensitivity)
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. HELPER FUNCTIONS
-- ============================================

-- Function to get content by type and id (for embeddings lookup)
CREATE OR REPLACE FUNCTION get_content_info(
    p_content_type VARCHAR(20),
    p_content_id INTEGER
) RETURNS TABLE (
    id INTEGER,
    title TEXT,
    description TEXT
) AS $$
BEGIN
    IF p_content_type = 'video' THEN
        RETURN QUERY
        SELECT v.id, v.title, v.description
        FROM videos v
        WHERE v.id = p_content_id AND v."isActive" = true;
    ELSIF p_content_type = 'playlist' THEN
        RETURN QUERY
        SELECT p.id, p.title, p.description
        FROM playlists p
        WHERE p.id = p_content_id AND p."isActive" = true;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- MIGRATION COMPLETE
-- ============================================
