-- Add show_in_frontend column to products table
-- This allows products to be active in backend but hidden from frontend

-- Add the new column
ALTER TABLE products 
ADD COLUMN show_in_frontend TINYINT(1) NOT NULL DEFAULT 1 
COMMENT 'Whether product should be visible in frontend registration (1=visible, 0=hidden)';

-- Add index for better query performance
CREATE INDEX idx_products_frontend_visibility ON products (is_active, show_in_frontend);

-- Update existing products to be visible in frontend by default
UPDATE products SET show_in_frontend = 1 WHERE show_in_frontend IS NULL;

-- Verify the changes
SELECT 
    COUNT(*) as total_products,
    SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) as active_products,
    SUM(CASE WHEN is_active = 1 AND show_in_frontend = 1 THEN 1 ELSE 0 END) as frontend_visible_products,
    SUM(CASE WHEN is_active = 1 AND show_in_frontend = 0 THEN 1 ELSE 0 END) as frontend_hidden_products
FROM products;
