-- Fix get_user_usage_stats to read from user_usage table instead of ai_usage table
-- This ensures the dashboard shows the same usage data that limits are enforced against

CREATE OR REPLACE FUNCTION get_user_usage_stats(p_user_id UUID)
RETURNS JSON
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    result JSON;
    current_usage_record user_usage;
BEGIN
    -- Get current usage record (which the increment_usage function updates)
    SELECT * INTO current_usage_record 
    FROM get_or_create_current_usage(p_user_id);
    
    -- Build result using the same data that limits are enforced against
    SELECT json_build_object(
        'current_month', json_build_object(
            'advanced', json_build_object(
                'count', current_usage_record.advanced_prompts_used,
                'tokens', 0, -- Token tracking is in ai_usage table, not user_usage
                'cost', 0
            ),
            'auto', json_build_object(
                'count', current_usage_record.auto_prompts_used,
                'tokens', 0, -- Token tracking is in ai_usage table, not user_usage  
                'cost', 0
            )
        ),
        'total_books', (
            SELECT COUNT(*) FROM books WHERE user_id = p_user_id
        ),
        'total_words', (
            SELECT COALESCE(SUM(word_count), 0) FROM books WHERE user_id = p_user_id
        )
    ) INTO result;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Also update can_user_make_request to use the same data source for consistency
CREATE OR REPLACE FUNCTION can_user_make_request(
    p_user_id UUID, 
    p_tier TEXT, 
    p_limit INTEGER
) RETURNS JSON
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    current_usage INTEGER := 0;
    current_usage_record user_usage;
    result JSON;
BEGIN
    -- Get current usage record (same source as increment_usage uses)
    SELECT * INTO current_usage_record 
    FROM get_or_create_current_usage(p_user_id);
    
    -- Get current usage for the specific tier
    CASE p_tier
        WHEN 'advanced' THEN
            current_usage := current_usage_record.advanced_prompts_used;
        WHEN 'auto' THEN
            current_usage := current_usage_record.auto_prompts_used;
        ELSE
            current_usage := 0;
    END CASE;
    
    -- Return usage info and whether request can proceed
    SELECT json_build_object(
        'can_proceed', (current_usage < p_limit OR p_limit = 0),
        'current_usage', current_usage,
        'limit', p_limit,
        'remaining', GREATEST(0, p_limit - current_usage)
    ) INTO result;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Add helpful comments
COMMENT ON FUNCTION get_user_usage_stats(UUID) IS 'Fixed to read from user_usage table for consistency with limit enforcement';
COMMENT ON FUNCTION can_user_make_request(UUID, TEXT, INTEGER) IS 'Fixed to read from user_usage table for consistency with increment_usage';