-- Migration: Fix custom_features foreign key constraint
-- Purpose: Allow custom_features to reference both templates and custom_templates tables

-- First, drop the existing foreign key constraint
ALTER TABLE IF EXISTS custom_features 
DROP CONSTRAINT IF EXISTS custom_features_template_id_fkey;

-- Add a new column to track the template type
ALTER TABLE IF EXISTS custom_features
ADD COLUMN IF NOT EXISTS template_type VARCHAR(20) DEFAULT 'default' CHECK (template_type IN ('default', 'custom'));

-- Update existing records to have the correct template_type
UPDATE custom_features 
SET template_type = CASE 
    WHEN EXISTS (SELECT 1 FROM templates WHERE id = template_id AND is_active = true) THEN 'default'
    WHEN EXISTS (SELECT 1 FROM custom_templates WHERE id = template_id) THEN 'custom'
    ELSE 'default'
END
WHERE template_type IS NULL OR template_type = '';

-- Fix any existing records where template_type is 'custom' but template_id exists in templates table
UPDATE custom_features 
SET template_type = 'default'
WHERE template_type = 'custom' 
AND EXISTS (SELECT 1 FROM templates WHERE id = template_id AND is_active = true)
AND NOT EXISTS (SELECT 1 FROM custom_templates WHERE id = template_id);

-- Create a function to validate template_id references
CREATE OR REPLACE FUNCTION validate_template_reference()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if template_id exists in either templates or custom_templates
    IF NEW.template_type = 'default' THEN
        IF NOT EXISTS (SELECT 1 FROM templates WHERE id = NEW.template_id AND is_active = true) THEN
            RAISE EXCEPTION 'Template ID % does not exist in templates table or is not active', NEW.template_id;
        END IF;
    ELSIF NEW.template_type = 'custom' THEN
        -- First check custom_templates, then fall back to templates table
        IF NOT EXISTS (SELECT 1 FROM custom_templates WHERE id = NEW.template_id) THEN
            IF NOT EXISTS (SELECT 1 FROM templates WHERE id = NEW.template_id AND is_active = true) THEN
                RAISE EXCEPTION 'Template ID % does not exist in custom_templates or templates table', NEW.template_id;
            END IF;
        END IF;
    ELSE
        RAISE EXCEPTION 'Invalid template_type: %. Must be either "default" or "custom"', NEW.template_type;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to validate template references
DROP TRIGGER IF EXISTS validate_template_reference_trigger ON custom_features;
CREATE TRIGGER validate_template_reference_trigger
    BEFORE INSERT OR UPDATE ON custom_features
    FOR EACH ROW
    EXECUTE FUNCTION validate_template_reference();

-- Create index for template_type
CREATE INDEX IF NOT EXISTS idx_custom_features_template_type ON custom_features(template_type);

-- Update the custom feature creation logic to automatically set template_type
-- This will be handled in the application code, but we ensure the constraint is enforced

SELECT 'Custom features foreign key constraint fixed successfully!' as message;
