-- ============================================
-- ADD CATEGORY/SUBCATEGORY SEARCH SUPPORT
-- ============================================
-- Adds search_vector columns and indexes for categories and subcategories
-- Enables searching by category/subcategory names
-- ============================================

-- Add search_vector to categories table
ALTER TABLE categories 
ADD COLUMN IF NOT EXISTS search_vector tsvector;

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

-- Create index
CREATE INDEX IF NOT EXISTS idx_categories_search_vector 
ON categories USING gin(search_vector);

-- Trigger function to auto-update category search_vector
CREATE OR REPLACE FUNCTION update_category_search_vector() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := to_tsvector('english', NEW.name || ' ' || COALESCE(NEW.description, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for categories
DROP TRIGGER IF EXISTS category_search_vector_update ON categories;
CREATE TRIGGER category_search_vector_update
BEFORE INSERT OR UPDATE OF name, description ON categories
FOR EACH ROW EXECUTE FUNCTION update_category_search_vector();

-- Add search_vector to subcategories table
ALTER TABLE subcategories 
ADD COLUMN IF NOT EXISTS search_vector tsvector;

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

-- Create index
CREATE INDEX IF NOT EXISTS idx_subcategories_search_vector 
ON subcategories USING gin(search_vector);

-- Trigger function to auto-update subcategory search_vector
CREATE OR REPLACE FUNCTION update_subcategory_search_vector() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := to_tsvector('english', NEW.name || ' ' || COALESCE(NEW.description, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for subcategories
DROP TRIGGER IF EXISTS subcategory_search_vector_update ON subcategories;
CREATE TRIGGER subcategory_search_vector_update
BEFORE INSERT OR UPDATE OF name, description ON subcategories
FOR EACH ROW EXECUTE FUNCTION update_subcategory_search_vector();

-- Add fuzzy search indexes for categories and subcategories
CREATE INDEX IF NOT EXISTS idx_categories_name_trgm 
ON categories USING gin (name gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_subcategories_name_trgm 
ON subcategories USING gin (name gin_trgm_ops);

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

