-- Fix table name reference in update_chat_metadata function
-- The function was referencing 'chat_messages' but the table is actually 'messages'

CREATE OR REPLACE FUNCTION update_chat_metadata()
RETURNS TRIGGER 
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
BEGIN
    -- Update metadata when a message is added/updated/deleted
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        UPDATE chats SET
            last_message_at = NOW(),
            message_count = (
                SELECT COUNT(*) 
                FROM messages 
                WHERE chat_id = NEW.chat_id
            ),
            updated_at = NOW()
        WHERE id = NEW.chat_id;
        
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE chats SET
            message_count = (
                SELECT COUNT(*) 
                FROM messages 
                WHERE chat_id = OLD.chat_id
            ),
            updated_at = NOW()
        WHERE id = OLD.chat_id;
        
        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Fix the functions from migration 013 that are missing proper search path
-- These are the functions causing the timestamp comparison errors

-- Function to get current usage period for a user
CREATE OR REPLACE FUNCTION get_current_usage_period(user_uuid UUID)
RETURNS TABLE (
    period_start TIMESTAMPTZ,
    period_end TIMESTAMPTZ
)
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    subscription_record RECORD;
    current_time TIMESTAMPTZ := NOW();
BEGIN
    -- Get the user's active subscription
    SELECT * INTO subscription_record
    FROM subscriptions s
    WHERE s.user_id = user_uuid 
    AND s.status IN ('active', 'trialing', 'past_due')
    AND s.current_period_end > current_time
    ORDER BY s.created_at DESC
    LIMIT 1;
    
    IF subscription_record IS NOT NULL THEN
        -- Return subscription period
        RETURN QUERY 
        SELECT 
            subscription_record.current_period_start,
            subscription_record.current_period_end;
    ELSE
        -- Return current month for free tier
        RETURN QUERY
        SELECT 
            DATE_TRUNC('month', current_time),
            (DATE_TRUNC('month', current_time) + INTERVAL '1 month');
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Function to get or create current usage record
CREATE OR REPLACE FUNCTION get_or_create_current_usage(user_uuid UUID)
RETURNS user_usage
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    usage_record user_usage;
    period_info RECORD;
BEGIN
    -- Get current period
    SELECT * INTO period_info FROM get_current_usage_period(user_uuid);
    
    -- Try to get existing usage record
    SELECT * INTO usage_record 
    FROM user_usage 
    WHERE user_id = user_uuid 
    AND period_start = period_info.period_start 
    AND period_end = period_info.period_end;
    
    -- Create if doesn't exist
    IF usage_record IS NULL THEN
        INSERT INTO user_usage (
            user_id, 
            period_start, 
            period_end
        ) VALUES (
            user_uuid,
            period_info.period_start,
            period_info.period_end
        )
        RETURNING * INTO usage_record;
    END IF;
    
    RETURN usage_record;
END;
$$ LANGUAGE plpgsql;

-- Function to increment usage with limit checking
CREATE OR REPLACE FUNCTION increment_usage(
    user_uuid UUID,
    feature_type_param TEXT,
    amount_param DECIMAL DEFAULT 1,
    metadata_param JSONB DEFAULT '{}'::jsonb,
    skip_limit_check BOOLEAN DEFAULT FALSE
)
RETURNS user_usage
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    usage_record user_usage;
    current_value DECIMAL;
    new_value DECIMAL;
BEGIN
    -- Get or create current usage record
    SELECT * INTO usage_record FROM get_or_create_current_usage(user_uuid);
    
    -- Get current value for the feature type
    CASE feature_type_param
        WHEN 'advanced_prompts' THEN
            current_value := usage_record.advanced_prompts_used;
        WHEN 'auto_prompts' THEN
            current_value := usage_record.auto_prompts_used;
        WHEN 'storage_gb' THEN
            current_value := usage_record.storage_used_gb;
        WHEN 'custom_templates' THEN
            current_value := usage_record.custom_templates_created;
        WHEN 'books' THEN
            current_value := usage_record.books_created;
        ELSE
            RAISE EXCEPTION 'Invalid feature_type: %', feature_type_param;
    END CASE;
    
    new_value := current_value + amount_param;
    
    -- Prevent negative values (except for storage which can decrease)
    IF new_value < 0 AND feature_type_param != 'storage_gb' THEN
        RAISE EXCEPTION 'Cannot decrement % below zero', feature_type_param;
    END IF;
    
    -- Log the usage increment
    INSERT INTO usage_logs (user_id, feature_type, action, amount, metadata)
    VALUES (user_uuid, feature_type_param, 'increment', amount_param, metadata_param);
    
    -- Update the appropriate counter
    CASE feature_type_param
        WHEN 'advanced_prompts' THEN
            UPDATE user_usage 
            SET advanced_prompts_used = advanced_prompts_used + amount_param::INTEGER,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'auto_prompts' THEN
            UPDATE user_usage 
            SET auto_prompts_used = auto_prompts_used + amount_param::INTEGER,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'storage_gb' THEN
            UPDATE user_usage 
            SET storage_used_gb = storage_used_gb + amount_param,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'custom_templates' THEN
            UPDATE user_usage 
            SET custom_templates_created = custom_templates_created + amount_param::INTEGER,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'books' THEN
            UPDATE user_usage 
            SET books_created = books_created + amount_param::INTEGER,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        ELSE
            RAISE EXCEPTION 'Invalid feature_type: %', feature_type_param;
    END CASE;
    
    RETURN usage_record;
END;
$$ LANGUAGE plpgsql;